PostgreSQL 9.1 on Amazon EC2

Amazon's Elastic Block Storage (EBS) may not be a perfect place to host your database e.g., performance and reliability are common gripes (Reddit once went down for a couple of hours due to this) but it's pretty easy to work with and is fairly flexible. You can also save database snapshots. In any case, I think it would be an interesting experiment to setup a Postgres DB on EBS and potentially check how it scales.

EBS falls under the free tier, as long as your EBS storage is under 30 GB, even with multiple EBS "volumes" [source]. Here are the steps I took in setting up Postgres 9.1 on my EC2 instance using EBS as the datastore:

1. Go to your Amazon online console and Click the "Volumes" link located under "Elastic Block Store". The volume which is already present is the root volume.
2. Create a new volume. I set the size as 10GiB. Check that the availability zone is the same as the zone of your root volume.
3. Check your new volume and click "Attach Volume" to associate it to your EC2 instance. My device was set to /dev/sdf:

4. Mount your device (i.e., your EBS volume) to a folder: 
• sudo su - (switches user to root)
• yes | mkfs -t ext3 /dev/sdf (makes an ext filesystem in your device)
• mkdir /pgdata (make some directory)
• mount /dev/sdf /pgdata (mount your device to this directory)
• exit (exit from root)
5. Make yum config changes so that we're able to create our own yum repo to circumvent dependency issues:
• sudo vim /etc/yum.repos.d/amzn-main.repo (At the bottom of the "[amzn-main]" section, after "enabled=1", add "exclude=postgresql*". This tells yum that we don't want to use an amazon repository for packages that meet the postgresql criteria.)
• sudo vim /etc/yum.repos.d/amzn-updates.repo (Add the same exclude to the bottom of the "[amzn-updates]" section)
6. Download the repository/key installation rpm from pgrpms.org:
• wget http://yum.pgrpms.org/reporpms/9.1/pgdg-redhat91-9.1-5.noarch.rpm
• sudo rpm -ivh pgdg-redhat91-9.1-5.noarch.rpm
7. Since the rpm is generated for Red Hat Enterprise Linux 5, we need to make a minor change:
• sudo vim /etc/yum.repos.d/pgdg-91-redhat.repo
• Make the following update:

## ORIGINAL
# baseurl=http://yum.postgresql.org/9.1/redhat/rhel-$releaserver-$basearch
## UPDATED
baseurl=http://yum.postgresql.org/9.1/redhat/rhel-5-$basearch

8. Install PostgreSQL 9.1:
• sudo yum install postgresql91-server
• sudo rm -rf /pgdata/lost+found (postgres' initdb will fail to initialize a database cluster in /pgdata when there are files or directories present)
• sudo chown -R postgres:postgres /pgdata (changes ownership of /pgdata to the postgres user)
• sudo su -
• su - postgres
9. Configure and launch the server:
• /usr/pgsql-9.1/bin/initdb -D /pgdata
• vim /pgdata/postgresql.conf
• Update the line #listen_addresses = 'localhost' to listen_addresses = '*'
• Update the line #port = 5432 to port = 5432
• Update pg_hba.conf (for client authentication). Navigate to the bottom of the file (vim /pgdata/pg_hba.conf) and change to:

TYPE    DATABASE        USER            CIDR-ADDRESS            METHOD

# "local" is for Unix domain socket connections only
local   all             postgres                                trust
# IPv4 local connections:
host    all             power_user      0.0.0.0/0               md5  
# IPv6 local connections:
host    all             all             ::1/128                 md5

10. Start the server and create the database for "power_user":
• /usr/pgsql-9.1/bin/pg_ctl start -D /pgdata
• /usr/pgsql-9.1/bin/createuser power_user (press "y" when prompted)
• /usr/pgsql-9.1/bin/psql -p 5432
• postgres=# ALTER USER power_user WITH PASSWORD 'pwd';
• postgres=# CREATE DATABASE foo WITH OWNER power_user;
• postgres=# \q
11. In your Amazon console, you need to make sure that you update your security groups and add a Custom TCP rule for the port 5432. This is to access your database remotely:

12. The following are your database properties:
• URL is jdbc:postgresql://yourwebsite.com:5432/foo (note that you can replace yourwebsite.com with the Elastic IP of your instance)
• Username is power_user
• Password is pwd

You can pgAdmin to connect and manage your database remotely if you want. Other tools like DbVisualizer also provide a GUI for you to view your schema and data stored in your database (DBVisualizer even allows you to view blob data).

(This post is based on Postgres 9.0 in EC2.)