Ich habe das Script jetzt umgeschrieben, es werden nur die public-Schemas der DBs gesichert.
# #!/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 public" -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
Ein Fehler kommt aber immer noch:
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)$'
Kennt jemand hier ein Forum, bei dem shell-Probleme gepostet werden können?
kretschmer@tux:~$ psql test -c "\dn"
List of schemas
Name | Owner
--------------------+------------
(1 | kretschmer
information_schema | postgres
pg_catalog | postgres
pg_toast | postgres
pg_toast_temp_1 | postgres
public | postgres
symbiont | kretschmer
(7 rows)
kretschmer@tux:~$ psql test -c "SELECT oid, n.nspname FROM pg_catalog.pg_namespace n WHERE n.nspname ~ E'^\\\(1$'"
oid | nspname
-------+---------
19261 | (1
(1 row)
Was ich grad nicht versteh: da ist ein -c “\dn public”, was Dir alle Schemas auflisten soll. Dürfte es aber nicht tun, es sollte einklich nur alle listen, die “public” sich nennen. Davon abgesehen, sicherst Du wohl erst mal alle Datenbanken komplett und dann nochmals alle Datenbanken und Schemas separat. Absicht?
was ak sagen wollt … in regulaeren ausdruecken (pg - java - etc) stehen normale klammern fuer “rueckgabewerte” … du kannst so in verschiedenen fkt. welche RE verwenden die ensprechenden stellen referenzieren …
d.h. wenn du nach einer klammer suchst musst du die escapen … ueblicherweise mit backslashs
In Anlehnung an das erste Skript hab ich gestern mal das untere Skript zusammengenagelt. Es macht ein Vacuum und ein Backup jeder Datenbank im Cluster und funktioniert hier testweise bisher ganz gut. Vielleicht hilft es dir ein wenig.
Grüssle Uwe
#! /bin/bash
PG_HOST="localhost"
PG_PORT="5432"
PG_USER="postgres"
PG_BIN="/usr/local/pgsql/bin/"
#LOG_DIR="/backup/backup_pg"
#LOGFILE=$LOG_DIR/"pgBackup_log"
LOGFILE="pg_backup.log"
ACT_DATE=$(date '+%d-%m-%y')
EXP_DATE=$(date -d '1 week ago' '+%d-%m-%y')
BACKUP_DIR="/srv/geodata/tmp"
COMPRESSION="9"
PG_CON="-U $PG_USER -h $PG_HOST -p $PG_PORT"
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_BIN/psql -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