Search code examples
bashpostgresqlpg-restore

pg_restore ignores .pgpass and PGPASSWORD environment variable

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

Solution

  • As per the doc, -W will prompt for a password. -w will not

    -w
    --no-password

    Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

    -W
    --password

    Force pg_restore to prompt for a password before connecting to a database.