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@ceedees.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@ratlhead.com [mailto:ratlhead@ratlhead.com]
> > Enviada em: sábado, 20 de julho de 2002 06:26
> > Para: pgsql-general@postgresql.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@ratlhead.com
> > else
> >   echo "The PostGreSQL backup for today was unsuccessful" | mail
> > ratlhead@ratlhead.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!