2

I have a database dump that's about 140Gb. (When loaded, the database is about 350Gb.) I occasionally load copies of my dumped data into development and testing databases, which takes a while.

Any thoughts on what configurations I might be able to change during the restore in order to speed things along?

So far I have thought of:

in postgresql.conf:

synchronous_commit=off
checkpoint_timeout=1d

Although I'm less sure about that checkpoint timeout value.

in my shell

PGSSLMODE=disable

And at some point I will try copying the dump file to the target system so that pg_restore can run on the same box, and ideally use the unix domain socket, rather than going over a TCP/IP network.

(9.6 on Ubuntu 17.04)

Improve this question
3
  • Have you considered using physical backup instead of logical ?
    – Arkhena
    May 4, 2017 at 6:14
  • Unfortunately, my production DB is at a cloud provider (on a shared server), so I don't have access to the underlying data files. Thanks for the thought, tho!
    – Chris Curvey
    May 4, 2017 at 12:15
  • If you can convince the cloud provider to grant you the replication privilege, you can get at the underlying data files via pg_basebackup and pg_walreceiver. Of course of it is a shared instance, they probably aren't willing to do that.
    – jjanes
    May 6, 2017 at 4:54

1 Answer 1

Reset to default
1

Have you tried parallel pg_restore, i.e. the -j flag?

fsync=off can be better than just synchronous_commit=off. In fact, the latter might not do much at all because most of the loading should be done in large COPY commands which wouldn't be committing until the end anyway.

You can also try increasing max_wal_size.

Finally, use tools like top, vmstat, sar, perf, or strace to figure out what the bottleneck is, IO, CPU, or perhaps network or something else.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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