Skip site navigation (1) Skip section navigation (2)

Re: Cron jobs and backup

From: Aarni Ruuhimäki <aarni(at)kymi(dot)com>
To: Carlos Bergueira <carlos(dot)bergueira(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Cron jobs and backup
Date: 2005-08-16 08:32:44
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Hi Carlos,

Did you get your backup going ?

Here's what I actually did for automatically backing up my Postgres data from 
remote machine.

1. On remote machine I have crons for vacuuming and dumping all data daily ( = 
nightly) and  'incrementally' with datestamps:

/usr/local/pgsql/bin/vacuumdb -a

/usr/local/pgsql/bin/pg_dumpall -c > /usr/local/pgdumps/all/all_`date 

Similar dumps for MySQL as well.

2. I created id_rsa and keys for my backup-user on local machine.

cd /home/backup-user/.ssh/

ssh-keygen -t rsa -N ""

3. I moved and copied the key line to remote 
machine's /home/backup-user/.ssh/ and named it to authorized_keys2

4. I wrote a shell script which I cron jobbed for my local backup-user:

cd /var/remote_backup # here we fetch stuff

# ssh-connect and give commands from batch file
ssh  < /etc/backup_from_remote/ssh-B.batch

# sftp-connect and give commands from batch file
sftp -b /etc/backup_from_remote/sftp-B.batch

# = remote ip-address

ssh-B.batch looks like this:

#cd /somewhere/ # here we tar
#tar czvf name.tar.gz /path/somewhere #what we tar
#no tarring or anything, just exit the batch

sftp-B.batch looks like this:

cd /usr/local/pgdumps/all/ #where we fetch from
get *
rm *

This fetches and removes whatever you have in the remote directory where we 
get the dumps from remote crons.

Logging and email reporting would be nice too ...

Best regards,


This is a bugfree broadcast to you
from **Kmail**
on **Fedora Core 2** linux system

Hi Carlos,

You can find sample scripts here:

Modify them to your needs, they are quite straight forward. I would not allow 
root remote login, though.

Not sure about the logs with all commands and results or sending email, 
something I been wondering to do too, but as I sit in front of my backup 
machine every work day I see if the files have come down or not and have not 
yet bothered. Another thing on the 'to do' list ...

Best regards,


On Thursday 04 August 2005 19:30, Carlos Bergueira wrote:
> Hi Aarni ,
>  Could you pls help me ?
> I need to create a cron job to backup some especifics folders sftp'ing the
> gzip file from a server to another one.
> I know how tar and gzip folders i want.
>  Could you pls send me the script for:
> - SFTP'ing the gzip file;
> - Create a log with all commands and results;
> - Sending e-mail to administrator system if the backup was done succesfully
> or not.
> Thanks.
>  --
> Rgds,
> Carlos Bergueira

pgsql-novice by date

Next:From: Saranya SivakumarDate: 2005-08-16 15:25:02
Subject: Re: [pgsql-novice] Daily digest v1.1520 (14 messages)
Previous:From: Walker, Jed SDate: 2005-08-15 20:51:17
Subject: Re: Adding variable intervals to timestamps

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group