Recover PostgreSQL database from mounted volume

My EC2 database server failed, preventing SSH or other access (not sure why ... grrr AWS ... that's a different story).

I was able to take a snapshot of the EBS root volume. I am unable to load a new instance from this volume (I assume the boot partition is corrupted). However, I can mount and mount the volume in the new instance.

Now I need to boot PostgreSQL 8.4 on a new machine (Ubuntu 10.04) in order to load data from the mounted volume. Is it possible? I tried:

pg_ctl start -D /<mount_dir>/etc/postgresql/8.4/main/

      

But no fun ... PostgreSQL just starts with empty tables.

Is the /etc/postgresql/8.4/main/

correct location for PostgreSQL data files?

Is there a way to recover data from a mounted volume in a way that PostgreSQL can read again?

+3


source to share


1 answer


(You should really list your distribution and version etc. with the sysadmin question like that.)

Running Pg through pg_ctl

as shown above should work, assuming the source database was from Pg 8.4, and therefore using the binaries you are trying to use to run it. Perhaps you forgot to stop the PostgreSQL instance automatically started by the distribution? Or connected to the wrong port, so you got a defaultro instance instead of your DB on a different port (or a different unix socket path for UNIX sockets)?

Personally, I wouldn't do what you do. First, before I did anything else, I would make a full backup of the entire data directory , because you clearly don't have good backups, or you wouldn't be worried about it. Take them now, because if you break something as you recover, you will hate yourself. As demonstrated by this bug, Amazon's reliable storage (snapshot or otherwise) is probably not good enough.

Once you have done that: the easiest way to restore your database is to make sure that in a new instance you know that you do not have important data that has the same major version (eg "8.4" or "9.0") postgresql as your original instance did:



/etc/init.d/postgresql-8.4 stop 
datadir=/var/lib/postgresql/8.4/main
rm -rf "$datadir"
cp -aR /<mount_dir>/etc/postgresql/8.4/main/ "$datadir"
chown -R postgres:postgres "$datadir"
/etc/init.d/postgresql-8.4 start

      

In other words: take a copy, fix the permissions, start the DB.

You may need to edit /etc/postgresql/8.4/main/postgresql.conf

and / or /etc/postgresql/8.4/main/pg_hba.conf

as there are no more changes that you made to the originals; they are on the damaged root FS. postgresql.conf

and pg_hba.conf

in datadir are only symbolic links to those in etc. in Debian - something I understand the rationale, but I do not like.

Once you run it, do an immediate pg_dumpall and / or just pg_dump your important db, and then copy it somewhere safe.

+5


source







All Articles