5

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?

| improve this question | |
  • With a huge database, I think you should backup using file level backup, and upgrade with pg_upgrade – binhgreat Oct 18 '19 at 10:01
2
+25

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.

| improve this answer | |
  • You can't use pg_upgrade to move a database to a new server. – a_horse_with_no_name Oct 15 '19 at 13:19
  • @a_horse_with_no_name if you have access to the raw files and the server, I believe you can. No? I think it copies the data files by default with access to an older data dir. Another option would be pg_basebackup which is faster than pg_dump iirc, but I'm not sure if it works between different major versions. – Evan Carroll Oct 15 '19 at 14:09
0

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)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.