Search code examples
postgresqldatabase-backupsdatabase-restoretimescaledb

Couldn't restore postgres v11 from pg_basebackup

I'm making a backup from my postgresql database (v11) using pg_basebackup like this:

pg_basebackup -h localhost -p 5432 -U postgres -D /tmp/backup -Ft -z -Xs -P

The backup process is completed just fine without any errors.Then as a test I'd like to recover from the backup and for that I do the following steps:

  1. Shutdown the postgres instance.

  2. Delete everything from /var/lib/postgresql/data folder

    rm -rf /var/lib/postgresl/data/*

  3. Untar the base archive there:

    tar xvf /tmp/backup/base.tar.gz -C /var/lib/postgresql/data

  4. Untar the wal archives to a temporary directory

    tar xvf /tmp/backup/pg_wal.tar.gz -C /tmp/archived_wals

  5. Create a file recovery.conf in the /var/lib/postgresql/data folder:

    touch /var/lib/postgresql/data/recovery.conf
chown postgres:postgres /var/lib/postgresql/data/recovery.conf

  6. Specify the recovery command in the recovery.conf file:

    restore_command = 'cp /path/archived_wals/%f "%p" '

  7. Restart the postgres instance, and wait for the recovery to be finished.

However when postgres starts up I get the following error in the logs:

2020-02-04 11:34:52.599 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-02-04 11:34:52.599 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2020-02-04 11:34:52.613 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-02-04 11:34:52.709 UTC [18] LOG:  database system was interrupted; last known up at 2020-02-04 08:39:54 UTC
cp: can't stat '/tmp/archived_wals/00000002.history': No such file or directory
2020-02-04 11:34:52.735 UTC [18] LOG:  starting archive recovery
2020-02-04 11:34:52.752 UTC [18] LOG:  invalid checkpoint record
2020-02-04 11:34:52.752 UTC [18] FATAL:  could not locate required checkpoint record
2020-02-04 11:34:52.752 UTC [18] HINT:  If you are not restoring from a backup, try removing the file "/var/lib/postgresql/data/backup_label".
2020-02-04 11:34:52.753 UTC [1] LOG:  startup process (PID 18) exited with exit code 1
2020-02-04 11:34:52.753 UTC [1] LOG:  aborting startup due to startup process failure
2020-02-04 11:34:52.769 UTC [1] LOG:  database system is shut down

I guess I'm missing something, maybe I need to do additional configuration to postgres to be able to restore a pg_basebackup backup?

Solution

  • After all, it was my bad.

    I enclosed the destination placeholder within % instead of double quotes in recovery.conf.

    However the way I described for the backup and restore is valid and since I corrected the typo it works just fine for me, so I leave the question as it is for further reference.