Re: Issue with rsync based incremental backup : postgres: startup process waiting for 0000000100000001000000D2

Thanks Mike,

It worked!

I had even setup streaming replication after doing incremental replication,
without needing to stop postgres on the primary server.

Here is the script i came up with :

#!/bin/bash
> if [ $# -ne 1 ]; then
> echo "you must specify the hostname of the backup server";
> exit 0;
> fi;
> BACKUP_SERVER=$1
> PGDATA=/var/lib/pgsql/data
> PGXLOG=$PGDATA/pg_xlog
> PGEXEC="sudo su -l postgres -s /bin/bash -c"
> RSYNC="rsync"
> OPTIONS="--archive --checksum --compress --progress"
> EXCLUDES="--exclude postmaster.pid --exclude postgresql.conf --exclude
> pg_hba.conf --exclude server.crt --exclude server.key"
> ROLLOVER=32
> SSH="ssh -q -o StrictHostKeyChecking=no -o BatchMode=yes $BACKUP_SERVER"
> REPLICATION_CHECK="$SSH ps aux | grep postgres | grep wal | grep receiver"
> #On BACKUP_SERVER
> if [ -n "$(service postgresql status | grep "pid[:blank:]*[0-9]*")" ];
> then
> $SSH "service postgresql stop"
> fi;
> #On PRIMARY
> echo "Running VACUUM"
> $PGEXEC "psql -c \"VACUUM FULL;\""
> echo "VACUUM completed"
>
> for f in $(ls -tr $PGXLOG | head -n ${ROLLOVER}); do
> $RSYNC $OPTIONS $PGXLOG/$f $BACKUP_SERVER:$PGXLOG/
> done;
> $PGEXEC "psql -c \"SELECT pg_start_backup('incremental_backup',true);\""
> $RSYNC $OPTIONS $EXCLUDES --exclude pg_xlog $PGDATA $BACKUP_SERVER:$PGDATA
> $PGEXEC "psql -c \"SELECT pg_stop_backup();\""
> $RSYNC $OPTIONS $PGXLOG $BACKUP_SERVER:$PGXLOG
> $RSYNC $OPTIONS $PGXLOG $BACKUP_SERVER:$PGXLOG
> #On BACKUP_SERVER
> $SSH "service postgresql start"
> if [ -z "$(service postgresql status | grep "pid[:blank:]*[0-9]*")" ];
> then
> echo "Failed to start database on backup server"
> echo "Look into the postgres logs for more details"
> echo "exiting..."
> exit 1;
> fi;
> #need to improve this delay-check to wait until the backup server has
> finished recovery and started into streaming mode
> sleep 30
> if [ -n "$(${REPLICATION_CHECK})" ] ; then
> echo "SUCCESS in synching BACKUP_SERVER with the latest data from
> Primary";
> #On BACKUP_SERVER
> $SSH "service postgresql stop"
> echo "Stopped the backup server in good state; it will get updated in the
> next scheduled incremental backup"
> else
> echo "FAILED to sync backup server with Primary";
> echo "Leaving the backup server running in the failed state for
> further debugging"
> exit 1;
> fi;
> exit 0;

I hope this would help others in need...

Thanks and Regards,
Samba

----------------------------------------------------------------------------------------------------------------------------------------------------------------
On Thu, May 3, 2012 at 11:55 PM, Michael Nolan <htfoot(at)gmail(dot)com> wrote:

>
>
> On Thu, May 3, 2012 at 11:49 AM, Samba <saasira(at)gmail(dot)com> wrote:
>
>> Hi,
>>
>>
>> Please advise me if what i'm doing is makes sense and is an accepted
>> mechanism for taking backups or if there is any other procedure that i can
>> emplpoy to avoid unnecessarily archiving gigabytes of WAL logs which may be
>> growing many times the size of the actual data directory.
>>
>>
>> Thanks and Regards,
>> Samba
>>
>
> The problem is that rsync isn't copying all the xlog files created during
> the time the rsync is taking place, which is why it is complaining that
> there are files missing.
>
> There may be other logical flaws with your process as well.
>
> Something similar to the steps given in "Starting Replication with only a
> Quick Master Restart" as laid out in the wiki tutorial on binary
> replication might give you a way to make this work. (You probably won't
> need the restart of the master, since you're not actually setting up
> replication, so you won't be changing the postgresql.conf file on your
> master.)
>
> This uses a two-step process. First you copy all the files EXCEPT the
> ones on pg_xlog, then you copy those files, so you have a complete set.
>
> See http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial
> --
> Mike Nolan
>