Re: Postgres 9.01 and WAL files issue

Hi Mathew,

Thanks for your reply.

Its a test machine, I am testing streaming replication with archive.

I figured out he problem, the /mnt/nfs didnt had enough space.

Thanks for looking into this issue.

Thanks
Deepak

On Wed, Nov 24, 2010 at 10:36 AM, Matthew Walden <matthew(dot)walden(at)bcs(dot)org>wrote:

> 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
>>
>>
>>
>>
>>