Refreshing development environment from backup that contains FDW in Postgres

Question

Background:

We recently added a foreign data wrapper from one of our Postgres (v14) databases to another Postgres (v14) database in a different system. It works fine in our production environment, but now when I try to refresh data in our development environment from a production backup, it fails because our firewall rules don't allow our dev environments to "talk" to prod environments.

I am using pg_dump for a nightly backup and pg_restore to refresh the the development environment.

Question:

Periodically refreshing a development environment from a production backup seems like it would be a pretty common task. How can I configure or script this so it doesn't fail? I need to change both the hostname and password so that my refreshed development database points to and connects to another development database. My only real idea is to use sed on the backup file before doing the restore to change the host (from prod to dev) and to change the password. But, this seems very fragile to me. My only other idea is to use a superuser and not require a password, but I know the security guys are not going to let me do this.

Answer 1

After restoring the database that contains the foreign tables, connect to the database and change the definition of the foreign server:

ALTER SERVER myserver OPTIONS (SET host 'some.test.server');

Then all foreign tables will be changed to use that new server. If you have different credentials on that server, you will need to ALTER the user mapping as well.