Install and deploy a Postgresql database for free
Published: 2023-03-16 23:13:49

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 db 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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ehxpqdiubzj481bdfb4x.png)
4. Select the instance type you want to use based on your requirements. I pick instance type t2.micro for 1GB free of memory
5. Configure the instance details. This includes setting the number of instances you want to launch, network settings, storage options, and other advanced settings.
6. Configure security group settings. This includes setting up inbound and outbound rules to allow traffic to and from your instance.
7. Review your instance configuration and launch the instance.
8. 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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/29woccx3bo6xlysh0o07.png)

9. 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

```
postgres@ipv4.address.EC2.instance:~$ 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

![https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mmx37cttcfyzx8cpen3p.png](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mmx37cttcfyzx8cpen3p.png)

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](https://tableplus.com/download) 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 :

```sql
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
```