Sample Header Ad - 728x90

pg_restore with --no-acl --role=postgres still try set table owner to other role than postgres

0 votes
1 answer
360 views
I have put in a lot of effort trying to understand the process of pg_dump and pg_restore but there are a few key things that i still dont understand. My goal is to copy a schema from a production database with many roles to my local replication where i only need to use a postgres admin user. I dump myschema with this syntax pg_dump -h "pgprod" -p "5432" -d prod_db -U "myuser" -Fc -C --verbose --schema "myschema" -f .\test.backup Restore pg_restore -U postgres -h localhost -p 5432 -d localdb -C -v --no-acl --role=postgres test.backup But I cannot work out how to ignore any form of roles that was exisiting in the prod_db. I also tried the flag –no-owner with the same result. I am completely stuck at this step, since i dont understand why this is happening. Running postgres 16 pg_restore: error: could not execute query: ERROR: role "user1" does not exist Command was: ALTER TABLE public."mytable" OWNER TO user1;
Asked by geogrow (384 rep)
Jan 16, 2025, 01:01 PM
Last activity: Jan 16, 2025, 01:34 PM