Search code examples
postgresqlpostgres-fdw

Generate script creation postgres_fdw

We use postgres_fdw and we have many servers created and many schema imported. Each time we need to refresh the data of an environment by another environment we need to manually update all the servers to link to the right server.

Is there a way we can generate the script of the foreign server creation and import schema for all the databases in a server?

Thank you

Solution

  • You don't need to recreate the server and reimport the schema. You can just change the hostname on the existing server.

    ALTER SERVER foo OPTIONS (SET host 'newhost');

    But why not just use dns to repoint the server name to the new machine?