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/ #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
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=#