Обсуждение: Postgres 9.01 and WAL files issue

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

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.aeqa@gmail.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

On Wed, Nov 24, 2010 at 12:52 PM, DM <dm.aeqa@gmail.com> wrote:
> Here is my Archive Command:
> archive_command = 'cp -i %p /mnt/nfs/primary/%f < /dev/null'
>

Just curious... why would you turn on the interactive version of cp
for an automated script?  Is that why you feed it /dev/null as input?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general