HOW TO INSTALL POSTGRESQL ON AN AWS EC2 INSTANCE

1.) Launch an Amazon Linux AMI such as the one picture above in to an existing VPC or new VPC.

2.) Create a security group and add a rule that opens ports 5432 (PostgreSQL) and 22 (SSH) in your security group

3.) SSH into the instance and run the following commands

to connect to your instance using SSH in a terminal window.

To use the ssh command to connect to the instance. You must specify :

A.) The private key (.pem) file. - [Generated during EC2 creation]

B.) The user name for your AMI - [EC2]

C.) The public DNS name for your instance. - [Found in the AWS Console]

For our example we are using an Amazon Linux AMI so the user name is:

"ec2-user"

4.) A simple way to connect to your EC2 instance is to choose the connect button on the AWS console. It will give you the complete string you need to enter in at the terminal.

5.) Remember to change the permissions on your .pem file by using the chmod command followed by the name of your .pem file

"EX: chmod 400 yourEC2instance.pem"

Once you have established a connection to your EC2 instance it's time to prepare the EC2 instance for installing PostgresSQL.

6.) Update the EC2 instance by running the following command:

$ sudo yum update

7.) Once the EC2 instance has been updated run the following command to install PostgresSQL on the EC2 instance:

$ sudo yum install postgresql postgresql-server postgresql-devel postgresql- contrib postgresql-docs

8.) Once the installation is complete you must initialize the database by running the following command:

$ sudo service postgresql initdb

There are 2 more files on the EC2 instance that need to be edited.

The file names are pg_hba.conf and postgresql.conf.

9.) The files should be located in /var/lib/pgsql/data/

If you are not sure where the files are located perform the below command to locate the files so that you don't run into an error when actually running the command:

$ sudo find / -name pg_hba.conf

$ sudo find / -name postgresql.conf

10.) Now let's edit the pg_hba.conf file.

It will initially look similar to the image below, but you will need to make a couple of changes to get it to work properly.

Edit the file to look just like the image below.

The section of the file that will be need to be edited will be at the bottom so you will need to scroll down until you find it.

If you are running in to issues making a connection remove all the lines and

reenter them as shown. This will be especially important if you are using a visual tool like pgAdmin to connect to the database.

Now run the command below to edit the file using the vim editor:

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

11.) Now that we've updated the authorization settings for the pg_hba.conf file we need to update PostgreSQL to enable remote connections to the database.

This is done through the postgresql.conf file.

At the command line enter command below:

$ sudo vim/var/lib/psql9/data/postgresql.conf

12.) We will need to change a couple of settings by uncommenting them and changing some of the values.

We will need to uncomment and edit a few lines:

Look for #listen_addresses = 'localhost'

It should be at around line 58 to 60 depending on your installation.

After you find it change it.

from: #listen_addresses = 'localhost'

to: listen_addresses=' * '

Now look for #port = 5432

It should be at around line 63 to 65 depending on your installation.

After you find it change it.

from: #port = 5432

to: port = 5432

13.) You will now need to start the PostgreSQL service.

Run the command below to start the service:

$ sudo service postgresql start

14.) You will now need to log into the server:

At the command prompt enter these commands in the following order:

$ sudo su - postgres - [This will place you at the bash shell]

-bash-4.2$

At the bash shell enter the command:

-bash-4.2$ psql -U postgres

If you are successful you will be at the PostgreSQL shell.

postgres=#

15.) Now that you are logging as the user 'postgres'

for the first time you will need to change your password.

Enter the command below at the 'postgres=#' shell to change the password:

ALTER USER user_name WITH PASSWORD 'new_password';

16.) You can also create and update your PostgresSQL users.

For example:

"Power users" will basically have admin rights.

"Other users" will be good for users who wish to query the data.

CREATE USER power_user SUPERUSER;

ALTER USER power_user WITH PASSWORD ‘poweruserpassword';

CREATE USER other_user NOSUPERUSER;

ALTER USER other_user WITH PASSWORD 'otheruserpassword';

\q