Brilliant. Thanks Michael. That looks great.
On Thu, Jul 18, 2013 at 11:54 PM, Michael Paquier <michael.paqu...@gmail.com > wrote: > > > On Thu, Jul 18, 2013 at 11:15 PM, Tim Kane <tim.k...@gmail.com> wrote: > > Hi all, > > > > I'm currently playing a few ideas out in my head and wanted to see if > this > > was feasible. There may be some limitation I am not aware of. > > > > I would like to create a foreign table into a hot standby instance using > > FDW's. I appreciate that the hot standby is always going to be > read-only, > > and that foreign tables are currently read-only anyway. > > > > Can anyone tell me if there is any reason this wouldn't work? > Yes it will work, the only thing necessary is to create the foreign table > on master. Then the foreign table created is propagated to the slaves. > > Here is the example of a foreign table referencing a table created on > master and linked directly to the slave, using postgres_fdw. The cluster > uses one master in sync with one slave, master listening to port 5432 and > slave to 5532 on the same server. > $ ps x | egrep "5432|5532" > 787 pts/0 S 0:00 /home/mpaquier/bin/pgsql/bin/postgres -D > /home/mpaquier/bin/pgsql/master -i -p 5432 > 809 pts/0 S 0:00 /home/mpaquier/bin/pgsql/bin/postgres -D > /home/mpaquier/bin/pgsql/slave1 -i -p 5532 > > $ psql postgres > postgres=# SELECT pid, application_name, > pg_xlog_location_diff(sent_location, flush_location) AS replay_delta, > sync_state FROM pg_stat_replication; > pid | application_name | replay_delta | sync_state > -----+------------------+--------------+------------ > 821 | slave1 | 0 | sync > (1 row) > postgres=# CREATE EXTENSION postgres_fdw; > CREATE EXTENSION > postgres=# CREATE SERVER postgres_server FOREIGN DATA WRAPPER postgres_fdw > OPTIONS (host 'localhost', port '5532', dbname 'postgres'); > CREATE SERVER > postgres=# CREATE USER MAPPING FOR PUBLIC SERVER postgres_server OPTIONS > (password ''); > CREATE USER MAPPING > postgres=# CREATE TABLE foo AS SELECT generate_series(1,3) AS a; > SELECT 3 > postgres=# CREATE FOREIGN TABLE aa_foreign (a int) SERVER postgres_server > OPTIONS (table_name 'foo'); > CREATE FOREIGN TABLE > postgres=# select * from aa_foreign; > a > --- > 1 > 2 > 3 > (3 rows) > In this case the process has read the data directly from table from with a > foreign table queried on master. > > As the foreign table has been created on master, you can as well read the > foreign table directly on slave (in this case the foreign table will simply > read data on the same node as you connect to). > $ psql -p 5532 postgres -c 'select * from aa_foreign' > a > --- > 1 > 2 > 3 > (3 rows) > > Of course you can as well link multiple clusters like that, etc. > > > > > I'm unable to test it just yet as I've not setup replication, nor am I > on a > > recent enough postgres. yet. ;-) > There are many scripts and manuals around to help you in that. > > > While I'm at it, is there any facility (future?) to provide a foreign > schema > > variant - such that I could access an entire schema using FDW's? > No plans AFAIK. > > Regards, > -- > Michael >
