pg-to-s3 Backup Script

Posted on 28 Jan 2015 by Eric Oestrich

This is a small script I wrote to backup a postgres database to S3 via cron. It runs nightly and saves 30 days of databases. The README has a good bit of information about what is required to set it up. I will copy some of it here though.


A .pgpass file is required for this script as a password cannot be passed in. The format is listed below. It should be only accessible to the user running the script and will be located in the home folder. See the postgress wiki for more information.



This is the command I used for cron, it sets up the environment for rbenv.

0 0 * * * /bin/bash -c 'PATH=/opt/rbenv/shims:/opt/rbenv/bin:$PATH RBENV_ROOT=/opt/rbenv ruby /home/deploy/pg-to-s3/backup.rb'
#!/usr/bin/env ruby
require 'time'
require 'aws-sdk'
require 'fileutils'

# .pgpass file required, it is in the following format
#    hostname:port:database:username:password
pg_user = ENV["POSTGRES_USERNAME"] || "postgres"
pg_host = ENV["POSTGRES_HOST"] || "localhost"
pg_port = ENV["POSTGRES_PORT"] || "5432"
pg_database = ENV["POSTGRES_DATABASE"]

bucket_name = ENV["BACKUP_BUCKET_NAME"]
project_name = ENV["PROJECT_NAME"]

# backup pg

time ="%Y-%m-%d")
filename = "backup.#{}.#{time}.sql.dump"

`pg_dump -Fc --username=#{pg_user} --no-password --host #{pg_host} --port #{pg_port} #{pg_database} > #{filename}`

# verify file exists and file size is > 0 bytes
unless File.exists?(filename) && > 0
  raise "Database was not backed up"

s3 = AWS.s3
bucket = s3.buckets[bucket_name]
object = bucket.objects["#{project_name}/#{filename}"]
object.write(, {
  :acl => :private,

if object.exists?

DAYS_30 = 30 * 24 * 60 * 60

objects = do |object|
  time ="/").last.split(".")[1].to_i)
  time < - DAYS_30

objects.each do |object|
comments powered by Disqus
Creative Commons License
This site's content is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License unless otherwise specified. Code on this site is licensed under the MIT License unless otherwise specified.