I have a production DB PostgreSQL 9.6 that is on master/standby structure. Every at 1 AM I restore a few huge tables(from the other DB server) to the master node during restore till finished the archive log generated around 100GB. Is there any good way to minimize the archive_log from the pg_restore task?

  • What is "the other DB server"? It can't be the physical replica, as how could that diverge in a way that needs to have this pushed back to master? Does this other server turn the huge tables over entirely every day? Can't you just restore the differences rather than the whole tables? – jjanes Jan 7 at 14:27
  • the other DB server is another physical DB server, we can't do physical replica since we need only just a few tables and we can't also do logical replica because the source is running on Postgresql 9.3. And just restore the differences rather than the whole tables, I don't know how to do in postgresql. – BongSey Jan 8 at 1:18

The best you can do is to set wal_compression = on to compress whole blocks that are written to WAL, but that will cost CPU.

The information has to be transferred from the master to the slave, so you cannot avoid generating WAL.

However, that WAL need not be stored on disk permanently, it should be streamed from master to slave directly. Perhaps the network is not strong enough to stream all that information in a timely fashion, and it needs to be kept around on disk for a while.

  • yes it cost the cpu in order to do so – BongSey Jan 7 at 11:44

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.