Postgres Pro
Downloads
Home   >   mailing lists

Re: Missing wal_files Postgres 9.2 - Mailing list pgsql-admin

From Keith
Subject Re: Missing wal_files Postgres 9.2
Date
Msg-id CAHw75vsRk5j5v8kZ01_-QBQLtmpYNDDYkjHoURMusdEOHeQt6Q@mail.gmail.com
Whole thread Raw
In response to Missing wal_files Postgres 9.2  (Patrick B <patrickbakerbr@gmail.com>)
Responses Re: Missing wal_files Postgres 9.2
List pgsql-admin
Tree view


On Sun, Jul 3, 2016 at 7:30 PM, Patrick B <patrickbakerbr@gmail.com> wrote:
Hi guys,



I'm currently using PostgreSQL 9.2.

One of my backup servers went down, and I had to re-sync the all the DB.
I used pg_basebackup and, of course, at the same time wal_archive.

Steps I did:

1 - Confirm the wal_files are being copied into the new server.
2 - Delete /var/lib/pgsql/9.2/data/*
3 - Double check the wal_files are ok into the server
4 - Start pg_basebackup
5 - pg_basebackup took 10 days, as our DB is 2.0TB.
6 - Start postgres with the recovery.conf.restore_comand
7 - Wait for the wal_files being processed

I got an error here: 00000002000013B40000001A` not found

How is that possible? If I double checked!! :(
I can't find that file anywhere.. and now I'll have to re-do all the work =(


Questions: 

1 - Is there any way to restore the DB without that file?

2 - If I'll have to re-do all the steps above, is there any way to check if the wal_files exists once the pg_basebackup has started?
Once the pg_basebackup command is running, I could see if the next wal_file would be available... it would be easier to cancel the command or to restart it if I need to.

Can I check the next wal_file by catting the backup_label files?

cat backup_label: 

START WAL LOCATION: 13B4/EBEECA8 (file 00000002000013B40000000E)
CHECKPOINT LOCATION: 13B4/1AACC868
BACKUP METHOD: streamed
BACKUP FROM: standby
START TIME: 2016-06-24 04:04:48 UTC


Thanks


Look into using the --xlog-method=stream (-Xs) option to pg_basebackup. That opens up a second replication stream and keeps all the WAL files generated during the basebackup so that you can bring up your basebackup to a consistent state at the point when the backup finishes. You'll have to be sure --max_wal_senders is set high enough to allow 2 replication connections during the backup.

You then also have to bring that slave up before the master removes all the WAL files from the pg_xlogs folder that that slave may need for replay to sync itself with the master. If you're not going to be bringing that slave up rather quickly after the base-backup, then you're going to have to archive your WAL files off somewhere else that the slave will have access to until its ready.

If you can get upgraded to 9.4, you can use replication slots and not have to worry about the master removing WALs from pg_xlog when the associated slave is down.

Keith

pgsql-admin by date:

Previous
From: Patrick B
Date:
Subject: Missing wal_files Postgres 9.2
Next
From: Patrick B
Date:
Subject: Re: Missing wal_files Postgres 9.2