HiveGrid

Install Postgresql Server

Prerequisites

  • An AWS account setup
  • Know how to launch a EC2 instance using AWS console
  • A terminal to connect to the EC2 instance

Introduction

After referring many blog posts and working through many error messages, I found the instructions that work. We will:

  • Launch an EC2 instance
  • Download the PEM file
  • Connect to the EC2 instance
  • Manually install PostgreSQL

First step is to launch an EC2 instance using the AWS console. Then, connect to the EC2 instance using SSH. Finally, install the PostgreSQL 16 server manually. Once we have a working database, we will copy the commands to the shell provisioner block in Packer template to automate the installation.

Caution

If you install Postgresql that comes with the builtin apt package, you will get old version of Postgresql.

Steps

Download the PEM file from AWS console. Save it on your local machine.

Change Permission
chmod 400 postgres-test-key.pem
Connect to EC2 Instance
ssh -i "postgres-test-key.pem" ubuntu@ec2-12-123-45-67.us-west-2.compute.amazonaws.com

Change the server name ec2-12-123-45-67.us-west-2.compute.amazonaws.com for your EC2 instance. This can be found in the networking connection section of the EC2 instance.

Check Ubuntu version:

Ubuntu Version
lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 22.04.4 LTS
Release:	22.04
Codename:	jammy

The following are already installed by default on the EC2 instance:

SoftwareVersion
Git2.34.1
Wget1.21.2
Curl7.81.0
sudo apt update
sudo apt list --upgradable
sudo apt upgrade

This pops up window for restarting services. To turn off the popup:

Turn Off Popup
sudo DEBIAN_FRONTEND=noninteractive apt-get upgrade -yq

The current Postgresql version is 16. To install this version:

Install PostgreSQL
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
sudo DEBIAN_FRONTEND=noninteractive apt install postgresql-16 postgresql-contrib-16 -y

This results in error:

E: Unable to locate package postgresql-16
E: Unable to locate package postgresql-contrib-16

The reason is that the packages are not available in the default repository for this version of Ubuntu.

Run the following commands:

Import Repository Signing Key
wget -qO - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/postgresql.asc
Add the PostgreSQL Repository
echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
Update the Package Lists
sudo apt-get update
Install PostgreSQL 16
sudo DEBIAN_FRONTEND=noninteractive apt install postgresql-16 postgresql-contrib-16 -y
Verify Database is Running
sudo -u postgres psql

You can see the version of the installed database:

Database Version
sudo -u postgres psql
psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
Type "help" for help.

postgres=# select version();

You can see the database users and their permissions:

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

postgres=# \q

After installation, PostgreSQL will be running by default. If you want to start:

Start Database
sudo systemctl start postgresql

Setup the database as a service that runs when the server boots up:

Setup Database as Service
sudo systemctl enable postgresql

Dependencies

Dependencies to install PostgreSQL 16:

Package NameDescription
postgresql-16The main PostgreSQL database server
postgresql-contrib-16Additional contributed modules for PostgreSQL

What is postgresql-client package?

The postgresql-client-16 package provides the necessary tools for managing and interacting with PostgreSQL databases, whether for development, administration, or automation tasks, without needing to install the full PostgreSQL server package on the client machine.

Check if postgresql-client-16 is Installed
deploy@ip-172-31-44-28:~$ dpkg -l | grep postgresql-client-16
ii  postgresql-client-16             16.2-1.pgdg22.04+1                      amd64        front-end programs for PostgreSQL 16
deploy@ip-172-31-44-28:~$ apt list --installed | grep postgresql-client-16

WARNING: apt does not have a stable CLI interface. Use with caution in scripts.

postgresql-client-16/jammy-pgdg,now 16.2-1.pgdg22.04+1 amd64 [installed,automatic]

Create deploy Database User

Login to the PostgreSQL Command Line
sudo -u postgres psql
Create the User
postgres=# CREATE ROLE deploy WITH LOGIN PASSWORD 'password' CREATEDB;
Output
CREATE ROLE
Verify the Role and Permission
postgres=# \du
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 deploy    | Create DB
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
Exit the Command Line
\q

Create deploy Ubuntu User

Add deploy User
ubuntu@ip-172-31-44-28:~$ sudo adduser deploy
Adding user `deploy' ...
Adding new group `deploy' (1001) ...
Adding new user `deploy' (1001) with group `deploy' ...
Creating home directory `/home/deploy' ...
Copying files from `/etc/skel' ...
New password: 
Retype new password: 
passwd: password updated successfully
Changing the user information for deploy
Enter the new value, or press ENTER for the default
	Full Name []: 
	Room Number []: 
	Work Phone []: 
	Home Phone []: 
	Other []: 
Is the information correct? [Y/n] Y
Grant Sudo Privileges
sudo usermod -aG sudo deploy

Connect to Database

Access PostgreSQL as deploy User
$ psql -U deploy -d postgres
Output
psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
Type "help" for help.

postgres=> \du
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 deploy    | Create DB
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

postgres=> \q
Connect to Database as deploy User with Password
deploy@ip-172-31-44-28:~$ psql -U deploy -d postgres -W
Password: 
psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
Type "help" for help.

postgres=> \du
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 deploy    | Create DB
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

postgres=> \q

The authentication method a PostgreSQL user must use to access the psql prompt is determined by PostgreSQL’s own configuration in the pg_hba.conf file, and it is independent of whether a Linux system user is part of the sudo group. Thus, a deploy PostgreSQL user will need to use a password if the pg_hba.conf file is configured to require password authentication (e.g., md5 or scram-sha-256) for their connections.

The contents of /etc/postgresql/16/main/pg_hba.conf file:

Contents of pg_hba.conf
 1local   all             postgres                                peer
 2
 3# TYPE  DATABASE        USER            ADDRESS                 METHOD
 4
 5# "local" is for Unix domain socket connections only
 6local   all             all                                     peer
 7# IPv4 local connections:
 8host    all             all             127.0.0.1/32            scram-sha-256
 9# IPv6 local connections:
10host    all             all             ::1/128                 scram-sha-256
11# Allow replication connections from localhost, by a user with the
12# replication privilege.
13local   replication     all                                     peer
14host    replication     all             127.0.0.1/32            scram-sha-256
15host    replication     all             ::1/128                 scram-sha-256

The line number 6 is related to local connections:

local   all             all                                     peer

Change peer to md5:

local   all             all                                     md5

This change requires users to authenticate with a password when connecting locally.

Reload PostgreSQL Configuration
sudo systemctl reload postgresql
Connect Using Password Authentication
psql -U deploy -d postgres -W
Password: password
Output
psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
Type "help" for help.

postgres=>

Caution

If this configuration change is not made, you will get the error:

psql: error: connection to server on socket “/var/run/postgresql/.s.PGSQL.5432” failed: FATAL: Peer authentication failed for user “postgres”

When you try to connect:

psql -U postgres -W
Password:

The changes to pg_hba.conf file should be made with a user that has sudo privileges. The ubunutu user has sudo privileges by default. We provided sudo privileges to deploy user. So we can use any of these two users to make the changes.

Prev
Lima