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:

  1. Create oldRole in my newCluster (to load without errors),
  2. Load my dump into the newDatabase using this command:

    pg_restore mydump/ -d newDatabase
  3. Reassign objects owned by oldRole to newRole,
  4. Rename oldSchema to newSchema,
  5. 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.

  • A: No. Q: Why are you renaming one of the schemas in the database? is this related to the role name change? use of dot in identifiers makes your question confusing. insert into xxx.yyy.zzz is not valid SQL. – Jasen Apr 16 '18 at 4:21
  • A: @Jasen oldSchema is renamed to newSchema because environment change between both clusters. And yes, role name were also changed for this reason. I modified the dot usage to avoid confusion. – Mika Apr 16 '18 at 5:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.