Search code examples
postgresqlpostgres-fdw

Query tables from multiple servers with postgreSQL

I have several databases on the different PostgreSQL servers with the tables with the same columns in it (installs_1, installs_2 and installs_3) installs(country varchar, date datetime,paid boolean, installs int) I want to write a function that a user could use to query across all these databases at once, how can I do it? my query is:select country,count(*) from t1,t2

Solution

  • A PostgreSQL extension that offer this feature is the postgres_fdw. Here is an example of how to set it up:

    First you create the extension:

    CREATE EXTENSION postgres_fdw

    After that you create a server pointing to the foreign postgres server

    CREATE SERVER remote_postgres 
 FOREIGN DATA WRAPPER postgres_fdw
 OPTIONS (dbname 'mydb', host 'remoteserver', port '5432');

    Then an user mapping, so that an user in your current database may access the foreign database:

    CREATE USER MAPPING FOR local_user
SERVER remote_postgres
OPTIONS (user 'foreign_user', password 'secret');

    And finally you create a foreign table to link both tables

    CREATE FOREIGN TABLE foreign_table_test
(id INT, description TEXT)
SERVER remote_postgres
OPTIONS (schema_name 'public', table_name 'table_test');

    Once your table is created you can query it like you'd query a normal/local table:

    SELECT * FROM foreign_table_test

    Further reading: