RES: RES: Shell Script help for backup

Started by Elielson Fontaneziover 23 years ago1 messagesgeneral

ElielsonF@prodam.sp.gov.br

over 23 years ago

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

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

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

PGDB=$1

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

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

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

# 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

# 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$
----------------------------------------------------------------------------
-----------------

Show quoted text

-----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!