Re: Too many WAL(s) despite low transaction - Mailing list pgsql-admin

We have let the production DB to run for about one week and monitor the situation. As days passed the DB began to settle down and the log shipping was more consistent. So as suggested by Stephen, we did a tape restore of the base backup and wal-backup from the previous backup done. We have scheduled a backup of base+wal(s) every 12 hours.

So this was the steps taken to test whether our backup is good or not.

1. Our primary MachineA is replicated to MachineB for pass one week. And WAL(s) are also shipped to Machine C from where all backups are done.
2. Backup (base+wal) restored from tape to MachineD.
3. Use a home-written scripts to unzip and untar the base and wal to the appropriate directories.
4. Database started in MachineD as a secondary to MachineA.
5. Database in MachineD started and attempted to replicate from MachineA. But unable to replicate because state of base is inconsistent with MachineA.
6. We pushed the un-backedup WAL(s) from MachineC to the archive directory in MachineD.
7. It was a great sight to witness as and when the WAL(S) arrive in MachineC it attempts to restore and replicate with MachineA.
8. Finally after the last WAL was restored the MachineD was in-synch as MachineA in full replication mode.

We suppose our backup(s) can be restored if this worked. Comments are welcomed.

Our next plan of action is

1. To do a PITR from these backups.
2. Implement pg_compress, or pg_lesslog, pg_clearxlogtail to WAL(S) to reduce the size of WALs.

There are other DB issues that I would like to highlight but that it's better to begin another thread for everyone's clarity.

Thank you.

Regards,

Selvam