Postgresql – How to mount a pg_basebackup on a stand alone server to retrieve accidently deleted data

backuppg-basebackuppostgresql

Had a bit of a brain fade the other day and deleted all the rows in one of the tables in our database and didn't realise until a couple of hours later.

I have a pg_basebackup file system copy created before that and have setup a virtual server with the same OS and same version postgresql server. I just want to have postgresql start and let me query the database to retrieve information from that one table. I do not want to use it for live data or have it try to update itself from the main server. I have tried but failed so must be missing something.

Can someone please give me a simple point by point on what I need to do or point me to some plain documentation that describes the process.

Jeff

Best Answer

Problem solved. For future reference of anyone facing the same situation I have listed the process below

Saved test servers server.crt and server.key File structure created by pg_basebackup copied across to a test server. Replaced server.crt and server.key with saved versions

sudo /etc/init.d/postgresql start

Startup failed.

  • Starting PostgreSQL 9.1 database server
  • The PostgreSQL server failed to start. Please check the log output: 2018-07-22 16:19:45.055 AEST [8837] LOG: database system was interrupted; last known up at 2018-07-14 05:59:04 AEST 2018-07-22 16:19:45.126 AEST [8837] LOG: could not open file "pg_xlog/000000010000024600000093" (log file 582, segment 147): No such file or directory 2018-07-22 16:19:45.126 AEST [8837] LOG: invalid checkpoint record 2018-07-22 16:19:45.126 AEST [8837] FATAL: could not locate required checkpoint record 2018-07-22 16:19:45.126 AEST [8837] HINT: If you are not restoring from a backup, try removing the file "/var/lib/postgresql/9.1/main/backup_label". 2018-07-22 16:19:45.127 AEST [8836] LOG: startup process (PID 8837) exited with exit code 1 2018-07-22 16:19:45.127 AEST [8836] LOG: aborting startup due to startup process failure

Tried removing the backup_label file as suggested in the hint. sudo mv /var/lib/postgresql/9.1/main/backup_label /var/lib/postgresql/9.1/main/old_backup_label

sudo /etc/init.d/postgresql start Startup failed.

  • Starting PostgreSQL 9.1 database server

    • The PostgreSQL server failed to start. Please check the log output: 2018-07-22 16:22:53.689 AEST [8875] LOG: database system was interrupted; last known up at 2018-07-14 05:59:04 AEST 2018-07-22 16:22:53.735 AEST [8875] LOG: could not open file "pg_xlog/000000010000024600000093" (log file 582, segment 147): No such file or directory 2018-07-22 16:22:53.735 AEST [8875] LOG: invalid primary checkpoint record 2018-07-22 16:22:53.735 AEST [8875] LOG: could not open file "pg_xlog/000000010000024600000093" (log file 582, segment 147): No such file or directory emphasized text2018-07-22 16:22:53.735 AEST [8875] LOG: invalid secondary checkpoint record 2018-07-22 16:22:53.735 AEST [8875] PANIC: could not locate a valid checkpoint record 2018-07-22 16:22:53.785 AEST [8877] [unknown]@[unknown] LOG: incomplete startup packet 2018-07-22 16:22:54.141 AEST [8874] LOG: startup process (PID 8875) was terminated by signal 6: Aborted 2018-07-22 16:22:54.141 AEST [8874] LOG: aborting startup due to startup process failure

/usr/lib/postgresql/9.1/bin/pg_resetxlog -f /var/lib/postgresql/9.1/main

Note: This is where I went wrong in earlier attempts. I did not specifiy the full path to pg_resetxlog and received a message saying "The program 'pg_resetxlog' is currently not installed. You can install it by typing: sudo apt-get install postgres-xc". I installed that, ran it, destroyed my data and somehow lost the postgres role in the process". Specifiying the full path to the correct version solved the problem.

sudo /etc/init.d/postgresql start

  • Starting PostgreSQL 9.1 database server [ OK ]

Seems to have fixed it Tested with psql and I can query the databases.

Related Question