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?
-
1While the accepted answer is correct, it's not the optimal way by any means. The best method is postgresql replicatione4c5– e4c52016-06-08 23:51:47 +00:00Commented Jun 8, 2016 at 23:51
-
do you have any details about postgesql replicationmahendra kamble– mahendra kamble2016-06-09 06:51:58 +00:00Commented Jun 9, 2016 at 6:51
-
The manual has a whole section on ite4c5– e4c52016-06-09 07:04:36 +00:00Commented Jun 9, 2016 at 7:04
-
4Check this: wiki.postgresql.org/wiki/Automated_Backup_on_LinuxAyman Nedjmeddine– Ayman Nedjmeddine2017-04-24 08:09:09 +00:00Commented Apr 24, 2017 at 8:09
Add a comment
|
2 Answers
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)
4 Comments
Ghassan Zein
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
Matt Kleinsmith
You might need a .pgpass file to allow the script to perform pg_dump.
ibnɘꟻ
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
dpelisek
does it really need to be done under the root user (sudo)? Both crontab and pg_dump?
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:
