1

I have a postgres base backup taken via wal-e. Is it possible to restore and start the DB with just the base backup? I cannot use the restore_command in recovery.conf since the wal files are archived to deep glacier.

After copying the base backup with wal-e backup-fetch command and starting the DB, im getting the following logs:

2019-11-05 11:28:26 UTC [7417]: user=,db=,app=,client= LOG:  database system was interrupted; last known up at 2019-10-31 22:13:32 UTC
2019-11-05 11:28:26 UTC [7417]: user=,db=,app=,client= LOG:  creating missing WAL directory "pg_xlog/archive_status"
2019-11-05 11:28:26 UTC [7417]: user=,db=,app=,client= LOG:  invalid checkpoint record
2019-11-05 11:28:26 UTC [7417]: user=,db=,app=,client= FATAL:  could not locate required checkpoint record
2019-11-05 11:28:26 UTC [7417]: user=,db=,app=,client= HINT:  If you are not restoring from a backup, try removing the file "/var/lib/postgresql/9.6/main/backup_label".
2019-11-05 11:28:26 UTC [7415]: user=,db=,app=,client= LOG:  startup process (PID 7417) exited with exit code 1
2019-11-05 11:28:26 UTC [7415]: user=,db=,app=,client= LOG:  aborting startup due to startup process failure
2019-11-05 11:28:26 UTC [7415]: user=,db=,app=,client= LOG:  database system is shut down

Is it possible to start the DB and bring it to consistent state with only the base backup?

Improve this question

2 Answers 2

Reset to default
0

I have a postgres base backup taken via wal-e.

So I would suggest that your question would be best addressed to the people who created wal-e. I've never used it myself, but ...

Is it possible to start the DB and bring it to consistent state with only the base backup?

Short answer: No.

The base backup takes a "snapshot" of the database. That snapshot, taken from a running database, it unlikely to be consistent in itself.

Anything and everything that happens in the database after that snapshot is taken is written to the WAL files so, in order to do any meaningful database recovery, you need those as well.

Improve this answer
2
  • Thank you for the quick response! So it looks like the only way to start the DB again without access to wal backups is by using the pg_resetxlog tool?
    – Anirudh Coontoor
    Commented Nov 6, 2019 at 6:03
  • To clarify, the purpose of restoring the backups are mainly for reporting purposes. The last three months of base backups and wal backups are available in S3 and we can do a recovery. In case of backups older than 3 months, we only store the base backups and not the wal backups.
    – Anirudh Coontoor
    Commented Nov 6, 2019 at 6:20
0

Phil's right. When we put the database in backup mode all the changes related to it go to WAL and eventually to archives.

If there is no choice left (you lose your archives) then maybe you want to resetxlog and bring your database up but there is no guarantee that your database will be consistent.

You may not see the problem while the database is up but when you start accessing tables and read the os blocks it may be a problem.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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