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:
- 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. Usesudo
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
- Download and install the PGDG file from postgresql.org (https://www.postgresql.org/download/):
$ 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!
- 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 ~]$
- 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 thepsql
prompt, i.e.postgres=#
. Once you see thepsql
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.