Not able to restore to a --recovery-target-time

[AbhijitGharami]

I am testing the PITR with pghoard and I have some query regarding pghoard's behaviours with different recovery target options.

I have pghoard setup in the same machine where my postgres server is running. I am using postgresql 9.4 version and pg_receivexlog for WAL archiving. I am storing the basebackup + WAL files in Azure storage.

Backup and WAL achieving looks good to me . Even I am able to restore the database to the latest timeline, but restore with a --recovery-target-time option is not working properly.

Following steps I am using for recovery to a --recovery-target-time:

1. config:

{
        "backup_location": "/store/pghoard_azure",
        "json_state_file_path": "/store/pghoard_azure/pghoard_state.json",
        "backup_sites": {
            "default": {
                "active_backup_mode": "pg_receivexlog",
                "basebackup_count": 2,
                "basebackup_interval_hours": 24,
                "nodes": [
                    {
                        "host": "127.0.0.1",
                        "password": "******",
                        "port": 5432,
                        "user": "**********",
                        "dbname": "*********"
                    }
                ],
                "object_storage": {
                    "storage_type": "azure",
                    "account_name": "*********************",
                    "account_key": "*************************************",
                    "bucket_name": "*****************************"
                    },
            "pg_data_directory": "/store/postgresql",
            "pg_bin_directory": "/postgresql94/bin"
            }
        },
        "http_address": "127.0.0.1",
        "http_port": 16000,
        "log_level": "INFO",
        "syslog": false,
        "syslog_address": "/dev/log",
        "syslog_facility": "local2"
}

2. Run the pghoard process

3. After some database operation stopped the postgresql server

4. Restore the database to a particular time

pghoard_restore get-basebackup --/store/pitr/pghoard.json --target-dir /store/postgresql --recovery-target-time 2018-02-20T17:01:28Z --restore-to-master"

5. start the postgresql server

I can see the the database is running but it is in recovery mode.

# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)

When I checked the database relation I saw that it has data upto the timestamp that I have mentioned in the restore command.

pghoard logs

INFO    'DOWNLOAD' transfer of key: 'xx/xlog/00000001000000000000000B', size: 9697646, took 0.511s
INFO    'DOWNLOAD' transfer of key: 'xx/xlog/00000001000000000000000A', size: 797425, took 0.144s
INFO    'DOWNLOAD' transfer of key: 'xx/xlog/00000001000000000000000D', size: 9721615, took 0.919s
INFO    'DOWNLOAD' transfer of key: 'xx/xlog/000000010000000000000010', size: 9703535, took 1.178s
INFO    'DOWNLOAD' transfer of key: 'xx/xlog/00000001000000000000000C', size: 9721502, took 1.244s
INFO    'DOWNLOAD' transfer of key: 'xx/xlog/000000010000000000000011', size: 9720682, took 1.273s
INFO    'DOWNLOAD' transfer of key: 'xx/xlog/00000001000000000000000E', size: 9720224, took 1.516s
INFO    'DOWNLOAD' transfer of key: 'xx/xlog/00000001000000000000000F', size: 9718794, took 1.559s
127.0.0.1 - - [21/Feb/2018 05:59:56] "GET /xx/archive/00000001000000000000000A HTTP/1.1" 201 -
127.0.0.1 - - [21/Feb/2018 05:59:57] "GET /xx/archive/00000001000000000000000B HTTP/1.1" 201 -
127.0.0.1 - - [21/Feb/2018 05:59:58] "GET /xx/archive/00000001000000000000000C HTTP/1.1" 201 -
127.0.0.1 - - [21/Feb/2018 05:59:59] "GET /xx/archive/00000001000000000000000D HTTP/1.1" 201 -
127.0.0.1 - - [21/Feb/2018 06:00:00] "GET /xx/archive/00000001000000000000000E HTTP/1.1" 201 -
127.0.0.1 - - [21/Feb/2018 06:00:01] "GET /xx/archive/00000001000000000000000F HTTP/1.1" 201 -
127.0.0.1 - - [21/Feb/2018 06:00:02] "GET /xx/archive/000000010000000000000010 HTTP/1.1" 201 -

pg_xlog files:

postgresql/pg_xlog# ls
00000001000000000000000A  00000001000000000000000C  00000001000000000000000E  000000010000000000000010                   archive_status
00000001000000000000000B  00000001000000000000000D  00000001000000000000000F  000000010000000000000011.pghoard.prefetch

I am getting following error in the log:

LOG:  ending log output to stderr
HINT:  Future log output will go to log destination "syslog".
LOG:  ending log output to stderr
HINT:  Future log output will go to log destination "syslog".
LOG:  ending log output to stderr
HINT:  Future log output will go to log destination "syslog".
/var/vcap/store/pitr/pg_recovery.py: ERROR: '00000002.history' not found from archive

Though I can see the data upto the mentioned timestamp but the database is still in recovery mode.
I want the database to be restored to master after all the recovery process done.
However, if I remove the --recovery-target-time optioned it is working fine.

Could you please help me what I am doing wrong here.

[Ormod]

There's a check in restore.py that requires the use of PG 9.5+

if recovery_target_action:
    with open(os.path.join(dirpath, "PG_VERSION"), "r") as fp:
        pg_version = fp.read().strip()
    if LooseVersion(pg_version) >= "9.5":
        lines.append("recovery_target_action = '{}'".format(recovery_target_action))
    elif recovery_target_action == "promote":
        pass  # default action
    elif recovery_target_action == "pause":
        lines.append("pause_at_recovery_target = True")
    else:
        print("Unsupported recovery_target_action {!r} for PostgreSQL {}, ignoring".format(
            recovery_target_action, pg_version))

In 9.4 this PG option does not exist and it doesn't look like we handle it in any other way. (https://www.postgresql.org/docs/9.5/static/recovery-target-settings.html and https://www.postgresql.org/docs/9.4/static/recovery-target-settings.html)

You might want to take a look at the 9.4 options and see if one of them is usable for your case.