ROOT User Approach – How to Install and Configure PostgreSQL 13 in RHEL 7 –

by

How to install PostgreSQL 13 in REHL 7 From Repository

PostgreSQL can be installed by means of two ways

  1. Installing from source
  2. Installing binary packages

Each method has its own advantages and disadvantages, however, we will limit this post to learn how to install PostgreSQL using binaries packages.

Follow the steps here to get the automated scripts to install PostgreSQL 13 in RHEL 7.

Install PostgreSQL 13 – Yum Repository Method [step by step]:

I assume that all the prerequisites for installing PostgreSQL have met, I highly recommend to follow this link to learn more about prerequisites.

To use the PostgreSQL Yum Repository, follow these steps:

Open the following link to select Linux version.

Choose the following things from the repository list

  1. Select Version (I choose 13)
  2. Select Platform  (I choose Rhel 7)
  3. Select Architecture  ( I choose x86_64)

Install the repository RPM:

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Install PostgreSQL 13:

yum install -y postgresql13-server

The output looks like this

[root@postgreshelp yum.repos.d]# yum install -y postgresql13-server
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
..
..
Resolving Dependencies
--> Running transaction check
---> Package postgresql13-server.x86_64 0:13.1-1PGDG.rhel7 will be installed
--> Processing Dependency: postgresql13-libs(x86-64) = 13.1-1PGDG.rhel7 for package: postgresql13-server-13.1-1PGDG.rhel7.x86_64
--> Processing Dependency: postgresql13(x86-64) = 13.1-1PGDG.rhel7 for package: postgresql13-server-13.1-1PGDG.rhel7.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql13-server-13.1-1PGDG.rhel7.x86_64
--> Running transaction check
---> Package postgresql13.x86_64 0:13.1-1PGDG.rhel7 will be installed
---> Package postgresql13-libs.x86_64 0:13.1-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===============================================================================================================
 Package                          Arch                Version                        Repository           Size
===============================================================================================================
Installing:
 postgresql13-server              x86_64              13.1-1PGDG.rhel7               pgdg13              5.4 M
Installing for dependencies:
 postgresql13                     x86_64              13.1-1PGDG.rhel7               pgdg13              1.4 M
 postgresql13-libs                x86_64              13.1-1PGDG.rhel7               pgdg13              379 k

Transaction Summary
===============================================================================================================
Install  1 Package (+2 Dependent packages)

Total download size: 7.1 M
Installed size: 30 M
Downloading packages:
(1/3): postgresql13-libs-13.1-1PGDG.rhel7.x86_64.rpm                                    | 379 kB  00:00:27
(2/3): postgresql13-13.1-1PGDG.rhel7.x86_64.rpm                                         | 1.4 MB  00:00:27
(3/3): postgresql13-server-13.1-1PGDG.rhel7.x86_64.rpm                                  | 5.4 MB  00:00:03
---------------------------------------------------------------------------------------------------------------
Total                                                                          237 kB/s | 7.1 MB  00:00:30
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : postgresql13-libs-13.1-1PGDG.rhel7.x86_64                                                   1/3
  Installing : postgresql13-13.1-1PGDG.rhel7.x86_64                                                        2/3
  Installing : postgresql13-server-13.1-1PGDG.rhel7.x86_64                                                 3/3
  Verifying  : postgresql13-libs-13.1-1PGDG.rhel7.x86_64                                                   1/3
  Verifying  : postgresql13-13.1-1PGDG.rhel7.x86_64                                                        2/3
  Verifying  : postgresql13-server-13.1-1PGDG.rhel7.x86_64                                                 3/3

Installed:
  postgresql13-server.x86_64 0:13.1-1PGDG.rhel7

Dependency Installed:
  postgresql13.x86_64 0:13.1-1PGDG.rhel7              postgresql13-libs.x86_64 0:13.1-1PGDG.rhel7

Complete!

 

The above step completes PostgreSQL 13 Server installation. It installs below packages

postgresql13: Key clients and libraries, and documentation
postgresql13-server: Server executables and data files
postgresql13-libs: Client shared libraries

How to initialize the PostgreSQL 13 database cluster

Once the software is installed, as a root initialize the cluster with below command.

/usr/pgsql-13/bin/postgresql-13-setup initdb

[root@postgreshelp pgsql-13]# /usr/pgsql-13/bin/postgresql-13-setup initdb 
Initializing database ... OK

Then you will need to start PostgreSQL with below command

systemctl start postgresql-13.service

[root@postgreshelp pgsql-13]# systemctl start postgresql-13.service 
[root@postgreshelp pgsql-13]#

Upon successful cluster startup you can check the postmaster status with

[root@postgreshelp pgsql-13]# ps -ef | grep postmaster 
postgres 3793 1 0 15:46 ? 00:00:00 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/ 
root 3811 2943 0 15:46 pts/1 00:00:00 grep --color=auto postmaster 
[root@postgreshelp pgsql-13]#

If you want your postmaster to startup during the subsequent machine reboots run the below command

systemctl enable postgresql-13.service

[root@postgreshelp pgsql-13]# systemctl enable postgresql-13.service
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-13.service to /usr/lib/systemd/system/postgresql-13.service

Post Installation checks:

  • The user ‘postgres’ is created during installation of the server subpackage.
    This user by default is UID and GID 26. The user has the default shell set to
    bash, and the home directory set to /var/lib/pgsql.
  • This user also has no default password. If you want to be able to su to it from a non-root account or login as ‘postgres’ you will need to set a password using passwd.
[root@postgreshelp ~]# passwd postgres 
Changing password for user postgres. 
New password: 
BAD PASSWORD: The password contains the user name in some form 
Retype new password: passwd: all authentication tokens updated successfully..
  • The file /var/lib/pgsql/13/.bash_profile is packaged to help with the
    setting of environment variables.Users should not edit this file, because
    it may be overwritten during every new installation.
-bash-4.2$ cat .bash_profile [ -f /etc/profile ] && source /etc/profile 
PGDATA=/var/lib/pgsql/13/data export PGDATA 
# If you want to customize your settings, 
# Use the file below. This is not overridden 
# by the RPMS. [ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile
  • Default data directory location is /var/lib/pgsql/13/data

How to initialize PostgreSQL 13 database cluster in a non default location

Stop PostgreSQL 13 database cluster if it is already running

systemctl stop postgresql-13.service

[root@postgreshelp ~]# systemctl status postgresql-13.service
● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Mon 2020-11-23 16:00:43 IST; 8s ago
     Docs: https://www.postgresql.org/docs/13/static/
...
...

Create a cluster directory and provide necessary permissions.

mkdir -p /u01/pgsql/13
chown -R postgres:postgres /u01/pgsql/13
chmod 750 /u01/pgsql/13

[root@postgreshelp ~]# mkdir -p /u01/pgsql/13
[root@postgreshelp ~]# chown -R postgres:postgres /u01/pgsql/13
[root@postgreshelp ~]# chmod 750 /u01/pgsql/13
[root@postgreshelp ~]#

Then, customize the systemd service by changing PGDATA section.

vi /lib/systemd/system/postgresql-13.service

[root@postgreshelp ~]# cat /lib/systemd/system/postgresql-13.service | grep -i PGDATA
# Note: changing PGDATA will typically require adjusting SELinux
# Note: do not use a PGDATA pathname containing spaces, or you will
Environment=PGDATA=/u01/pgsql/13
ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-13/bin/postmaster -D ${PGDATA}
[root@postgreshelp ~]#

Reload systemd:

systemctl daemon-reload

[root@postgreshelp system]# systemctl daemon-reload
[root@postgreshelp system]#

Initialize the cluster:

/usr/pgsql-13/bin/postgresql-13-setup initdb

[root@postgreshelp system]# /usr/pgsql-13/bin/postgresql-13-setup initdb
Initializing database ... OK

Enable and start the service

systemctl start postgresql-13.service

systemctl enable postgresql-13.service

[root@postgreshelp system]#  systemctl enable postgresql-13.service
[root@postgreshelp system]#  systemctl start postgresql-13.service
[root@postgreshelp system]#

Optionally login and confirm

-bash-4.2$ psql
psql (13.1)
Type "help" for help.

postgres=# select name, setting from pg_settings where name like 'data_directory';
name | setting
----------------+---------------
data_directory | /u01/pgsql/13
(1 row)

How to have multiple PostgreSQL clusters in PostgreSQL 13:

  • As we have edited the file postgresql-13.service to change the cluster location, here to have a new cluster, you need to create a new unit file.

cp /lib/systemd/system/postgresql-13.service /etc/systemd/system/postgresql-13-secondary.service

  • Edit this file, and change PGDATA.

Environment=PGDATA=/u01/pgsql/14

  • Create a required directory at OS level and give necessary permissions

mkdir -p /u01/pgsql/14
chown -R postgres:postgres /u01/pgsql/14
chmod 750 /u01/pgsql/14

  • Initialize the cluster:

/usr/pgsql-13/bin/postgresql-13-setup initdb postgresql-13-secondary

[root@postgreshelp ~]# /usr/pgsql-13/bin/postgresql-13-setup initdb postgresql-13-secondary
Initializing database ... OK

[root@postgreshelp ~]#
  • Edit postgresql.conf to change the port, address, tcpip settings, etc.
[root@postgreshelp 14]# cat /u01/pgsql/14/postgresql.conf | grep port
port = 5433 # (change requires restart)
  • Start the postmaster with ‘systemctl start postgresql-13-secondary.service’
[root@postgreshelp 14]# systemctl start postgresql-13-secondary.service
[root@postgreshelp 14]#

Check if both the services are running ?

[root@postgreshelp 14]# ps -ef | grep postmaster
postgres   4851      1  0 16:22 ?        00:00:00 /usr/pgsql-13/bin/postmaster -D /u01/pgsql/13
postgres   4972      1  0 16:25 ?        00:00:00 /usr/pgsql-13/bin/postmaster -D /u01/pgsql/14
root       5000   4178  0 16:26 pts/1    00:00:00 grep --color=auto postmaster
[root@postgreshelp 14]#
[root@postgreshelp 14]#

Complete list of processes

[root@postgreshelp 14]# ps -ef | grep postgres
avahi       666      1  0 15:02 ?        00:00:00 avahi-daemon: running [postgreshelp.local]
root       4014   2943  0 15:53 pts/1    00:00:00 su - postgres
postgres   4015   4014  0 15:53 pts/1    00:00:00 -bash
postgres   4851      1  0 16:22 ?        00:00:00 /usr/pgsql-13/bin/postmaster -D /u01/pgsql/13
postgres   4853   4851  0 16:22 ?        00:00:00 postgres: logger
postgres   4855   4851  0 16:22 ?        00:00:00 postgres: checkpointer
postgres   4856   4851  0 16:22 ?        00:00:00 postgres: background writer
postgres   4857   4851  0 16:22 ?        00:00:00 postgres: walwriter
postgres   4858   4851  0 16:22 ?        00:00:00 postgres: autovacuum launcher
postgres   4859   4851  0 16:22 ?        00:00:00 postgres: stats collector
postgres   4860   4851  0 16:22 ?        00:00:00 postgres: logical replication launcher
postgres   4972      1  0 16:25 ?        00:00:00 /usr/pgsql-13/bin/postmaster -D /u01/pgsql/14
postgres   4976   4972  0 16:25 ?        00:00:00 postgres: logger
postgres   4978   4972  0 16:25 ?        00:00:00 postgres: checkpointer
postgres   4979   4972  0 16:25 ?        00:00:00 postgres: background writer
postgres   4980   4972  0 16:25 ?        00:00:00 postgres: walwriter
postgres   4981   4972  0 16:25 ?        00:00:00 postgres: autovacuum launcher
postgres   4982   4972  0 16:25 ?        00:00:00 postgres: stats collector
postgres   4983   4972  0 16:25 ?        00:00:00 postgres: logical replication launcher
root       5002   4178  0 16:26 pts/1    00:00:00 grep --color=auto postgres

What happens when you reboot your server where your database is hosted?

During a graceful reboot at OS level, PostgreSQL received a fast shutdown request.

The same can be found in log during graceful reboot.

cat /var/log/messages

Nov 23 16:39:54 postgreshelp systemd: Stopping RPC bind service…
Nov 23 16:39:54 postgreshelp systemd: Stopping PostgreSQL 13 database server…

PostgreSQL log

2020-11-23 16:39:55.445 IST [4851] LOG: received fast shutdown request
2020-11-23 16:39:55.458 IST [4851] LOG: aborting any active transactions
2020-11-23 16:39:55.465 IST [4851] LOG: background worker “logical replication launcher” (PID 4860) exited with exit code 1
2020-11-23 16:39:55.465 IST [4855] LOG: shutting down
2020-11-23 16:39:55.499 IST [4851] LOG: database system is shut down

How to start the PostgreSQL service as a postgres user

Add postgres user to sudoer file

[root@postgreshelp ~]# cat /etc/sudoers | grep postgres
postgres ALL=(ALL) NOPASSWD:ALL

Now, postgres user can run systemctl command to run postgres service.

-bash-4.2$
-bash-4.2$ whoami
postgres
-bash-4.2$
-bash-4.2$ systemctl status postgresql-13.service
● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2020-11-23 21:50:22 IST; 6min ago
     Docs: https://www.postgresql.org/docs/13/static/
  Process: 6724 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 6729 (postmaster)
    Tasks: 8
   CGroup: /system.slice/postgresql-13.service
           ├─6729 /usr/pgsql-13/bin/postmaster -D /u01/pgsql/13
           ├─6731 postgres: logger
           ├─6733 postgres: checkpointer
           ├─6734 postgres: background writer
           ├─6735 postgres: walwriter
           ├─6736 postgres: autovacuum launcher
           ├─6737 postgres: stats collector
           └─6738 postgres: logical replication launcher
-bash-4.2$

 

 

Words from postgreshelp

Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.

If you want to be updated with all our articles

please follow us on Facebook | Twitter
Please subscribe to our newsletter.

 

Leave a Comment