PostgreSQL Installation on CentOS 7

There are multiple ways of installation of the PostgreSQL database on Unix like platforms.

We will explore the installation of PostgreSQL version 12 on CentOS 7 using YUM installation.

Let’s start.

Verifying the version of CentOS:

root@localhost yum.repos.d]# rpm -q centos-release
centos-release-7-7.1908.0.el7.centos.x86_64

PostgreSQL allows us to download many version across multiple platform, one should visit its official download page for downloading the required version.

By default, it will prompt to download latest stable version. Once version, platform and it’s architecture selected, it will provide the complete yum command for installation of the selected version.

However, before performing this installation task you should validate if PostgreSQL’s YUM repository is installed on the machine.

We will start PostgreSQL version 12 installation by installing its repository. We are Downloading repository RPM from the PostgreSQL official website for PostgreSQL 12 on CentOS 7 x86_64.

The download RPM is listed :

[root@localhost Downloads]# ll
total 8
drwxr-xr-x. 2 root root 180 Jan 24 11:59 others
-rw-rw-r–. 1 sanjeeva sanjeeva 6048 Jan 24 11:58 pgdg-redhat-repo-latest.noarch.rpm
[root@localhost Downloads]#rpm -ivh pgdg-redhat-repo-latest.noarch

This can be downloaded using CURL to download this rpm:
`curl -O http://yum.postgresql.org/11/redhat/rhel-7-x86_64/postgresql11-contrib-11.10-1PGDG.rhel7.aarch64.rpm.rpm

This will create repository in the YUM configuration folder. To validate, navigate to the YUM folder.

[root@localhost yum.repos.d]# cd /etc/yum.repos.d
[root@localhost yum.repos.d]# pwd
/etc/yum.repos.d
[root@localhost yum.repos.d]#
[root@localhost yum.repos.d]# ls -ltr
total 52
-rw-r–r–. 1 root root 477 Jul 31 2014 nux-dextop.repo
-rw-r–r–. 1 root root 6639 Sep 5 18:35 CentOS-Vault.repo
-rw-r–r–. 1 root root 1331 Sep 5 18:35 CentOS-Sources.repo
-rw-r–r–. 1 root root 630 Sep 5 18:35 CentOS-Media.repo
-rw-r–r–. 1 root root 314 Sep 5 18:35 CentOS-fasttrack.repo
-rw-r–r–. 1 root root 649 Sep 5 18:35 CentOS-Debuginfo.repo
-rw-r–r–. 1 root root 1309 Sep 5 18:35 CentOS-CR.repo
-rw-r–r–. 1 root root 1664 Sep 5 18:35 CentOS-Base.repo
-rw-r–r–. 1 root root 1149 Sep 18 04:55 epel-testing.repo
-rw-r–r–. 1 root root 1050 Sep 18 04:55 epel.repo
-rw-r–r–. 1 root root 5904 Sep 27 18:34 pgdg-redhat-all.repo
[root@localhost yum.repos.d]#

Content of repository should look alike:

[root@localhost yum.repos.d]# more pgdg-redhat-all.repo

# PGDG Red Hat Enterprise Linux / CentOS stable repositories:

[pgdg12]
name=PostgreSQL 12 for RHEL/CentOS $releasever – $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

[pgdg11]
name=PostgreSQL 11 for RHEL/CentOS $releasever – $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
.
.
.

Once repository is installed, we can start installation of PostgreSQL database. If we have to install a specific version we should supply the version number in YUM command, else the installer will install it’s default version

PostgreSQL has defined a default version based on which operating system it is being installed. For CentOS 7, is PosgreSQL 9.2 version as default version. Hence, we have to provide the explicit version 12 for installation of this version during installation. Hence the command would be

[root@localhost Downloads]# yum install postgresql12-server
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: centos.myfahim.com
* epel: mirror2.totbb.net
* extras: centos.myfahim.com
* nux-dextop: li.nux.ro
* updates: centos.myfahim.com
Resolving Dependencies
–> Running transaction check
—> Package postgresql12-server.x86_64 0:12.1-2PGDG.rhel7 will be installed
–> Processing Dependency: postgresql12-libs(x86-64) = 12.1-2PGDG.rhel7 for package: postgresql12-server-12.1-2PGDG.rhel7.x86_64
–> Processing Dependency: postgresql12(x86-64) = 12.1-2PGDG.rhel7 for package: postgresql12-server-12.1-2PGDG.rhel7.x86_64
–> Processing Dependency: libpq.so.5()(64bit) for package: postgresql12-server-12.1-2PGDG.rhel7.x86_64
–> Running transaction check
—> Package postgresql12.x86_64 0:12.1-2PGDG.rhel7 will be installed
—> Package postgresql12-libs.x86_64 0:12.1-2PGDG.rhel7 will be installed
–> Finished Dependency Resolution
Dependencies Resolved
==============================================================================
Package                                                  Arch                         Version                                     Repository               Size
==============================================================================
Installing:
postgresql12-server                     x86_64                     12.1-2PGDG.rhel7                     pgdg12                     13 M
Installing for dependencies:
postgresql12                                    x86_64                     12.1-2PGDG.rhel7                     pgdg12                     2.7 M
postgresql12-libs                           x86_64                     12.1-2PGDG.rhel7                     pgdg12                     759 k
Transaction Summary
==============================================================================
Install 1 Package (+2 Dependent packages)
Total download size: 16 M
Installed size: 68 M
Is this ok [y/d/N]: y
Downloading packages:
(1/3): postgresql12-libs-12.1-2PGDG.rhel7.x86_64.rpm | 759 kB 00:00:02
(2/3): postgresql12-12.1-2PGDG.rhel7.x86_64.rpm | 2.7 MB 00:00:05
(3/3): postgresql12-server-12.1-2PGDG.rhel7.x86_64.rpm | 13 MB 00:00:11
—————————————————————————————————————————————————————————–
Total 1.1 MB/s | 16 MB 00:00:14
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql12-libs-12.1-2PGDG.rhel7.x86_64 1/3
Installing : postgresql12-12.1-2PGDG.rhel7.x86_64 2/3
Installing : postgresql12-server-12.1-2PGDG.rhel7.x86_64 3/3
Verifying : postgresql12-libs-12.1-2PGDG.rhel7.x86_64 1/3
Verifying : postgresql12-server-12.1-2PGDG.rhel7.x86_64 2/3
Verifying : postgresql12-12.1-2PGDG.rhel7.x86_64 3/3
Installed:
postgresql12-server.x86_64 0:12.1-2PGDG.rhel7
Dependency Installed:
postgresql12.x86_64 0:12.1-2PGDG.rhel7
postgresql12-libs.x86_64 0:12.1-2PGDG.rhel7
Complete!
[root@localhost Downloads]#

Once server is installed, you need to install it’s contrib package as well. This can be installed in one go, however, for the sake of clarity, I am installing it separate.

[root@localhost Downloads]# yum install postgresql12-contrib
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: centos.myfahim.com
* epel: mirror2.totbb.net
* extras: centos.myfahim.com
* nux-dextop: li.nux.ro
* updates: centos.myfahim.com
Resolving Dependencies
–> Running transaction check
—> Package postgresql12-contrib.x86_64 0:12.1-2PGDG.rhel7 will be installed
–> Finished Dependency Resolution
Dependencies Resolved
==============================================================================
Package                                                  Arch                         Version                                          Repository               Size
==============================================================================
Installing:
postgresql12-contrib                    x86_64                     12.1-2PGDG.rhel7                   pgdg12                    2.0 M
Transaction Summary
==============================================================================
Install 1 Package
Total download size: 2.0 M
Installed size: 7.5 M
Is this ok [y/d/N]: y
Downloading packages:
postgresql12-contrib-12.1-2PGDG.rhel7.x86_64.rpm | 2.0 MB 00:00:03
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql12-contrib-12.1-2PGDG.rhel7.x86_64 1/1
Verifying : postgresql12-contrib-12.1-2PGDG.rhel7.x86_64 1/1
Installed:
postgresql12-contrib.x86_64 0:12.1-2PGDG.rhel7
Complete!
[root@localhost Downloads]#

A folder with version name in folder /var/lib/pgsql with this installations is created which is empty and waiting for the database initialization procedure to get it filled. The DB initialization process is required to get started.

[root@localhost pgsql]# pwd
/var/lib/pgsql
[root@localhost pgsql]# ls -ltr
total 0
drwx——. 4 postgres postgres 33 Jan 24 12:14 12
[root@localhost pgsql]#

Lets start the DB initialization process. The database has given a tool named “postgresql-12-setup” which should be execute to initialize DB.

[root@localhost pgsql]# /usr/pgsql-12/bin/postgresql-12-setup initdb
Initializing database … OK

[root@localhost pgsql]#

Initialization creates a folder with version name. In this case, it is 12. You may delve into this folder for further details. It will show you 2 folders. one is data folder and other is backup.

[root@localhost pgsql]# ls 12/ backups data initdb.log [root@localhost pgsql]#

At this point your DB is ready to be started. You also require to enable this service.

[root@localhost Downloads]# 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.
[root@localhost Downloads]#
[root@localhost Downloads]# systemctl start postgresql-12.service
[root@localhost Downloads]#

Validating the installation and initialization of DB. Because of this DB is just initialized, it will not have any custom database. By default it will be creating postgres user for administration purpose. By this time, this user will also not have any password. An UNIX user is created by installation/initialization process by which you should logged in. This will be done by client tool psql supplied by default in PostgreSQL.

[root@localhost data]# su postgres
bash-4.2$

bash-4.2$ psql
psql (12.1)
Type “help” for help.
postgres=#
postgres=# select version();
                                          version
———————————————————————————————————
PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
postgres=#

You must reset password for postgres user to make your DB secure using alter user postgres with password ‘verystrongpassword’ once you logged into psql

I will post other blogs on how to secure your database at later stage.

Leave a Reply

Your email address will not be published. Required fields are marked *