How to speedup pg_dump big database

Question

I've got big database (about 900GB), and when I migrate it to new server dump speed is not more than 2MB/s (very slow). Link between servers is 10Gb/s.

The database contain 20 tables with different sizes (the bigest one has about 600GB). Because servers have different version of postgresql (source 9.3 and destination 9.4) I can't copy all cluster to new server. I try using:

pg_dump -h source | pg_restore -h 127.0.0.1

but no result. I also try dump to file and then restore with '-j' parameter but also no result (after 20 min only one thread work because others finish their job). Problem isn't disk speed because both methods give me similar speed.

I haven't any experience with migrate such big data so maybe this methods are bad. Have You any advices why this migration is to slow, and how speed it up?

Answer 1

You don't actually have to dump to upgrade a PostgreSQL instance. For example, you can bring up a new cluster with access to an older cluster using the pg_upgrade command. If you're using XFS you can even use --clone. I would read the rest of the docs for pg_upgrade.

If you're just looking to move the instance you can try pg_basebackup - though I'm not sure that works across major versions.

Answer 2

Try calling the COPY command instead to read the files into CV. It should be a simple matter to write a script that calls COPY on all data.

If that still does not give you the speed you want, run multiple COPY commands per table (for example, one per year of some date column)