pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWAL restoration is not happening completely
6+ messages / 4 participants
[nested] [flat]
* WAL restoration is not happening completely
@ 2016-11-14 09:49 rajan <vgmonnet@gmail.com>
2016-11-14 13:24 ` Re: WAL restoration is not happening completely Ian Barwick <ian.barwick@2ndquadrant.com>
2016-11-14 13:58 ` Re: WAL restoration is not happening completely Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
0 siblings, 2 replies; 6+ messages in thread
From: rajan @ 2016-11-14 09:49 UTC (permalink / raw)
To: pgsql-admin
Hi,
In postgresql.conf file we have set the following configuration,
wal_level=hot_standby
archive_mode=on
archive_command='test ! -f /archives/%f && cp %p /archives/%f'
max_wal_senders=1
wal_keep_segments=128
and took a base backup using the command, pg_basebackup --xlog --format=t -D
/backups/`date +%Y%m%d`
This base backup and WAL files from the '/archives' dir are moved to another
machine in which the base.rar is extracted into $PGDATA folder and WAL
files(9 files) are copied to '/archives' folder.
recovery.conf is created under the $PGDATA folder with the command
"restore_command = 'cp /archives/%f %p'" and then the postgres db service is
started.
*The restored database has changes which were done today and missing data
from changes did a couple of days before.*
Note: Can anybody suggest a way to verify/test the incremental backup?
-----
--
Thanks,
Rajan.
--
View this message in context: http://postgresql.nabble.com/WAL-restoration-is-not-happening-completely-tp5930204.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: WAL restoration is not happening completely
2016-11-14 09:49 WAL restoration is not happening completely rajan <vgmonnet@gmail.com>
@ 2016-11-14 13:24 ` Ian Barwick <ian.barwick@2ndquadrant.com>
1 sibling, 0 replies; 6+ messages in thread
From: Ian Barwick @ 2016-11-14 13:24 UTC (permalink / raw)
To: rajan <vgmonnet@gmail.com>; pgsql-admin
Hi
On 11/14/2016 06:49 PM, rajan wrote:
> Hi,
>
> In postgresql.conf file we have set the following configuration,
> wal_level=hot_standby
> archive_mode=on
> archive_command='test ! -f /archives/%f && cp %p /archives/%f'
> max_wal_senders=1
> wal_keep_segments=128
>
> and took a base backup using the command, pg_basebackup --xlog --format=t -D
> /backups/`date +%Y%m%d`
>
> This base backup and WAL files from the '/archives' dir are moved to another
> machine in which the base.rar is extracted into $PGDATA folder and WAL
> files(9 files) are copied to '/archives' folder.
>
> recovery.conf is created under the $PGDATA folder with the command
> "restore_command = 'cp /archives/%f %p'" and then the postgres db service is
> started.
>
> *The restored database has changes which were done today and missing data
> from changes did a couple of days before.*
Are you absolutely sure about this? All changes present in the archived
WAL *will* be replayed sequentially; it's not possible that an arbitrary
subset of changes will be left out.
Which PostgreSQL version are you using, and what are the exact contents
of the recovery.conf file?
For working with backups and WAL archiving I suggest you take a look
at "Barman" ( http://www.pgbarman.org/ ).
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: WAL restoration is not happening completely
2016-11-14 09:49 WAL restoration is not happening completely rajan <vgmonnet@gmail.com>
@ 2016-11-14 13:58 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2016-11-14 17:55 ` Re: WAL restoration is not happening completely Andres Freund <andres@anarazel.de>
1 sibling, 1 reply; 6+ messages in thread
From: Peter Eisentraut @ 2016-11-14 13:58 UTC (permalink / raw)
To: rajan <vgmonnet@gmail.com>; pgsql-admin
On 11/14/16 4:49 AM, rajan wrote:
> *The restored database has changes which were done today and missing data
> from changes did a couple of days before.*
That is pretty much impossible.
It is plausible to mess some of this up and end up with a database
instance that is missing the trailing end of changes because recovery
aborted early somehow. But missing changes *in the middle* is not
possible, unless you are encountering a serious bug.
I suggest that there is some other confusion or misunderstanding going
on. Just to be sure, make sure you are using the latest minor version.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: WAL restoration is not happening completely
2016-11-14 09:49 WAL restoration is not happening completely rajan <vgmonnet@gmail.com>
2016-11-14 13:58 ` Re: WAL restoration is not happening completely Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
@ 2016-11-14 17:55 ` Andres Freund <andres@anarazel.de>
2016-11-18 11:17 ` Re: WAL restoration is not happening completely rajan <vgmonnet@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andres Freund @ 2016-11-14 17:55 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; +Cc: rajan <vgmonnet@gmail.com>; pgsql-admin
On 2016-11-14 08:58:14 -0500, Peter Eisentraut wrote:
> On 11/14/16 4:49 AM, rajan wrote:
> > *The restored database has changes which were done today and missing data
> > from changes did a couple of days before.*
>
> That is pretty much impossible.
>
> It is plausible to mess some of this up and end up with a database
> instance that is missing the trailing end of changes because recovery
> aborted early somehow. But missing changes *in the middle* is not
> possible, unless you are encountering a serious bug.
Could quite well be a restore with a deleted backup_label - that tends
to cause symptoms like that.
Rajan, did you by chance delete backup_label?
Regards,
Andres
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: WAL restoration is not happening completely
2016-11-14 09:49 WAL restoration is not happening completely rajan <vgmonnet@gmail.com>
2016-11-14 13:58 ` Re: WAL restoration is not happening completely Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2016-11-14 17:55 ` Re: WAL restoration is not happening completely Andres Freund <andres@anarazel.de>
@ 2016-11-18 11:17 ` rajan <vgmonnet@gmail.com>
2016-11-18 17:19 ` Re: WAL restoration is not happening completely Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
0 siblings, 1 reply; 6+ messages in thread
From: rajan @ 2016-11-18 11:17 UTC (permalink / raw)
To: pgsql-admin
thanks Ian, Peter and Andres for the replying.
One thing I missed to update is that the Source(master) machine I was
running is switched off and on everyday. It may have caused any disruption
in the WAL files?
And one more question, The WAL files are created as 16MB segments, that is
obvious. But I do not know when the file is completely written. Is it like,
a file is created and then written till 16MB or 16MB data segment is
directly written at once.
These questions may be stupid but I thought it will help understand the
process.
-----
--
Thanks,
Rajan.
--
View this message in context: http://postgresql.nabble.com/WAL-restoration-is-not-happening-completely-tp5930204p5930928.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: WAL restoration is not happening completely
2016-11-14 09:49 WAL restoration is not happening completely rajan <vgmonnet@gmail.com>
2016-11-14 13:58 ` Re: WAL restoration is not happening completely Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2016-11-14 17:55 ` Re: WAL restoration is not happening completely Andres Freund <andres@anarazel.de>
2016-11-18 11:17 ` Re: WAL restoration is not happening completely rajan <vgmonnet@gmail.com>
@ 2016-11-18 17:19 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
0 siblings, 0 replies; 6+ messages in thread
From: Peter Eisentraut @ 2016-11-18 17:19 UTC (permalink / raw)
To: rajan <vgmonnet@gmail.com>; pgsql-admin
On 11/18/16 6:17 AM, rajan wrote:
> One thing I missed to update is that the Source(master) machine I was
> running is switched off and on everyday. It may have caused any disruption
> in the WAL files?
Well, the point of the WAL is to preserve data integrity in case of
crashes. So, no, that should normally not cause problems.
> And one more question, The WAL files are created as 16MB segments, that is
> obvious. But I do not know when the file is completely written. Is it like,
> a file is created and then written till 16MB or 16MB data segment is
> directly written at once.
It is written in smaller chunks and then archived or rotated when the 16
MB are full.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2016-11-18 17:19 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-11-14 09:49 WAL restoration is not happening completely rajan <vgmonnet@gmail.com>
2016-11-14 13:24 ` Ian Barwick <ian.barwick@2ndquadrant.com>
2016-11-14 13:58 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2016-11-14 17:55 ` Andres Freund <andres@anarazel.de>
2016-11-18 11:17 ` rajan <vgmonnet@gmail.com>
2016-11-18 17:19 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox