CODEWITHSUNDEEP
postgresqlpg-dumppg-restore
Rick H
Rick H

Reputation: 47

Pg_restore runs ALTER TABLE .. OWNER TO ignoring pg_dump --no-owner option

I'm using an mac mini m1 and postgresl14 on it for a database migration. For the migration a change of the owner is necessary.

To export the dump needed I log in on my mac, call sudo su to become root and use the following cmd:

pg_dump -Fc --no-owner --encoding=UTF8 -h 127.0.0.1 -U postgres [[dbname]] -n [[schemaname]] > upload.bak

[[dbname]] and [[schemaname]] are placeholders here.

After that, I upload the upload.bak file to my server instance of ubuntu24.

On that ubuntu server instance I try to import the file via

pg_restore -h 127.0.0.1 -U [[username]] -d [[dbname]] -n [[schemaname]]

The import begins to run, but it says:

pg_restore: error: could not execute query: 
ERROR:  role "[[old_role]]" does not exist
Command was: ALTER TABLE [[schemaname]].[[tablename]] OWNER TO [[old_role]];

Seems as if the "-O" approx. "--no-owner" param above is completely ignored, even though I called pg_dump as root and postgres user.

How to instruct pg_dump to not export the "update role" statements?

Upvotes: 0

Views: 325

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19724

From:

https://www.postgresql.org/docs/current/app-pgdump.html

--no-owner [...] This option is ignored when emitting an archive (non-text) output file. For the archive formats, you can specify the option when you call pg_restore.

You specified pg_dump -Fc which is one of the archive formats so --no-owner will be ignored. To have this work you will need to add --no-owner to the pg_restore command as in:

pg_restore -h 127.0.0.1 --no-owner -U [[username]] -d [[dbname]] -n [[schemaname]]

UPDATE

As to text vs archive, from pg_dump:

Dumps can be output in script or archive file formats. Script dumps are plain-text files containing the SQL commands required to reconstruct the database to the state it was in at the time it was saved.

-F format --format=format

Selects the format of the output. format can be one of the following:

p plain

Output a plain-text SQL script file (the default).

So everything else is an archive format.

From same link under:

Examples

To dump a database called mydb into an SQL-script file:

$ pg_dump mydb > db.sql

To reload such a script into a (freshly created) database named newdb:

$ psql -d newdb -f db.sql

To dump a database into a custom-format archive file:

$ pg_dump -Fc mydb > db.dump

To reload an archive file into a (freshly created) database named newdb:

$ pg_restore -d newdb db.dump

Upvotes: 2

Related Questions