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 epelAccept installation by pressing the y 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]: yAfter 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: 40086Step 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 CreatedAfter repository is has been added we can then install PostgreSQL 14 on Amazon Linux 2:
sudo yum install postgresql14 postgresql14-serverContinue 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]: yStep 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 ... OKYour 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.confRun 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 launcherStep 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 DATABASEStep 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.confThen restart the database service after making the change:
sudo systemctl restart postgresql-14Check 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.53Conclusion
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.
