Thread: How to create nightly backups in Linux

I'm using the  the following scheduler script to create nightly backups in
Windows:

set pgpassword=mypass
set FILENAME=%DATE:~8,4%%DATE:~5,2%%DATE:~2,2%mybackup.backup
"C:\Program Files\PostgreSQL\8.1\bin\pg_dump.exe" -i -Z9 -b -v -f
"%FILENAME%" -F c -h localhost -U postgres mydb

I'm bit new to Linux. I'm using white-box linux and Postgres 8.1.4
How to create backups of database with unique name in every night ?
Is there some script sample which can be called from /etc/crontab ?

Andrus.

Andrus wrote:
> I'm bit new to Linux. I'm using white-box linux and Postgres 8.1.4
> How to create backups of database with unique name in every night ?
> Is there some script sample which can be called from /etc/crontab ?

I use the following Z-shell script.  Rewriting to bash should be
trivial (probably no changes after the first line).
Note that this differs from yours in that it uses pg_dumpall
and dumps everything.  You would want to change that...

-----------------------------------------------
#!/bin/zsh

# We prefer backing up to the SAN, but use the local
#  disk as a fallback if the SAN isn't available.
backupdir=/u3/SolisDBbackups
if [ -d /mnt/san/SOLIS-files/db_backups ]; then
    backupdir=/mnt/san/SOLIS-files/db_backups
fi

backupdate=$(date --iso-8601=minutes)
backupfile=${backupdir}/${backupdate}.dbtxt
echo "Backing up SOLIS databases to ${backupfile}.gz"
date
pg_dumpall -c | gzip >${backupfile}.gz
echo "Backup of SOLIS databases to ${backupfile}.gz done."
date
exit 0
--------------------------------------------

The crontab entry is:
-------------------------------------------
45 21 * * * /u3/SolisDBbackups/fullDump.zsh
-------------------------------------------

Hope this helps.
--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.

----Original Message----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andrus Sent:
Wednesday, September 27, 2006 2:17 PM To: pgsql-general@postgresql.org
Subject: [GENERAL] How to create nightly backups in Linux

> I'm using the  the following scheduler script to create nightly
> backups in
> Windows:
>
> set pgpassword=mypass
> set FILENAME=%DATE:~8,4%%DATE:~5,2%%DATE:~2,2%mybackup.backup
> "C:\Program Files\PostgreSQL\8.1\bin\pg_dump.exe" -i -Z9 -b -v -f
> "%FILENAME%" -F c -h localhost -U postgres mydb
>
> I'm bit new to Linux. I'm using white-box linux and Postgres 8.1.4
> How to create backups of database with unique name in every night ?
> Is there some script sample which can be called from /etc/crontab ?

While Windows normally has just a single batch language, Linux has many
to choose from.  How you accomplish this depends on the shell language
you are using.  Since you are new, I'll guess you are using bash, which
is normally the default.  Here is a shell script that generates a file
name with a date on the end:

#!/bin/bash
FILEDATE=MyFileName-`date +%Y-%m-%d`
echo $FILEDATE

Enter these lines into file, e.g., filedate.sh.  To make this file
executable, at a command prompt, type "chmod +x filedate.sh".  Unlike
Windows, file types (e.g., executable vs text) are not determined by
their extensions, so you need to explicitly tell Linux this file can be
executed.  Having done that, you can now run it: "./filedate.sh".

--
Guy Rouillier