pg_restore ignores .pgpass and PGPASSWORD environment variable

Question

I want to import a backup using pg_restore without a password prompt. I tried several options but after I run the script it will always ask for a password. pg_dump is working but not pg_restore. I can run the pg_restore command if I enter my password but I want a passwordless command or at least I don't want to enter my password because the script has to work without user interaction.

What is working for me:

 PGPASSWORD=xyz pg_dump -h localhost -U user -Fc database > ~/dump_prod.pgsql

What is NOT working

1.)

PGPASSWORD=xyz pg_restore -h localhost -d database -U user -W --clean --no-owner ~/dump_prod.pgsql

2.)

pg_restore --dbname=postgresql://user:pass@localhost:5432/db -W --clean --no-owner ~/dump_prod.pgsql

3.)

touch ~/.pgpass
echo "*:*:*:*:password > ~/.pgpass
chmod 0600 ~/.pgpass
pg_restore -h localhost -d db -U user -W --clean --no-owner ~/dump_prod.pgsql

any ideas?

Regards

Answer 1

The .pgpass file worked for me.

My setup: I am restoring DB into the postgres instance running as Docker container.

The postgres instance is run using command:

docker run --name postgres_db -p 5432:5432 -e POSTGRES_PASSWORD=admin -d postgres:9.6

pg_restore is present but it is available as separate tool, outside docker setup.

The ~/.pgpass file entry looks like this:

localhost:5432:db_name:user:password

Where:

db_name is the target db name that one is going to restore.

user is the name of the user that is going to perform the restore - in my case an admin user. i.e. postgres user in the postgres instance.

password - admin user's password. i.e. admin