Cloud66 postgresql with wal backups on S3

README

You need to do base backups often...
sudo -u postgres envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-push /usr/local/pgsql/data
You can check how many objects are not archived:
./var/backup_status.sh

backup_status.sh

#!/bin/bash
source /var/.cloud66_env
(
cat <<-EOF
SELECT suffix, count(*)
    FROM (
        SELECT (regexp_matches(name, E'\[0-9A-F]+\.([^\.]*)$'))[1] AS suffix
        FROM pg_ls_dir('pg_xlog/archive_status') name
    ) AS matches
    GROUP BY suffix;
EOF
) | sudo -u postgres psql -d $POSTGRESQL_DATABASE

deploy_hooks.yml

# http://help.cloud66.com/stack-features/deploy-hooks.html
default: &default
    after_postgresql:
    # Setup Wal-E
      - source: /.cloud66/install.sh
        destination: /tmp/install.sh
        apply_during: all
        target: postgresql
        execute: true
        sudo: true
      - source: /.cloud66/backup_status.sh
        destination: /var/backup_status.sh
        apply_during: all
        target: postgresql
        execute: false
staging:
    <<: *default

production:
    <<: *default

install.sh

#!/bin/bash
BIN_CHECK=/usr/local/bin/wal-e

source /var/.cloud66_env

if [ -f $BIN_CHECK ]
then
	echo "already got wall-e installed"
else
	apt-get install daemontools python-setuptools lzop pv python-dev libevent-dev -y
	easy_install pip
	pip install wal-e
fi

ENV_CHECK=/etc/wal-e.d/env/WALE_S3_PREFIX

if [ -f $ENV_CHECK ]
then
	echo "We already got wall-e env setup"
else
	[ -z "$AWS_BACKUP_SECRET_ACCESS_KEY" ] && echo "need aws secret key" && exit 1;
	[ -z "$AWS_BACKUP_ACCESS_KEY_ID" ] && echo "need aws access key" && exit 1;
	[ -z "$AWS_BACKUP_BUCKET" ] && echo "need aws backup bucket" && exit 1;
	umask u=rwx,g=rx,o=
	mkdir -p /etc/wal-e.d/env
	echo "$AWS_BACKUP_SECRET_ACCESS_KEY" > /etc/wal-e.d/env/AWS_SECRET_ACCESS_KEY
	echo "$AWS_BACKUP_ACCESS_KEY_ID" > /etc/wal-e.d/env/AWS_ACCESS_KEY_ID
	echo "s3://$AWS_BACKUP_BUCKET/" > /etc/wal-e.d/env/WALE_S3_PREFIX
	chown -R root:postgres /etc/wal-e.d
fi

if [ -z `grep wal-push /usr/local/pgsql/data/postgresql.conf`]
then
(
cat <<-EOF
	wal_level = archive
	archive_mode = yes
	archive_command = 'envdir /etc/wal-e.d/env /usr/local/bin/wal-e wal-push %p'
	archive_timeout = 60
EOF
)  >> /usr/local/pgsql/data/postgresql.conf

sudo -u postgres pg_ctl restart -D /usr/local/pgsql/data -m fast

else
	echo "wal-e already in the archive_command"
fi
# TO BASE BACKUP
# We need to do base backups about once a day, otherwise replaying the wal will take forever
# sudo -u postgres envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-push /usr/local/pgsql/data

# TO RESTORE: 
# sudo -u postgres envdir /etc/wal-e.d/pull-env wal-e backup-fetch /usr/local/pgsql/data LATEST
# you can replace LATEST with a position, see: sudo -u postgres envdir /etc/wal-e.d/pull-env wal-e backup-list

# If using a recovery.conf file append restore_command = 'envdir /etc/wal-e.d/env wal-e wal-fetch "%f" "%p"'

# we can use the above to make a failover server