Re: Hot Standby and Foreign Tables

Brilliant. Thanks Michael. That looks great.

On Thu, Jul 18, 2013 at 11:54 PM, Michael Paquier <michael(dot)paquier(at)gmail(dot)com
> wrote:

>
>
> On Thu, Jul 18, 2013 at 11:15 PM, Tim Kane <tim(dot)kane(at)gmail(dot)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
>