Install Postgres 9.3 server on EC2 Ubuntu instance (14.04)

install_setup_postgres.md

Create Postgres 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

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.3 (bosolete version)
   sudo apt-get update
   sudo apt-get install postgresql-9.3 postgresql-contrib-9.3

2. Update configuration for remote access for clients

   # Edit pg_hba.conf in nano
   sudo nano /etc/postgresql/9.3/main/pg_hba.conf
   
   # Replace the following line:
   
   # "local" is for Unix domain socket connections only
   local    all             all                                    peer
   
   # with:
   
   # "local" is for Unix domain socket connections only
   local    all             all                                    md5
   
   # save file

   # Edit config in nano
   sudo nano /etc/postgresql/9.3/main/postgresql.conf
   
   # Change "listen_addresses" option 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