All we need is an easy explanation of the problem, so here it is.
- 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?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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:
- Sign up for an account with a VPS provider
- 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
- Install PostgreSQL
sudo apt install postgresql postgresql-contrib
- Confirm the installation was successful by connecting to the database:
sudo -i -u postgres psql
- So long as you can connect, then it’s all good. Let’s exit:
- Now we need to stop the server:
service postgresql stop
With the basics in place, it’s now time to do some file management.
- Delete the files located in the PostgreSQL data directory
sudo rm -Rf /var/lib/postgresql/12/main
- Copy the old data files to that location
- 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
- Restart PostgreSQL
sudo service postgresql start
- 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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂