Install and deploy a Postgresql database for free

At the time of writing this article, I was in need of a database for my personal project and AWS is one of the most popular choices. Before this I have never created a database server on a remote machine so I figured it would be a good opportunity to document it for future projects. This guide is meant for beginner.

We'll install a Postgresql database and host it on Ubuntu which is a Linux distribution. We'll first need to create the Ubuntu instance using AWS EC2 service. At the end of the tutorial, you should have a database ready to connect to other services.

Step 1 : Create an Ubuntu EC2 instance

1. Log in to the AWS Management Console and navigate to the EC2 dashboard.
2. Click on the "Launch Instance" button to begin creating a new EC2 instance.
3. Choose the Ubuntu AMI (Amazon Machine Image) you want to use. You can search for "Ubuntu" in the search bar and select the appropriate version of Ubuntu that you want to use.

1. Select the instance type you want to use based on your requirements. I pick instance type t2.micro for 1GB free of memory
2. Configure the instance details. This includes setting the number of instances you want to launch, network settings, storage options, and other advanced settings.
3. Configure security group settings. This includes setting up inbound and outbound rules to allow traffic to and from your instance.
4. Review your instance configuration and launch the instance.
5. Choose or create a key pair that will be used to authenticate SSH connections to the instance. You can do so either by going to Network & Security -> Key Pairs or select Create new Key Pair in the same modal as Key Pairs when creati ng the instance.

1. Once your instance is launched, you can connect to it using SSH and follow the instructions provided in this document to install PostgreSQL on your Ubuntu instance.

Step 2: Connect to your EC2 instance

To connect to your EC2 instance, you can use a terminal program such as SSH. You will need to have the private key associated with your EC2 instance to authenticate. You can use the following command to connect:

ssh -i /path/to/private/key.pem ubuntu@your-ec2-instance-ip-address

If the key file is stored in the Downloads directory, you'll likely see this error message

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@         WARNING: UNPROTECTED PRIVATE KEY FILE!          @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Permissions for '/Users/username/.ssh/id_rsa' are too open.
It is required that your private key files are NOT accessible by others.
This private key will be ignored.

This is because the key.pem file is stored in a unsecured location (Downloads folder) and the SSH client does not allow the use of private keys accessible by others. We need to update the access permissions of the key file to allow reading by you via the chmod command (The name chmod is short for “change mode”).

Default username of the ubuntu server is ubuntu. Run the command to change the permissions and connect to the instance

chmod 400 ssh -i /path/to/private/key.pem ubuntu@your-ec2-instance-ipv4-address

If you got a warning the authenticity of host is not established do you want to connect, answer Yes . You are now connected !

Step 3: Update your system

Once you have successfully logged into the machine, before installing any software, it is recommended to update your system to ensure that you have the latest security patches and bug fixes. You can do this by running the following command in your terminal:

sudo apt update && sudo apt upgrade

Step 4: Install PostgreSQL

To install PostgreSQL on Ubuntu, you can use the following command:

sudo apt install postgresql postgresql-contrib

This command will install PostgreSQL and its accompanying tools and libraries. During the installation process, you may be prompted to create a password for the PostgreSQL administrative user (postgres). Make sure to remember this password, as you will need it to log in to your database.

Step 5: Verify the installation

To verify that PostgreSQL has been installed correctly, you can use the following command to check its version:

psql --version

This should output the version number of PostgreSQL that you have installed.

Step 6 : Create an Ubuntu user in PostgreSQL service

After installing PostgreSQL, the service should automatically start. However, if you need to start or stop the service manually, you can use the following commands:

To start the service:

sudo systemctl start postgresql

To stop the service:

sudo systemctl stop postgresql

You have now successfully installed PostgreSQL on your Ubuntu EC2 instance. You can now start using it to create and manage your databases.

To connect to Postgres, switch to the postgres user

sudo -i -u postgres

To create a Postgres database user run the following command which will give an interactive prompt for configuring the new user called ubuntu. For the sake of simplicity, the ubuntu user will be a superuser, which is the equivalent of being a root user on linux. The super user will have the ability to create/delete/modify databases and users.

createuser --interactive

Prompt ask for the name of new user

Enter name of role to add: ubuntu
Shall the new role be a superuser? (y/n) y

Log in to postgres using the postgres user for now to verify the new ubuntu user was created successfully

[e-mail]:~$ psql
psql (12.4 (Ubuntu 12.4-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# \\du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

 ubuntu    | Superuser, Create role, Create DB                          | {}

postgres=#

Exit out of the psql by running \\q and also exit out of the postgres user by running exit on the command line. Let's try to run psql as the ubuntu user now. An error similar to the one below should be observed

psql: error: could not connect to server: FATAL:  database "ubuntu" does not exist

The reason for this is that Postgres by default tries to connect to a database that is the same name as the user. Since the user is ubuntu it tries to connect to a database called ubuntu as well which does not exist. Instead we can pass in the -d flag and connect to a database that we know exists, like postgres.

psql -d postgres

Right now the ubuntu user in Postgres does not have a password associated with it. We will need to add a password like so

ALTER USER ubuntu PASSWORD 'password';

Step 7: Allow connections to PostgreSQL

To allow remote connections to PostgreSQL on your Ubuntu EC2 instance, you need to modify the PostgreSQL configuration file.

1. Open the configuration file with the following command:

sudo vim /etc/postgresql/<version>/main/postgresql.conf

Note: Replace <version> with the version number of PostgreSQL that you have installed (e.g. 11).

1. Find the line that starts with listen_addresses and uncomment it by removing the # symbol at the beginning of the line.
2. Change the value of listen_addresses to “*” to allow connections from any IP address.
3. Save and close the file.
4. Next, you need to modify the pg_hba.conf file to allow connections from remote hosts.
5. Open the pg_hba.conf file with the following command:

sudo vim /etc/postgresql/<version>/main/pg_hba.conf

Add the following line at the end of the file:

host    all             all             0.0.0.0/0               md5

Note: This line will allow connections from any IP address. If you want to restrict access to specific IP addresses, replace 0.0.0.0/0 with the appropriate IP address range.

1. Save and close the file.
2. Restart the PostgreSQL service for the changes to take effect:

sudo systemctl restart postgresql

You should now be able to connect to your PostgreSQL server from a remote host by specifying the IP address of your EC2 instance as the host name.

Step 8 : Open the port in the AWS dashboard

To add an inbound rule on your EC2 instance to allow all connections on Postgres port 5432, follow these steps:

1. Log in to the AWS Management Console and navigate to the EC2 dashboard.
2. Select the instance that you want to modify.
3. Click on the "Security" tab at the bottom of the page.
4. Select the security group associated with your instance.
5. Click on the "Edit inbound rules" button.
6. Click on the "Add rule" button.
7. Select "PostgreSQL" from the "Type" dropdown menu.
8. Leave the "Source" field as "Anywhere" to allow all connections, or specify a specific IP address or range to restrict connections to specific hosts. Source 0.0.0.0/0 will accept all the incoming requests

1. Click on the "Save rules" button.

You should now be able to connect to your PostgreSQL server from any IP address on port 5432.

Step 9 : Connect the database to your app service

I used a database management tool/viewer called Tableplus to interact with the db and run sql commands. Add a connection and test it

Host: Public IPv4 DNS of the EC2 instance
User: ubuntu
Password: password
Port: 5432

Click Test, if everything is good, then Connect

Once connection is set up using Tableplus, we can run sql query to create db :

create database testdb;

Now your PostgreSQL db is ready to connect to your app back end. You will need to provide your app the following vars so it can connect to the db.

DATABASE_NAME: testdb
HOST: Public IPv4 DNS of the EC2 instance
USER: ubuntu
PASSWORD: password
PORT: 5432