Byte Sized Hacks: Quickly Backup & Restore a Postgresql Database from a .sql Backup File
In this article, I’m gonna write about the fastest way that I’ve discovered so far to backup and restore a Postgresql database using terminal (that worked for me)
I’m using Ubuntu 16.04 LTS for this tutorial, and I already have postgresql along with pgadmin III installed. Although, it is not required to have pgadmin III installed in your machine. I’m just gonna create a new db with it so no big deal. You can do it with a simple psql command if you don’t have pgadmin III.
Backup A Postgres DB:
I prefer pg_dump command for this part. I open up my terminal change my directory to `/tmp` and execute the following command:
cd /tmp
sudo -u postgres pg_dump -U postgres -E UTF-8 -F p -b -C -f backup_db.sql primary_db
In the above command, the first `postgres` is a user, the second `postgres` is the role. `backup_db.sql` is the sql backup file that we are going to use when restoring the database. And, `primary_db`is the database that we are going to backup.
Once the command finishes execution you will get a backup_db.sql file in your /tmp directory, copy it to anywhere you prefer.
Restore A Postgres DB:
Create a New Database on Postgresql. I usually do it with pgadmin III choosing the right user as owner (In my case owner is ‘wasi’ and db name is ‘backup_db’)
After creating the database, open up your terminal (use shortcut if you like, ctrl+alt+T). Navigate to the folder where you kept your sql file and execute the following command:
psql backup_db wasi
It will open up a psql shell using the new database that you have created a moment ago. Inside postgres shell execute the following command:
\i ‘backup_db.sql’
note that, you will have to use single quote when writing the sql filename. It will restore all the tables in backup_db. Now, you if you like you can rename the database back to primary_db (Assuming, the primary_db was dropped).
Please, don’t hesitate to comment your preferred way of backup & restore.
Author:
Thinker, Day Dreamer, Python Enthusiast, Javascript Admirer An Introvert with Exception!
CEO, Founder
Dreamcatcher IT
twitter: twitter.com/wasi0013
github: github.com/wasi0013
facebook: fb.me/wasi0013
