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?
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