Thread: 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()"
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
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()"
NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments to be archived
Gettign Error Message as below
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.
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()"NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments to be archived
Gettign Error Message as below
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
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
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.walden@bcs.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.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()"NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments to be archived
Gettign Error Message as below
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?
I thought that when I first read about WAL archiving but the documentation explains quite well. Basically it is to stop a successful result being returned in the event that the file already exists in the archive destination (to cause an error in the event it tries to overwrite a file).
--
View my Linkedin profile
On Thu, Nov 25, 2010 at 2:43 PM, Vick Khera <vivek@khera.org> wrote:
On Wed, Nov 24, 2010 at 12:52 PM, DM <dm.aeqa@gmail.com> wrote:Just curious... why would you turn on the interactive version of cp
> Here is my Archive Command:
> archive_command = 'cp -i %p /mnt/nfs/primary/%f < /dev/null'
>
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
--
View my Linkedin profile