How to install and configure PostgreSQL on Amazon Linux

Posted on 2023-05-25 Edited on 2023-10-22 In Tech , Big Data Views:

Download and install updates

[ec2-user@ip-192-168-28-223 ~]$ cat /etc/os-release
NAME="Amazon Linux"
VERSION="2"
ID="amzn"
ID_LIKE="centos rhel fedora"
VERSION_ID="2"
PRETTY_NAME="Amazon Linux 2"
ANSI_COLOR="0;33"
CPE_NAME="cpe:2.3:o:amazon:amazon_linux:2"
HOME_URL="https://amazonlinux.com/"
[ec2-user@ip-192-168-28-223 ~]$ cat /etc/system-release
Amazon Linux release 2 (Karoo)

[ec2-user@ip-192-168-28-223 ~]$ sudo yum update -y

Add PostgreSQL Amazon extras repository

PostgreSQL is part of the amazon extras library.

To check the available postgresql version in the Amazon extras repository:

[ec2-user@ip-192-168-28-223 ~]$ amazon-linux-extras | grep postgresql
  6  postgresql10             available    [ =10  =stable ]
 41  postgresql11             available    [ =11  =stable ]
 58  postgresql12             available    [ =stable ]
 59  postgresql13             available    [ =stable ]
 63  postgresql14=latest      enabled      [ =stable ]

To enable the Amazon extras repository:

[ec2-user@ip-192-168-28-223 ~]$ sudo amazon-linux-extras enable postgresql14

Install PostgreSQL server

[ec2-user@ip-192-168-28-223 ~]$ sudo yum install postgresql-server

Installed:
  postgresql-server.x86_64 0:14.3-2.amzn2.0.1

[ec2-user@ip-192-168-28-223 ~]$ sudo rpm -ql  postgresql-server | grep -v share | grep -v lib
/etc/pam.d/postgresql
/etc/postgresql-setup
/etc/postgresql-setup/upgrade
/etc/postgresql-setup/upgrade/postgresql.conf
/usr/bin/initdb
/usr/bin/pg_basebackup
/usr/bin/pg_checksums
/usr/bin/pg_controldata
/usr/bin/pg_ctl
/usr/bin/pg_receivewal
/usr/bin/pg_recvlogical
/usr/bin/pg_resetwal
/usr/bin/pg_rewind
/usr/bin/pg_verifybackup
/usr/bin/postgres
/usr/bin/postgresql-setup
/usr/bin/postgresql-upgrade
/usr/bin/postmaster
/usr/sbin/postgresql-new-systemd-unit
/var/run/postgresql

Initialize the DB

[ec2-user@ip-192-168-28-223 ~]$ sudo postgresql-setup initdb

Add the PostgreSQL service to the system service

[ec2-user@ip-192-168-28-223 ~]$ sudo systemctl start postgresql

[ec2-user@ip-192-168-28-223 ~]$ sudo systemctl enable postgresql

[ec2-user@ip-192-168-28-223 ~]$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2023-05-27 19:18:00 UTC; 45s ago
 Main PID: 96673 (postmaster)
   CGroup: /system.slice/postgresql.service
           ├─96673 /usr/bin/postmaster -D /var/lib/pgsql/data
           ├─96674 postgres: logger
           ├─96676 postgres: checkpointer
           ├─96677 postgres: background writer
           ├─96678 postgres: walwriter
           ├─96679 postgres: autovacuum launcher
           ├─96680 postgres: stats collector
           └─96681 postgres: logical replication launcher
[...]

Set password for Postgres user

[ec2-user@ip-192-168-28-223 ~]$ sudo -u postgres psql

postgres=# alter user postgres password 'password';
ALTER ROLE

Enable remote connections to PostgreSQL

Locate the line that starts with “listen_addresses“ and change it to “listen_addresses = ‘*’“. This will allow connections from any IP address. You also can increase the max_connections as needed.

[ec2-user@ip-192-168-28-223 ~]$ sudo vi /var/lib/pgsql/data/postgresql.conf
listen_addresses = ‘*’
max_connections = 10000

Next, open /var/lib/pgsql/data/pg_hba.conf file to authenticate the remote access with password by modifying the following line.

[ec2-user@ip-192-168-28-223 ~]$ sudo vi /var/lib/pgsql/data/pg_hba.conf
host    all          all            0.0.0.0/0  md5

Restart the PostgreSQL service to take effect:

[ec2-user@ip-192-168-28-223 ~]$ sudo systemctl start postgresql

Access the PostgreSQL database remotely

Install the psql client:

[ec2-user@ip-192-168-93-151 ~]$ sudo amazon-linux-extras install postgresql14

[ec2-user@ip-192-168-93-151 ~]$ which psql
/usr/bin/psql

Connect to the remote PostgreSQL server:

[ec2-user@ip-192-168-93-151 ~]$  psql -h 192.168.28.223 -U postgres -d postgres
Password for user postgres:

postgres=#

Create a database:

postgres=# CREATE DATABASE testdb;
CREATE DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

To drop a database:

postgres=# drop database testdb;
DROP DATABASE

Using pgbench to run a benchmark test on PostgreSQL

pgbench is a simple program for running benchmark tests on PostgreSQL. It runs the same sequence of SQL commands over and over, possibly in multiple concurrent database sessions, and then calculates the average transaction rate (transactions per second). By default, pgbench tests a scenario that is loosely based on TPC-B, involving five SELECT, UPDATE, and INSERT commands per transaction. However, it is easy to test other cases by writing your own transaction script files.

Available built-in scripts are: tpcb-like, simple-update and select-only. You also can create your own script for benchmarking.

To install the pgbench:

[ec2-user@ip-192-168-93-151 ~]$ sudo yum install postgresql-contrib

[ec2-user@ip-192-168-93-151 ~]$ which pgbench
/usr/bin/pgbench

To load data to the target database:

[ec2-user@ip-192-168-93-151 ~]$ pgbench -h 192.168.28.223 -p 5432 -U postgres -i -s 1000 testdb
Password:
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
100000000 of 100000000 tuples (100%) done (elapsed 213.06 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 398.67 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 218.56 s, vacuum 15.52 s, primary keys 164.58 s).

To run the benchmark on the database:

[ec2-user@ip-192-168-93-151 ~]$ pgbench -h 192.168.28.223 -p 5432 -U postgres -c 200 -j 32 -t 100000 testdb -b simple-update

Refer to this post for more detail on how to run pgbench on PostgreSQL.