PostgreSQL is the world’s most advanced open source database and the fourth most popular database. In development for more than 20 years, PostgreSQL is managed by a well-organized and highly principled, and experienced open source community. It is an object-oriented database that is fully ACID compliant and highly extensible, enabling the community to add new features and capabilities as workload demands evolved.

In this post, I am going to guide you step by step on how to install PostgreSQL 13.3 from source in Ubuntu 20.4 in AWS EC2

STEP 1: Download Source from PostgreSQL website

Visit the website PostgreSQL https://www.postgresql.org/ and download PostgreSQL version 13.3 into your ubuntu server

wget https://ftp.postgresql.org/pub/source/v13.3/postgresql-13.3.tar.gz

STEP 2: Install Operating System Packages

The packages we are about to install are all the additional OS packages that support the installation of PostgreSQL

sudo apt-get install gcc
sudo apt install libreadline-dev
sudo apt install zlib1g-dev

STEP 3: Add OS User & Prepare directory for PostgreSQL program, data, archive, and backup

Create group dba and user postgres for accessing PostgreSQL

sudo groupadd -g 54321 dba
sudo useradd -u 54321 -g dba postgres
sudo passwd postgres

New password:
Retype new password:
passwd: password updated successfully

sudo mkdir -p /app/postgres/product/13.3/db_1/
sudo chown -R postgres:dba /app/postgres/product/13.3/db_1/

sudo mkdir -p /data01
sudo chown -R postgres:dba /data01
sudo mkdir -p /reco
sudo chown -R postgres:dba /reco
sudo mkdir -p /backup
sudo chown -R postgres:dba /backup

Create a home directory for newly created user postgres

#Create postgres folder
sudo mkdir /home/postgres
sudo chown -R postgres:dba /home/postgres/
#Setting permissions 
sudo chmod 755 /home/postgres
#initialization
sudo cp -a /etc/skel/. /home/postgres
#change from default from shell to bash
sudo usermod -s /bin/bash postgres
#allow postgres to use sudo
sudo usermod -aG sudo postgres
groups postgres
postgres : dba sudo

STEP 4: Modify User "postgres" Profile

The reason to modify user postgres profile is to easily access PostgreSQL later after done installation such as the path of program directory or data directory.  If you don't want to change it's also fine.

su - postgres

vi ~/.profile

#!/bin/sh
# The script sets environment variables helpful for PostgreSQL

export PGHOME=/app/postgres/product/13.1/db_1
export PATH=$PGHOME/bin:$PATH
export PGDATA=/data01
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5432
export PGLOCALEDIR=$PGHOME/share/locale
export MANPATH=$MANPATH:$PGHOME/share/man

source ~/.profile

STEP 5: Installation of PostgreSQL 13.3

Install PostgreSQL  using make and this is a full feature installation

#Login as postgres user and navigate to the folder where you download the source of PostgreSQL and
#change permission as needed to user postgres
cd /backup/
tar -zxvf postgresql-13.3.tar.gz
cd /backup/postgresql-13.3

./configure --prefix=/app/postgres/product/13.3/db_1/
sudo apt install make
make
make world
make install

make install-docs
make install-world
make -C src/bin install
make -C src/include install
make -C src/interfaces install
make -C doc install

Initialize PostgreSQL database cluster by using $PGHOME/bin/initdb -D $PGDATA

$PGHOME/bin/initdb -D $PGDATA

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /data01 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /app/postgres/product/13.3/db_1/bin/pg_ctl -D /data01 -l logfile start

STEP 6: PostgreSQL Service Configuration

Login as root and add service unit for PostgreSQL, then enable and start

sudo vi /etc/systemd/system/postgresql.service

[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
Type=forking
## Add a service section and set the max number of open files
LimitNOFILE=9999999

User=postgres
#Group=postgres

# Maximum number of seconds pg_ctl will wait for postgres to start.  Note that
# PGSTARTTIMEOUT should be less than TimeoutSec value.
Environment=PGSTARTTIMEOUT=270

Environment=PGDATA=/data01
PIDFILE=/data01/postmaster.pid

ExecStart=/app/postgres/product/13.3/db_1/bin/pg_ctl start -w -D "/data01" -l "/data01/log/startup.log"
ExecStop=/app/postgres/product/13.3/db_1/bin/pg_ctl stop -m fast -w -D "/data01"
ExecReload=/app/postgres/product/13.3/db_1/bin/pg_ctl reload -D "/data01"

# Give a reasonable amount of time for the server to start up/shut down.
# Ideally, the timeout for starting PostgreSQL server should be handled more
# nicely by pg_ctl in ExecStart, so keep its timeout smaller than this value.
TimeoutSec=300

[Install]
WantedBy=multi-user.target

And, don't forget to create startup.log file in /data01/log directory

cd /data01
sudo mkdir log
cd /data01/log
sudo vi startup.log
sudo chown -R postgres:dba startup.log

sudo systemctl enable postgresql
sudo systemctl start postgresql
sudo systemctl status postgresql
sudo systemctl stop postgresql

Let try to connect to PostgreSQL 13.3 using psql

postgres@ip-172-31-27-153:/data01/log$ psql
psql (13.3)
Type "help" for help.

postgres=# select version();
                                               version
------------------------------------------------------------------------------------------------------
 PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
(1 row)

postgres=#

YOU ARE NOW SUCCESSFULLY INSTALLED POSTGRESQL 13.3 INTO YOUR UBUNTU SERVER