I'm going to make backups from a standby server. I use the following commands to create binary backup:
psql -c 'select pg_xlog_replay_pause()'
tar c data --exclude=pg_xlog/* | lzop --fast > /mnt/nfs/backup/xxxx.tar.lzop
psql -c 'select pg_xlog_replay_resume()'
All WAL logs from master database are stored on external storage for several days and recovery using these logs works great. However, backup becomes invalid after logs are cleaned. The solution is to copy all needed WAL logs starting from some point until the last log when backup is done.
The question is what is the first file?
pg_controldata shows:
pg_control version number: 942
Catalog version number: 201409291
Database system identifier: 6185091942558520564
Database cluster state: in archive recovery
pg_control last modified: Thu 08 Oct 2015 03:14:23 PM UTC
Latest checkpoint location: 1C41/F662E1F8
Prior checkpoint location: 1C41/B4435EE8
Latest checkpoint's REDO location: 1C41/DE003400
Latest checkpoint's REDO WAL file: 0000000200001C41000000DE
Latest checkpoint's TimeLineID: 2
Latest checkpoint's PrevTimeLineID: 2
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0/3550951620
Latest checkpoint's NextOID: 83806
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 3152230057
Latest checkpoint's oldestXID's DB: 16385
Latest checkpoint's oldestActiveXID: 3550951620
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 16385
Time of latest checkpoint: Thu 08 Oct 2015 03:10:44 PM UTC
Fake LSN counter for unlogged rels: 0/1
Minimum recovery ending location: 1C42/4CC934E0
So what is the first file? AFAIK PostgreSQL always begin recovery from a checkpoint. I have tried to restore several backups and noticed that PostgreSQL starts recovery from Prior checkpoint location. Is this always true? What's the difference between Prior checkpoint location and Latest checkpoint location?
According to pg_controldata:
First file: 1C41/B4
Minimum last file: 1C42/4C (Must be greater of equal to `Minimum recovery ending location`)
Am I right?
You need from the "last checkpoint's redo location" - the first WAL for which is identified with "last checkpoint's REDO WAL file" - through to the WAL segment containing "minimum recovery ending location" on timeline "Last checkpoint's TimeLineID".
In your example that'd be from LSN 1C41/DE003400 through to 1C42/4CC934E0, both on TimeLineID 2.
That corresponds to WAL segments 0000000200001C41000000DE through 0000000200001C42????????.