Install Postgres Server on EC2 Instance

Create Postgres 9.4 / Postgis 2.1 Server EC2 Box

Launch Ubuntu instance

1. Click on Launch Instance` button
2. Choose Ubuntu Server 14.04 LTS (HVM), SSD Volume Type
3. Configure Security Group
   In step 6 Configure Security Group add the rule:
   Type: PostgreSQL
   Protocol: TCP
   PortRange: 5432 (default postgres port)
   Source: 0.0.0.0/0
4. Launch instance and ssh into it (instructions provided by amazon)

Install Postgres and Postgis

1. Install packages

   # Add repositories to source list:
   sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt trusty-pgdg main" >> /etc/apt/sources.list'
   
   # Add keys
   wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
   
   # Install postgres 9.4 and postgis 2.1 (both stable)
   sudo apt-get update
   sudo apt-get install postgresql-9.4-postgis-2.1 postgresql-contrib-9.4

2. Enable postgis

   # Log into psql as default postgres user
   sudo -u postgres psql
   
   # Enable postgis extension
   CREATE EXTENSION postgis;
   
   # Exit psql
   \q
   
   # Swich back to ubuntu user
   exit

3. Update configuration for remote access for clients

   # Edit pg_hba.conf in vim
   sudo vim /etc/postgresql/9.4/main/pg_hba.conf
   
   # Near bottom of file after local rules, add rule (allows remote access):
   hostssl    all             all             0.0.0.0/0               md5
   
   # save file

   # Edit config in vim
   sudo vim /etc/postgresql/9.4/main/postgresql.conf
   
   # Change line 59 to listen to external requests:
   # from
     #listen_addresses = 'localhost'
   # to
     listen_address='*'
     
   # save file

   # Restart postgres server to bring changes in effect
   sudo /etc/init.d/postgresql restart

Restore database from S3 backup

Use pg_dumpall function to backup your database and save it to S3. This dumps the whole database cluster, including users, roles, etc., so we do not need to create role / db before restoring.

1. Get backup file

   # Log in as postgres user
   sudo -i -u postgres
   
   # Use wget to get backup file (allow download in S3 bucket before that)
   wget https://s3.amazonaws.com/db_backup

2. Import data from backup

   # Play in backup
   psql -f db_backup postgres