Thread: pg_basebackup vs archive_command - looking for WAL archive older than archive_command start

I turned on archive_command and have wal archiving going.

I did a pg_basebackup and copied the resulting file from source machine
to target, yet when I restore I am getting

requested WAL segment 00000005000008AE0000009B has already been removed

The earliest WAL archives I have are

00000005000008D20000005C.00000028.backup.gz
00000005000008D20000005C.gz
00000005000008D20000005D.gz

Why is the pg_basebackup restore looking for a WAL file that is even
older than the ones I have, when I turned on WAL archiving before I
started the pg_basebackup?

Also, why is that one WAL archive named differently with .backup?

Normally I restore with pg_basebackup and streaming replication, which
works well, but lately due to constant network disconnects I have to
make the pg_basebackup in the source machine and rsync. That is why I
turned on WAL archiving before starting the base backup since it would
take some time to copy the base backup file over.

The command I am using to create the base backup is
pg_basebackup  -U UserName -h 127.0.0.1 -D - -P -Ft | gzip >
Basebackup.tar.gz

Any suggestions?

On Mon, Jul 18, 2016 at 7:35 AM, Francisco Reyes <lists@natserv.net> wrote:
> I turned on archive_command and have wal archiving going.
>
> I did a pg_basebackup and copied the resulting file from source machine to
> target, yet when I restore I am getting
>
> requested WAL segment 00000005000008AE0000009B has already been removed

This is decided by the backup_label file located in the backup

> The earliest WAL archives I have are
>
> 00000005000008D20000005C.00000028.backup.gz
> 00000005000008D20000005C.gz
> 00000005000008D20000005D.gz
>
> Why is the pg_basebackup restore looking for a WAL file that is even older
> than the ones I have, when I turned on WAL archiving before I started the
> pg_basebackup?
>
> Also, why is that one WAL archive named differently with .backup?

This gives an indication where a backup has been taken. Depending on
the situation, like node promotion, there could be as well timeline
history files.

> Any suggestions?

It seems to me that you do not retain enough history, or simply that
you did not turn on WAL archiving before taking the base backup.. In
the current situation and you don't have those segments, the backup
you took is likely useless. It may not be able to reach a consistent
state.
--
Michael

On 07/17/2016 06:35 PM, Francisco Reyes wrote:
>
> Why is the pg_basebackup restore looking for a WAL file that is even
> older than the ones I have, when I turned on WAL archiving before I
> started the pg_basebackup?

Figured it out.. the error is from a secondary slave trying to sync from
the machine I just restored the pg_basebackup.

>
> Also, why is that one WAL archive named differently with .backup?

Still would be nice to know the above..

On 07/17/2016 04:03 PM, Francisco Reyes wrote:
> On 07/17/2016 06:35 PM, Francisco Reyes wrote:
>>
>> Why is the pg_basebackup restore looking for a WAL file that is even
>> older than the ones I have, when I turned on WAL archiving before I
>> started the pg_basebackup?
>
> Figured it out.. the error is from a secondary slave trying to sync from
> the machine I just restored the pg_basebackup.
>
>>
>> Also, why is that one WAL archive named differently with .backup?
>
> Still would be nice to know the above..
>
>

https://www.postgresql.org/docs/9.5/static/continuous-archiving.html

24.3.2. Making a Base Backup



--
Adrian Klaver
adrian.klaver@aklaver.com