Re: Too many WAL(s) despite low transaction

[Selva manickaraja <mavles78@xxxxxxxxx>]

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

On Sun, Apr 3, 2011 at 6:52 PM, Stephen Frost <sfrost@xxxxxxxxxxx> wrote:

* Selva manickaraja (mavles78@xxxxxxxxx) wrote:

> Next actions to do:
>
>    1. Implement pg_compress, or pg_lesslog, pg_clearxlogtail
>    2. Check on how well autovacuum is running and how much to tune it.

Have you tested that you can do a restore using the base backup and
WALs?  Have you made sure the database is consistent/correct after doing
such a restore?  If you change to using pg_compress or anything else,
you should be sure to repeat that testing to make sure everything works
as expected.

       Thanks,

               Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)

iEYEARECAAYFAk2YUYMACgkQrzgMPqB3kijQfgCeJ9F4HPrpNdRePlR4SwJ2A89W
ikUAoIM9cM23J43vvX/wuW7Iq1UqQsac
=JnLy
-----END PGP SIGNATURE-----