2

I was tasked with recovering data from an old, unmaintaned server, where its main purpose application was no longer working. Basically just want to get a postgres db dump before burning the server to the ground.

I ssh into the instance, but cannot access the database.

psql: FATAL:  database "foobar" does not exist

That's weird. I do pg_lscluster

Version Cluster   Port Status Owner    Data directory                     Log file
9.1     main      5433 online postgres /var/lib/postgresql/9.1/main       /var/log/postgresql/postgresql-9.1-main.log

Okay, something is running. I check the data dir ls /var/lib/postgresql/

8.4  9.1

Huh, okay then. looking at the contents of the two dirs, it is pretty clear that all of the data is in the 8.4 folder, and the 9.1, I can see only holds default data, when I look using psql for the running cluster.

So somehow the cluster that ran 8.4 was removed. No idea why or how (I'm not the only one with access to the server, but I cannot find anything supicious in bash history).

So how would I go about restoring access to the data, so I can pull out a dump?

2
  • pg_ctlcluster, pg_lsclusters and friends aren’t part of the Postgres project; they’re extensions installed by the Debian/Ubuntu packaging infrastructure. I’m not exactly sure why pg_lsclusters doesn’t show your existing 8.4 installation. It might be that you have more than one cluster named main. The first thing to do might be to check whether its configuration files still exist in /etc/postgresql/8.4/<clustername>.
    – wjv
    May 8 '18 at 13:27
  • It is: $ ls /etc/postgresql/8.4/main/ environment pg_ctl.conf pg_hba.conf pg_ident.conf postgresql.conf start.conf
    – Eldamir
    May 11 '18 at 6:54
2

Alright, I managed to solve it.

I tried to create the cluser

pg_createcluster 8.4 main

It complained that 8.4 was not an installed version. Alright then, this confirms my suspicion, that at some point postgres was upgraded to the latest version in the debian repos, and the data left behind.

So I went to reinstall 8.4 (Remember to backup your data dir, if you want to follow these steps. Don't hold me accountable for any dataloss)

sudo touch /etc/apt/sources.list.d/pgdg.list
sudo echo "deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-8.4
pg_lsclusters 

The output of pg_lsclusters showed that the 8.4 main cluster was created during the installation. I was afraid that it would have nuked the data dir with a fresh installation, but it didn't. Running psql revealed all the old data was still there

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.