How to Create Database Backup and restore using pg_dump postgresql

Summary: I tried to explain how to create database backup with pg_dump and restore the database in posgresql.

To create dump file as a backup from postgresql follow the simple commands below:

Pg_dump is an easy way and effective way to backup and restore the postgres databases. It will create .sql dump file for ease of use and identifications of the backup file. To restore the backup we use the psql command. You can review below how i use the psql to restore the database backup to postgresql.
Step-1: login to your server through ssh

ssh root@yourserveripaddress
passwd: Your Server Root User password

and run the commands:

Step-2:A Basic Command to dump the database.

pg_dump -U {database-user-name} {posgres-custom-database} -f > {dumpbackupfilename.sql}

In my case it will be

pg_dump -U shahmeer firstdb > firstdb.sql

Step-3: Backup all postgres databases
To backup all databases, list out all the available databases;
login as postgres /psql user:

# su – postgres
#psql -l

will show you list of the databases;
\q :(Exit)
Run the command to backup all the databases:

#pg_dumpall > alldatabasesbackup.sql

Now as the backup completed you have to verify the backup;

# grep “^[\]connect” alldatabasesbackup.sql
\connect db1
\connect db2
\connect db3..

Step-4: How to Backup a single table from specific database:
The command listed below will backup the database firstdb table contacts table.

# pg_dump –table contacts -U shahmeer -d firstdb -f conatactstable.sql

How To Restore Postgres Database:

Step-1: In order to Restore postgres database follow the steps below.

# psql -U shahmeer firstdb.sql < newdb

If you are going to restore the local database on the remote server then use the below command

#pg_dump firstdb.sql | psql -h hostname dbname

Step-2: Restore All the postgres databases using pgsql

#su postgres
#psql -f alldatabasesbackup.sql

Step-3: Restore a single postgres table

#psql -f contacts.sql newdb

Where new db is the database name and contacts.sql is a dump of table contacts

Step-4:How to create a dump of two tables in a single command:

# pg_dump -t table1, -t table2 -U firstdb > 2tables.sql

Thats it, if you have any query regarding backup and restore or if facing any issue comments here.

  1. 4 years ago

Leave a Reply

Your email address will not be published. Required fields are marked *