NAS Storage Blog

NAS storage news, reviews, tips and tricks

Dump and restore databases easily with PostgreSQL

My favorite open source SQL database is PostgreSQL because it is easy to use, fast and very flexible. It has become the only open source SQL database that I use in some of my software projects. It is very easy to install and update on different operating systems. I use PostgreSQL on Debian (install and update via apt) and Mac OS X (install and update via Macports).
When PostgresSQL is installed it is quite easy to dump and restore databases.

Dump the PostgreSQL database

There are several approaches to dump (backing up) a SQL database in PostgreSQL but they all use the pg_dump utility. My favorite approach is to dump the database to SQL and pipe the result into a zipped file via gzip.
The following command is very useful:

pg_dump -O -U postgres foo | gzip > foo-16032013.sql.gz

  • Dump the database called foo with no owner with user postgres.
  • Gzip the result into file foo-16032013.sql.gz.

This will create file called foo-16032013.sql.gz that you can use for backup.

Restore the PostgreSQL database

Restoring the PostgreSQL database is also very simple. For this task you will have to use psql which is a terminal PostgreSQL client.
The following command is very useful when restoring a PostgreSQL database (assumed you have database called foo that is empty):

gunzip < foo-16032013.sql.gz | psql -U postgres foo

  • Unzip the zipped file foo-16032013.sql.gz.
  • Use the unzipped data for restore in database data.

Voila. You now have a database called foo with data.

Leave a Reply