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.