Couldn't restore postgres v11 from pg_basebackup

1
votes

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?

2
postgresqldatabase-backupsdatabase-restoretimescaledb
This should work. At least, it works for me without the timescaledb. You can copy the wal files from /tmp/archived_wals/ directly into /var/lib/postgresql/data/pg_wal before you start it up, but that should not be necessary. – jjanes
@jjanes Yes indeed. I had a type on the recovery.conf file. I had "%p% instead of "%p" and it screwed up things, however the error message was not really described the source of the problem really well. – Szabolcs

2 Answers

1
votes

Try this:

 /pgsql-11/bin/pg_basebackup -D /pgdata/pg11/data -X stream --waldir=/pglog/pg11/wal_log/ -P -c fast -h remoteserver\localserver -U username

Let me know if that works.

1
votes

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.