0

I'm trying to use the answer here to allow me to transfer my PostgreSQL dbs from an old server (sourcepc) to a new server (receiverpc) on a completely different network. I am therefore on sourcepc trying to push the db dump to receiverpc using the following command:

pg_dump -C -h localhost -U dbuser dbname | psql -h receiverpc_ipaddress -U dbuser dbname

This isn't working - it just sits there after the dbuser password is given. I am running Ubuntu (9.10 on sourcepc, and 14.04 on receiverpc). PostgreSQL is 8.4 on sourcepc and 9.3 on receiverpc.

What I've done:

  1. Opened the firewall on receiverpc to allow access to the PostgreSQL port.

  2. Edited /etc/postgresql/9.3/main/postgresql.conf on receiverpc to let it receive external addresses:

    listen_addresses = '*'

  3. Edited /etc/postgresql/9.3/main/pg_hba.conf on receiverpc to add an entry for sourcepc:

    host all all sourcepc_ipaddress/32 md5

  4. Restarted PostgreSQL.

I can't really understand why it's not working.

Community
  • 1
  • 1
donnek
  • 221
  • 1
  • 9
  • Can you execute a simplest SQL command on `receiverpc` using such `psql` call? You'd rather check its working before transferring. – Netch Jun 07 '14 at 12:00
  • You may also want to consider splitting them into 2 separate commands, with the dump to an SQL file and the psql command using that file. Pipes, while convenient, can eat errors and result in hangs. – khampson Jun 07 '14 at 18:30

1 Answers1

0

@netch: Yes, it's working.

echo "\dt" | psql -U dbuser -d dbname

gives a list of the tables in a db.

@Ken Hampson: I think you're right. I spent another 2 hours on this, fiddling about with pg_pass, etc, and finally decided it was less bother to just do:

su - postgres

pg_dump --clean dbname > dbs/dbname.sql

scp -r dbs/ myuser@receiverpc:/home/myuser

and then run psql on each database on receiverpc.

Thanks anyway.

donnek
  • 221
  • 1
  • 9