PostgreSQL pg_restore error from pgadmin4 showing role doesn't exist

2
votes

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 error as shown below.

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

I tried changing the restore options like

  1. Do not save owner as yes
  2. Privileges yes etc but still there was no luck.

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;

Thanks

1
databasepostgresqldatabase-backups

1 Answers

4
votes

In PostgreSQL, users and tablespaces are not part of the database, so they are not included in pg_dump output.

You'll have to run pg_dumpall -a to get a dump of these objects. Apply that dump first, then it should work.

Alternatively, use the options -O -x of pg_restore to skip restoring ownership and permissions. Then the objects will belong to the user who ran pg_restore.