Thread: pg_dump command inside shell scripts

pg_dump command inside shell scripts

From

Elielson Fontanezi

Date:

08 October 2002, 12:37:51

Hi Folks!

    Again I need a help from you!

    I have develop a shell script (seen below) to do dump and vaccum
automatically.
Once pg_dump command prompts for password, I changed the $PGDATA/*hba.conf
file
to identify Linux user which starts the shell script to avoid password
prompt.
    But now I need change the $PGDATA/*hba.conf file as it was originally
and
face the password prompt again.

    In shell script programming how do I can do to answer this prompt?

I have done something like this:

#> pd_dump -d zakal <<!
my_password
!

But without success!

Who can help me?

#---------------------------------------------------------------------------
--

#!/bin/sh

#
# Script de dump de banco da dados para PostgreSQL
#
# Written by: Elielson Fontanezi - 16.07.2002 #
#
# NOTA: Lembre-se sempre de verificar se o caminho
# apontado por $BKROOT exite, senão co cron não executará
#

PGHOME=/usr/local/pgsql
PGPATH=$PGHOME/bin
DUMPCMD=$PGPATH/pg_dump
VACUUMCMD=$PGPATH/vacuumdb

PGDB=$1

BKROOT=$HOME/backups

if [ ! -d ${BKROOT} ]; then
   mkdir $BKROOT
   if [ $? -ne 0 ]; then
      ERR_VAL=$?
      echo "Erro no: ${ERR_VAL} na criação de ${BKROOT}" |\
         mail -s "`hostname`: *** ERRO ***" elielsonf@prodam.sp.gov.br
   fi
fi

# Se o mes mudar, remova os diretorios
# do mes anterior

mes_atual=`date +%m`
for a in `find ${BKROOT} -print | grep -E "*[0-90-90-90-9]$"`
do
   b=`echo ${a} | cut -d"-" -f 3`
   if [ $mes_atual != $b ]; then
      rm -rf ${a}
   fi
done;
#
#

BKHOME=${BKROOT}/`date +%Y-%m-%d`

if [ ! -d ${BKHOME} ]; then
   mkdir $BKHOME
   if [ $? -ne 0 ]; then
      ERR_VAL=$?
      echo "Erro no: ${ERR_VAL} na criação de ${BKHOME}" |\
         mail -s "`hostname`: *** ERRO ***" elielsonf@prodam.sp.gov.br
   fi
fi

BKHOME=${BKHOME}/${PGDB}
if [ ! -d ${BKHOME} ]; then
   mkdir $BKHOME
   if [ $? -ne 0 ]; then
      ERR_VAL=$?
      echo "Erro no: ${ERR_VAL} na criação de ${BKHOME}" |\
         mail -s "`hostname`: *** ERRO ***" elielsonf@prodam.sp.gov.br
   fi
fi

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

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

echo "********** DDLs do banco <${PGDB}> **********" > ${LOG}

PARAM="-U ${USER} -Fp -v -s -c -X use-set-session-authorization"
${DUMPCMD} ${PARAM} ${PGDB} -f ${ARQ} >> ${LOG} 2>&1

if [ $? -ne 0 ]; then
   ERR_VAL=$?
   echo "*** Erro no: ${ERR_VAL}" >> ${LOG}
   echo "*** Comando: ${DUMPCMD} ${PARAM} ${PGDB} -f ${ARQ} >> ${LOG} 2>&1"
>> ${LOG}
   cat ${LOG} | \
      mail -s "`hostname`: Erro no deump DLL do banco ${PGDB}"
elielsonf@prodam.sp.gov.br
fi

ARQ=${BKHOME}/${PGDB}.data

echo "********** ascii do banco <${PGDB}> **********" >> ${LOG}

PARAM="-U ${USER} -Fp -a -v -X use-set-session-authorization"
${DUMPCMD} ${PARAM} ${PGDB} -f ${ARQ} >> ${LOG} 2>&1

if [ $? -ne 0 ]; then
   ERR_VAL=$?
   echo "*** Erro no: ${ERR_VAL}" >> ${LOG}
   echo "*** Comando: ${DUMPCMD} ${PARAM} ${PGDB} -f ${ARQ} >> ${LOG} 2>&1"
>> ${LOG}
   cat ${LOG} |\
       mail -s "`hostname`: Erro no dump ASCII do banco ${PGDB}"
elielsonf@prodam.sp.gov.br
fi

ARQ=${BKHOME}/${PGDB}.tar

echo "********** tar do banco <${PGDB}> **********" >> ${LOG}

PARAM="-U ${USER} -Ft -v -X use-set-session-authorization"
${DUMPCMD} ${PARAM} ${PGDB} -f ${ARQ} >> ${LOG} 2>&1

if [ $? -ne 0 ]; then
   ERR_VAL=$?
   echo "*** Erro no: ${ERR_VAL}" >> ${LOG}
   echo "*** Comando: ${DUMPCMD} ${PARAM} ${PGDB} -f ${ARQ} >> ${LOG} 2>&1"
>> ${LOG}
   cat ${LOG} |\
      mail -s "`hostname`: Erro no dump tar do banco ${PGDB}"
elielsonf@prodam.sp.gov.br
fi

###
# Parametro de vaccum
###
# -d: dbname
# -z: Calculate statistics for use by the optimizer.
# -f: Perform "full" vacuuming
# -v: verbose
###

echo "********** vacuum de <${PGDB}> **********" >> ${LOG}

PARAM="-U ${USER} -z -v -f"
${VACUUMCMD} ${PARAM} -d ${PGDB} > ${LOG} 2>&1

if [ $? -ne 0 ]; then
   ERR_VAL=$?
   echo "*** Erro no: ${ERR_VAL}" >> ${LOG}
   echo "ERRO: vacuumdb ${VACUUMCMD} ${PARAM} -d ${PGDB} > ${LOG} 2>&1" >>
${LOG}
   cat ${LOG} |\
      mail -s "`hostname`: Erro no vacuum do banco ${PGDB}"
elielsonf@prodam.sp.gov.br
fi

..............................................
A Question...
Since before your sun burned hot in space
and before your race was born,
I have awaited a question.
Elielson Fontanezi
DBA Technical Support - PRODAM
Parque do Ibirapuera s/n - SP - BRAZIL
+55 11 5080 9493

Re: pg_dump command inside shell scripts

From

Richard Huxton

Date:

08 October 2002, 13:14:48

On Tuesday 08 Oct 2002 2:35 pm, Elielson Fontanezi wrote:
> Hi Folks!
>
>     Again I need a help from you!
>
>     I have develop a shell script (seen below) to do dump and vaccum
> automatically.

>     But now I need change the $PGDATA/*hba.conf file as it was originally
> and
> face the password prompt again.

Do a search on "expect" - probably included with your system and designed for
this sort of interactive chatting (traditionally with a modem). There's also
a perl module to do the same.

- Richard Huxton

Re: [ADMIN] pg_dump command inside shell scripts

From

"David F. Skoll"

Date:

08 October 2002, 15:37:46

On Tue, 8 Oct 2002, Elielson Fontanezi wrote:

> to identify Linux user which starts the shell script to avoid password
> prompt.

There's a virtually-undocmented environment variable: PGPASSWORD

    PGPASSWORD="secret-password"
    export PGPASSWORD
    # Now pg_dump will not prompt for password.
    pg_dump -U postgres ...

Make sure you only use this in a script which normal users cannot read,
and on a system which hides root-process environment variables from
being displayed by normal users (with ps).

--
David.

Re: pg_dump command inside shell scripts

From

Roj Niyogi

Date:

08 October 2002, 18:03:41

Hi,

Yes, Richard is right about suggesting the expect command. If you're running a Red Hat-based system, you can download the expect rpm from rpmfind.net.

Once you've installed this, create a file called pg-script.exp and put the following in there:

#!/usr/bin/expect -f

set username [lindex $argv 0]
set password [lindex $argv 1]
set database [lindex $argv 2]

spawn {pg_dump} -u -Ft $database --file=$database.sql.tar
expect "Username:"
send "$username\r"
expect "Password:"
send "$password\r"
expect eof

This will take the username as the 1st argument, password as the second argument and database as the third. Dumps them into local variables and passes the values when prompted by postgresql. It "expects" Username: and when comes across that, it sends the value for username along with a carriage return.

Note that it assumes that expect binary is in the /usr/bin directory.

:)

Roj Niyogi
niyogi@pghoster.com

pgHoster - PostgreSQL Web Hosting
http://www.pghoster.com

Richard Huxton wrote:

On Tuesday 08 Oct 2002 2:35 pm, Elielson Fontanezi wrote:

Hi Folks!
   Again I need a help from you!
   I have develop a shell script (seen below) to do dump and vaccum
automatically.

    But now I need change the $PGDATA/*hba.conf file as it was originally
and
face the password prompt again.

Do a search on "expect" - probably included with your system and designed for 
this sort of interactive chatting (traditionally with a modem). There's also 
a perl module to do the same.

- Richard Huxton

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly