PostgreSQL getting error during pg_restore using pgadmin pg_restore: [archiver (db)] could not execute query: ERROR: role does not exist

Question

I am trying to restore a database from one schema to another schema in PostgreSQL using pgadmin backup and restore utility. The backup is successful for the database A but when I try to do the restore of this to another database B I get the following error:

Sample:

pg_restore: creating TABLE "public.app_role"
pg_restore: [archiver (db)] Error from TOC entry 227; 1259 197811 TABLE app_role schedule
pg_restore: [archiver (db)] could not execute query: ERROR:  role "schedule" does not exist
    Command was: ALTER TABLE public.app_role OWNER TO schedule;

I tried changing the restore options like do not save owner as yes, privileges yes etc but still there was no luck.

Does anyone know about this or have faced an issue like this before?

Answer 1

You need the roles referenced in the backup. To obtain those, do the following:

pg_dumpall --globals-only > globals.dump

Then you can pg_restore -f globals.dump <database> This will dump all of the roles on the source database cluster.

If you are unable to do that for whatever reason, you can also edit your backup file for references to that schedule role for another that you have setup on your target system