How to mount a pg_basebackup on a stand alone server to retrieve accidently deleted data 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
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.