Re: push db backup from one server to another with cron
On 13/05/15 13:59, Red Henry wrote:
> I have a Debian server running PostgreSQL and I want to push a
> daily backup from that server to another server. Although I think I
> know how many of the pieces work to make this happen I don't know
> how to put it all together in a nice Debian way.
>
> Where should I put the bash script that will do the work of making
> the backup and pushing the backup to another server? I think this
> script must run as the `postgres` user.
You probably need to run it as postgres to run pg_dumpall or similar,
yes. I'd put it in /usr/local/bin, and call it from a cronjob in
/etc/cron.d. I'd have it write to files with the date in the name (eg
pgdump.20150513.dump or something like that), and then rsync it to the
same directory (I've used /var/backups/pg in the past, but whatever
suits you) on the other server.
Another thing you might like to consider, though not to replace this,
is to use PostgreSQL streaming replication to the other server as well
- that will give you a 'backup' that's about as current as you can
get, though without covering for accidental 'drop table' type events
:-) It's only useful if your other server is also running PostgreSQL,
of course. It's also rather more effort to set up.
https://wiki.postgresql.org/wiki/Streaming_Replication
Richard
Reply to: