How To Install PostgreSQL 14 on Amazon Linux 2

Posted on 81 views

PostgresSQL a very powerful and open-source object-oriented relational database system. PostgreSQL database is known for its reliability, robustness, and good performance making one of top choices when selecting a database server to power Mission-critical production workloads. In this article we will be covering the installation steps of PostgreSQL 14 on an Amazon Linux 2 server.

PostgreSQL 14 was released officially by the PostgreSQL Global Development Group on 30th September 2021 and is ready for use in Production environments. This new release of PostgreSQL ships with a number of performance improvements that aims to simplify the life of database Administrators and Developers in implementing data-driven application.

Here is a short summary of PostgreSQL 14 main features:

  • There is a significant throughput boost in PostgreSQL 14 for workloads that use many connections. Some benchmarks shows a 2x speedup in this release.
  • More performance gains to the vacuuming system, including optimizations for reducing overhead from B-Trees
  • Several improvements to PostgreSQL’s query parallelism support, including better performance of parallel sequential scans.
  • Better security through a feature to assign read-only and write-only privileges to users on tables, views, and schemas using predefined roles
  • PostgreSQL 14 now lets you access JSON data using subscripts
  • Among many other features. See release changelog for more details

Below are the minimum requirements for the installation in Production setup.

  • Memory requirement: 2GB
  • CPU requirement: 1 vcpu
  • Disk requirement: 1GB of disk space for installation
  • Linux user with sudo privileges

Step 1: Enable EPEL repository to Amazon Linux 2

Some dependency packages are contained in EPEL YUM repository which is not enabled by default on Amazon Linux 2. You’ll need to manually add and enable the repository.

Use these commands to install EPEL repository on Amazon Linux 2:

sudo amazon-linux-extras install epel

Accept installation by pressing the key when given a prompt:

Dependencies Resolved

======================================================================================================================================================================================================
 Package                                           Arch                                        Version                                     Repository                                            Size
======================================================================================================================================================================================================
Installing:
 epel-release                                      noarch                                      7-11                                        amzn2extra-epel                                       15 k

Transaction Summary
======================================================================================================================================================================================================
Install  1 Package

Total download size: 15 k
Installed size: 24 k
Is this ok [y/d/N]: y

After EPEL repository is enabled you can proceed to the next step of adding PostgreSQL repo.

$ sudo yum repolist
Failed to set locale, defaulting to C
Loaded plugins: langpacks, priorities, update-motd
209 packages excluded due to repository priority protections
repo id                                                                             repo name                                                                                                status
amzn2-core/2/x86_64                                                                 Amazon Linux 2 core repository                                                                               26556
amzn2extra-docker/2/x86_64                                                          Amazon Extras repo for docker                                                                                   50
amzn2extra-epel/2/x86_64                                                            Amazon Extras repo for epel                                                                                      1
epel/x86_64                                                                         Extra Packages for Enterprise Linux 7 - x86_64                                                           13479+209
repolist: 40086

Step 2: Install PostgreSQL 14 on Amazon Linux 2

Run the following commands to ensure PGDG repository is added to your Amazon Linux 2 server.

For 64-bit CPU architecture instance:

sudo tee /etc/yum.repos.d/pgdg.repo<

For Graviton 2 (ARM64) EC2 instance:

sudo tee /etc/yum.repos.d/pgdg.repo<

Update YUM package index:

$ sudo yum makecache
Loaded plugins: langpacks, priorities, update-motd
amzn2-core                                                                                                                                                                     | 3.7 kB  00:00:00
amzn2extra-docker                                                                                                                                                              | 3.0 kB  00:00:00
amzn2extra-epel                                                                                                                                                                | 3.0 kB  00:00:00
epel/x86_64/metalink                                                                                                                                                           |  14 kB  00:00:00
pgdg14                                                                                                                                                                         | 3.6 kB  00:00:00
(1/23): amzn2-core/2/x86_64/group_gz                                                                                                                                           | 2.5 kB  00:00:00
(2/23): amzn2-core/2/x86_64/updateinfo                                                                                                                                         | 419 kB  00:00:00
(3/23): amzn2-core/2/x86_64/filelists_db                                                                                                                                       |  44 MB  00:00:01
(4/23): amzn2-core/2/x86_64/primary_db                                                                                                                                         |  57 MB  00:00:01
(5/23): amzn2-core/2/x86_64/other_db                                                                                                                                           |  505 B  00:00:00
(6/23): amzn2extra-docker/2/x86_64/filelists_db                                                                                                                                |  21 kB  00:00:00
(7/23): amzn2extra-docker/2/x86_64/updateinfo                                                                                                                                  |   76 B  00:00:00
(8/23): amzn2extra-docker/2/x86_64/primary_db                                                                                                                                  |  85 kB  00:00:00
(9/23): amzn2extra-docker/2/x86_64/other_db                                                                                                                                    |  505 B  00:00:00
(10/23): amzn2extra-epel/2/x86_64/filelists_db                                                                                                                                 |  882 B  00:00:00
(11/23): amzn2extra-epel/2/x86_64/updateinfo                                                                                                                                   |   76 B  00:00:00
(12/23): amzn2extra-epel/2/x86_64/primary_db                                                                                                                                   | 1.8 kB  00:00:00
(13/23): amzn2extra-epel/2/x86_64/other_db                                                                                                                                     |  507 B  00:00:00
(14/23): epel/x86_64/group_gz                                                                                                                                                  |  96 kB  00:00:00
(15/23): epel/x86_64/filelists_db                                                                                                                                              |  12 MB  00:00:00
(16/23): epel/x86_64/updateinfo                                                                                                                                                | 1.0 MB  00:00:00
(17/23): epel/x86_64/prestodelta                                                                                                                                               | 3.1 kB  00:00:00
(18/23): epel/x86_64/primary_db                                                                                                                                                | 7.0 MB  00:00:00
(19/23): epel/x86_64/other_db                                                                                                                                                  | 3.4 MB  00:00:00
(20/23): pgdg14/group_gz                                                                                                                                                       |  244 B  00:00:00
(21/23): pgdg14/filelists_db                                                                                                                                                   |  79 kB  00:00:00
(22/23): pgdg14/primary_db                                                                                                                                                     |  64 kB  00:00:00
(23/23): pgdg14/other_db                                                                                                                                                       |  19 kB  00:00:00
Metadata Cache Created

After repository is has been added we can then install PostgreSQL 14 on Amazon Linux 2:

sudo yum install postgresql14 postgresql14-server

Continue with the installation if packages installed doesn’t conflict with any other packages currently installed in the system.

Dependencies Resolved

======================================================================================================================================================================================================
 Package                                                Arch                                      Version                                             Repository                                 Size
======================================================================================================================================================================================================
Installing:
 postgresql14                                           x86_64                                    14.0-1PGDG.rhel7                                    pgdg14                                    1.5 M
 postgresql14-server                                    x86_64                                    14.0-1PGDG.rhel7                                    pgdg14                                    5.5 M
Installing for dependencies:
 postgresql14-libs                                      x86_64                                    14.0-1PGDG.rhel7                                    pgdg14                                    265 k

Transaction Summary
======================================================================================================================================================================================================
Install  2 Packages (+1 Dependent package)

Total download size: 7.3 M
Installed size: 31 M
Is this ok [y/d/N]: y

Step 3: Initialize PostgreSQL 14 on Amazon Linux 2

Next we generate initial database configurations file before we can start and enable the postgresql service.

$ sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OK

Your PostgreSQL 14 server main configuration file is /var/lib/pgsql/14/data/postgresql.conf. You can edit the file as desired to customize its parameters.

sudo vim /var/lib/pgsql/14/data/postgresql.conf

Run the following command to start PostgreSQL 14 service and enable it for autostart.

$ sudo systemctl enable --now postgresql-14
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-14.service to /usr/lib/systemd/system/postgresql-14.service.

Check if the services is started successfully. You should see “active (running)” in the output:

$ systemctl status postgresql-14
● postgresql-14.service - PostgreSQL 14 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2021-10-28 10:11:57 UTC; 48s ago
     Docs: https://www.postgresql.org/docs/14/static/
  Process: 3075 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir $PGDATA (code=exited, status=0/SUCCESS)
 Main PID: 3080 (postmaster)
   CGroup: /system.slice/postgresql-14.service
           ├─3080 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
           ├─3083 postgres: logger
           ├─3085 postgres: checkpointer
           ├─3086 postgres: background writer
           ├─3087 postgres: walwriter
           ├─3088 postgres: autovacuum launcher
           ├─3089 postgres: stats collector
           └─3090 postgres: logical replication launcher

Step 4: Access PostgreSQL 14 terminal console

You should now be able to access PostgreSQL database server console and perform DB operations.

$ sudo su - postgres 
$ psql
psql (14.0)
Type "help" for help.

Let’s create test database and user

postgres=# CREATE DATABASE testdb;
CREATE DATABASE

postgres=# DROP DATABASE testdb;
DROP DATABASE

Step 5: Changing Listen Address of PostgreSQL service

By default the service listens on localhost Loopback interface address.

$ sudo ss -tunelp | grep 5432
tcp  LISTEN 0      128                            127.0.0.1:5432        0.0.0.0:*          users:(("postmaster",pid=3080,fd=7)) uid:26 ino:25799 sk:b <->
tcp  LISTEN 0      128                                [::1]:5432           [::]:*          users:(("postmaster",pid=3080,fd=6)) uid:26 ino:25798 sk:f v6only:1 <->

The setting to change is applied on below line:

# grep listen_addresses /var/lib/pgsql/14/data/postgresql.conf
#listen_addresses = 'localhost'		# what IP address(es) to listen on;

To change the list address set like below:

$ sudo vim /var/lib/pgsql/14/data/postgresql.conf
listen_addresses = '172.21.100.15'

You may need to update the authentication methods for remote connections:

sudo vim /var/lib/pgsql/14/data/pg_hba.conf

Then restart the database service after making the change:

sudo systemctl restart postgresql-14

Check new listen address:

$ sudo ss -tunelp | grep 5432
tcp  LISTEN 0      128                       172.21.100.15:5432        0.0.0.0:*          users:(("postmaster",pid=820,fd=6)) uid:26 ino:84686 sk:10 <->

Let’s test connection on the given IP address

psql -h 104.131.175.53

Conclusion

PostgreSQL 14 has some of the best improvements when compared to prior releases. We hope this guide helped you to install PostgreSQL 14 on Amazon Linux 2 and set you on a path to enjoy this release of PostgreSQL. If you encounter any issue during the installation let us know on the comment section.

coffee

Gravatar Image
A systems engineer with excellent skills in systems administration, cloud computing, systems deployment, virtualization, containers, and a certified ethical hacker.