Keine Ahnung, warum es das nicht für pg_dumpall gibt.
pg_dumpall sichert den kompletten Cluster, soll also auch alles wiederherstellen. Das custom Format generiert ja im Endeffekt auch wieder nur reines SQL. Also ist das praktisch egal.
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)$'
Hier die Ausgaben im backup-Ordner. Mit dem letzen Schema hat er Probleme.
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?
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?
awk ist ein äusserst mächtiger Textprozessor, in etwa ähnlich sed. Damit kannst du eine Datei zeilenweise in ihre Bestandteile zerlegen und neu zusammensetzen. Hier gibt er dir allerdings nur das erste Wort einer Zeile, den Namen der Datenbank zurück.
Hast du den Befehl mit copy/paste in der Konsole eingefügt? Wenn ja, dann schreib den awk-Part mal von Hand, bei mir gings dann.