Project

General

Profile

Archiving

There are two possible methods of recovery:
  1. From a full dump, these are done once a month.
  2. From the write ahead logs. These will give an up to the minute recovery when used in conjunction with the base backup.
  • postgresql.conf
    emacs /var/lib/pgsql/data/postgresql.conf -nw
        bytea_output='escape'
        wal_level = hot_standby
        archive_mode = on
        archive_command = 'test ! -f ~/data/pg_xlog/archive/%f && cp -p %p ~/data/pg_xlog/archive/%f && chmod 640 ~/data/pg_xlog/archive/%f'
  • create directories
    mkdir /var/lib/pgsql/data/pg_xlog/archive
    chmod 750 /var/lib/pgsql/data/pg_xlog/archive
    touch /var/lib/pgsql/data/pg_xlog/archive/current
    chmod 640 /var/lib/pgsql/data/pg_xlog/archive/current
    mkdir /var/lib/pgsql/scripts
  • create log archive script, this archives the write ahead logs which can be used to make a complete recovery
    cat > /var/lib/pgsql/scripts/pg-archive-current-wal-cron.sh << EOF
    #!/bin/sh
    CURRENT=\`find ~/data/pg_xlog -maxdepth 1 -type f -printf "%T@ %p\n" | grep -v history | grep -v backup | sort -n | tail -n 1 | cut -d' ' -f 2-\`
    rsync -t \$CURRENT ~/data/pg_xlog/archive/current
    EOF
  • make log archive script executable
    chmod 700 /var/lib/pgsql/scripts/pg-archive-current-wal-cron.sh
  • run log archive script every minute
    cat > /etc/cron.d/pg-archive << EOF
    */1 * * * * postgres /var/lib/pgsql/scripts/pg-archive-current-wal-cron.sh &> /dev/null
    EOF
  • create base backup script
    cat > /var/lib/pgsql/scripts/pg-base-backup-cron.sh << EOF
    #!/bin/sh
    
    LABLE=/var/lib/pgsql/data/pg_xlog/archive
    START=\`mktemp -t start.XXXXXXXXXX\`
    echo "SELECT pg_start_backup('\$LABLE');" > \$START
    STOP=\`mktemp -t stop.XXXXXXXXXX\`
    echo "SELECT pg_stop_backup();" > \$STOP
    TS=\`date +%Y-%m-%d\`
    
    psql < \$START
    tar -czf ~/data/pg_xlog/archive/db-base-backup-\${TS}.gz ~/data --exclude ~/data/pg_xlog
    psql < \$STOP
    
    chmod 640 ~/data/pg_xlog/archive/db-base-backup-\${TS}.gz
    rm -rf \$START \$STOP
    EOF
  • make base backup script executable
    chmod 700 /var/lib/pgsql/scripts/pg-base-backup-cron.sh
  • run base backup script every month
    cat > /etc/cron.monthly/pg-base-backup << EOF
    #!/bin/sh
    su - postgres /var/lib/pgsql/scripts/pg-base-backup-cron.sh &> /dev/null
    EOF
  • make base backup cron executable
    chmod 700 /etc/cron.monthly/pg-base-backup
  • create dump script
    cat > /var/lib/pgsql/scripts/pg-dump-cron.sh << EOF
    #!/bin/sh
    rm -f ~/data/pg_xlog/archive/db-dump-*
    TS=\`date +%Y-%m-%d\`
    pg_dumpall | gzip > ~/data/pg_xlog/archive/db-dump-\${TS}.gz
    chmod 640 ~/data/pg_xlog/archive/db-dump-\${TS}.gz
    EOF
  • make dump script executable
    chmod 700 /var/lib/pgsql/scripts/pg-dump-cron.sh
  • run dump script every month
    cat > /etc/cron.monthly/pg-dump << EOF
    #!/bin/sh
    su - postgres /var/lib/pgsql/scripts/pg-dump-cron.sh &> /dev/null
    EOF
  • make dump cron executable
    chmod 700 /etc/cron.monthly/pg-dump