0

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.

2
  • What about pg_dump / pg_restore? Commented Feb 2, 2023 at 17:26
  • Yes. I'm using pg_dump and pg_restore. I will update the question to make it more clear.
    – David S
    Commented Feb 2, 2023 at 17:28

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.