snippetMinor
How to speedup pg_dump big database
Viewed 0 times
pg_dumpspeedupdatabasebighow
Problem
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:
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?
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.1but 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?
Solution
You don't actually have to
If you're just looking to move the instance you can try
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.Context
StackExchange Database Administrators Q#124567, answer score: 4
Revisions (0)
No revisions yet.