Install PostgreSQL on AWS EC2

Geeks we’re back with our new series on PostgreSQL where we will be installing on AWS EC2 and access it through pgAdmin. In this post we will be installing PostgreSQL on Linux Machine and will see how we can access using pgAdmin.

So let’s get started!!!

Prerequisite: AWS Account, do not worry if you don’t have one you can create from here. Have a Linux instance up and running in our case we will be using Ubuntu 18.4, you can always go through this to set up an instance for yourself.

Step 1: Open EC2 instance and connect through Putty or MobaXterm or terminal and login to your instance.

Ubuntu’s default repositories contain Postgres packages, so you can install these using the apt packaging system.

sudo apt update
sudo apt install postgresql postgresql-contrib -y

Postgres uses a concept called “roles” to handle in authentication and authorization. Which are some what similar to Linux accounts, but Postgres does not distinguish between users and groups and instead prefers the more flexible term “role”.

Step 2: The installation procedure created a user account called postgres that is associated with the default Postgres role. In order to use Postgres, you can log into that account.

sudo -i -u postgres

You can now access a Postgres promp by typing

psql

You should see something like below snapshot

Now you’re logged into the database management and you are free to interact.

Type help to know more.

Step 3: Create a new database and a user using the following commands.

postgres=# CREATE USER nucleargeeks WITH PASSWORD 'xyzabc@wd';
postgres=# CREATE DATABASE nucleargeeksdb; 
postgres=# GRANT ALL PRIVILEGES ON DATABASE nucleargeeksdb to nucleargeeks; 
postgres=# \q

Step 4: Configuring Authentication and access.

You have to make PostgreSQL listening for remote incoming TCP connections.To b able to reach the server remotely you have to add the following line into the file /etc/postgresql/10/main/postgresql.conf and add listen_addresses = ‘*’

vi  /etc/postgresql/10/main/postgresql.conf

PostgreSQL by default refuses all connections it receives from any remote address, you have to relax these rules by adding this line to /etc/postgresql/10/main/pg_hba.conf and add host all all 0.0.0.0/0 md5

vi /etc/postgresql/10/main/pg_hba.conf

Step 5: When you’re done restart your postgresql server by below commands

sudo /etc/init.d/postgresql stop 
sudo /etc/init.d/postgresql start

In the next post we will be seeing how you can connect the same using pgAdmin.