Installing PostgreSQL on Linux [CentOS] – A Step by Step Guide

Installing PostgreSQL on Linux [CentOS] – A Step by Step Guide

PostgreSQL has earned a strong reputation for being the world’s most advanced open-source Relational database. Over the past few years, it has seen a sharp rise in popularity as a Tier-1 RDBMS and more and more organizations are embracing PostgreSQL nowadays.

Moreover, it’s open-source, free to use, community-driven, without being owned by a single company, standards-compliant, has proven architecture, reliability and data integrity, is filled with useful features and is very extensible at the same time.

I think as a database professional, it's a good time to get our hands dirty and learn this amazing technology.

We will install PostgreSQL 12 on the CentOS distribution of the Linux Server for this demonstration.

Talking about the installation prerequisite, just make sure that you have logged in as a user with sudo privileges on the system. PostgreSQL does not ask for any fancy requirements and a modern Unix-compatible system should be able to run PostgreSQL without any issues. For all the curious souls out there, take a look here https://www.postgresql.org/docs/12/install-requirements.html for details.

The below guide will take you through the steps used to install PostgreSQL 12 on CentOS:

  1. Install the PostgreSQL Software:

PostgreSQL provides a repository of packages of all supported versions for the most common Linux distributions including CentOS.

So, there are PostgreSQL packages in the base repositories for CentOS already and if this isn’t the version we want, we need to add the specific repo and pull the software from there to obtain a specific version.

You can get more information on the installed package by running the below command:

$ rpm -qi pgdg-redhat-repo

To keep our packages from being pulled from the wrong repository or being overwritten from the wrong repository in the future, we are first going to edit the repo file for our distribution.

  • Open the distribution .repo file in vi editor. Use sudo to elevate the permission to run the command.
$ sudo vi /etc/yum.repos.d/CentOS-Base.repo
  • Exclude PostgreSQL by appending the following line to both the [base] and [updates] sections. Make sure to put a blank line after the line.
$ exclude=postgresql*
# CentOS-Base.repo
#
# The mirror system uses the connecting IP address of the client and the
# update status of each mirror to pick mirrors that are updated to and
# geographically close to the client.  You should use this for CentOS updates
# unless you are manually picking other mirrors.
#
# If the mirrorlist= does not work for you, as a fall back you can try the
# remarked out baseurl= line instead.
#
#

[base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os&infra=$infra
#baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
exclude=postgresql*

#released updates
[updates]
name=CentOS-$releasever - Updates
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates&infra=$infra
#baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
exclude=postgresql*

#additional packages that may be useful
[extras]
name=CentOS-$releasever - Extras
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=extras&infra=$infra
#baseurl=http://mirror.centos.org/centos/$releasever/extras/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7

#additional packages that extend functionality of existing packages
[centosplus]
name=CentOS-$releasever - Plus
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=centosplus&infra=$infra
#baseurl=http://mirror.centos.org/centos/$releasever/centosplus/$basearch/
gpgcheck=1
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
$ sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
[user@server ~]$ sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Loaded plugins: fastestmirror
pgdg-redhat-repo-latest.noarch.rpm                                                                                                                                                                                     | 6.5 kB  00:00:00     
Examining /var/tmp/yum-root-GABjzR/pgdg-redhat-repo-latest.noarch.rpm: pgdg-redhat-repo-42.0-11.noarch
Marking /var/tmp/yum-root-GABjzR/pgdg-redhat-repo-latest.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package pgdg-redhat-repo.noarch 0:42.0-11 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================================================================================================
 Package                                                   Arch                                            Version                                             Repository                                                                Size
==============================================================================================================================================================================================================================================
Installing:
 pgdg-redhat-repo                                          noarch                                          42.0-11                                             /pgdg-redhat-repo-latest.noarch                                           11 k

Transaction Summary
==============================================================================================================================================================================================================================================
Install  1 Package

Total size: 11 k
Installed size: 11 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pgdg-redhat-repo-42.0-11.noarch                                                                                                                                                                                            1/1
  Verifying  : pgdg-redhat-repo-42.0-11.noarch                                                                                                                                                                                            1/1

Installed:
  pgdg-redhat-repo.noarch 0:42.0-11                                                                                                                                                                                                           

Complete!
  • Install PostgreSQL 12:
$ sudo yum install -y postgresql12-server postgresql12-contrib
[user@server ~]$ sudo yum install -y postgresql12-server postgresql12-contrib
Loaded plugins: fastestmirror
Determining fastest mirrors
epel/x86_64/metalink                                                                                                                                                                                                   | 5.3 kB  00:00:00     
 * base: d36uatko69830t.cloudfront.net
 * epel: d2lzkl7pfhq30w.cloudfront.net
 * extras: d36uatko69830t.cloudfront.net
 * nux-dextop: mirror.li.nux.ro
 * updates: d36uatko69830t.cloudfront.net
base                                                                                                                                                                                                                   | 3.6 kB  00:00:00     
epel                                                                                                                                                                                                                   | 4.7 kB  00:00:00     
extras                                                                                                                                                                                                                 | 2.9 kB  00:00:00     
nux-dextop                                                                                                                                                                                                             | 2.9 kB  00:00:00     
pgdg-common                                                                                                                                                                                                            | 2.9 kB  00:00:00     
pgdg10                                                                                                                                                                                                                 | 3.6 kB  00:00:00     
pgdg11                                                                                                                                                                                                                 | 3.6 kB  00:00:00     
pgdg12                                                                                                                                                                                                                 | 3.6 kB  00:00:00     
pgdg95                                                                                                                                                                                                                 | 3.6 kB  00:00:00     
pgdg96                                                                                                                                                                                                                 | 3.6 kB  00:00:00     
updates                                                                                                                                                                                                                | 2.9 kB  00:00:00     
xrdp                                                                                                                                                                                                                   | 2.9 kB  00:00:00     
(1/20): base/7/x86_64/group_gz                                                                                                                                                                                         | 153 kB  00:00:00     
(2/20): extras/7/x86_64/primary_db                                                                                                                                                                                     | 194 kB  00:00:00     
(3/20): epel/x86_64/updateinfo                                                                                                                                                                                         | 1.0 MB  00:00:00     
(4/20): epel/x86_64/group_gz                                                                                                                                                                                           |  95 kB  00:00:00     
(5/20): epel/x86_64/primary_db                                                                                                                                                                                         | 6.8 MB  00:00:00     
(6/20): base/7/x86_64/primary_db                                                                                                                                                                                       | 6.1 MB  00:00:01     
(7/20): pgdg10/7/x86_64/group_gz                                                                                                                                                                                       |  245 B  00:00:01     
(8/20): nux-dextop/x86_64/primary_db                                                                                                                                                                                   | 1.8 MB  00:00:01     
(9/20): pgdg-common/7/x86_64/primary_db                                                                                                                                                                                | 116 kB  00:00:02     
(10/20): pgdg11/7/x86_64/group_gz                                                                                                                                                                                      |  245 B  00:00:01     
(11/20): pgdg11/7/x86_64/primary_db                                                                                                                                                                                    | 219 kB  00:00:01     
(12/20): pgdg95/7/x86_64/group_gz                                                                                                                                                                                      |  249 B  00:00:00     
(13/20): pgdg12/7/x86_64/primary_db                                                                                                                                                                                    | 135 kB  00:00:00     
(14/20): pgdg96/7/x86_64/group_gz                                                                                                                                                                                      |  249 B  00:00:00     
(15/20): pgdg12/7/x86_64/group_gz                                                                                                                                                                                      |  245 B  00:00:01     
(16/20): pgdg95/7/x86_64/primary_db                                                                                                                                                                                    | 206 kB  00:00:00     
(17/20): pgdg10/7/x86_64/primary_db                                                                                                                                                                                    | 220 kB  00:00:02     
(18/20): pgdg96/7/x86_64/primary_db                                                                                                                                                                                    | 215 kB  00:00:00     
(19/20): updates/7/x86_64/primary_db                                                                                                                                                                                   | 2.9 MB  00:00:00     
(20/20): xrdp/primary_db                                                                                                                                                                                               | 1.8 MB  00:00:02     
Resolving Dependencies
--> Running transaction check
---> Package postgresql12-contrib.x86_64 0:12.3-5PGDG.rhel7 will be installed
--> Processing Dependency: postgresql12-libs(x86-64) = 12.3-5PGDG.rhel7 for package: postgresql12-contrib-12.3-5PGDG.rhel7.x86_64
--> Processing Dependency: postgresql12(x86-64) = 12.3-5PGDG.rhel7 for package: postgresql12-contrib-12.3-5PGDG.rhel7.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql12-contrib-12.3-5PGDG.rhel7.x86_64
---> Package postgresql12-server.x86_64 0:12.3-5PGDG.rhel7 will be installed
--> Running transaction check
---> Package postgresql12.x86_64 0:12.3-5PGDG.rhel7 will be installed
---> Package postgresql12-libs.x86_64 0:12.3-5PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================================================================================================
 Package                                                          Arch                                               Version                                                         Repository                                          Size
==============================================================================================================================================================================================================================================
Installing:
 postgresql12-contrib                                             x86_64                                             12.3-5PGDG.rhel7                                                pgdg12                                             609 k
 postgresql12-server                                              x86_64                                             12.3-5PGDG.rhel7                                                pgdg12                                             5.0 M
Installing for dependencies:
 postgresql12                                                     x86_64                                             12.3-5PGDG.rhel7                                                pgdg12                                             1.6 M
 postgresql12-libs                                                x86_64                                             12.3-5PGDG.rhel7                                                pgdg12                                             369 k

Transaction Summary
==============================================================================================================================================================================================================================================
Install  2 Packages (+2 Dependent packages)

Total download size: 7.5 M
Installed size: 31 M
Downloading packages:
warning: /var/cache/yum/x86_64/7/pgdg12/packages/postgresql12-12.3-5PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY                                                             ] 486 kB/s | 864 kB  00:00:14 ETA 
Public key for postgresql12-12.3-5PGDG.rhel7.x86_64.rpm is not installed
(1/4): postgresql12-12.3-5PGDG.rhel7.x86_64.rpm                                                                                                                                                                        | 1.6 MB  00:00:03     
(2/4): postgresql12-contrib-12.3-5PGDG.rhel7.x86_64.rpm                                                                                                                                                                | 609 kB  00:00:03     
(3/4): postgresql12-libs-12.3-5PGDG.rhel7.x86_64.rpm                                                                                                                                                                   | 369 kB  00:00:00     
(4/4): postgresql12-server-12.3-5PGDG.rhel7.x86_64.rpm                                                                                                                                                                 | 5.0 MB  00:00:03     
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                                                                         1.1 MB/s | 7.5 MB  00:00:06     
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsqlrpms-hackers@pgfoundry.org>"
 Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
 Package    : pgdg-redhat-repo-42.0-11.noarch (@/pgdg-redhat-repo-latest.noarch)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : postgresql12-libs-12.3-5PGDG.rhel7.x86_64                                                                                                                                                                                  1/4
  Installing : postgresql12-12.3-5PGDG.rhel7.x86_64                                                                                                                                                                                       2/4
  Installing : postgresql12-server-12.3-5PGDG.rhel7.x86_64                                                                                                                                                                                3/4
  Installing : postgresql12-contrib-12.3-5PGDG.rhel7.x86_64                                                                                                                                                                               4/4
  Verifying  : postgresql12-libs-12.3-5PGDG.rhel7.x86_64                                                                                                                                                                                  1/4
  Verifying  : postgresql12-contrib-12.3-5PGDG.rhel7.x86_64                                                                                                                                                                               2/4
  Verifying  : postgresql12-server-12.3-5PGDG.rhel7.x86_64                                                                                                                                                                                3/4
  Verifying  : postgresql12-12.3-5PGDG.rhel7.x86_64                                                                                                                                                                                       4/4

Installed:
  postgresql12-contrib.x86_64 0:12.3-5PGDG.rhel7                                                                         postgresql12-server.x86_64 0:12.3-5PGDG.rhel7                                                                        

Dependency Installed:
  postgresql12.x86_64 0:12.3-5PGDG.rhel7                                                                              postgresql12-libs.x86_64 0:12.3-5PGDG.rhel7                                                                             

Complete!
  1. Initialize the Database and start PostgreSQL:
  • Initialize the database:
$ sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
[user@server ~]$ sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
Initializing database ... OK
  • Start the PostgreSQL-12 service:
$ sudo systemctl start postgresql-12
[user@server ~]$ sudo systemctl start postgresql-12
[user@server ~]$
  • Configure PostgreSQL to start on boot:
$ sudo systemctl enable postgresql-12
[user@server ~]$ sudo systemctl enable postgresql-12
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-12.service to /usr/lib/systemd/system/postgresql-12.service.
[user@server ~]$
  1. Connect to the instance:

When you install PostgreSQL, a Linux user named postgres is created by default with superuser privilege. We will use this user to connect to the newly installed PostgreSQL Instance.

  • Let’s change to the postgres user:
$ sudo su - postgres
[user@server ~]$ sudo su - postgres
-bash-4.2$
  • Type psql and press enter. It will take you to the psql prompt, i.e. postgres=# . Once you see the psql prompt, rest assured that your installation is correct.
$ psql
-bash-4.2$ psql
psql (12.3)
Type "help" for help.

postgres=#
  • Now, let’s give the postgres role a password
\password postgres
postgres=# \password postgres
Enter new password: 
Enter it again: 
postgres=#

We are all set now🚀 This marks the completion of the installation steps for PostgreSQL on Linux.

Next comes the configuration of the newly installed PostgreSQL instance as per our requirement. This has been discussed in a separate post.