can't backup postgres 13 with pg_dump

Question

I'm trying to backup a postgresql 13 database with the following command on MacOS:

PGPASSWORD=“my_password” /Library/PostgreSQL/13/bin/pg_dump -h xx.xx.xx.xx -p 5432 -U postgres dbname > my_backup.backup

I get the following error:

pg_dump: error: connection to database "dbname" failed: could not initiate GSSAPI security context:  The operation or option is not available: Credential for asked mech-type mech not found in the credential handle
FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"

I'm looking up this error on google but I didn't found the solution yet. I'm providing the correct password, because I can connect to the host with this username/password-combination through pgadmin 4.

It's a Google Cloud Platform SQL Instance.

Anyone who knows the solution?

Answer 1

For supplying credentials to any PostgreSQL utility, I would strongly recommend using the Postgres Password File instead of trying to pass passwords around in variables.

Why? You have to get those values from somewhere, which inevitably leads to them being left lying around in a file somewhere. If that's the case, then why not use the [de facto standard] one that PostgreSQL itself already works with?
There is no value to be gained in reinventing the wheel here.

Answer 2

You are trying to connect to the cluster as the postgres user. Which you should not have access to and which should not be available over the network.

It appears that you are trying to create a logical backup of a single database in the cluster; which means you should connect as the owner of that database.

Can you connect to the database using psql? Is a useful check.