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
STEP 2: Install Prerequisite Packages
There are some dependent packages such as
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/
sudo chmod 755 /home/postgres
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
#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
# The script sets environment variables helpful for PostgreSQL
- Reload the configuration
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
#checking possible issue by installing this PostgreSQL 14
#install make command if it's not yet install on your server
sudo apt install make
#now let's start installing PostgreSQL 14
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
Description=PostgreSQL database server
## Add a service section and set the max number of open files
# Maximum number of seconds pg_ctl will wait for postgres to start. Note that
# PGSTARTTIMEOUT should be less than TimeoutSec value.
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.
Create a log file in order to start up the PostgreSQL service.
sudo mkdir 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
Type "help" for help.
postgres=# select 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