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
-
2Have you considered using a foreign data wrapper?Jim Jones– Jim Jones2020-02-05 09:26:48 +00:00Commented Feb 5, 2020 at 9:26
-
2See here: dba.stackexchange.com/questions/189654user330315– user3303152020-02-05 09:35:46 +00:00Commented Feb 5, 2020 at 9:35
Add a comment
|
1 Answer
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:
- A
closer lookinto postgres_fdw - postgres_fdw
documentation examples
2 Comments
Fernando C.
This answers how to connect to remote databases but I don't fully understand how would you query multiple databases at once.
Fernando C.
This seems to answer my comment: dev.to/aurelmegn/…