Problem in Postgresql DB backup setup

Hi,

We are facing a problem in setting up a database backup for our production
server using pg-standby.

We are using Postgres 8.3.5 in our production environment. We have an active
user-base of 2 million and need to support 5000 TPS. The service needs to be
up and running 24x7. The production database has a size of 200GB and growing
and is expected to reach 1TB in a couple of months.

In the past we have tried various strategies for db-backup including pg-dump
and Warm-backup using WAL shipping.
But our current needs demand a Hot-backup which can be set-up without
stopping the database server. For this we have evaluated pg-standby with WAL
shipping in a test-environment that has continous db inserts. The set-up
works fine and the slave (pg-standby) is able to consume WAL logs generated
by the master.

We tried to replicate the scenario of the master db going down in this test
environment in the following fashions:
a. Killing the master postgres server (using kill command).
b. Properly stopping the master postgres server (using pg_ctl command).
In both the above cases we saw that after the slave has consumed all the
generated WAL logs and the recovery completes, the slave lags the master by
a set of records. Additionally, the missing records don't seem to be a
consecutive set; a random set (non-consecutive) of records is missing in the
slave as compared with the master (by bringing the master back up).

Following are the steps that we have used for creating the pg-standby slave:
1. SLAVE: Ensure that postgres is not running on the slave. Stop it if it is
running.
Create and INIT a new data directory on the slave
(/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/test_data).
2. SLAVE: Ensure that the slave can pull wal-logs from the MASTER. Authorize
the SLAVE on the MASTER if needed.
3. MASTER: The archive command should be enabled on the master server.
[/usr/local/pgsql/data/postgresql.conf]
===================================
archive_mode = on # allows archiving to be done
# (change requires restart)
archive_command = '/usr/local/pgsql/bin/verseSQLs/storeWAL.sh %p %f
< /dev/null'
4. MASTER: Ensure that the consumeWAL.sh has WAL storage off. This would
automatically be turned on by the backup_rsync.sh script (see stemp 6).
5. SLAVE: The wal-consumption should be enabled on the slave server cron.
The consume_wals.sh script pulls a set of WALs in the shared storage on the
master, so that these WALs can be later consumed on the slave.
#-------------------------------------------------------------------
# [CONSUME WALs from MASTER : VERY CRUCIAL]
#-------------------------------------------------------------------
* * * * * cd /usr/local/pgsql/bin/verseSQLs/consume_wal_scripts &&
./consume_wals.sh
6. MASTER: Run backup_rsync.sh.
It does the following:
a. Enables WAL storage in the storeWAL.sh script.
b. Issues a pg_start_backup() to postgres on master.
c. CD to the data dir of master. rsyncs the contents of the data
directory of the master to the slave.
rsync -avz --delete --exclude=backup_label --exclude=.*
--exclude=*.pid --exclude=*.conf --exclude=pg_xlog --exclude=pg_tblspc
--exclude=pg_log/* --copy-unsafe-links . postgres@
<SLAVE_IP>:/usr/local/pgsql/test_data
d. Issues a pg_stop_backup() to postgres on master.
e. Cleans pg_xlog on slave.
7. SLAVE: Copy recovery.conf to the data dir. Start POSTGRES server. It
starts in STANDBY mode.

The above steps set up the slave server using pg-standby. Note that during
all this process the master db is up and there are continuous batch INSERTs
happening in the db by an automated process. Also note that this automated
process is the only process interacting with the db and there are no other
CRUD operations besides these batch INSERTs.

Further, in order to test the scenario for the master going down follow
these steps:
8. MASTER: If you want to recreate the scenario of the master db going down,
either kill the postgers process or stop the postgres server using pg_ctl.
9. MASTER: Disable the automated process that generates continuous batches
of INSERTs in the db
10. SLAVE: Once you have verified that there are no unconsumed WAL logs
left, trigger the pg-standby that the recovery has completed (touch the
trigger file expected by the pg-standby command specified in recovery.conf).
11. SLAVE: Once pg-standby sees the trigger file, the recovery completes and
the postgres server comes up in the production mode.
12. MASTER: Ensure that the storeWAL.sh has WAL storage off. Though by now
the slave is in a non-recovery mode, this step prevents the unnecessary
consumption of space bye WAL storage.
13. MASTER: Query the count of the records produced by the automated process
on the master.
14. SLAVE: Query the count of the records as present on the slave.
Compare the results of 13 and 14. We observe a difference here.

We would appreciate if you can help us figure out the cause for the
difference in the record set and how can it be avoided.

Thanks and regards,
Ujjawal