Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Backup-Script for Postgres

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Backup-Script for Postgres
# 1  
Old 12-18-2009
parentheses () not balanced
Hi,
I'm working with postgres. Now I found a script to backup my databases.

Code:
# #!/bin/bash 
# 
# # setup environment 
PG_PORT=5432 
 
PG_HOME=/usr/lib/postgresql/8.3 
PG_VAR=/backup 
 
PG_DATA=/backup/data 
PG_BACKUPS=/backup/backup 
PG_LOGS=/backup/logs 
 
PG_BIN=/usr/lib/postgresql/8.3/bin 
PG_DUMPALL=/usr/lib/postgresql/8.3/bin/pg_dumpall 
PG_DUMP=/usr/lib/postgresql/8.3/bin/pg_dump 
PG_PSQL=/usr/lib/postgresql/8.3/bin/psql 
PG_VACUUMDB=/usr/lib/postgresql/8.3/bin/vacuumdb 
 
PG_BACKUP=/backup/backup/17122009_1400 
PG_LOG_FILE=/backup/logs/backup_17122009_1400.log 
 
COMPRESS="bzip2 -9" 
DUMPALL_ARGS="-p 5432 -c -g -o -U postgres" 
DUMP_ARGS="-p 5432 -c -o -F t -U postgres" 
VACUUM_ARGS="-z -p 5432 -d -U postgres" 
PSQL_ARGS="-p 5432 -U postgres -q -c" 
 
echo "`date`: Script $0 starting" >> backup_17122009_1400.log 2>&1 
 
# Make the backup directory 
 
mkdir -p /backup/backup/17122009_1400 >> backup_17122009_1400.log 2>&1 
 
# Iterate over all databases 
databases=`/usr/lib/postgresql/8.3/bin/psql -p 5432 -U postgres -q -c "\l" 2>> /backup/logs/backup_17122009_1400.log | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'}` 
for database in $databases 
do 
 
#   echo "`date`: Vacuuming database '$database'" >> /backup/logs/backup_17122009_1400.log 2>&1 
#   /usr/lib/postgresql/8.3/bin/vacuumdb -z -p 5432 -d -U postgres $database  >> /backup/logs/backup_17122009_1400.log 2>&1 
#   echo "`date`: Vacuuming of database '$database' complete" >> /backup/logs/backup_17122009_1400.log 2>&1 
 
    PG_DUMP_DB_FILE=/backup/backup/17122009_1400/backup.17122009_1400.$database.tar.bz2 
    echo "`date`: Backing up database '$database' to '$PG_DUMP_DB_FILE'" >> /backup/logs/backup_17122009_1400.log 2>&1 
    /usr/lib/postgresql/8.3/bin/pg_dump -p 5432 -c -o -F t -U postgres $database 2>> /backup/logs/backup_17122009_1400.log | bzip2 -9 > $PG_DUMP_DB_FILE 2>> /backup/logs/backup_17122009_1400.log 
    echo "`date`: Backing up database '$database' to '$PG_DUMP_DB_FILE' complete" >> /backup/logs/backup_17122009_1400.log 2>&1 
 
    # iterate over all schemas in this database 
    schemas=`/usr/lib/postgresql/8.3/bin/psql -p 5432 -U postgres -q -c "\dn" -d $database 2>> /backup/logs/backup_17122009_1400.log | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'}` 
    for schema in $schemas 
    do 
        PG_DUMP_SCHM_FILE=/backup/backup/17122009_1400/backup.17122009_1400.$database.$schema.tar.bz2 
        echo "`date`: Backing up schema '$schema' of database '$database' to '$PG_DUMP_SCHM_FILE'" >> /backup/logs/backup_17122009_1400.log 2>&1 
        /usr/lib/postgresql/8.3/bin/pg_dump -p 5432 -c -o -F t -U postgres -n $schema $database 2>> /backup/logs/backup_17122009_1400.log |  bzip2 -9 > $PG_DUMP_SCHM_FILE 2>> /backup/backup/logs/backup_17122009_1400.log 
        echo "`date`: Backing up schema '$schema' of database '$database' to '$PG_DUMP_SCHM_FILE' complete" >> /backup/logs/backup_17122009_1400.log 2>&1 
    done 
done

But there is an error. Here the log-file:
Code:
Do 17. Dez 15:33:23 CET 2009: Backing up schema '(1' of database 'alktemplate' to '/backup/backup/17122009_1400/backup.17122009_1400.alktemplate.(1.tar.bz2' complete 
Do 17. Dez 15:33:23 CET 2009: Backing up database 'augsburg_doms' to '/backup/backup/17122009_1400/backup.17122009_1400.augsburg_doms.tar.bz2' 
Do 17. Dez 15:33:25 CET 2009: Backing up database 'augsburg_doms' to '/backup/backup/17122009_1400/backup.17122009_1400.augsburg_doms.tar.bz2' complete 
Do 17. Dez 15:33:25 CET 2009: Backing up schema 'public' of database 'augsburg_doms' to '/backup/backup/17122009_1400/backup.17122009_1400.augsburg_doms.public.tar.bz2' 
Do 17. Dez 15:33:27 CET 2009: Backing up schema 'public' of database 'augsburg_doms' to '/backup/backup/17122009_1400/backup.17122009_1400.augsburg_doms.public.tar.bz2' complete 
Do 17. Dez 15:33:27 CET 2009: Backing up schema '(1' of database 'augsburg_doms' to '/backup/backup/17122009_1400/backup.17122009_1400.augsburg_doms.(1.tar.bz2' 
pg_dump: SQL command failed 
pg_dump: Error message from server: FEHLER:  ungültiger regulärer Ausdruck: parentheses () not balanced 
pg_dump: The command was: SELECT oid FROM pg_catalog.pg_namespace n 
WHERE n.nspname ~ '^((1)$'

Why is there the '(1'?

Is the error part of the pgsql-line?

thanks,
karl
Last edited by pludi; 12-18-2009 at 05:00 AM.. Reason: code tags instead of PHP tags please...
# 2  
Old 12-18-2009
Translation of the last 4 lines
Code:
pg_dump: SQL command failed 
pg_dump: Error message from server: ERROR:  invalid regular expression: parentheses () not balanced 
pg_dump: The command was: SELECT oid FROM pg_catalog.pg_namespace n 
WHERE n.nspname ~ '^((1)$'

The location of the problem depends on your view:
  1. It's with pg_dump because it tries to use regular expressions without properly escaping the search term (especially when matching against a fixed string...)
  2. It's with your database for using a schema name of '(1'

Since the faulty SQL statement originates with pg_dump, which is an executable you can't easily change, I doubt you can do very much about a.
# 3  
Old 12-18-2009
Quote:
Originally Posted by pludi
Since the faulty SQL statement originates with pg_dump, which is an executable you can't easily change, I doubt you can do very much about a.
cheers.

Do you know how to solve it in a different way?
# 4  
Old 12-18-2009
You could try by escaping it, eg changing this line
Code:
schemas=`/usr/lib/postgresql/8.3/bin/psql -p 5432 -U postgres -q -c "\dn" -d $database 2>> /backup/logs/backup_17122009_1400.log | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'}`

to
Code:
schemas=`/usr/lib/postgresql/8.3/bin/psql -p 5432 -U postgres -q -c "\dn" -d $database 2>> /backup/logs/backup_17122009_1400.log | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'} | sed 's/(/\\(/g'`

But I haven't tested it.
# 5  
Old 12-18-2009
sed 's/(/\\(/g'`
Code:
sed 's/(/\\(/g'`

Sorry, it's not working.

Code:
Fr 18. Dez 11:03:33 CET 2009: Script ./backupscript starting
Fr 18. Dez 11:03:34 CET 2009: Backing up database 'alktemplate' to '/backup/backup/20091218/backup.20091218_110334.alktemplate.tar.bz2'
Fr 18. Dez 11:03:34 CET 2009: Backing up database 'alktemplate' to '/backup/backup/20091218/backup.20091218_110334.alktemplate.tar.bz2' complete
Fr 18. Dez 11:03:34 CET 2009: Backing up schema 'information_schema' of database 'alktemplate' to '/backup/backup/20091218/backup.20091218_110334.alktemplate.information_schema.tar.bz2'
Fr 18. Dez 11:03:34 CET 2009: Backing up schema 'information_schema' of database 'alktemplate' to '/backup/backup/20091218/backup.20091218_110334.alktemplate.information_schema.tar.bz2' complete
Fr 18. Dez 11:03:34 CET 2009: Backing up schema 'pg_catalog' of database 'alktemplate' to '/backup/backup/20091218/backup.20091218_110334.alktemplate.pg_catalog.tar.bz2'
Fr 18. Dez 11:03:35 CET 2009: Backing up schema 'pg_catalog' of database 'alktemplate' to '/backup/backup/20091218/backup.20091218_110334.alktemplate.pg_catalog.tar.bz2' complete
Fr 18. Dez 11:03:35 CET 2009: Backing up schema 'pg_toast' of database 'alktemplate' to '/backup/backup/20091218/backup.20091218_110335.alktemplate.pg_toast.tar.bz2'
Fr 18. Dez 11:03:35 CET 2009: Backing up schema 'pg_toast' of database 'alktemplate' to '/backup/backup/20091218/backup.20091218_110335.alktemplate.pg_toast.tar.bz2' complete
Fr 18. Dez 11:03:35 CET 2009: Backing up schema 'pg_toast_temp_1' of database 'alktemplate' to '/backup/backup/20091218/backup.20091218_110335.alktemplate.pg_toast_temp_1.tar.bz2'
Fr 18. Dez 11:03:35 CET 2009: Backing up schema 'pg_toast_temp_1' of database 'alktemplate' to '/backup/backup/20091218/backup.20091218_110335.alktemplate.pg_toast_temp_1.tar.bz2' complete
Fr 18. Dez 11:03:35 CET 2009: Backing up schema 'public' of database 'alktemplate' to '/backup/backup/20091218/backup.20091218_110335.alktemplate.public.tar.bz2'
Fr 18. Dez 11:03:36 CET 2009: Backing up schema 'public' of database 'alktemplate' to '/backup/backup/20091218/backup.20091218_110335.alktemplate.public.tar.bz2' complete
Fr 18. Dez 11:03:36 CET 2009: Backing up schema '(5' of database 'alktemplate' to '/backup/backup/20091218/backup.20091218_110336.alktemplate.(5.tar.bz2'
pg_dump: SQL command failed
pg_dump: Error message from server: FEHLER:  ungültiger regulärer Ausdruck: parentheses () not balanced
pg_dump: The command was: SELECT oid FROM pg_catalog.pg_namespace n
WHERE n.nspname ~ '^((5)$'

shell & sed is still new for me.
Last edited by pludi; 12-21-2009 at 09:38 AM..
# 6  
Old 12-21-2009
Here is a script which is working:

Code:
ST="localhost"
PG_PORT="5432"
PG_USER="postgres"
PG_BIN="/usr/lib/postgresql/8.3/bin/"

#LOG_DIR="/backup/logs"
#LOGFILE=$LOG_DIR/"pgBackup_log"
LOGFILE="/backup/logs/pg_backup.log"

ACT_DATE=$(date '+%d-%m-%y')
EXP_DATE=$(date -d '1 day ago' '+%d-%m-%y')
BACKUP_DIR="/backup/tmp"
COMPRESSION="9"
PG_CON="-U $PG_USER"
BACKUP_OPTIONS="$PG_CON -b -C -F c -Z $COMPRESSION"
VACUUM_OPTIONS="$PG_CON -e"

echo "***** DB_BACKUP $ACT_DATE *****" >>$LOGFILE
for db in `$PG_wk {'print $1'}`BIN/psql $PG_CON -lt | sed /\eof/p | grep -v rows\) | awk {'print $1'}`
  do
    # vacuum
    echo $(date '+%c')" -- vacuuming database $db" >> $LOGFILE
    if $PG_BIN/vacuumdb $VACUUM_OPTIONS $db
      then
      echo "OK!" >>$LOGFILE
    else
      echo "No Vacuum in database $db!" >>$LOGFILE
    fi
    # backup
    echo $(date '+%c')" -- backing up database $db" >>$LOGFILE
    if  $PG_BIN/pg_dump $BACKUP_OPTIONS -f $BACKUP_DIR/$db-$ACT_DATE.pgdump $db
      then
      echo "OK, deleting old backup" >>$LOGFILE
      rm ./$db-$EXP_DATE.pgdump
    else
      echo "Database $db not backuped!" >>$LOGFILE
    fi
  done



---------- Post updated at 01:27 PM ---------- Previous update was at 01:26 PM ----------

Code:
awk {'print $1'}`

What is awk doing?
Last edited by pludi; 12-21-2009 at 09:39 AM.. Reason: code tags instead of php please...
# 7  
Old 12-21-2009
It limits the output to the first column (separated by whitespace):
Code:
$ echo "Foo Bar Baz" | awk '{print $1}'
Foo

Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Taking information from a postgres sql query and putting it into a shell script array

I have a postgres sql statement that is the following: select age from students; which gives me the entries: Age --- 10 15 13 12 9 14 10 which is about 7 rows of data. Now what I would like to do with this is use a shell script to create an array age. As a results... (3 Replies)
Discussion started by: JSNY
3 Replies

2. Shell Programming and Scripting

Script for removing Postgres

I made a script to remove Postgres if this is already installed on your system. I have a other script to install Postgres, so this script can be used before you going to install Postgres. Do you like this script? I would love to hear feedback. #!/bin/bash # #################################... (2 Replies)
Discussion started by: dannyvdberg
2 Replies

3. Shell Programming and Scripting

Postgres in Linux

I have this 15 postgres sql queries similar to below to run in linux... Its taking a lot of time to run (3hours) . can any one plz guide me how can i reduce the time of execution execute 'insert into cc.rpt_cons_sub_ccdb_data(report_date, server_name, report_type, count) select... (3 Replies)
Discussion started by: nikhil jain
3 Replies

4. Shell Programming and Scripting

Help with Backup Shell Script for Network Device Configuration backup

HI all, im new to shell scripting. need your guidence for my script. i wrote one script and is attached here Im explaining the requirement of script. AIM: Shell script to run automatically as per scheduled and backup few network devices configurations. Script will contain a set of commands... (4 Replies)
Discussion started by: saichand1985
4 Replies

5. Shell Programming and Scripting

rsync backup mode(--backup) Are there any options to remove backup folders on successful deployment?

Hi Everyone, we are running rsync with --backup mode, Are there any rsync options to remove backup folders on successful deployment? Thanks in adv. (0 Replies)
Discussion started by: MVEERA
0 Replies

6. Shell Programming and Scripting

help copy *.txt to postgres

hi all, a have problem to load *.txt to postgres my database: id_list_ip (nextval) list_ip (varchar) txt file (list_ip.txt) hasilping_10.8.248.1 hasilping_119.110.112.226 hasilping_119.110.125.33 hasilping_125.22.1.25 hasilping_192.168.2.1 hasilping_202.73.96.70 script... (3 Replies)
Discussion started by: adi0926
3 Replies

7. Shell Programming and Scripting

testing postgres connection in shell script

I have a script that does db query in postgres. I'm trying to put in some error checking in the script. I keep running into the password prompt problem where when invalid credentials/or database is put it, it would prompt me for a password which hangs the script. Is there a way that I can check for... (0 Replies)
Discussion started by: zerofire123
0 Replies

8. Shell Programming and Scripting

Postgres : pg_dump Error

hi friends, i have script to take backup of postgresql server ....i am getting error like access denied myscript.sh sendEmail=/home/backup/scripts/sendEmail.pl DAY=`/bin/date +%d-%m-%y` DIR=/home/backup/psql/$DAY test -d $DIR | mkdir -p $DIR dblist="test test1 postgres" for db in... (8 Replies)
Discussion started by: jagnikam
8 Replies

9. Shell Programming and Scripting

how will i connect postgres database from unix shell script

I have got the solution so just closing this issue. (3 Replies)
Discussion started by: jambesh
3 Replies
Login or Register to Ask a Question