About
Archives

Setting Up Postgress on AWS

Tue 02 April 2013 | -- (permalink)

I'm doing an analysis of volunteer data and I set up an aws instance with Postgres to act as a database for the project.

I thought it might be helpful to document the steps I took to set this up.

1. Sign up for AWS, and login

I've been using AWS for a while so I really don't have any advice here. I think it was pretty straight forward.

2. Create a new EC2 instance

From the EC2 Management console click the "Launch Instance" Button.

Use the Quick Launch wizard

Chose a working key pair, use Amazon Linux AMI as the Launch configuration, then Continue.

3. Create a security group

I created a new security group that enabled only ssh (22) and postgres (5432). This may have been unnecessary, let me know if the default security groups would have worked.

  • Click on edit details
  • Choose the Security Settings button
  • Create a new Security Group
  • Call it Postgres
  • Add rule SSH
    • Port: 22
    • Source 0.0.0.0/0
  • Add rule Postgres
    • Port: 5432
    • Source 0.0.0.0/0
  • Save
  • Launch

4. Login in to the box

Download the key pem file to your local machine. I called it ami.pem on my machine.

Then use ssh to log in:

ssh -i w.pem ec2-user@ec2-xx-xx-xx-xx.compute-1.amazonaws.com

You can get the DNS from the EC2 config console.

5. Update

Update the installed services using yum:

sudo yum update

6. Install PostgreSQL

Install the psql client:

sudo yum install postgresql

7. Install PostgreSQL server

Install the psql server:

sudo yum install postgresql-server

Configure by following the install notes:

service postgresql initdb
chkconfig postgresql on

8. Start postgres and create a user

Start the postgres server:

service postgresql start

Test login:

sudo su - postgres
psql
postgres=# create user jsmith with password 'testpass';

9. Configure Postgres for external connections

Set up postgres to allow external access. Configure the pg_hba.conf first by adding to the end of /var/lib/pgsql9/data/pg_hba.conf:

# Allow access from any ip
host    all             all             0.0.0.0/0                 md5

Also update the Postgres config file to listen on an address by changing the listen address parameter in /var/lib/pgsql9/data/postgres.conf:

listen_addresses = '*'

10. Restart

Restart postgres to pick up the new config files:

service postgresql restart

Test that your user can login from the server:

psql -U jsmith -h localhost

And now try from your local machine (assuming you have the psql client installed):

psql -U jsmith -h ec2-user@ec2-xx-xx-xx-xx.compute-1.amazonaws.com

11. Success

At this point you should be all set to start loading data. You'll want to create users for the other collaborators and grant them necessary permissions.