Transferring (backup/restore) from development to an online server

Question

I have a PostgreSQL database that is on my computer owned by postgres user and another database on a server, owned by a different user let's say user_server

I'm using pgAdmin.

1. I create a backup on my computer using pgAdmin with the default settings (custom or tar).
2. I connect to database on the server
3. I execute a restore on the server database using the backup from my computer

After doing the operations, pgAdmin says that it fails, but the database looks ok. There are sometimes issues with sequences where the start number is lower than the max corresponding ID/PK.

I checked the logs and I have multiple errors, but all of them are similar to the following:

----
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3398; 0 0 COMMENT EXTENSION plpgsql 
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql
    Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
--------

pg_restore: [archiver (db)] Error from TOC entry 217; 1259 87892 TABLE association postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  must be member of role "postgres"
    Command was: ALTER TABLE public.association OWNER TO postgres;

----

pg_restore: [archiver (db)] Error from TOC entry 216; 1259 87890 SEQUENCE association_id_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  must be member of role "postgres"
    Command was: ALTER TABLE public.association_id_seq OWNER TO postgres;

Looks like I'm having an issue because they are owned by 2 different users.

Can this be done from pgAdmin, like a setting to ignore users or do I need to modify something on the server?

Answer 1

pgAdmin backup dialog screen has option "Do not save owner".

Reference:

• https://www.pgadmin.org/docs/pgadmin3/1.22/backup.html
• https://www.pgadmin.org/docs/pgadmin4/3.x/backup_dialog.html