Restoring an old postgresql database

I’ve just had cause to restore a two year old backup of a database I haven’t used in a while

Since then postgresql (on Debian at least) has added the option to
have multiple ‘clusters’ and multple versions running on the same
machine.

Fortunately the old version (7.4) was still available

Steps

  • restore /var/lib/postgresql/data
  • restore /etc/postgresql
  • mkdir /var/lib/postgresql/7.4
  • mv /var/lib/postgresql/data /var/lib/postgresql/7.4/
  • chown -R postgres.postgres /var/lib/postgresql/7.4/
  • pg_createcluster 7.4 data (this moves the old data into the new structure)
  • pg_upgradecluster 7.4 data (upgrade the data to the latest postgres version installed)

Other issues: my old data was in a character set I didn’t have installed as I now use UTF8

I had to add en_GB ISO-8859-1 to /etc/locale.gen

and run locale-gen

But I now have access to my old data in both old and new server
versions (I’ll probably delete the old one but it might be useful for
testing)

Tags

Post new comment

Got something to add - just enter a comment
all other fields are optional.

Your email address will not be published.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

More information about formatting options

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Copy the characters (respecting upper/lower case) from the image.