Re: Postgres 9.01 and WAL files issue

Deepak,

Does your backup script exclude the pg_xlog directory? There is no point
backing up the WAL files but you will want to make sure the archived WAL
files are added to the backup set afterwards.

How many files are in pg_xlog at the point where you stop the backup? It
may actually be that it takes a while to copy them all to the NFS if your
database has been active overnight. I would hope that it is a low traffic
system if you are leaving it in backup mode all night though.

Ideally you would build the stop and start SQL into a backup script which
handles the rsync and then also configure the script to add the archived WAL
files to the backup set afterwards.

Another thing I would try is to run your exact archive command manually
(using one of the completed WAL files as an example) to the NFS. See what
feedback you get as to why it isn't returning either a positive or negative
return.

On Wed, Nov 24, 2010 at 5:52 PM, DM <dm(dot)aeqa(at)gmail(dot)com> wrote:

> Hi All,
>
> pg_stop_backup is not stopping...
>
> Postgresql version 9.01
> OS: Centos
> Postgresql.conf ==> default configuration did not change anything
>
> Steps
>
> 1. Initiated psql -c "SELECT pg_start_backup('label', true)"
> 2. Started RSYNC job to sync slave box
> rsync -a /var/lib/pgsql/data/ dbpg9-test-02:/var/lib/pgsql/data
>
> Left over night to rsync - it was only around 10 GB of data to be rsynced,
> but i left overnight...
>
> 3. psql -c "SELECT pg_stop_backup()"
>
>
> *Gettign Error Message as below*
>
> NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments to
> be archived
> WARNING: pg_stop_backup still waiting for all required WAL segments to be
> archived (60 seconds elapsed)
> HINT: Check that your archive_command is executing properly.
> pg_stop_backup can be cancelled safely, but the database backup will not be
> usable without all the WAL segments.
> WARNING: pg_stop_backup still waiting for all required WAL segments to be
> archived (120 seconds elapsed)
> HINT: Check that your archive_command is executing properly.
> pg_stop_backup can be cancelled safely, but the database backup will not be
> usable without all the WAL segments.
>
> WARNING: pg_stop_backup still waiting for all required WAL segments to be
> archived (240 seconds elapsed)
> HINT: Check that your archive_command is executing properly.
> pg_stop_backup can be cancelled safely, but the database backup will not be
> usable without all the WAL segments.
>
> I waited quite long and it was not stopping, I had to issue Ctrl+c to
> cancel it.
>
>
> ========
>
> I tried to start backup and stop backup again, same problem.
>
> Here is my Archive Command:
> archive_command = 'cp -i %p /mnt/nfs/primary/%f < /dev/null'
>
> Directory /mnt/nfs/primary ==> is an NFS mount.
>
>
> Any solution how to fix it.
>
>
> Thanks
> Deepak Murthy
>
>
>
>
>