Log in as the postgres user:
su - postgres
Dump the contents of a database to a file by running the following command. Replace dbname with the name of the database to be backed up.
pg_dump dbname > dbname.bak
The resulting backup file, dbname.bak, can be transferred to another host with scp or stored locally(var/lib/postgresql) for later use.
To demonstrate restoring lost data, delete your example database and create an empty database in its place:
dropdb dbname
createdb dbname
Restore the database using psql:
psql test < dbname.bak
There are several options for the backup format:
- .bak: compressed binary format
- .sql: plaintext dump
- .tar: tarball
Remote Database
Just as psql allows you to connect to a remote host, pg_dump can be run from a client computer to back up data on a remote server. Use the -h flag to specify the IP address of your VPS and -p to identify the port on which PostgreSQL is listening:
pg_dump -h 198.51.100.0 -p 5432 dbname > dbname.bak
All Databases
Because pg_dump only creates a backup of one database at a time, it does not store information about database roles or other cluster-wide configuration. To store this information, and back up all of your databases simultaneously, you can use pg_dumpall.
Create a backup file:
pg_dumpall > pg_backup.bak
Restore all databases from the backup:
psql -f pg_backup.bak postgres
Automate Backups with a Cron Task
You may want to set up a cron job so that your database will be backed up automatically at regular intervals. The steps in this section will set up a cron task that will run pg_dump once every week.
Make sure you are logged in as the postgres user:
su - postgres
Create a directory to store the automatic backups:
mkdir -p ~/postgres/backups
Edit the crontab to create the new cron task:
crontab -e
Add the following line to the end of the crontab:
* * * * * pg_dump -U postgres dbname > ~/postgres/backups/dbname.bak
Save and exit from the editor. Your database will be backed up at midnight every Sunday. To change the time or frequency of the updates, see our Schedule Tasks with Cron guide.
Reference: https://www.linode.com/docs/guides/how-to-back-up-your-postgresql-database/
