Can't find the "timeline history file" to get the replication working

Question

I'm running PostgreSQL 9.4, trying to get the replication going.

What I'm doing, taking inspiration from the instructions on the wiki and documentation:

1. SELECT pg_start_backup('clone', true);
2. rsync of the database to the would-be replica
3. SELECT pg_stop_backup();
4. rsync of the pg_xlog folder to the would-be replica

I start replica and it says:

LOG:  fetching timeline history file for timeline 3 from primary server
FATAL:  could not receive timeline history file from the primary server:
    ERROR:  could not open file "pg_xlog/00000003.history": No such file or directory

Naturally I'm looking for the .history file in pg_xlog/ on both servers, but there's none.

I'm skimming through the docs to find out that

To make use of the backup, you will need to keep all the WAL segment files generated during and after the file system backup. To aid you in doing this, the pg_stop_backup function creates a backup history file that is immediately stored into the WAL archive area. This file is named after the first WAL segment file that you need for the file system backup. For example, if the starting WAL file is 0000000100001234000055CD the backup history file will be named something like 0000000100001234000055CD.007C9330.backup.

However it just so happens that after I do pg_stop_backup() there's still nothing like this in pg_xlog/, nor anywhere.

So where do I get this "timeline history file"?

Solution

As per the Six For Two post, you may just be able to create a file and then it'll move on with the replication setup, but essentially it's a PostgresSQL bug where it needs this file even though it's not applicable or it's deleted per the operation.

Note: For Postgresql 10 and newer the function got renamed to pg_current_wal_lsn()

When PostgreSQL promotes a new primary server, it creates a marker of the timeline split in the form of a small text file placed in the WAL file directory. This file makes it possible to achieve Point-In-Time-Recovery under some rather complex failover and fail-back scenarios.

So it seems that you will have to recreate the file. You can find a very nice summary of the .history file on the Postgres wiki. Since the info is in .pdf, though, it tends to be harder to index, so you may have trouble locating the doc if you don't already know it's there.

But we're never going to go back to that timeline, because it's before our upgrade. All we'd need to recreate a lost file is a good enough number. And you can get one by running:

# SELECT pg_current_xlog_location();
 pg_current_xlog_location
--------------------------
 1/38F70328
(1 row)

Mock up a .history file in your WAL directory with these values, et voila. The replica will immediately be able to start.

_source

Create the file with these (above) results but with the expected name per the error.

---

Further Resources

• Understanding PostgreSQL timelines

• System Administration Functions

  Name: pg_current_xlog_location()

  Return Type: text

  Description: Get current transaction log write location

  pg_current_xlog_location displays the current transaction log write location in the same format used by the above functions. Similarly, pg_current_xlog_insert_location displays the current transaction log insertion point. The insertion point is the "logical" end of the transaction log at any instant, while the write location is the end of what has actually been written out from the server's internal buffers. The write location is the end of what can be examined from outside the server, and is usually what you want if you are interested in archiving partially-complete transaction log files. The insertion point is made available primarily for server debugging purposes. These are both read-only operations and do not require superuser permissions.

  You can use pg_xlogfile_name_offset to extract the corresponding transaction log file name and byte offset from the results of any of the above functions.

  Similarly, pg_xlogfile_name extracts just the transaction log file name. When the given transaction log location is exactly at a transaction log file boundary, both these functions return the name of the preceding transaction log file. This is usually the desired behavior for managing transaction log archiving behavior, since the preceding file is the last one that currently needs to be archived.

  _source