How to migrate data between Postgres Servers which both need password?

Question

I try to use the command to migrate data between two PG servers, but both need password:

pg_dump -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

Then I get the error:

pg_dump: [archiver (db)] connection to database "database" failed: FATAL:  password authentication failed for user "postgres"

Is there any option to solve the issue? I'm not sure how to specific the password in the command.

You can put the passwords into `.pgpass` – a_horse_with_no_name Aug 17 '21 at 07:25 — a_horse_with_no_name, Aug 17 '21 at 07:25

score 0 · Accepted Answer · answered Aug 17 '21 at 07:30

Use a password file to store the passwords for both databases, then the PostgreSQL client will be able to connect to both without prompting for a password.

The password file would look like this:

localhost:5432:dbname:localuser:password1
remotehost:5432:dbname:remoteuser:password2

How to migrate data between Postgres Servers which both need password?

1 Answers1