Introduction
Another article for showing the installation process of PostgreSQL database server in a machine or server running in Linux CentOS 8. The process for installing PostgreSQL database server will use a specific tool. It is using the yum package management tool. Before installing the PostgreSQL database server, there is another important step which is very important. It is installing the correct PostgreSQL database server yum repository. It is very useful for yum to be able to install the correct version of the PostgreSQL database server. The reason is because the yum tool will look for the PostgreSQL database server through the yum repository.
PostgreSQL database server Installation
In the installation process. before going out to the detail, the installation itself consists of several parts. It is the part before the actual installation. Following with the installation step itself and the last one is the part after installation.
PostgreSQL Database Server Before Installation Part
So, what are the things which is must be ready before the actual installation of PostgreSQL database server ?. Well, the following steps will inform what are things which need to be available in order the actual installation process can took place. Just perform the following steps :
-
First of all, just install the specific yum repository in order for yum to search and recognize the correct version of PostgreSQL database server for further exection. Just execute the following command :
[root@localhost ~]# 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 | 7.7 kB 00:00:00 Examining /var/tmp/yum-root-Jmy_Cq/pgdg-redhat-repo-latest.noarch.rpm: pgdg-redhat-repo-42.0-20.noarch Marking /var/tmp/yum-root-Jmy_Cq/pgdg-redhat-repo-latest.noarch.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package pgdg-redhat-repo.noarch 0:42.0-20 will be installed --> Finished Dependency Resolution Dependencies Resolved ======================================================================================================================== Package Arch Version Repository Size ========================================================================================================================Installing: pgdg-redhat-repo noarch 42.0-20 /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-20.noarch 1/1 Verifying : pgdg-redhat-repo-42.0-20.noarch 1/1 Installed: pgdg-redhat-repo.noarch 0:42.0-20 Complete! [root@localhost ~]#
-
Next, after successfully installing the PostgreSQL database server yum repository, just search the package name of the PostreSQL database server by typing the following command :
[root@localhost ~]# yum search postgresql14 Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: xxxxxxxxxx.xxxxxxxxxx.xxx.xxx * epel: xxxxxxxxxx.xxxxxxxxxx.xxx.xxx * extras: xxxxxxxxxx.xxxxxxxxxx.xxx.xxx * updates: xxxxxxxxxx.xxxxxxxxxx.xxx.xxx pgdg-common/7/x86_64/signature | 198 B 00:00:00 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-20.noarch (@/pgdg-redhat-repo-latest.noarch) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Is this ok [y/N]: y pgdg-common/7/x86_64/signature | 2.9 kB 00:00:03 !!! pgdg10/7/x86_64/signature | 198 B 00:00:00 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-20.noarch (@/pgdg-redhat-repo-latest.noarch) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Is this ok [y/N]: y pgdg10/7/x86_64/signature | 3.6 kB 00:00:01 !!! pgdg11/7/x86_64/signature | 198 B 00:00:00 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-20.noarch (@/pgdg-redhat-repo-latest.noarch) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Is this ok [y/N]: y pgdg11/7/x86_64/signature | 3.6 kB 00:00:01 !!! pgdg12/7/x86_64/signature | 198 B 00:00:00 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-20.noarch (@/pgdg-redhat-repo-latest.noarch) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Is this ok [y/N]: y pgdg12/7/x86_64/signature | 3.6 kB 00:00:01 !!! pgdg13/7/x86_64/signature | 198 B 00:00:00 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-20.noarch (@/pgdg-redhat-repo-latest.noarch) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Is this ok [y/N]: y pgdg13/7/x86_64/signature | 3.6 kB 00:00:01 !!! pgdg14/7/x86_64/signature | 198 B 00:00:00 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-20.noarch (@/pgdg-redhat-repo-latest.noarch) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Is this ok [y/N]: y pgdg14/7/x86_64/signature | 3.6 kB 00:00:01 !!! pgdg96/7/x86_64/signature | 198 B 00:00:00 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-20.noarch (@/pgdg-redhat-repo-latest.noarch) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Is this ok [y/N]: y pgdg96/7/x86_64/signature | 3.6 kB 00:00:01 !!! (1/13): pgdg-common/7/x86_64/primary_db | 147 kB 00:00:06 (2/13): pgdg12/7/x86_64/group_gz | 245 B 00:00:00 (3/13): pgdg10/7/x86_64/primary_db | 355 kB 00:00:07 (4/13): pgdg13/7/x86_64/group_gz | 246 B 00:00:00 (5/13): pgdg12/7/x86_64/primary_db | 240 kB 00:00:00 (6/13): pgdg13/7/x86_64/primary_db | 155 kB 00:00:00 (7/13): pgdg14/7/x86_64/group_gz | 244 B 00:00:00 (8/13): pgdg14/7/x86_64/primary_db | 72 kB 00:00:00 (9/13): pgdg96/7/x86_64/group_gz | 249 B 00:00:00 (10/13): pgdg96/7/x86_64/primary_db | 341 kB 00:00:00 (11/13): pgdg11/7/x86_64/group_gz | 245 B 00:00:08 (12/13): pgdg11/7/x86_64/primary_db | 380 kB 00:00:09 (13/13): pgdg10/7/x86_64/group_gz | 245 B 00:00:12 ============================================== N/S matched: postgresql14 ===============================================postgresql14.x86_64 : PostgreSQL client programs and libraries postgresql14-contrib.x86_64 : Contributed source and binaries distributed with PostgreSQL postgresql14-devel.x86_64 : PostgreSQL development header files and libraries postgresql14-docs.x86_64 : Extra documentation for PostgreSQL postgresql14-libs.x86_64 : The shared libraries required for any PostgreSQL clients postgresql14-llvmjit.x86_64 : Just-in-time compilation support for PostgreSQL postgresql14-odbc.x86_64 : PostgreSQL ODBC driver postgresql14-plperl.x86_64 : The Perl procedural language for PostgreSQL postgresql14-plpython3.x86_64 : The Python3 procedural language for PostgreSQL postgresql14-pltcl.x86_64 : The Tcl procedural language for PostgreSQL postgresql14-server.x86_64 : The programs needed to create and run a PostgreSQL server postgresql14-tcl.x86_64 : A Tcl client library for PostgreSQL postgresql14-test.x86_64 : The test suite distributed with PostgreSQL Name and summary matches only, use "search all" for everything. [root@localhost ~]#
The above search parameter is using postgresql14 with the definite version of it. It is just trying to predict the available package name which is normally have the pattern of ‘postgresqlxx’ with ‘xx’ as the main version number.
PostgreSQL Database Server Installation
This is the next part where the actual installation of PostgreSQL database server exist. Continuing the previous part, since the postgresql14 package name exist in the search step in the previous part, the installation can proceed. So, perform the following installation steps as follows :
-
In this first step, just perform the following command :
[root@localhost ~]# yum -y install postgresql14 postgresql14-server Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: xxxxxxxxxx.xxxxxxxxxx.xxx.xxx * epel: xxxxxxxxxx.xxxxxxxxxx.xxx.xxx * extras: xxxxxxxxxx.xxxxxxxxxx.xxx.xxx * updates: xxxxxxxxxx.xxxxxxxxxx.xxx.xxx Resolving Dependencies --> Running transaction check ---> Package postgresql14.x86_64 0:14.1-1PGDG.rhel7 will be installed --> Processing Dependency: postgresql14-libs(x86-64) = 14.1-1PGDG.rhel7 for package: postgresql14-14.1-1PGDG.rhel7.x86_64 --> Processing Dependency: libicu for package: postgresql14-14.1-1PGDG.rhel7.x86_64 --> Processing Dependency: libpq.so.5()(64bit) for package: postgresql14-14.1-1PGDG.rhel7.x86_64 ---> Package postgresql14-server.x86_64 0:14.1-1PGDG.rhel7 will be installed --> Running transaction check ---> Package libicu.x86_64 0:50.2-4.el7_7 will be installed ---> Package postgresql14-libs.x86_64 0:14.1-1PGDG.rhel7 will be installed --> Finished Dependency Resolution Dependencies Resolved ======================================================================================================================== Package Arch Version Repository Size ========================================================================================================================Installing: postgresql14 x86_64 14.1-1PGDG.rhel7 pgdg14 1.5 M postgresql14-server x86_64 14.1-1PGDG.rhel7 pgdg14 5.5 M Installing for dependencies: libicu x86_64 50.2-4.el7_7 base 6.9 M postgresql14-libs x86_64 14.1-1PGDG.rhel7 pgdg14 265 k Transaction Summary ========================================================================================================================Install 2 Packages (+2 Dependent packages) Total download size: 14 M Installed size: 55 M Downloading packages: (1/4): libicu-50.2-4.el7_7.x86_64.rpm | 6.9 MB 00:00:00 warning: /var/cache/yum/x86_64/7/pgdg14/packages/postgresql14-libs-14.1-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY Public key for postgresql14-libs-14.1-1PGDG.rhel7.x86_64.rpm is not installed (2/4): postgresql14-libs-14.1-1PGDG.rhel7.x86_64.rpm | 265 kB 00:00:01 (3/4): postgresql14-14.1-1PGDG.rhel7.x86_64.rpm | 1.5 MB 00:00:02 (4/4): postgresql14-server-14.1-1PGDG.rhel7.x86_64.rpm | 5.5 MB 00:00:01 ------------------------------------------------------------------------------------------------------------------------Total 4.9 MB/s | 14 MB 00:00:02 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-20.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 : libicu-50.2-4.el7_7.x86_64 1/4 Installing : postgresql14-libs-14.1-1PGDG.rhel7.x86_64 2/4 Installing : postgresql14-14.1-1PGDG.rhel7.x86_64 3/4 Installing : postgresql14-server-14.1-1PGDG.rhel7.x86_64 4/4 Verifying : postgresql14-14.1-1PGDG.rhel7.x86_64 1/4 Verifying : postgresql14-server-14.1-1PGDG.rhel7.x86_64 2/4 Verifying : postgresql14-libs-14.1-1PGDG.rhel7.x86_64 3/4 Verifying : libicu-50.2-4.el7_7.x86_64 4/4 Installed: postgresql14.x86_64 0:14.1-1PGDG.rhel7 postgresql14-server.x86_64 0:14.1-1PGDG.rhel7 Dependency Installed: libicu.x86_64 0:50.2-4.el7_7 postgresql14-libs.x86_64 0:14.1-1PGDG.rhel7 Complete! [root@localhost ~]#
-
After successfully installing PostgreSQL database server, just execute the following command to initialize it :
[root@localhost ~]# /usr/pgsql-14/bin/postgresql-14-setup initdb Initializing database ... OK [root@localhost ~]#
-
Following after, just start the PostgreSQL database server by performing the following command :
[root@localhost ~]# systemctl start postgresql-14 [root@localhost ~]#
PostgreSQL Database Server After Installation Part
After successfully installing PostgreSQL database server, the following are the steps to start using it :
-
First of all, initialize the super user of the PostgreSQL database server’s password by executing the following command :
[root@localhost ~]# su - postgres Last login: Wed Nov 17 15:33:42 UTC 2021 on pts/2 -bash-4.2$ psql -c "alter user postgres with password 'password'" ALTER ROLE -bash-4.2$ exit logout [root@localhost ~]#
-
Next step, before logging in to the PostgreSQL database server using the password as in the above step, edit a file which is representing the authentication method for logging into it. It exist in /var/lib/pgsql/14/data/pg_hba.conf in this context. By default it will have ‘peer’ authentication method for every connection to the local machine or server using every account. Change the following line below :
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer
Remark the original line which is requiring peer method authentication with a ‘scram-sha-256’ method authentication. So, the content will exist as follows :
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only #local all all peer local all all scram-sha-256
-
Following after the above step, do not forget to restart it. Actually, it is important so the changes to the configuration file above is implemented.
[root@localhost ~]# systemctl restart postgresql-14 [root@localhost ~]#
-
Last but not least, just try to access the PostgreSQL database server again using the password before as follows :
[root@localhost ~]# psql -Upostgres Password for user postgres: psql (14.1) Type "help" for help. postgres=# \q [root@localhost ~]#