RES: RES: Shell Script help for backup

Good morning Shan!

How are you?
I wish everything be fine with you!

I have done some simple shell scripts to dump and restore my
postgres database.
What Have I done?
Well, this is the files names:

dump - which generates a three files:
<database_name.ddl> -> SQL scripts to recreate you database
tables
<database_name.data> -> SQL scripts of database data.
<database_name.tar> -> database data tar file

dumpdata - generates <database_name.data>
dumptar - generates <dtabase_name.tar>
dumpdll - generates <database_name.dll>

retoreddl - restores the <database_name.ddl>
restoredata - restores the <database_name.data>
restoretar - restores the <database_name.tar>

Fom that schell script files, you can schedul your database backup
job.

My regards.

----------------------------------------------------------------------------
-----------------
#!/bin/sh

### ###
# Written by: Elielson Fontanezi - 16.07.2002 #
### ###

PGDB=$1

if [$PGDB = ""]; then
echo "digite: dump.sh <nome_do_banco>"
exit 0;
fi

# -v: verbose
# -Fp: caracter
# -Fc: pg_dump compressed format
# -Ft: tar
# -c: com drops
# -C: com DDL para criacao do banco
# -d: nome do banco
# -a: somente dados
# -s: DDL apenas
# -R: sem \connect
# -X: colocaca comando SET AUTHORIZATION COMMANDS para o usuario corrente.

DIR=`date +%Y-%m-%d`
if [ ! -d ${DIR} ]; then
mkdir $DIR
fi

DIR=${DIR}/${PGDB}
if [ ! -d ${DIR} ]; then
mkdir $DIR
fi

LOG=${DIR}/${PGDB}.dumplog
ARQ=${DIR}/${PGDB}.ddl

echo "********** dump de DDLs do banco <${PGDB}> **********" > ${LOG}
PARAM="-Fp -v -s -c -X use-set-session-authorization"
pg_dump ${PARAM} -d ${PGDB} -f ${ARQ} >> ${LOG} 2>&1

if [ $? -ne 0 ]; then
echo "ERRO: pg_dump ${PARAM} -d ${PGDB} -f ${ARQ} >> ${LOG} 2>&1" >>
${LOG}
fi

ARQ=${DIR}/${PGDB}.data
echo "********** dump ascii dos dados do banco <${PGDB}> **********" >>
${LOG}
PARAM="-Fp -a -v -X use-set-session-authorization"
pg_dump ${PARAM} -d ${PGDB} -f ${ARQ} >> ${LOG} 2>&1

if [ $? -ne 0 ]; then
echo "ERRO: pg_dump ${PARAM} -d ${PGDB} -f ${ARQ} >> ${LOG} 2>&1" >>
${LOG}
fi

ARQ=${DIR}/${PGDB}.tar
echo "********** dump tar dos dados do banco <${PGDB}> **********" >> ${LOG}
PARAM="-Ft -v -X use-set-session-authorization"
pg_dump ${PARAM} -d ${PGDB} -f ${ARQ} >> ${LOG} 2>&1

if [ $? -ne 0 ]; then
echo "*** ERRO: pg_dump ${PARAM} -d ${PGDB} -f ${ARQ} >> ${LOG} 2>&1" >>
${LOG}
fi
----------------------------------------------------------------------------
-----------------#!/bin/sh

### ###
# Written by: Elielson Fontanezi - 16.07.2002 #
### ###

PGDB=$1

if [$PGDB = ""]; then
echo "digite: dumpdata.sh <nome_do_banco>"
exit 0;
fi

# -v: verbose
# -Fp: caracter
# -Fc: pg_dump compressed format
# -Ft: tar
# -c: com drops
# -C: com DDL para criacao do banco
# -d: nome do banco
# -a: somente dados
# -s: DDL apenas
# -R: sem \connect
# -X: colocaca comando SET AUTHORIZATION COMMANDS para o usuario corrente.

DIR=`date +%Y-%m-%d`
if [ ! -d ${DIR} ]; then
mkdir $DIR
fi

DIR=${DIR}/${PGDB}
if [ ! -d ${DIR} ]; then
mkdir $DIR
fi

LOG=${DIR}/dump.logdata
ARQ=${DIR}/${PGDB}.data

echo "********** dump ascii dos dados do banco <${PGDB}> **********" >
${LOG}
PARAMETROS="-Fp -a -v -X use-set-session-authorization"
pg_dump ${PARAMETROS} -d ${PGDB} -f ${ARQ} > ${LOG} 2>&1

if [ $? -ne 0 ]; then
echo "*** ERRO: pg_dump ${PARAM} -d ${PGDB} -f ${ARQ} >> ${LOG} 2>&1" >>
${LOG}
fi
#!/bin/sh
----------------------------------------------------------------------------
-----------------
### ###
# Written by: Elielson Fontanezi - 16.07.2002 #
### ###

PGDB=$1

if [$PGDB = ""]; then
echo "digite: dumpddl.sh <nome_do_banco>"
exit 0;
fi

# -v: verbose
# -Fp: caracter
# -Fc: pg_dump compressed format
# -Ft: tar
# -c: com drops
# -C: com DDL para criacao do banco
# -d: nome do banco
# -a: somente dados
# -s: DDL apenas
# -R: sem \connect
# -X: colocaca comando SET AUTHORIZATION COMMANDS para o usuario corrente.

DIR=`date +%Y-%m-%d`
if [ ! -d ${DIR} ]; then
mkdir $DIR
fi

DIR=${DIR}/${PGDB}
if [ ! -d ${DIR} ]; then
mkdir $DIR
fi

LOG=${DIR}/dump.logddl
ARQ=${DIR}/${PGDB}.ddl

echo "********** dump de DDLs do banco <${PGDB}> **********" > ${LOG}
PARAMETROS="-Fp -v -s -c -X use-set-session-authorization"
pg_dump ${PARAMETROS} -d ${PGDB} -f ${ARQ} >> ${LOG} 2>&1

if [ $? -ne 0 ]; then
echo "*** ERRO: pg_dump ${PARAM} -d ${PGDB} -f ${ARQ} >> ${LOG} 2>&1" >>
${LOG}
fi
#!/bin/sh
----------------------------------------------------------------------------
-----------------
### ###
# Written by: Elielson Fontanezi - 16.07.2002 #
### ###

PGDB=$1

if [$PGDB = ""]; then
echo "digite: dumptar.sh <nome_do_banco>"
exit 0;
fi

# -v: verbose
# -Fp: caracter
# -Fc: pg_dump compressed format
# -Ft: tar
# -c: com drops
# -C: com DDL para criacao do banco
# -d: nome do banco
# -a: somente dados
# -s: DDL apenas
# -R: sem \connect
# -X: colocaca comando SET AUTHORIZATION COMMANDS para o usuario corrente.

DIR=`date +%Y-%m-%d`
if [ ! -d ${DIR} ]; then
mkdir $DIR
fi

DIR=${DIR}/${PGDB}
if [ ! -d ${DIR} ]; then
mkdir $DIR
fi

LOG=${DIR}/dump.logtar
ARQ=${DIR}/${PGDB}.tar

echo "********** dump tar dos dados do banco <${PGDB}> **********" > ${LOG}
PARAMETROS="-Ft -v -X use-set-session-authorization"
pg_dump ${PARAMETROS} -d ${PGDB} -f ${ARQ} >> ${LOG} 2>&1

if [ $? -ne 0 ]; then
echo "*** ERRO: pg_dump ${PARAM} -d ${PGDB} -f ${ARQ} >> ${LOG} 2>&1" >>
${LOG}
fi
#!/bin/sh
----------------------------------------------------------------------------
-----------------
### ###
# Written by: Elielson Fontanezi - 16.07.2002 #
### ###

PGDB=$1

if [${PGDB} = ""]; then
echo ">>>>>> restoredata.sh <nome_do_banco>"
exit 0;
fi

# -v: verbose
# -Fc: custom format of pg_dump
# -Ft: tar format
# -c: Clean (drop) database objects before recreating them.
# -C: Create the database before restoring into it.
# -d: database name
# -a: data only
# -s: schemma only
# -l: List the contents of the archive
# -R: no reconnect
# -X: specifies SET AUTHORIZATION COMMANDS
# -P: Specify a procedure or function to be restored.
# -O: Prevent any attempt to restore original object ownership. Objects
# will be owned by the user name used to attach to the database

DIR=/tmp/`date +%Y-%m-%d`
if [ ! -d ${DIR} ]; then
mkdir $DIR
fi

DIR=${DIR}/${PGDB}
if [ ! -d ${DIR} ]; then
mkdir $DIR
fi

# Lendo dados ascii
ARQ=${PGDB}.data
LOG=restore.logdata
PARAM=
psql ${PARAM} -d ${PGDB} -f ${ARQ} > ${LOG} 2>&1
#!/bin/sh
----------------------------------------------------------------------------
-----------------
### ###
# Written by: Elielson Fontanezi - 16.07.2002 #
### ###

PGDB=$1

if [${PGDB} = ""]; then
echo ">>>>>> restoreddl.sh <nome_do_banco>"
exit 0;
fi

# -v: verbose
# -Fc: custom format of pg_dump
# -Ft: tar format
# -c: Clean (drop) database objects before recreating them.
# -C: Create the database before restoring into it.
# -d: database name
# -a: data only
# -s: schemma only
# -l: List the contents of the archive
# -R: no reconnect
# -X: specifies SET AUTHORIZATION COMMANDS
# -P: Specify a procedure or function to be restored.
# -O: Prevent any attempt to restore original object ownership. Objects
# will be owned by the user name used to attach to the database

# lendo as DDLs
ARQ=${PGDB}.ddl
LOG=restore.logddl
psql ${PARAM} -d ${PGDB} -f ${ARQ} > ${LOG} 2>&1
#!/bin/sh
----------------------------------------------------------------------------
-----------------
### ###
# Written by: Elielson Fontanezi - 16.07.2002 #
### ###

PGDB=$1

if [${PGDB} = ""]; then
echo ">>>>>> restoretar.sh <nome_do_banco>"
exit 0;
fi

# -v: verbose
# -Fc: custom format of pg_dump
# -Ft: tar format
# -c: Clean (drop) database objects before recreating them.
# -C: Create the database before restoring into it.
# -d: database name
# -a: data only
# -s: schemma only
# -l: List the contents of the archive
# -R: no reconnect
# -X: specifies SET AUTHORIZATION COMMANDS
# -P: Specify a procedure or function to be restored.
# -O: Prevent any attempt to restore original object ownership. Objects
# will be owned by the user name used to attach to the database

# Lendo dados tar
ARQ=${PGDB}.tar
LOG=restore.logtar
PARAM="-a -v -Ft -X use-set-session-authorization"

#lendo os dados
pg_restore ${PARAM} -d ${PGDB} ${ARQ} > ${LOG} 2>&1
#!/bin/sh
----------------------------------------------------------------------------
-----------------
### ###
# Written by: Elielson Fontanezi - 16.07.2002 #
### ###

PGDB=$1

if [${PGDB} = ""]; then
echo ">>>>>> vacuum.sh <nome_do_banco>"
exit 0;
fi

LOG=${PGDB}.vacuumlog

# -z: Calculate statistics for use by the optimizer.
# -f: Perform "full" vacuuming
# -v: verbose

PARAM="-z -v -f"

vacuumdb ${PARAM} -d ${PGDB} > ${LOG} 2>&1
zakal$
----------------------------------------------------------------------------
-----------------

> -----Mensagem original-----
> De: Shanmugasundaram Doraisamy [mailto:shan(at)ceedees(dot)com]
> Enviada em: terça-feira, 23 de julho de 2002 00:58
> Para: Elielson Fontanezi
> Assunto: Re: RES: [GENERAL] Shell Script help for backup
>
>
> Dear Elielson,
> I would also be interested in seeing your
> script. We are currently
> doing a manual backup every evening. I would be great if you could
> share your script with the group. Thanks in advance,
>
> Yours sincerely,
> Shan.
> On Mon, 2002-07-22 at 19:42, Elielson Fontanezi wrote:
> > Hi!
> >
> > I am so interested in your working on this!
> > I've just do some shell scripts to do backup and restore.
> > May be we can talk a little more.
> >
> > About FTP'ing files. I can tell you that if you transfer backup
> > file which was dumped with -Fc option, it gets corrupted
> just after the
> > transfer.
> > I suggest that you use ascii format (-Fp) or tar format (-Ft).
> >
> > Do you wanna see may shell scripts?
> >
> > About your question, I need to find a way to fail the
> pg_dump file.
> > In what way? That is a good question. I'll think on it.
> >
> > -----Mensagem original-----
> > De: ratlhead(at)ratlhead(dot)com [mailto:ratlhead(at)ratlhead(dot)com]
> > Enviada em: sábado, 20 de julho de 2002 06:26
> > Para: pgsql-general(at)postgresql(dot)org
> > Assunto: [GENERAL] Shell Script help for backup
> >
> >
> > Hey all,
> >
> > I'm thinkin' maybe I should post this in a shell scripting group but
> > thought someone here may be able to help me out...
> >
> > I'm lookin' to run a shell script through the crontab on a nightly
> > basis for backup, and not keep more than a week of backups.
> In those
> > terms, I have a script working.
> >
> > But I wanna take the script to another level. Basically, I
> want it to
> > email me if pg_dump fails, but I'm having a hard time doing so.
> > here's what I got
> >
> >
> > #!/bin/bash
> > DATE=`date +%Y-%m-%d`
> > PGUSER=<my username>
> > PGPASSWORD=<my password>
> > export PGUSER PGPASSWORD
> > pg_dump <dbname> | gzip > /folder/pg_backup.$DATE.gzip
> > if [ !$? ]
> > then
> > find /folder/pg_backup/* -mtime 8 -exec rm -f {} \;
> > echo "The PostGreSQL backup for today completed
> successfully" | mail
> > ratlhead(at)ratlhead(dot)com
> > else
> > echo "The PostGreSQL backup for today was unsuccessful" | mail
> > ratlhead(at)ratlhead(dot)com
> > fi
> >
> >
> > The problem is that is always gets detected as being
> successful, even
> > if I say, comment out the export line of my user/pass. Any
> > suggestions on how I can detect whether or not it worked?
> >
> > Something else I'll be lookin' to do is FTP'ing the backup
> to another
> > server. If anyone has suggestions on where I can look for help on
> > that, it'd be great.
> >
> > Thanks!