Install PostgreSQL 14 on Linux server : Easiest way

PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance

PostgreSQL 14 introduces several new features that help ease developments and administration in implementing data-driven applications.

Every developer knows the importance of choosing the right database for building modern apps. Among the many open-source relational databases available, PostgreSQL is a popular choice among developers.

It continues to make improvements in the sector of complex data types, including more accessible JSON access and the support for non-contiguous data ranges. PostgreSQL 14 has played a big role in PostgreSQL’s history of high performance and distributed workloads with improvements in logical replication, query parallelism, high-write workloads, connection concurrency.

PostgreSQL server versions, Release Dates and EOL:

Steps to install PostgreSQL 14 on Linux

Installation of PostgreSQL 14 on Linux is easy and straightforward. This guide will help you on how to install PostgreSQL 14 on Linux servers.

Prerequisites:

• sudo privileges.
• Stable internet connection.
• Disable SELinux and Firewall.

Installation on Red Hat family:

Steps to install latest PostgreSQL 14 on Red Hat Enterprise Linux, CentOS, Oracle Linux and Rocky Linux

First install PostgreSQL rpm repository:

For RHEL, CentOS and Oracle version 7
# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

For RHEL, CentOS, Oracle and Rocky version 8
# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

For RHEL, CentOS, Oracle and Rocky version 9
# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

 Need to disable the built-in PostgreSQL module for OS version 8 and 9:

# dnf -qy module disable postgresql

 Now install PostgreSQL 14 server:

For RHEL, CentOS and Oracle version 7
# yum install -y postgresql14-server

For RHEL, CentOS, Oracle and Rocky version 8 and 9
# dnf install -y postgresql14-server

 Initialize the database and enable automatic start:

# /usr/pgsql-14/bin/postgresql-14-setup initdb
# systemctl enable postgresql-14
# systemctl start postgresql-14

Manual installation for particular version
Note: Choose rpm package from here https://yum.postgresql.org/rpmchart/

# cd /tmp

# wget https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-8-x86_64/postgresql14-14.5-1PGDG.rhel8.x86_64.rpm

# wget https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-8-x86_64/postgresql14-libs-14.5-1PGDG.rhel8.x86_64.rpm

# wget https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-8-x86_64/postgresql14-server-14.5-1PGDG.rhel8.x86_64.rpm

# rpm -ivh postgresql14*.rpm

Installation on Debian family (Debian and Ubuntu):

Steps to install latest PostgreSQL 14 on Debian and Ubuntu

 Create the file repository configuration:

# sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Import the repository signing key:

# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Update the package lists:

# sudo apt-get update

Now install the latest version of PostgreSQL:
Note: If you want a specific version, use ‘postgresql-12’ or similar instead of ‘postgresql’

# sudo apt-get -y install postgresql

Configure PostgreSQL:

Connect to a Local Database:

# sudo -u postgres psql

OR

# psql -h localhost -p 5432 -U postgres -d postgres

Connect to Remote Database:

If we want to connect from remote then we need to make changes in conf files

# sudo vim /var/lib/pgsql/14/data/pg_hba.conf
and add below line
host    all             all             0.0.0.0/0            md5

Then
# sudo vim /var/lib/pgsql/14/data/postgresql.conf
and edit below line
listen_addresses = '*'

# systemctl restart postgresql-14.service

# psql -h <database ip> -p 5432 -U postgres -d postgres

Create user and database:

postgres=# create database dbname;
postgres=# create user dbuser with encrypted password 'password';
postgres=# grant all privileges on database dbname to dbuser;

Check created database and user:

List database:
postgres=# \l

List user:
postgres=# \du

Connect to database:
postgres=# \c dbname

Congratulations! We’ve installed and configured PostgreSQL 14 database server on Linux system. We hope this 2 minutes guide helped you and thank you for visiting our website.
Cheers!!!