Automatic postgres backup

Question

What is the best way to automatically backup a Postgres database on Linux every day?

score 12 · Answer 1 · answered Aug 18 '09 at 17:15

12

Run pg_dumpall from cron.

answered Aug 18 '09 at 17:15

Bill Weiss

10,979
3
38
66

7

pg_dumpall is better than the other answers because this backs up your global database objects. (Ex: users, etc..) You will need these to completely recover your DB and if you have a large number of these than it can be non-trivial to recreate them. However, pg_dumpall could be overkill for you if you simply want to backup a single database instance within a multiple database cluster because pg_dumpall will backup _every_ instance. So... as with most tech Q&A, it depends. – KevinRae Aug 21 '09 at 22:52
1

I tend to conflate "database" and "database server", this being a good example. I assumed the poster would want to back up the whole thing Good point. – Bill Weiss Aug 23 '09 at 17:21

score 11 · Accepted Answer · edited Oct 16 '14 at 15:52

11

You can use pg_dump like this:

$ pg_dump -h db_host -U user_name db_name > dump_file.sql

Please think first to set the .pgpass file, that contain passwords to be used if the connection requires a password. This file should have lines of the following format:

hostname:port:database:username:password

And each of the first four fields may be a literal value, or *, which matches anything. For example: *:*:*:postgres:pg_password.

This .pgpass file must reside in the home directory ~/ and the permissions on it must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass.

edited Oct 16 '14 at 15:52

SaeX

185
8

answered Aug 18 '09 at 18:50

Ali Mezgani

3,850
2
24
36

does it back up automatically everyday?? – Anuj Patel Oct 17 '11 at 09:05
@indyaah: no, you would have to run this daily from cron (or similar). – Bill Weiss Dec 09 '11 at 05:29

score 6 · Answer 3 · 2009-08-18T21:32:08.617

6

Try AutoPostgreSQLBackup. It is a single script file, can be easily configured to your needs, does daily, weekly and monthly scheduling, logs per email, log file or stdout, etc.

edited Aug 18 '09 at 21:32

answered Aug 18 '09 at 20:44

I also use AutoPostgreSQLBackup and it's working very well for me ;-) – Matthieu Aug 23 '09 at 21:42

score 4 · Answer 4 · answered Jan 11 '10 at 15:57

4

pg_rman is a new tool, offering incremental backups, which works for PostgreSQL 8.4 or newer.

answered Jan 11 '10 at 15:57

Adam Matan

13,194
19
55
75

score 4 · Answer 5 · answered Aug 18 '09 at 17:14

4

If it's a reasonably small database, and such low requirements on the backup as just once a day, just run pg_dump from cron to dump to a local file, and then use whatever you have to backup the files on the machine to archive the dump away.

answered Aug 18 '09 at 17:14

Magnus Hagander

2,287
15
9

score 2 · Answer 6 · answered Aug 23 '09 at 14:09

2

Try astrails-safe. It knows how to backup mysql (mysqldump), postgres (pg_dump), or just plain files (tar) with encryption (gnupg) and upload to S3/Sftp.

answered Aug 23 '09 at 14:09

Vitaly Kushner

1,187
9
8

Silverfire · Answer 7 · 2013-01-15T12:06:05.063

This is a script that will backup each database individually as well as the often forget but important PostgreSQL GLOBALS and user login info.

The importance of this is to take advantage of compression that pg_dumpall does not provide and the forgoten data that pg_dump ignores.

This will require a pgpass or similar setup as described here http://wiki.postgresql.org/wiki/Pgpass

This is setup for OSX but simply change the Program paths and it will work fine.

Backs up to /sqlbackups The script will return the directory size/directory and has breakpoints that will return a non zero status if it fails. I used it in combination with pgAgent to do daily backups.

Script redacted, sorry about that :(

Javier Novoa C. · Answer 8 · 2009-08-18T21:26:35.803

1

pg_dump is a nice solution, but if you are trying to backup a lot of data, perhaps this may help:

http://www.postgresql.org/docs/8.1/static/backup-online.html

which in fact is a kind of 'raw' logging, but that can be useful as an incremental backup method...

edited Aug 18 '09 at 21:26

answered Aug 18 '09 at 21:14

Javier Novoa C.

377
1
6
18

score 1 · Answer 9 · answered Oct 16 '14 at 16:26

1

Why settle down with a daily backup when you can easily have point-in-time recovery with barman?

answered Oct 16 '14 at 16:26

Giovanni Toraldo

2,587
19
27

I'll let the moderators decide, but I believe resurrecting 5 years old questions to plug commercial products is bad form here. – gparent Oct 16 '14 at 16:31
ehm, it's open source: http://sourceforge.net/projects/pgbarman/files/ – Giovanni Toraldo Oct 16 '14 at 16:32
You're right, I googled the wrong thing. My main concern wasn't that, anyway, it was about suggesting an answer that's not really what the user asked 5 years afterwards. However, after reading the other answers, they also seem to suggest similar back up solutions so I guess it isn't that bad. My flag will probably be declined and we'll call it a day :D – gparent Oct 16 '14 at 16:37

score 0 · Answer 10 · answered Aug 23 '09 at 21:25

As others have said: pg_dumpall.

Also, take a look at log shipping. Then you can get more point-in-time backups which you can play back: http://www.postgresql.org/docs/8.3/static/runtime-config-wal.html

Or how about the section on backups in the manual:

http://www.postgresql.org/docs/8.3/static/backup.html

Automatic postgres backup

10 Answers10