Postgres migration local to remote, issue with terminal command

Question

I'm trying to use the first answer here to transfer a local postgre db to elephantSQL

https://stackoverflow.com/questions/1237725/copying-postgresql-database-to-another-server

The command given is: pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

I have no chance to enter a password in the following:

owner@G700:~$ pg_dump -C -h localhost -U rep1 repdb1 | psql -h baasu.db.elephantsql.com -U bgpacbpe bgpacbpe
Password: 
Password for user bgpacbpe: pg_dump: [archiver (db)] connection to database "repdb1" failed: fe_sendauth: no password supplied

How can I resolve this?

If there is a less error prone way to do this I would also like to try that.

owner@G700:~$ psql --version
psql (PostgreSQL) 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1)

Answer 1

Having two programs in the same pipeline both trying to prompt for a password at the same time is confusing your terminal.

There may be a shell-specific way to fix this, but I'd fix it at the PostgreSQL level by either changing the local server to use an authentication method that doesn't need a password, or making a .pgpass file to accommodate the local connection (and maybe the remote connection, too).

For what it is worth, a shell way to get around this would be:

pg_dump -C -h localhost ... | (sleep 10; psql -h ...)

Where the sleep gives you enough time to enter the local password.