I exported a database called oldDatabase using
pg_dump in directory format. This database contains a schema, tables and functions owned by oldRole. These functions contain SQL statements like
INSERT INTO oldSchema.table(...)
To load mydump/ into newDatabase, I followed these steps:
- Create oldRole in my newCluster (to load without errors),
Load my dump into the newDatabase using this command:
pg_restore mydump/ -d newDatabase
- Reassign objects owned by oldRole to newRole,
- Rename oldSchema to newSchema,
- And drop oldRole.
But functions still contain statements like
INSERT INTO oldSchema.table(...) and I get errors when I run these functions, because
oldSchema does not exist. I have to modify manually all functions to get
INSERT INTO newSchema.table(...).
Is there a best practice to load properly from a database to another database with different schema and owner names? How to perform a correct mapping?
I use PostgreSQL v9.5 with Red Hat 7.