PostgreSQL on Ubuntu on EC2: Backing It All Up

This post continues what I started with "PostgreSQL on Ubuntu on EC2: The Installation Guide". Once you have your PostgreSQL database server instance running, you will need to backup two different things: your database data, and the instance itself. The database data will be backed up using Elastic Block Storage (EBS) snapshots. Once we have the instance running the backups correctly, we will then create an Amazon Machine Image (AMI) that will allow you to launch a new instance to replace the database server in case it goes down.

Backing Up The Database
First, we need to connect to our database server instance via SSH using the ubuntu user.

We will need to install some dependencies to get our backup script to run:

sudo apt-get install build-essential
sudo apt-get install ruby1.8-dev
sudo apt-get install rubygems
sudo gem update --system


You will need to tweak RubyGems so that the update works correctly, as described here.

Now you can install Gemcutter, which is the new ultra cool repository for gems:

sudo gem install gemcutter
sudo gem tumble


Finally we are ready to install the Amazon EC2 rubygem:

sudo gem install amazon-ec2


Now we can create our backup script. Save this code into the ~/ directory under the name backup_database.rb. You will need to substitute the Amazon ACCESS_KEY_ID and SECRET_ACCESS_KEY, as well and entering the correct EBS volume for the DATABASE_VOLUME constant:

#!/usr/bin/env ruby

require 'rubygems'
require 'AWS'

ACCESS_KEY_ID = 'YOUR_ACCESS_KEY'
SECRET_ACCESS_KEY = 'YOUR_SECRET_ACCESS_KEY'
DATABASE_VOLUME = 'vol-XXXXXXXX'

puts "Starting database snapshot..."
ec2 = AWS::EC2::Base.new(:access_key_id => ACCESS_KEY_ID, :secret_access_key => SECRET_ACCESS_KEY)
ec2.create_snapshot(:volume_id => DATABASE_VOLUME)
puts "Database snapshot completed."


Due to the finicky way that Ruby runs as part of a cron job, we are best off creating a shell script that then runs the Ruby backup script. Save this code into the ~/ directory under the name backup_db.sh:

#!/bin/sh
cd /home/ubuntu
ruby /home/ubuntu/backup_database.rb


Don't forget to make the backup shell script executable:

chmod +x /home/ubuntu/backup_db.sh


Now we just need to configure this script to run as part of a cron job, so that the backups take place automatically. The crontab command brings up the list of configured cron tasks for the current user:

crontab -e


This example crontab entry runs the backup daily at midnight, but you may want it to run more frequently:

0 0 * * * /home/ubuntu/backup_db.sh


At this point, you should have a fully functional automated backup system. Verify after midnight that the script has run as you expect, by looking to see if a new snapshot has been created, using Elastifox or however you administrate your EC2 instances.

Creating The AMI
Creating the AMI to backup the entire database instance is pretty easy. First, you need to upload the PEM files. Remember you are authenticating as the "ubuntu" user:

scp -i id_rsa-gsg-keypair pk-YOUR.pem cert-YOUR.pem ubuntu@domU-12-34-31-00-00-05.usma1.compute.amazonaws.com:


Use your SSH connection into the database server instance to copy the PEM files to the /mnt directory:

sudo cp /home/ubuntu/*.pem /mnt


Now create the bundle. Make sure you use your Amazon account number (without dashes) as the value for the -u parameter. This can take quite a while, so do not get impatient:

sudo ec2-bundle-vol -d /mnt -k /mnt/pk-YOUR.pem -c /mnt/cert-YOUR.pem -r i386 -u YOURUSERACCOUNTNUMBER


You can now upload the bundle to your Amazon S3 account, in preparation for making available as an AMI. Use something versioned for the -b parameter which is the name of the bundle:

sudo ec2-upload-bundle -b my-database-server-1.0-ami -m /mnt/image.manifest.xml -a YOUR_ACCESS_KEY -s YOUR_SECRET_ACCESS_KEY


Final step is going back to your local machine, and making the newly created bundle available to be used to start a new instance:

ec2-register my-database-server-1.0-ami/image.manifest.xml


You can now now launch a brand new database server instance based on this AMI, and it will be a clone of your existing database server. This is the procedure you would follow if you need to restore your database server instance from backups.

Restoring Your Database Server From The Backups
In the case that something goes terribly terribly wrong, you can get back to normal as follows:
- startup a new EBS volume from your most recent snapshot backup,
- start up a new server from your database AMI
- configure your new server instance to use the new volume started from the backup data
- switch your elastic IP to point to the new server, or update the references in your application to point to the new server

This concludes part 2 of the great PostgreSQL config post for the EC2 cloud. Hopefully it will help you with a nice simple way to take the basic PostgreSQL instance that you got up and running on Ubuntu/EC2 using the directions in the part 1 post, and add the confidence that backed up data and a completely reproducible configuration provides.

PostgreSQL on Ubuntu on EC2: The Installation Guide

For some time, I have had clients hosting a couple different applications on Amazon EC2 using Ubuntu. One of these apps uses PostgreSQL, and has been running without event for quite a while. Yesterday, I got to catch up for lost time, by spending the entire day wrestling with data recovery issues related to a failed apt-get upgrade on an important database server. Luckily, the awesome Eric Hammond was around on IRC, came to my rescue, and coached my thru my self-inflicted pain.

If you are not interested in PostgreSQL, you probably just stop here. Nothing to see folks, move along. However, if you are looking for the well-lit path to getting PostgreSQL installed on Amazon EC2 will all the trimmings, read on.

I went to find various web pages to use as source material, expecting since the last time I went through this, someone would have written a nice definitive guide to installing PostgreSQL on Ubuntu, running it on a dedicated instance on Amazon EC2, and using Elastic Block Storage (EBS). Naturally, you want to be using the XFS file system too. However, no such luck: just a big collection of pages of instructions on the various parts, without any nice simple path to getting things working together.

Hence, this post tries to provide a set of instructions for getting things working, and avoiding a couple of problems that I have run into while running Postgres in production for the last couple years.

Step 0 - You are signed up for Amazon EC2, no? If not, there are plenty of pages with instructions on how to do so.

Step 1 - Choose your AMI
There are several AMI's available to you. I currently run Hardy 8.04 LTS x86 architecture in the USA, so I am using ami-5d59be34, but you may have other requirements. The Ubuntu EC2 starter guide has good info on your options.

Step 2 - Launch your instance
I like to use Elasticfox, cause I am super lazy. The command line works well, also.

What size instance? This AMI supports small and medium. PostgreSQL is pretty efficient these days, and especially using a dedicated instance and not running anything but the database server improves raw database performance considerably. You would probably be pretty surprised how well a small instance can perform, but choose medium if you think you will have more significant needs.

One key pattern I use for my EC2 hosted apps, is creating security groups in EC2 to separate my database servers from the public internet. I never use the default security group, but instead create a group for each tier of my application like "database", "web", "transcoder" and then allow specific groups to communicate with each other.

Step 3 - Create the EBS Volume
You can do this via Elasticfox, or via command line. Either way, make sure you do two key things: make sure you create the EBS volume in the same availabilty zone as your database server instance, and also make sure you create a volume with enough space. Here is how you would use the command line tools to create a 10GB volume in the 'us-east-1a' zone:

ec2-create-volume -z us-east-1a -s 10

One the volume is ready, attach it to the database instance. For example, this attaches an EBS volume named 'vol-VVVV1111' to the instance 'i-IIII1111' on device /dev/sdh:

ec2-attach-volume -d /dev/sdh -i i-IIII1111 vol-VVVV1111


Step 4 - Connect to the database instance
You need to SSH in to configure you new instance. Remember, you cannot connect as 'root' user in Ubuntu, you need to connect using the 'ubuntu' user. This page has good details about using sudo and SSH on the official Ubuntu EC2 AMIs.

OK, so now you are connected via SSH to your server. Of course, start with the usual update/upgrade:

sudo apt-get update && sudo apt-get upgrade -y


Step 5 - Install XFS
We will need to install the XFS file system. Actually, your could use some other file system, but XFS is quite mature and has good performance. Plus if you are crazy, you can scale up to a massive virtual RAID drive that will cost $4000 per month.


sudo apt-get install -y xfsprogs
Step 6 - Format the EBS volume using XFS
We need to install a file system on the EBS volume before we can do anything with it. Here is an example:

sudo modprobe xfs
sudo mkfs.xfs /dev/sdh

echo "/dev/sdh /data xfs noatime 0 0" | sudo tee -a /etc/fstab
sudo mkdir /data
sudo mount /data

Now we have a /data directory that maps to our EBS volume. Anything we write to /data will be persisted, even if the database server instance itself terminates.

Step 7 - Install PostgreSQL
Now we need to get PostgreSQL installed. This page has a very nice simple set of instructions on how to do that correctly for Ubuntu, but here is a synopsis especially for a headless server. First install Postgres:

sudo apt-get install postgresql postgresql-client postgresql-contrib

Now reset the password for the postgres account in the PostgreSQL server:

sudo su postgres -c psql template1
template1=# ALTER USER postgres WITH PASSWORD 'password';
template1=# \q

And then change the password on the user account to match:

sudo passwd -d postgres
sudo su postgres -c passwd


Now we need to modify the postgres configuration file postgresql.conf. First, to allow other machines to connect to our instance, and also to have PostgreSQL use our nice shiny /data directory.


sudo nano /etc/postgresql/8.3/main/postgresql.conf

Change the line containing

data_directory = '/var/lib/postgresql/8.3/main'

to

data_directory = '/data/main'

Now change

#listen_addresses = 'localhost'

to

listen_addresses = '*'

and also change

#password_encryption = on

to

password_encryption = on

Save the file, then open the pg_hba.conf file so we can control who can access the server:

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database
# super user can access the database using some other method.
# Noninteractive
# access to all databases is required during automatic maintenance
# (autovacuum, daily cronjob, replication, and similar tasks).
#
# Database administrative login by UNIX sockets
local all postgres ident sameuser
# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5

# Connections for all PCs on the subnet
#
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
host all all 0.0.0.0/0 md5 # wide-open, you may want to make this more specific to your database


Step 8 - Move the database files
We need to stop the PostgreSQL server, move the database files to our EBS volume, then restart the server.

sudo /etc/init.d/postgresql-8.3 stop
sudo mv /var/lib/postgresql/8.3/main /data
sudo /etc/init.d/postgresql-8.3 start

You are now running PostgreSQL on Amazon EC2 using EBS for your database, with the XFS file system. Congratulations!

I will write a followup post on how to setup your database server for self-backing up using EBS snapshots, but that is all I have time for right now. Hopefully this pared-down set of instructions has been useful to you. Thanks again to Eric Hammond, and everyone else who's blogs were culled together into this post.

In Search Of Search

My client was having troubles. The site was crashing, seemingly at random. QA was baffled, management concerned. After hunting thru logs, the culprit was found: the ferret search service.

Despite having configured acts_as_ferret to use the DRb server option, the "whole thing" would fall apart on a regular basis whenever the ferret daemon would crash... which was pretty often. The ruby on rails plugin acts_as_ferret would revert to a local mode of operation, which would only make matters worse when competing mongrels would corrupt the index files on disk.

Much has been written already about this by more famous Rubyists then myself, so the time had come to make the switch to Sphinx like all the rest of the cool kids. However, there were a few tricky issues that awaited.

One is that the client site was using PostgreSQL instead of the more common MySQL. This meant you had to have already downloaded the pgsql client libs, and also when compiling the sphinx server use the --with-pgsql flag. However, what they didn't mention was that you would need to also download and include the MySQL client libs AS WELL, even if you were not using MySQL. Good to know... seems like a big of baggage to carry, but whatever.

That was one obstacle avoided. The next would prove to be more subtle and complex. There are four different choices of Ruby on Rails clients for Sphinx:

• acts_as_sphinx


• Sphincter


• UltraSphinx


• Thinking Sphinx

I looked briefly at acts_as_sphinx, but my superficial prejudices against anything named "acts_as_" caused me to continue my search for search. Next, I tried Sphincter, just to prove that I could be accepting about project names. However, I was not successful as getting it working correctly, and project development seemed like it had slowed to near non-existence. The search moved on.

My search was getting desperate as half of the options had been eliminated. I briefly perused UltraSphinx and I started to get a little excited. Development was active, and proper functionality with Postgres was claimed. But there was just one little problem: this client's site is still Rails 1.2.x.

Now, before you all start throwing various disparaging comments my way, consider how much work some clients are willing to pay far vs. something that they can actually see. Multiply that by how long since they started their project, and you are starting to get the idea of where this thing is at. I probably should have MADE them upgrade at that point, but I'm just not that kind of guy.

So that left Thinking Sphinx. I dove in with the manic desperation of someone looking to avoid major amounts of work. That rarely works out well, but in this case it wasn't so bad. The latest trunk of thinking sphinx had comments that said "basic postgres functionality" so perhaps it was possible after all.

Several hours and some major hacking of the original plug-in to actually handle proper psql syntax later, and something was working! I will try to come up with patch to thinking-sphinx for freelancing-god, cause his work was a great help.

Lastly, the delta index. By adding a single field (boolean for MySQL, integer for Postgres) you can remove the need to reindex all data every time a record is updated. Sphinx allows storing the changed records in a separate index (delta) that it will search along with the main index. This way, you do not have to wait for reindexing to be able to search, but you also do not have to reindex the entire data set every time any data within it is changed. You still need to do a complete reindex, but that can occur at off-peak hours, instead of constantly.

The code was deployed, and now all was well with the world. My search was complete.