How to Set Up the Latest Version of PostgreSQL on AWS Linux 2

Although AWS does provide their own RDS service for running PostgreSQL I recommend it only (1) if you frequently require restoring the database up to a particular minute’s snapshot and (2) if the CPU usage of the database server would be substantial. Otherwise, a daily backup of the database to an AWS S3 Bucket in an Infrequent Access Storage Class provides sufficient safety net.

While one can install in AWS Linux 2 PostgreSQL via “yum install”, the AWS provided versions are too old to be useful.

The PostgreSQL yum repo is  https://yum.postgresql.org/repopackages/ which for AWS Linux 2 translates into https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm. Nevertheless, due to the changes in the yum packages, one cannot directly use the packages in AWS Linux 2.

The below steps are for PostgreSQL 11, but can be applied to PostgreSQL 10, 12, or 13. Just change the postgresql11 to postgresql10, or postgresql-11 to postgresql-10 etc.

1. Install PostgreSQL

sudo rpm -ivh --nodeps https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo sed -i 's/\$releasever/7/g' /etc/yum.repos.d/pgdg-redhat-all.repo
sudo yum install -y postgresql11 postgresql11-server
PGSETUP_INITDB_OPTIONS='--encoding=UTF-8 --locale=C.UTF-8' sudo /usr/pgsql-11/bin/postgresql-11-setup initdb
sudo systemctl enable --now postgresql-11
systemctl status postgresql-11

2. Create a JIRA Database

sudo su - postgres
psql
create user jirauser with password 'XXXXX';
CREATE DATABASE jira WITH ENCODING 'UTF-8' LC_COLLATE 'C.UTF-8' LC_CTYPE 'C.UTF-8' TEMPLATE template0 owner jirauser;
grant all privileges on database jira to jirauser;
alter role jirauser login;

3. Set Up Outside Connection

Add this line to /var/lib/pgsql/11/data/postgresql.conf

listen_addresses = '*'

4. Set up Login Access

Edit /var/lib/pgsql/11/data/pg_hba.conf and disable the ident. The file should look like as follows

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
#host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
#host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
#host    replication     all             127.0.0.1/32            ident
#host    replication     all             ::1/128                 ident

host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust

5. Restart the Database Service

sudo systemctl reload postgresql-11
sudo systemctl restart postgresql-11