Is there any simple way to backup all your local databases daily (same postgresql server)? I found some bash script that I can use but they seem very complex for such a job.
Any easy suggestions ?
Since my databases are fairly small I "cheat" and use logrotate to do my postgresql-backups. Something like this in /etc/logrotate.d/postgresql-backup :
This gives me a conveniently available complete dump, no more than a day old, and automatically compresses and rotates previous dumps.
Obviously this won't scale with larger databases, but for my purpose it's just about perfect. (I also use BackupPC to handle off-site backups which include the local postgresql-backups)
If you have /etc/cron.daily, put there a one-liner that calls pg_dumpall.
Note: this is the simplest way, but creates an uncompressed SQL script, to be executed from psql.
For compressing, gzip (faster), bzip2 or lzma (slower, smaller output)
Keeping a different copy per each week day could be enough in simple cases. Storing weekly, monthly, or incremental backups are up to you.
I suppose the complex scripts you have seen back up the databases in binary format using pg_dump/pg_restore, which is more flexible.
I set up my daily backup like this:
I then use rsnapshot from a different site to keep copies of these dumps
The reason I chose not to use pg_dumpall is that pg_dump has more flexible options. You will need to use multiple