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=