PostgreSQL 14 has been officially released on 30 September 2021 with hundreds of improvements, fixes, and changes that have been mentioned in the release note on their official website. In this release, there are no huge gigantic features but there are many small things that will make your task better and easier. This release was highly recommended to upgrade especially for large, high-connection-count sites such as bank or telecom sector. If you are using PostgreSQL with cloud providers such as Amazon Web Services or Digital Oceans you may have to wait for their update or you can check with their support in order to get at least the planned upgrade date. 

Now, let's start to fresh source installation of PostgreSQL 14 on ubuntu 20.04. In this experiment, we are going to use Google Cloud Engine (Similar to AWS EC2 or Digital Ocean Droplet) to perform it. 

STEP 1: Download Source from PostgreSQL website

First, you have to download the source code of new released PostgreSQL 14 from the official website here. You can download using the wget command as following

wget https://ftp.postgresql.org/pub/source/v14.0/postgresql-14.0.tar.gz

STEP 2: Install Prerequisite Packages

There are some dependent packages such as gcc, zlib1g as a prerequisite to installing PostgreSQL. Therefore, we have to install those packages first as the following command

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

STEP 3: Server Preparation

In this step, you are going to prepare your server before you start the installation of PostgreSQL 14 such as group and user that you want to specifically assign permission to work only with Postgresql. This is a very important security measurement since you want only the created user to have the ability to access your data in the database. Furthermore, we are going to prepare a data directory as well as archive and backup in case you back up your data into the same server before transferring it to the backup disk.

• Create Group and User

Let's assume we want to create a group named dba and user postgres to have permission to access data in the PostgreSQL database.

#Create a group "dba"
sudo groupadd -g 54321 dba
#Create a user "postgres" in group "dba"
sudo useradd -u 54321 -g dba postgres
#Giving password to user "postgres" (Let use Pgs123456 as password)
sudo passwd postgres

Next, we have to create a home directory of the user postgres as the following command

#Create home directory of user postgres
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
#checking sudo permission
groups postgres
#switching to user postgres
su - postgres

• Create data directory

#Create directory for postgresql software to be installed on
sudo mkdir -p /app/postgres/product/14/db_1/
sudo chown -R postgres:dba /app/postgres/product/14/db_1/
#Create directory for data store
sudo mkdir -p /data01
sudo chown -R postgres:dba /data01

• Create Archive directory

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

• Create Backup director

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

STEP 4: User "postgres" Profile Preparation

The preparation of user postgres profile is to easily access PostgreSQL instance after done installation such as entering to the path of program directory or data directory.  If you do not wish to change that is also fine. You can do this by following the below instruction

• Switch to user postgres

su - postgres

• Edit and save ~/.profile

sudo vi ~/.profile

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

export PGHOME=/app/postgres/product/14/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

• Reload the configuration

source ~/.profile

STEP 5: Installation of PostgreSQL 14

Now it's time to install PostgreSQL 14 from the source code we just download from step 1 by extracting using the tar command and using the make command to install as you can see below (Please read comment carefully)

#login as postgres user and navigate to directory where you download the source code of PostgreSQL 14 and change permission if necessary
#Make sure the ownership and permission of downloaded file is belong to user postgres
tar -zxvf postgresql-14.0.tar.gz
cd postgresql-14.0
#checking possible issue by installing this PostgreSQL 14
./configure --prefix=/app/postgres/product/14/db_1/
#install make command if it's not yet install on your server
sudo apt install make
#now let's start installing PostgreSQL 14
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

It might take several minutes for each make command to execute. After finishing you may now initialize the PostgreSQL data cluster in the data directory. It is where your data is really stored by using command

$PGHOME/bin/initdb -D $PGDATA

$PGHOME and $PGDATA are the parameters you have been defined in postgres user profile. 

STEP 6: Configuration of PostgreSQL Service

The reason you need to configure the PostgreSQL service is to easily start, stop, or restart the PostgreSQL service at anytime you want without entering the directory of the PostgreSQL program file or shell script. You need to perform this with root permission access.

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/14/db_1/bin/pg_ctl start -w -D "/data01" -l "/data01/log/startup.log"
ExecStop=/app/postgres/product/14/db_1/bin/pg_ctl stop -m fast -w -D "/data01"
ExecReload=/app/postgres/product/14/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

Create a log file in order to start up the PostgreSQL service.

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

Let perform the service command below to see if it is ok. If there is no issue, it means that you've now successfully installed PostgreSQL 14 on your ubuntu server.

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

STEP 7: Final Check

Finally, you can use psql command to check the version of PostgreSQL which just has been installed on your server. Follow command below

postgres@instance-1:/data01/log$ psql
psql (14.0)
Type "help" for help.
postgres=# select version();
                                               version                                                
------------------------------------------------------------------------------------------------------
 PostgreSQL 14.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
(1 row)
postgres=# 

THANK YOU

Find Us @
Facebook
Telegram
Twitter
LinkedIn