From 7355535ee18efc472f5a598b8474e8d05765d6f1 Mon Sep 17 00:00:00 2001 From: Jacob Anawalt Date: Mon, 11 Oct 2010 18:53:00 -0600 Subject: Enable pg_dump format option. The format option of pg_dump enables tar and custom archive file formats in addition to the default plain-text file containing SQL commands. When either the tar or custom format are selected the behaviour of database=all is changed to no longer dump a single file via pg_dumpall. Instead pg_dumpall is called once to export the "global" data (roles & tablespaces) and then pg_dump is called once for each non-template table in the database. To support the GZIP and GZIP_OPTS variables in backupninja and to give the default --rsyncable gzip compression flag a chance at working on a PostgreSQL backup, the custom output is forced to not use compression. Instead compression is done via a pipe to gzip. Hopefully this benefits rsync and rdiff-backup style backups for reduced backup and storage costs that outweigh the restoration ones. --- etc/backupninja.conf.in | 1 + examples/example.pgsql | 13 ++++++ handlers/pgsql.helper.in | 30 ++++++++++++++ handlers/pgsql.in | 100 +++++++++++++++++++++++++++++++++++++++++------ src/backupninja.in | 1 + 5 files changed, 134 insertions(+), 11 deletions(-) diff --git a/etc/backupninja.conf.in b/etc/backupninja.conf.in index dee9fff..7e3d347 100644 --- a/etc/backupninja.conf.in +++ b/etc/backupninja.conf.in @@ -83,6 +83,7 @@ vservers = no # MYSQL=/usr/bin/mysql # MYSQLHOTCOPY=/usr/bin/mysqlhotcopy # MYSQLDUMP=/usr/bin/mysqldump +# PSQL=/usr/bin/psql # PGSQLDUMP=/usr/bin/pg_dump # PGSQLDUMPALL=/usr/bin/pg_dumpall # GZIP=/bin/gzip diff --git a/examples/example.pgsql b/examples/example.pgsql index 42f045e..5a4e02f 100644 --- a/examples/example.pgsql +++ b/examples/example.pgsql @@ -17,6 +17,19 @@ # compress = < yes | no > (default = yes) # if yes, compress the pg_dump/pg_dumpall output. +# format = < plain | tar | custom > (default = plain) +# plain - Output a plain-text SQL script file with the extension .sql. +# When dumping all databases, a single file is created via pg_dumpall. +# tar - Output a tar archive suitable for input into pg_restore. More +# flexible than plain and can be manipulated by standard Unix tools +# such as tar. Creates a globals.sql file and an archive per database. +# custom - Output a custom PostgreSQL pg_restore archive. This is the most +# flexible format allowing selective import and reordering of database +# objects at the time the database is restored via pg_restore. This +# option creates a globals.sql file containing the cluster role and +# other information dumped by pg_dumpall -g and a pg_restore file +# per selected database. See the pg_dump and pg_restore man pages. + ### You can also set the following variables in /etc/backupninja.conf: # PGSQLDUMP: pg_dump path (default: /usr/bin/pg_dump) # PGSQLDUMPALL: pg_dumpall path (default: /usr/bin/pg_dumpall) diff --git a/handlers/pgsql.helper.in b/handlers/pgsql.helper.in index ff1cfd4..82e6b48 100644 --- a/handlers/pgsql.helper.in +++ b/handlers/pgsql.helper.in @@ -67,6 +67,21 @@ pgsql_wizard() { pgsql_compress="compress = no" fi + # pg_dump format, defaults to plain, custom is recommended by PostgreSQL + menuBox "$pgsql_title" "Choose a pg_dump format:" \ + plain "Default plain-text sql script, use with psql." \ + tar "More flexible than the plain, use with pg_restore." \ + custom "The most flexible format, use with pg_restore." + if [ $? = 0 ]; then + result="$REPLY" + case "$result" in + "tar") pgsql_format="format = tar";; + "custom") pgsql_format="format = custom";; + *) pgsql_format = "format = plain";; + esac + fi + + # write config file get_next_filename $configdirectory/20.pgsql cat >> $next_filename < (default = plain) +# plain - Output a plain-text SQL script file with the extension .sql. +# When dumping all databases, a single file is created via pg_dumpall. +# tar - Output a tar archive suitable for input into pg_restore. More +# flexible than plain and can be manipulated by standard Unix tools +# such as tar. Creates a globals.sql file and an archive per database. +# custom - Output a custom PostgreSQL pg_restore archive. This is the most +# flexible format allowing selective import and reordering of database +# objects at the time the database is restored via pg_restore. This +# option creates a globals.sql file containing the cluster role and +# other information dumped by pg_dumpall -g and a pg_restore file +# per selected database. See the pg_dump and pg_restore man pages. +$pgsql_format + ### You can also set the following variables in backupninja.conf: +# PSQL: psql path (default: /usr/bin/psql) # PGSQLDUMP: pg_dump path (default: /usr/bin/pg_dump) # PGSQLDUMPALL: pg_dumpall path (default: /usr/bin/pg_dumpall) # PGSQLUSER: user running PostgreSQL (default: postgres) diff --git a/handlers/pgsql.in b/handlers/pgsql.in index 0b7badf..0564f5a 100644 --- a/handlers/pgsql.in +++ b/handlers/pgsql.in @@ -8,6 +8,8 @@ getconf backupdir /var/backups/postgres getconf databases all getconf compress yes getconf vsname +# format maps to pg_dump --format= option, old/default was plain +getconf format plain localhost=`hostname` @@ -35,17 +37,31 @@ fi # Make sure that the system to backup has the needed executables if [ $usevserver = yes ]; then debug "Examining vserver '$vsname'." - if [ "$databases" == "all" ]; then + if [ "$databases" == "all" ] && [ "$format" = "plain" ]; then [ -x "$vroot`$VSERVER $vsname exec which $PGSQLDUMPALL`" ] || \ fatal "Can't find $PGSQLDUMPALL in vserver $vsname." + elif [ "$format" != "plain" ]; then + [ -x "$vroot`$VSERVER $vsname exec which $PGSQLDUMPALL`" ] || \ + fatal "Can't find $PGSQLDUMPALL in vserver $vsname." + [ -x "$vroot`$VSERVER $vsname exec which $PGSQLDUMP`" ] || \ + fatal "Can't find $PGSQLDUMP in vserver $vsname." + [ -x "$vroot`$VSERVER $vsname exec which $PSQL`" ] || \ + fatal "Can't find $PSQL in vserver $vsname." else [ -x "$vroot`$VSERVER $vsname exec which $PGSQLDUMP`" ] || \ fatal "Can't find $PGSQLDUMP in vserver $vsname." fi else - if [ "$databases" == "all" ]; then + if [ "$databases" == "all" ] && [ "$format" = "plain" ]; then + [ -x "`which $PGSQLDUMPALL`" ] || \ + fatal "Can't find $PGSQLDUMPALL." + elif [ "$format" != "plain" ]; then [ -x "`which $PGSQLDUMPALL`" ] || \ fatal "Can't find $PGSQLDUMPALL." + [ -x "`which $PGSQLDUMP`" ] || \ + fatal "Can't find $PGSQLDUMP." + [ -x "`which $PSQL`" ] || \ + fatal "Can't find $PSQL." else [ -x "`which $PGSQLDUMP`" ] || \ fatal "Can't find $PGSQLDUMP." @@ -71,6 +87,30 @@ chown $pguid $vroot$backupdir debug "chmod 700 $vroot$backupdir" chmod 700 $vroot$backupdir + +# If we are using the custom (best) or tar pg_dump format, and +# dumping "all" databases, we will substitute "all" for a list +# of all non-template databases to avoid the use of pg_dumpall. +dumpglobals="no" +if [ "$databases" = "all" ] && [ "$format" != "plain" ]; then + cmdprefix="" + if [ "$usevserver" = "yes" ]; then + cmdprefix="$VSERVER $vsname exec " + fi + execstr="${cmdprefix} su - $PGSQLUSER -c 'psql -AtU $PGSQLUSER -c \"SELECT datname FROM pg_database WHERE NOT datistemplate\"'" + debug execstr + dblist="" + for db in $(eval $execstr 2>&1); do + dblist="$dblist $db" + done + if [ "$dblist" != "" ]; then + databases="$dblist" + fi + # Dump globals (pg_dumpall -g) for roles and tablespaces + dumpglobals="yes" +fi + + # if $databases = all, use pg_dumpall if [ "$databases" == "all" ]; then if [ $usevserver = yes ]; then @@ -101,20 +141,58 @@ if [ "$databases" == "all" ]; then # else use pg_dump on each specified database else - for db in $databases; do + # If we're not doing plain format, database=all may now be database=list + # so we track the database=all selection in dumpglobals which tells us + # to also dump the roles and tablespaces via pg_dumpall -g + if [ "$dumpglobals" = "yes" ]; then + globalscmd="" + if [ "$compress" == "yes" ]; then + globalscmd="set -o pipefail ; $PGSQLDUMPALL -g | $GZIP $GZIP_OPTS > '$backupdir/globals.sql.gz'" + else + globalscmd="$PGSQLDUMPALL -g > '$backupdir/globals.sql'" + fi if [ $usevserver = yes ]; then - if [ "$compress" == "yes" ]; then - execstr="$VSERVER $vsname exec su - $PGSQLUSER -c \"set -o pipefail ; $PGSQLDUMP $db | $GZIP $GZIP_OPTS > '$backupdir/${db}.sql.gz'\"" - else - execstr="$VSERVER $vsname exec su - $PGSQLUSER -c \"$PGSQLDUMP $db | > '$backupdir/${db}.sql'\"" - fi + execstr="$VSERVER $vsname exec su - $PGSQLUSER -c \"$globalscmd\"" else - if [ "$compress" == "yes" ]; then - execstr="su - $PGSQLUSER -c \"set -o pipefail ; $PGSQLDUMP $db | $GZIP $GZIP_OPTS > '$backupdir/${db}.sql.gz'\"" + execstr="su - $PGSQLUSER -c \"$globalscmd\"" + fi + debug "$execstr" + if [ ! $test ]; then + output=`eval $execstr 2>&1` + code=$? + if [ "$code" == "0" ]; then + debug $output + info "Successfully finished pgsql globals (roles and tablespaces) dump" else - execstr="su - $PGSQLUSER -c \"$PGSQLDUMP $db > '$backupdir/${db}.sql'\"" + warning $output + warning "Failed to dump pgsql globals (roles and tablespaces)" fi fi + fi + for db in $databases; do + dumpext=".sql" + if [ "$format" != "plain" ]; then + dumpext="pg_dump" + fi + # To better support the backupninja global GZIP and rsync-friendly GZIP_OPTS + # the custom archive format is told to disable compression. The plain format + # is uncompressed by default and the tar format doesn't support pg_dump compression. + disablecustomcompress="" + if [ "$format" = "custom" ]; then + disablecustomcompress="--compress=0" + fi + dumpcmd="" + globalscmd="" + if [ "$compress" == "yes" ]; then + dumpcmd="set -o pipefail ; $PGSQLDUMP --format=$format ${disablecustomcompress} $db | $GZIP $GZIP_OPTS > '$backupdir/${db}.${dumpext}.gz'" + else + dumpcmd="$PGSQLDUMP --format=$format ${disablecustomcompress} $db | > '$backupdir/${db}.${dumpext}'" + fi + if [ $usevserver = yes ]; then + execstr="$VSERVER $vsname exec su - $PGSQLUSER -c \"$dumpcmd\"" + else + execstr="su - $PGSQLUSER -c \"$dumpcmd\"" + fi debug "$execstr" if [ ! $test ]; then output=`eval $execstr 2>&1` diff --git a/src/backupninja.in b/src/backupninja.in index 789debd..aadc162 100755 --- a/src/backupninja.in +++ b/src/backupninja.in @@ -476,6 +476,7 @@ getconf MYSQLADMIN /usr/bin/mysqladmin getconf MYSQL /usr/bin/mysql getconf MYSQLHOTCOPY /usr/bin/mysqlhotcopy getconf MYSQLDUMP /usr/bin/mysqldump +getconf PSQL /usr/bin/psql getconf PGSQLDUMP /usr/bin/pg_dump getconf PGSQLDUMPALL /usr/bin/pg_dumpall getconf PGSQLUSER postgres -- cgit v1.2.3