Restore Postgres Database From Dump

Introduction

Instructions for this kind of thing are all over the web and very easy to find but adding this as a quick pointer for myself. It is something I do very infrequently so I often find it less than obvious once I get round to it.

I was transferring application data from one server to another. The application in question was Netbox which I highly recommend for your DCIM requirements. Give it a try.

Solution

These are the steps:

1. Dump the database from the old server to /tmp directory
2. Perform an rsync of the dump to the new server
3. Stop the newly provisioned service on the new server
4. Drop the database
5. Recreate a new database
6. Use the psql command to pull in the database dump
7. start the service

Now for the commands. I won’t bother including the data transfer steps. Choose your preferred method to get the dump to the required server. Once you’re on the new server though, you’ll need something like:

sudo supervisorctl stop netbox
sudo -u postgres psql -c 'drop database netbox'
sudo -u postgres psql -c 'create database netbox'
sudo -u postgres psql netbox < netbox.sql
sudo supervisorctl start netbox
sudo supervisorctl status netbox

I will put a warning here that the drop of a database is not always a requirement . I was setting up a new server though so wanted to make sure the new was a complete copy of the original.

Good luck.