What would be an efficient way to backup several PostgreSQL databases? The
number of DB's is not a constant because it depends on the number of clients
our company has. Because our company is new, we actually have 1 customer,
but in the future we plan to grow so we probably have to manage many DB's.
The application we offer is a web app and we also provide hosting so one
server can have more than one database.

Respectfully,
Jorge Maldonado

Search Discussions

  • Rolando Edwards at Feb 5, 2011 at 10:18 pm
    I wrote a script a few months ago to pg_dump separate databases and archive them by date-named folder, except the template databases

    Enjoy !!!

    #!/bin/sh

    if [ "${1}" == "" ] ; then exit ; fi
    if [ "${2}" == "" ] ; then exit ; fi
    BACKUP_FOLDER=${1}
    FOLDERS_TO_KEEP=${2}

    WHICH=/usr/bin/which
    MKDIR=`${WHICH} mkdir`
    GREP=`${WHICH} grep`
    GZIP=`${WHICH} gzip`
    DATE=`${WHICH} date`
    ECHO=`${WHICH} echo`
    HEAD=`${WHICH} head`
    TAIL=`${WHICH} tail`
    SORT=`${WHICH} sort`
    AWK=`${WHICH} awk`
    CAT=`${WHICH} cat`
    WC=`${WHICH} wc`
    PS=`${WHICH} ps`
    RM=`${WHICH} rm`
    LS=`${WHICH} ls | ${TAIL} -1 | ${AWK} '{print $1}'`

    POSTGRES_RUNNING=0
    PSQL=`${WHICH} psql`
    PG_CTL=`${WHICH} pg_ctl`
    PG_DUMP=`${WHICH} pg_dump`
    FOUND_POSTMASTER=`${PS} -ef | ${GREP} "postmaster -D" | ${GREP} -v grep | ${WC} -l`
    if [ ${FOUND_POSTMASTER} -gt 0 ]
    then
    DATADIR=`${PS} -ef | ${GREP} "postmaster -D" | ${GREP} -v grep | ${SORT} -u | ${AWK} '{print $10}'`
    else
    DATADIR=`${PS} -ef | ${GREP} "postgres -D" | ${GREP} -v grep | ${SORT} -u | ${AWK} '{print $10}'`
    fi
    POSTGRES_RUNNING=`${PG_CTL} status -D ${DATADIR} | ${GREP} -c "server is running"`
    if [ ${POSTGRES_RUNNING} -eq 0 ] ; then exit ; fi

    #
    # Launch the Backup
    #

    cd ${BACKUP_FOLDER}
    DBLISTFILE=/tmp/PG_DBList.txt
    ${PSQL} -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('template0','template1')" > ${DBLISTFILE}
    DBLIST=""
    SPC=""
    for DB in `${CAT} ${DBLISTFILE}`
    do
    DBLIST="${DBLIST}${SPC}${DB}"
    SPC=" "
    done
    BACKUP_DATE=`${DATE} +"%Y%m%d_%H%M%S"`
    ${MKDIR} ${BACKUP_DATE}
    for DB in `${ECHO} ${DBLIST}`
    do
    PGDUMP_FILE=${BACKUP_DATE}/pgData_${DB}.sql.gz
    ${PG_DUMP} ${DB} | ${GZIP} > ${PGDUMP_FILE} &
    done
    wait

    #
    # Delete Old Folders
    #

    FOLDER_LIST=/tmp/dbbackup_folder_list.txt
    FOLDERS_TO_ZAP=/tmp/dbbackup_folder_zaplist.txt
    ${LS} -l | ${GREP} "^drwxr" > ${FOLDERS_TO_ZAP}
    FOLDER_COUNT=`${WC} -l < ${FOLDERS_TO_ZAP}`

    if [ ${FOLDER_COUNT} -gt ${FOLDERS_TO_KEEP} ]
    then
    DIFF=`${ECHO} ${FOLDER_COUNT}-${FOLDERS_TO_KEEP}|bc`
    ${LS} -l | ${GREP} "^drwxr" | ${AWK} '{print $9}' | ${HEAD} -${DIFF} > ${FOLDERS_TO_ZAP}
    for FOLDER_TO_ZAP in `${CAT} ${FOLDERS_TO_ZAP}`
    do
    ${RM} -rf ${FOLDER_TO_ZAP}
    done
    fi

    Rolando A. Edwards
    MySQL DBA (SCMDBA)
    [cid:image001.jpg@01CBC557.E63AC930]
    155 Avenue of the Americas, Fifth Floor
    New York, NY 10013
    212-625-5307 (Work)
    201-660-3221 (Cell)
    AIM & Skype : RolandoLogicWorx
    redwards@logicworks.net
    http://www.linkedin.com/in/rolandoedwards

    From: pgsql-novice-owner@postgresql.org On Behalf Of JORGE MALDONADO
    Sent: Saturday, February 05, 2011 3:44 PM
    To: pgsql-novice@postgresql.org
    Subject: [NOVICE] Backing up several PostgreSQL databases

    What would be an efficient way to backup several PostgreSQL databases? The number of DB's is not a constant because it depends on the number of clients our company has. Because our company is new, we actually have 1 customer, but in the future we plan to grow so we probably have to manage many DB's. The application we offer is a web app and we also provide hosting so one server can have more than one database.

    Respectfully,
    Jorge Maldonado
  • Jasen Betts at Feb 9, 2011 at 12:13 pm

    On 2011-02-05, Rolando Edwards wrote:

    I wrote a script a few months ago to pg_dump separate databases and archive=
    them by date-named folder, except the template databases
    this doesn't backup the users, (and possibly other global things excluded
    from pg_dump output?)

    A few months ago I wrote a perl script to burst pg_dumpall output into
    separate files... it was my first ever perl program written from scratch
    so I favoured understandability over style.

    It's going to fail slightly if it hits a database containing no tables but
    containig a funtion with a line that starts "COPY" (so don't do that)

    #!/usr/bin/perl
    #
    # perl script to split a postgres cluster dump into database dump files
    # also emits input on stdout, use redirection to supress if desired.
    #
    $o=0;

    while( ( $a = <STDIN> ) && ! ( $a =~ /^-- Roles\n$/ ) ){
    $h[$o++] = "$a";
    }

    exit if (! $o);
    open OH, ">!roles.sql";
    print OH @h;
    print @h;

    $o=1;
    do
    {
    if ( $o ){
    if( $a =~ /^\\connect (.*)\n$/ ){
    close OH;
    open OH, ">$1.sql";
    # printf STDERR "dump of $1\n";
    }
    $o=1 if ( $a =~ /^COPY / );
    }else{
    $o=0 if($a="\\.");
    }
    print "$a";
    print OH "$a";
    }
    while( $a = <STDIN> );

    close OH;

    ##END##

    --
    ⚂⚃ 100% natural
  • Lew at Feb 6, 2011 at 6:21 pm

    On 02/05/2011 03:43 PM, JORGE MALDONADO wrote:
    What would be an efficient way to backup several PostgreSQL databases? The
    number of DB's [sic] is not a constant because it depends on the number of clients
    our company has. Because our company is new, we actually have 1 customer, but
    in the future we plan to grow so we probably have to manage many DB's. The
    application we offer is a web app and we also provide hosting so one server
    can have more than one database.
    I am no expert in this area, as undoubtedly some other responders will be, but
    even I know that this depends on how you deploy those databases. Will they be
    colocated on one server? Will you have virtual hosts, with one database (or
    more) on each? Will you have physically separate nodes hosting each database?

    It also depends on the parameters for your backup. Will you pull the systems
    offline to back them up? How much downtime can you allow, if any? How often
    will you back up? What are your operational requirements for restore
    scenarios? (For some reason, restore operations are often much less well
    planned than backup operations.) What does "efficient" mean to you? What are
    the data quality requirements for the restores?

    There are quite a few ways to back up databases, such as PG's own utilities,
    replicators like slony, and hard-drive image backups. I defer to the experts
    and the documentation regarding the strengths and weaknesses of each. You
    should consider an adaptable strategy, so that the technique you use today for
    one database need not be the one you use tomorrow for a thousand.

    There is no "one size fits all" answer. You have to provide the operational
    constraints for the question of the right backup (and restore!) strategy even
    to make sense.

    --
    Lew
    Ceci n'est pas une fenêtre.
    .___________.
    ###] | [###|
    ##/ | *\##|
    #/ * | \#|
    #----|----#|
    * ||
    o * | o|
    _____|_____|
    ===========|

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedFeb 5, '11 at 8:43p
activeFeb 9, '11 at 12:13p
posts4
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase