Thread: pg_stop_backup is not archiving latest transaction log from pg_xlog directory
pg_stop_backup is not archiving latest transaction log from pg_xlog directory
From
girish R G peetle
Date:
Hi
The question is regrading PostgreSQL 8.0.26 version on Linux platform. I know that 8.0 version is no longer supported. But one of our customer is using this version.
We are using File System based backup method to protect PostgreSQL server.
In PostgreSQL 8.2 and above versions, I can see that latest transaction log will be archived when pg_stop_backup is executed.
But in PostgreSQL 8.0 version this is not happening. A transaction log file gets archived only when it is completely filled.
Due to this behavior, the changes made to the server during pg_start_backup and pg_stop_backup won't be captured in the archived transaction log.
Is there a way to switch the current transaction log file in Postgres 8.0 ?
The question is regrading PostgreSQL 8.0.26 version on Linux platform. I know that 8.0 version is no longer supported. But one of our customer is using this version.
We are using File System based backup method to protect PostgreSQL server.
In PostgreSQL 8.2 and above versions, I can see that latest transaction log will be archived when pg_stop_backup is executed.
But in PostgreSQL 8.0 version this is not happening. A transaction log file gets archived only when it is completely filled.
Due to this behavior, the changes made to the server during pg_start_backup and pg_stop_backup won't be captured in the archived transaction log.
Is there a way to switch the current transaction log file in Postgres 8.0 ?
Thanks
Girish
Re: pg_stop_backup is not archiving latest transaction log from pg_xlog directory
From
Raghavendra
Date:
On Wed, Jun 19, 2013 at 2:02 PM, girish R G peetle <giri.anamika0@gmail.com> wrote:
Hi
The question is regrading PostgreSQL 8.0.26 version on Linux platform. I know that 8.0 version is no longer supported. But one of our customer is using this version.
We are using File System based backup method to protect PostgreSQL server.
In PostgreSQL 8.2 and above versions, I can see that latest transaction log will be archived when pg_stop_backup is executed.
But in PostgreSQL 8.0 version this is not happening. A transaction log file gets archived only when it is completely filled.
Due to this behavior, the changes made to the server during pg_start_backup and pg_stop_backup won't be captured in the archived transaction log.
Is there a way to switch the current transaction log file in Postgres 8.0 ?ThanksGirish
Can you try CHECKPOINT before pg_stop_backup() and see whether it meets your requirement ?
Re: pg_stop_backup is not archiving latest transaction log from pg_xlog directory
From
Simon Riggs
Date:
On 19 June 2013 09:32, girish R G peetle <giri.anamika0@gmail.com> wrote: > Is there a way to switch the current transaction log file in Postgres 8.0 ? No, that was an addition to 8.2 Suggest that you send more data until the WAL file switches. You should upgrade with some urgency. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: pg_stop_backup is not archiving latest transaction log from pg_xlog directory
From
girish R G peetle
Date:
Hi
Thanks everyone for your quick response.
@Raghavendra,
Calling CHECKPOINT did not help. It will just force a transaction log checkpoint, but it won't ensure/enforce archiving of current active transaction log file.
@Albe
Even I was thinking to backup the transaction log files under pg_xlog directory. But my concern is, will it lead some kind of error or inconsistency ?
@Simon
Thanks for the confirmation. Unfortunately our client has more than 150 servers configured, and they are not willing to upgrade their servers.
Can you give some suggestions on what Albe has suggested ( Archiving transaction log files from pg_xlog directory after pg_stop_backup) ?
Thanks
Girish
On Wed, Jun 19, 2013 at 3:01 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
On 19 June 2013 09:32, girish R G peetle <giri.anamika0@gmail.com> wrote:No, that was an addition to 8.2
> Is there a way to switch the current transaction log file in Postgres 8.0 ?
Suggest that you send more data until the WAL file switches.
You should upgrade with some urgency.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Re: pg_stop_backup is not archiving latest transaction log from pg_xlog directory
From
Albe Laurenz
Date:
girish R G peetle wrote: > @Albe > Even I was thinking to backup the transaction log files under pg_xlog directory. But my concern is, > will it lead some kind of error or inconsistency ? No. During recovery, archived WAL files will overwrite the ones in pg_xlog. The WAL files in pg_xlog will only be used if no archived version is available. As long as pg_xlog is backed up after the end of pg_stop_backup(), you should be safe. Yours, Laurenz Albe
Re: pg_stop_backup is not archiving latest transaction log from pg_xlog directory
From
girish R G peetle
Date:
On Wed, Jun 19, 2013 at 4:32 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
girish R G peetle wrote:> @AlbeNo.
> Even I was thinking to backup the transaction log files under pg_xlog directory. But my concern is,
> will it lead some kind of error or inconsistency ?
During recovery, archived WAL files will overwrite the ones in
pg_xlog. The WAL files in pg_xlog will only be used if
no archived version is available.
As long as pg_xlog is backed up after the end of
pg_stop_backup(), you should be safe.
Yours,
Laurenz Albe
Hi Laurenz Albe,
I was wondering if I can use the modified version of your solution.
1. Convert output of pg_stop_backup to transaction log file name. (This refers to the latest active transaction log file)
2. If we are able to see this file under archive(WAL) directory, backup this file from this directory.
3. If the file is not found under archive(WAL) directory, backup this file form pg_xlog directory.
Thanks
Girish
Re: pg_stop_backup is not archiving latest transaction log from pg_xlog directory
From
Albe Laurenz
Date:
girish R G peetle wrote: > I was wondering if I can use the modified version of your solution. > > 1. Convert output of pg_stop_backup to transaction log file name. (This refers to the latest active > transaction log file) > 2. If we are able to see this file under archive(WAL) directory, backup this file from this > directory. > 3. If the file is not found under archive(WAL) directory, backup this file form pg_xlog directory. That should work just fine. Yours, Laurenz Albe
Re: pg_stop_backup is not archiving latest transaction log from pg_xlog directory
From
girish R G peetle
Date:
Thanks a lot Laurenz Albe.
On Wed, Jun 19, 2013 at 6:16 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
girish R G peetle wrote:> I was wondering if I can use the modified version of your solution.That should work just fine.
>
> 1. Convert output of pg_stop_backup to transaction log file name. (This refers to the latest active
> transaction log file)
> 2. If we are able to see this file under archive(WAL) directory, backup this file from this
> directory.
> 3. If the file is not found under archive(WAL) directory, backup this file form pg_xlog directory.
Yours,
Laurenz Albe