Re: Hot Standby and Foreign Tables

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