Re: Hot Standby and Foreign Tables

[Tim Kane <tim.kane@xxxxxxxxx>]

Brilliant. Thanks Michael. That looks great.

On Thu, Jul 18, 2013 at 11:54 PM, Michael Paquier <michael.paquier@xxxxxxxxx> wrote:

On Thu, Jul 18, 2013 at 11:15 PM, Tim Kane <tim.kane@xxxxxxxxx> 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