Re: Too many WAL(s) despite low transaction

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(at)snowman(dot)net> wrote:

> * Selva manickaraja (mavles78(at)gmail(dot)com) 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-----
>
>