0

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

  • What did you try, and what error did you get? Do you have a WAL archive? What parameters did you give to pg_basebackup when you took the backup? – jjanes Jul 22 '18 at 1:09
0

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.

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.