1

We have a postgresql 9.4 DB server that handles 2 DBs, each ~800GB. No replication is implemented yet.

We reduced pg_dump time with using -j & -Fd options to about 2 hours.

The problem is, we need to separate DBs, so we setup a fresh new server with Postgresql 9.6, but pg_restore takes a very long time (~2 days). I tried parallel pg_restore using -j (4, 8, 16, although VM has a 4 core CPU and 16GB of RAM) and tuned some parameters in posotgresql.conf like shared_buffers, effective_cache_size, work_mem, maintenance_work_mem, wal_buffers and so forth, but they didn't have any significant effect on reducing time of restore.

Even, I tried to restore data and schema separately, but same results. I think the problem is we have many tables with 15M records that all have index and indices will increase restore time but I'm not sure of that

We thought about replication, so we do not have any significant downtime (after sync is complete, we can stop old/master server and change replica/slave to acts as a master server), but as version of postgresql servers is not same, it didn't work out! Updating main server postgresql version to 9.6 and then start replication will take such a time!

Any advice and suggestions will be greatly appreciated!

3

Bit of a FAQ.

  • pg_upgrade with --link (but understand that you can't easily go back);

  • Logical streaming replication with pglogical

  • Trigger-based logical replication with Londiste etc

Search for zero or low downtime PostgreSQL major version upgrade.

  • Thanks Craig. I'll research about methods you have introduced and will tell you the results. – Ali Yousefi Sabzevar May 2 '17 at 10:34
0

Although pglogical seems a great tool, but I didn't take and breaking stability and reliability of Debian's Postgresql packages. In the other hand, I figured it out I can reduce time of restore with having a good balance between hardware resources and configurations in postgresql.conf file and -j option of pg_restore command.

For example restore time with 2*2 core CPU, 32 GB of RAM and -j 32 took more than 16 hours.

Finally, pg_restore with 2*2 core CPU, 16 GB of RAM and -j 16 worked out for me. It took 5 hours 17 minutes for me!

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.