Hi

[Harrison Akum]

I am automating a CLOUDSQL postgres database refresh (Export/Import) from PROJECT_1(Source db) --to--  PROJECT_2 (Target db).

When I do this manually, it sometimes fails when importing the database into the Target db (ERROR user/role does not exist). To solve this problem, I create all those users and roles that exist in the Source database, that are missing in the Target. This only works when the task is done manually; however, It is not possible if I have to automate it to run on a daily basis.

Below is a command I need to use to backup all users/roles in Source and then restore them in the Target, prior to importing database in the Target. But I keep getting the error you see below.

QUESTION: Is there a problem with the " pg_dumpall " command OR CLOUDSQL postgres does not support the use of "pg_dumpall" ????

 

PGPASSFILE=/home/ubuntu/.pgpass pg_dumpall "host=HOSTNAME user=postgres dbname=DATBASE-NAME sslcert=/xxx/client-cert.pem sslkey=/xxx/client-key.pem sslrootcert=/xxx/server-ca.pem" --verbose --roles-only -f "/BACKUPS/USER_ACCTS.sql"

 ERROR

pg_dumpall: error: too many command-line arguments (first is "host=104.197.110.184 user=postgres dbname=

[Mary (Cloud Platform Support)]

Hello Harrison, 

After some verification, it seems like only pg_dump[1] is supported in Cloud SQL PostgreSQL. Since the pg_dump command also exports the permissions and ownerships you can use this to export and then import it before you create all the roles in the new database instance manually. 

Please be aware, since our Google groups posts are visible to our members, for your privacy and security we recommend to redact any PII information including those that may be a part of error messages. 

[1] https://www.postgresql.org/docs/12/app-pgdump.html

[2] https://cloud.google.com/sql/docs/postgres/import-export/exporting#cloud-sql