PostgreSQL Data Recovery – How to Recover Data from Data Directory

disaster recoverypostgresqlrecoveryrestore

  • I had an old iMac which stopped working a while ago
  • I take weekly backups of the database I was updating which contains feed items from several rss feeds
  • I am still missing the data for the last week
  • I currently have the hard drive of the old machine which contains the postgres data directory (it was an Intel based iMac)
  • My new machine is an Apple M1 based mac mini and I want to load all those feed items
  • I want to recover a particular table called feed_items from a specific database called ch_v3 from the data directory on the hard drive containing old postgres files
  • How do I recover the 100k+ feed items which are stored in raw format on my old hard drive into my new postgres installation on the new machine?

Best Answer

If you need a method to restore the database but do not have an Intel/AMD-based system available, then a virtual machine on Amazon, DigitalOcean, or elsewhere may give you what you need. If you’ve never had an Amazon Web Services account, you can even do all of this for “free”.

The process might work like this:

  1. Sign up for an account with a VPS provider
  2. Configure it with an operating system of your choice. For the sake of this example, I’ll use Ubuntu Server 20.04 and PostgreSQL 12
  3. Install PostgreSQL
    sudo apt install postgresql postgresql-contrib
  4. Confirm the installation was successful by connecting to the database: 
    sudo -i -u postgres
psql
  5. So long as you can connect, then it’s all good. Let’s exit: 
    \q
  6. Now we need to stop the server: 
    service postgresql stop

With the basics in place, it’s now time to do some file management.

  1. Delete the files located in the PostgreSQL data directory 
    sudo rm -Rf /var/lib/postgresql/12/main
  2. Copy the old data files to that location
  3. Set file permissions accordingly: 
    sudo chown -R postgres:postgres /var/lib/postgresql/12/main
sudo chmod -R u+rwx,g-rwx,o-rwx /var/lib/postgresql/12/main
  4. Restart PostgreSQL 
    sudo service postgresql start
  5. Get your data back

All in all, this may be about 30~45 minutes of work. Hope it allows you to recover the missing records.

Note: If you choose to create an AWS account and use their free tier options, remember to terminate the instance when you’re done to avoid any unexpected charges one year from now.

Note 2: Because these are virtual machines, if anything goes wrong, simply terminate the machine and start over.

Related Question