Lately as a process of joining different Postgres databases with 1 schema each into 1 database with different schemas, I had to export a schema from a database and to import it into another database with a different schema name.
Turned out postgres does not support setting schema name on import or export, so I had to export to plain text file, edit it and then import. But actually we can do it on the fly too by using a simple onliner. The key here are the options:
- we assign PGPASSWORD environment variable, so that psql does not ask for a password.
- we dump only 1 schema (
- we do not store ownership (
-O), privileges (
-x), table spaces and security labels, because those will be different in the new database.
- we pipe the plain text dump to
sedand find the line where postgres sets current schema and replace it with creating a new schema and setting it as current for the import.
- and in the end we import the dump again using the PGPASSWORD environment variable, also setting ON_ERROR_STOP to 1, so that the dump stops, if some error occures.