Обсуждение: WAL restoration is not happening completely
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.
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
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
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
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.
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