How to install PostgreSQL on AWS EC2, and how can I access that

Depending upon the OS (I have considered Amazon Linux, yum based installation)

Amazon Linux (assuming instance is launched and port 5432 is opened for PostgreSQL access and 22 for SSH Access)

SSH into instance and run the following commands -

1. sudo yum update 
2. sudo yum install postgresql postgresql-server postgresql-devel postgresql-contrib postgresql-docs 

Once the above installation completes, initialize the DB

1. sudo service postgresql initdb 

Edit the pg_hba config file -

1. sudo vim /var/lib/pgsql9/data/pg_hba.conf 

Update the bottom of the file, which will read something like this, by default:

1. # TYPE DATABASE USER ADDRESS METHOD 
2.  
3. # "local" is for Unix domain socket connections only 
4. local all all ident 
5. # IPv4 local connections: 
6. host all all 127.0.0.1/32 ident 
7. # IPv6 local connections: 
8. host all all ::1/128 ident 

To read this:

1. # TYPE DATABASE USER ADDRESS METHOD 
2.  
3. # "local" is for Unix domain socket connections only 
4. local all all trust 
5. # IPv4 local connections: 
6. host all power_user 0.0.0.0/0 md5 
7. host all other_user 0.0.0.0/0 md5 
8. host all storageLoader 0.0.0.0/0 md5 
9. # IPv6 local connections: 
10. host all all ::1/128 md5 

Edit the following file -

1. sudo vim /var/lib/pgsql9/data/postgresql.conf 

Uncomment and edit the following lines -

1. #listen_addresses = 'localhost' 

to

1. listen_addresses='*' 

and

1. #port = 5432 

to

1. port = 5432 

Start PostgreSQL server,

1. sudo service postgresql start 

Login into Postgres user -

1. sudo su - postgres 
2. psql -U postgres 

Change your login password -

1. ALTER USER postgres WITH PASSWORD ''; 

You can create more users with their passwords using the following example (replace variables with actual secure passwords)

1. CREATE USER power_user SUPERUSER; 
2. ALTER USER power_user WITH PASSWORD ''; 

For further details visit the following links for detailed installation guides for other OS and compilation from source -

Snowplow (on github)

PostgreSQL Installation guides

How To Install and Use PostgreSQL on Ubuntu 16.04