CODEWITHSUNDEEP
postgresqlpostgres-fdw
anna
anna

Reputation: 51

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

Upvotes: 3

Views: 2264

Answers (1)

Jim Jones
Jim Jones

Reputation: 19643

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:

Upvotes: 3

Related Questions