- Click on Launch Instance` button
- Choose Ubuntu Server 14.04 LTS (HVM), SSD Volume Type
- 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
- Launch instance and ssh into it (instructions provided by amazon)
-
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
-
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
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.
-
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
-
Import data from backup
# Play in backup psql -f db_backup postgres