Postgresql – Proper way of restoring base backups

backuppostgresqlrestore

We have daily backups of a postgres db and it is in the form of a tarred (gz) base backup.
I need to restore this into another db of the same version. However, I am confused with the article that I am reading about doing this.
The version of the postgres DB is 8.2.4.
These are the main steps given :

rm -rf /var/lib/postgresql/10/main/*
cp -r /basebackup/* /var/lib/postgresql/10/main/
rm -rf /var/lib/postgresql/10/main/pg_wal/*

So in my case, the postgresql data directory is /usr/local/pgsql824/data. I believe this must be /var/lib/postgresql/main in the command above. I have untarred the tar.gz base backup into /usr/local/temp. So I suppose that this path is /basebackup in the command above. Please correct me if I am wrong.

Now, there comes the part of the "recovery.conf" file. These are the contents of the file as given by the article :

vim /var/lib/postgresql/10/main/recovery.conf
restore_command = 'cp /wal_archive/%f "%p"'
recovery_target_lsn = '3/72658818'

What is the purpose of the "/wal_archive" if I have already untarred contents of the base backup into /usr/local/temp ? This is what I am not sure about.

This is the link to the full article :

https://severalnines.com/database-blog/point-time-postgresql-database-restoration

I just need a clear explanation on how to restore the base backup to the new database data directory.

Best Answer

Usually a basebackup with no further description would mean the thing taken by pg_basebackup. But that didn't exist back in 8.2., so it isn't clear what "a basebackup" means here. Not that it is very clear anyway, as pg_basebackup has many options.

The article you linked to is for point-in-time recovery. Obviously a base backup cannot include changes that had not yet happened at the time the backup was taken. The WAL archive is needed to roll the database forward from the ending time of the backup, to the desired point in time. If you don't actually want to do a PITR but just restore to the ending time of the basebackup, then you won't need the recovery.conf, and you also need to not do the rm -rf /var/lib/postgresql/10/main/pg_wal/*. (Although if you did run that command, it wouldn't do any harm anyway as the directory in question was not called 'pg_wal' back then.) You just start up the database.

Related Question