[SOLVED] How to use pg_dump correctly to replicate my local database to a remote server

How to use pg_dump correctly to replicate my local database to a remote server

I'm struggling trying to move my local postgres db to my Digital Ocean cluster. I want to make my new remote server my main dB, hence pass all roles and privilege's. I read the pg_dump doc and still couldn't figure out how to resolve my issue. These are my commands below:

create the dump file:

pg_dump -U postgres --no-owner sb > dump_file

connect to remote server from DO and move dB:

pg_restore -d {MY_URI_CONNECTION} dump_file

The data, schema, and tables get created, but not the roles, which throws an error in my terminal:

ERROR:  role "admin" does not exist
ERROR:  role "admin" does not exist
ERROR:  role "web_anon" does not exist
WARNING:  no privileges could be revoked for "public"
REVOKE
WARNING:  no privileges were granted for "public"
GRANT
ERROR:  role "web_anon" does not exist
ERROR:  role "web_anon" does not exist
ERROR:  role "web_anon" does not exist
ERROR:  role "web_anon" does not exist
ERROR:  role "web_anon" does not exist
ERROR:  must be member of role "postgres"
ERROR:  role "USER" does not exist

I've tried multiple ways to migrate my dB, but I keep running into the same error. How can I migrate all of my dB including roles and privilege's, overcoming my error above?

Solution

Use pg_dumpall -g > globals_file.sql. See here pg_dumpall. This will give you a plain text file of all globals(roles included). Restore to new cluster first with psql -d db -U superuser -f globals_file.sql Be aware this will dump all roles from a given cluster.