0

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.

2
  • You need PostgreSQL 8.2 to restore an 8.2 backup. Other than that, stick with the documentation. I'd just forget about the database - if somebody is running 8.2 today, the data cannot be important. – Laurenz Albe Sep 21 '20 at 10:45
  • I am going to upgrade the DB version to 12.3 once it is restored, but since we only have base backups and not a dump, I can't do the restoration directly to the new version. – anaigini Sep 22 '20 at 2:07
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.