thank you for this great article of yours! I already learned a lot from it but have a question.

My database I am trying to connect to has a SSL certificate. What do I have to chnage in the code to connect to the Server? Is it Step 8 I have to alter and if yes, what do I need to do?

Thank you for any help!

Hav a great week,
Florian

I am getting below error while importing foreign schema.

SQLSTATE[08001]: could not translate host name to address: Name or service not known.

I already tested below things :
– I can able to connect to both local and foreign db with psql command
– Can able to connect to my DB instance using ‘nc -v hosturl’

What else could be possible reasons ?

Please correct me If I’m wrong or tell me if the above statements are true.
Thanks.

I am facing issue while creating DB link between two databases dev_abc_db (source db) and dev_xyz_db (destination db) present on the same machine. And the owner of both database is same user (dev_user).
Here is what I am doing.

1. CREATE SERVER IF NOT EXISTS resdb FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host ‘dev.abc.com’, dbname ‘dev_abc_db’);
2. CREATE USER MAPPING FOR dev_user SERVER resdb OPTIONS (user ‘dev_user’, password ‘abc’);
3. GRANT USAGE ON FOREIGN SERVER resdb TO dev_user;

I have created a view in my destination database which uses resdb (created in point 1) to fetch the data from source database. While executing view I am getting below error.

SQL Error [2F003]: ERROR: password is required
Detail: Non-superuser cannot connect if the server does not request a password.
Hint: Target server’s authentication method must be changed.

Is it must to have super user role to use DB link ?

But I am stuck at a point when the app database changes in source then how is it going to be updated in the destination schema. For example: if we add a table, or drop a column—our foreign tables will need updating too. And in case it is happening frequently then how can I trigger the change in my destination as well. Kindly explain how can I achieve this.

https://postgrespro.com/list/id/CAE7uO5hVKdUGJHsBbV8KKXfEWJQ4SCynRMbgG3vgwjQh+HTz7A@mail.gmail.com

one more query though.

coz now we are in a distributed setup, there may be cases of servers having issues and we run a pg_terminate_backend(pid) on the remote server or on the mgmt server (from the above setup)

now pg_terminate_backend is (like kill -9?) so if we kill a query like FETCH from c1 on the shard or
pg_terminate_backend(pid) on the mgmt server (from where we make the remote query) it may forget about the query on the remote server, but remote server query is already triggered and will keep running until it returns the rows and then figures out the tcp connection is broken ?
i mean are we at the risk of creating sql orphans ? coz the parent is dead and child keep waiting for parent
if i am wrong about this 🙂 then does a pg_cancel_backend(pid) cascade to foriegn server and kill all the remote queries before killing itself?

i mean
A -> FDW -> B

kill on A (will it kill on B too) ?
kill on B( will it signal A that B is dead and you should abort)

Awesome writeup.
I use a setup to simulate sharded architecture as following
server a
server b
server c
server d

each shard has FDW mapping talking to each other server.
i.e
server a -> (via fdw) -> can query (server b, server c, server d)
and so on

this worked great for our usecase where we had data in one shard server a has no direct relation to server b and so on.
now for queries we has a view which does a query across all the shards
create view from_all_shards as
select * from remote_server_a.tablea
union all
select * from remote_server_b.tablea
union all
select * from remote_server_c.tablea
union all
select * from remote_server_d.tablea

so even though the queries seem to be making isolated query to shards, i am not able to have parallel queries (even via parallel setup in pg10)
i mean if all query take 10s, if takes 40 s to return.

in simple table setups parallel workers come into play, but in FDW i did not see the case?
Am i doing things correctly?