Automatic backup of PostgreSQL database in Ubuntu

Question

How to take an automatic backup of a PostgreSQL database in Ubuntu? Or is there a script available to take time-to-time PostgreSQL database backups?

While the accepted answer is correct, it's not the optimal way by any means. The best method is postgresql replication — e4c5, Jun 08 '16 at 23:51
Check this: https://wiki.postgresql.org/wiki/Automated_Backup_on_Linux — Ayman Nedjmeddine, Apr 24 '17 at 08:09

score 14 · Accepted Answer · answered Jun 08 '16 at 13:14

14

you can use the following:

sudo crontab -e

at the end of the file add this:

0 6 * * * sudo pg_dump -U USERNAME -h REMOTE_HOST -p REMOTE_PORT NAME_OF_DB > LOCATION_AND_NAME_OF_BACKUP_FILE

This command will take an automated backup of your selected db everyday at 6:00 AM (after changing options of the command to fit to ur db)

answered Jun 08 '16 at 13:14

Ghassan Zein

4,089
3
19
30

No... it will run by itself at the selected time (you can change the time to wt you need). To test the command instantaneously copy the one above and paste it in the command line ex: sudo pg_dump -U USERNAME -h REMOTE_HOST -p REMOTE_PORT NAME_OF_DB > LOCATION_AND_NAME_OF_BACKUP_FILE – Ghassan Zein Jun 08 '16 at 13:31
1

You might need a [.pgpass file](https://www.postgresql.org/docs/9.3/static/libpq-pgpass.html) to allow the script to perform pg_dump. – Matt Kleinsmith Dec 04 '17 at 21:59
3

For those who has been set password Postgres and want to create backup database with date time. `PGPASSWORD="postgres" pg_dump -U postgres -h 127.0.0.1 -p 5432 MY_DATABASE > MY_DATABASE_$(date +%d-%m-%Y_%H-%M-%S).sql` – ibnɘꟻ Jun 23 '20 at 04:17
does it really need to be done under the root user (sudo)? Both crontab and pg_dump? – dpelisek Nov 16 '20 at 06:56

score 2 · Answer 2 · answered Feb 01 '21 at 19:14

It is advisable to create a backup every time with a new name in order to be able to restore data to a specific date. Also good practice to send notifications in case the backups fail.

Here is a good script for automatic backup, as well as general recommendations for automating backups:

How to Automate PostgreSQL Database Backups

Automatic backup of PostgreSQL database in Ubuntu

2 Answers2