Backup PostgreSQL Database

Without having to use tools like pgAdmin, you can backup and restore database using console

Backup using pg_dump

this command will dump database into a single file

pg_dump dbname > dbname.dmp -v

Change dbname with the database name of your choosing, -v will allow to display the process that is happening, this is particularly useful if you backup large database since it can take quite some time

Backup and compress using pg_dump in gz format

often times for large database the dump file size can be enormous, pg_dump provide mechanism to automatically zip the dump result so the file size can be smaller

pg_dump dbname | gzip > dbname.gz -v

the same dump file will be inside the zip file which you can extract using unzip or gunzip

Additional Backup options

• "-a" : dump only data, not schema
• "-c" : include command to drop database first before restoring
• "-C" : include command to create database in dump
• "-s" : dump schema only (no data)
• "-O" : skip restoration of object ownership
• "-x" : do not dump privileges, this is useful if you have 2 database server with different user

for more information on dumping postgresql database, go to postgre documentation site