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?
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.