Configure another instance of PostgreSql and administer using systemctl

May 24, 2021
()

Configure another instance of PostgreSQL and administer using systemctl


In the previous post, we saw How to Install PostgreSQL 13 on Linux Using YUM Command. In this blog, we will create another PostgreSQL Server in the same machine and will register the database server in systemctl system manager for easy management.


Step 1. Verify first PostgreSQL Server running: We have to validate first PostgreSQL server running on default port 5432 which was configured using yum command.

[root@test-machine02 ~]#
[root@test-machine02 ~]# psql -h test-machine02 -U postgres -W postgres
Password:
psql (13.2)
Type "help" for help.

postgres=#
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "test-machine02" (address "192.168.114.176") at port "5432".
postgres=#


Step 2. Create Directory Structure: For our second PostgreSQL server, we will create a directory psql-13 under /u01 mount point and will provide ownership to Postgres OS User.

[root@test-machine02 ~]# cd /u01
[root@test-machine02 u01]# mkdir psql-13
[root@test-machine02 u01]#
[root@test-machine02 u01]# chown postgres:postgres psql-13
[root@test-machine02 u01]#
[root@test-machine02 u01]# ls -ld psql-13
drwxr-xr-x. 2 postgres postgres 6 May 23 12:10 psql-13
[root@test-machine02 u01]#


Step 3. Initialize PostgreSQL server: Switch to Postgres OS User and change to directory /usr/pgsql-13/bin/ as under this directory we have all postgresql server utilities which was installed using yum command. We will use utility pg_ctl to initialize using “-D” option. After initialization is done all required files will be created under /u01/psql-13.

[root@test-machine02 u01]# su - postgres
Last login: Thu May 20 17:40:27 +03 2021 on pts/0
-bash-4.2$


-bash-4.2$ cd /usr/pgsql-13/bin/

-bash-4.2$
-bash-4.2$ ./pg_ctl -D /u01/psql-13 initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /u01/psql-13 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Riyadh
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/pgsql-13/bin/pg_ctl -D /u01/psql-13 -l logfile start

-bash-4.2$
-bash-4.2$
-bash-4.2$ cd /u01/psql-13
-bash-4.2$
-bash-4.2$ ls -ltr
total 52
drwx------. 2 postgres postgres     6 May 23 12:17 pg_twophase
drwx------. 2 postgres postgres     6 May 23 12:17 pg_snapshots
drwx------. 2 postgres postgres     6 May 23 12:17 pg_serial
drwx------. 2 postgres postgres     6 May 23 12:17 pg_replslot
drwx------. 2 postgres postgres     6 May 23 12:17 pg_notify
drwx------. 4 postgres postgres    36 May 23 12:17 pg_multixact
drwx------. 2 postgres postgres     6 May 23 12:17 pg_dynshmem
drwx------. 2 postgres postgres     6 May 23 12:17 pg_commit_ts
-rw-------. 1 postgres postgres     3 May 23 12:17 PG_VERSION
drwx------. 2 postgres postgres     6 May 23 12:17 pg_tblspc
drwx------. 2 postgres postgres     6 May 23 12:17 pg_stat_tmp
drwx------. 2 postgres postgres     6 May 23 12:17 pg_stat
-rw-------. 1 postgres postgres 28004 May 23 12:17 postgresql.conf
-rw-------. 1 postgres postgres    88 May 23 12:17 postgresql.auto.conf
-rw-------. 1 postgres postgres  1636 May 23 12:17 pg_ident.conf
-rw-------. 1 postgres postgres  4760 May 23 12:17 pg_hba.conf
drwx------. 2 postgres postgres    18 May 23 12:17 pg_xact
drwx------. 3 postgres postgres    60 May 23 12:17 pg_wal
drwx------. 2 postgres postgres    18 May 23 12:17 pg_subtrans
drwx------. 2 postgres postgres  4096 May 23 12:17 global
drwx------. 5 postgres postgres    41 May 23 12:17 base
drwx------. 4 postgres postgres    68 May 23 12:17 pg_logical
-bash-4.2$


Step 4. Change port in PostgreSQL config file: With initialization postgresql.conf file get created under /u01/psql-13 which has default port 5432. But as we already have our 1st PostgreSql Server running on Port 5432 we cannot use it again for second PostgreSql Server. We will be using 5433 Port. Edit file postgresql.conf using vi command line utiliy and navigate to section “CONNECTIONS AND AUTHENTICATIONuncomment parameter port and replace 5432 with 5433.

-bash-4.2$ vi postgresql.conf
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5433                             # (change requires restart)



If you skip above Step# 4 will receive below error while starting postgresql

-bash-4.2$ /usr/pgsql-13/bin/pg_ctl -D /u01/psql-13 -l logfile start
waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.
-bash-4.2$

-bash-4.2$ cd /u01/psql-13/log
-bash-4.2$
-bash-4.2$ ls -ltr
total 4
-rw-------. 1 postgres postgres 2721 May 23 12:38 postgresql-Sun.log

-bash-4.2$ vi postgresql-Sun.log
2021-05-23 12:26:17.742 +03 [126094] LOG:  starting PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-05-23 12:26:17.802 +03 [126094] LOG:  could not bind IPv6 address "::1": Address already in use
2021-05-23 12:26:17.802 +03 [126094] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2021-05-23 12:26:17.802 +03 [126094] LOG:  could not bind IPv4 address "127.0.0.1": Address already in use
2021-05-23 12:26:17.802 +03 [126094] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2021-05-23 12:26:17.802 +03 [126094] WARNING:  could not create listen socket for "localhost"
2021-05-23 12:26:17.802 +03 [126094] FATAL:  could not create any TCP/IP sockets
2021-05-23 12:26:17.804 +03 [126094] LOG:  database system is shut down


Step 5. Start PostgreSQL Server: Once PostgreSQL server started you can verify postmaster process is running using the command ps -ef|grep psql-13. Connect to the server using psql -p option.

-bash-4.2$
-bash-4.2$ /usr/pgsql-13/bin/pg_ctl -D /u01/psql-13 -l logfile start
waiting for server to start.... done
server started
-bash-4.2$
-bash-4.2$ ps -ef|grep psql-13
postgres   9707   8220  0 15:34 pts/1    00:00:00 grep --color=auto psql-13
postgres 127116      1  0 12:38 ?        00:00:00 /usr/pgsql-13/bin/postmaster -D /u01/psql-13

-bash-4.2$ netstat -ntlpue |grep post
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      26         2437667    88808/postmaster
tcp        0      0 127.0.0.1:5433          0.0.0.0:*               LISTEN      26         3029308    126249/postgres
tcp6       0      0 :::5432                 :::*                    LISTEN      26         2437668    88808/postmaster
tcp6       0      0 ::1:5433                :::*                    LISTEN      26         3029307    126249/postgres
-bash-4.2$

-bash-4.2$ psql -p 5433
psql (13.2)
Type "help" for help.

postgres=#   \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5433".
postgres=#
postgres=#


Step 6. Register PostgreSQL Server with systemctl: Now PostgreSQL server is up and running follow the below steps to register our newly created 2nd PostgreSQL server with systemctl.

1. Stop postgresql server using pg_ctl stop command
2. Change directory to /usr/lib/systemd/system/
3. Copy file postgresql-13.service as psql-13.service
4. Edit file psql-13.service and replace variable Environment=PGDATA with “/u01/psql-13
5. Enable service with command systemctl enable psql-13.service
6. Start PostgreSQL Server using command systemctl start psql-13.service
7. Check the status of PostgreSQL Server with command systemctl status psql-13.service
8. Verify by connecting to PostgreSql Server

-bash-4.2$
-bash-4.2$ /usr/pgsql-13/bin/pg_ctl -D /u01/psql-13 stop
waiting for server to shut down.... done
server stopped
-bash-4.2$
-bash-4.2$ exit
logout

[root@test-machine02 u01]# cd  /usr/lib/systemd/system/

[root@test-machine02 system]# ls -ltr *post*
-rw-r--r--. 1 root root  463 Nov 22  2019 postfix.service
-rw-r--r--. 1 root root 1665 Feb 11 04:42 postgresql-13.service
[root@test-machine02 system]#


[root@test-machine02 system]# cp postgresql-13.service psql-13.service
[root@test-machine02 system]# vi psql-13.service
Environment=PGDATA=/u01/psql-13


[root@test-machine02 system]#
[root@test-machine02 system]# systemctl enable psql-13.service
Created symlink from /etc/systemd/system/multi-user.target.wants/psql-13.service to /usr/lib/systemd/system/psql-13.service.
[root@test-machine02 system]#
[root@test-machine02 system]# systemctl start psql-13.service
[root@test-machine02 system]#
[root@test-machine02 system]# systemctl status psql-13.service
â psql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/psql-13.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2021-05-23 12:38:22 +03; 5s ago
     Docs: https://www.postgresql.org/docs/13/static/
  Process: 127111 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 127116 (postmaster)
   CGroup: /system.slice/psql-13.service
           ââ127116 /usr/pgsql-13/bin/postmaster -D /u01/psql-13
           ââ127119 postgres: logger
           ââ127121 postgres: checkpointer
           ââ127122 postgres: background writer
           ââ127123 postgres: walwriter
           ââ127124 postgres: autovacuum launcher
           ââ127125 postgres: stats collector
           ââ127126 postgres: logical replication launcher

May 23 12:38:22 test-machine02 systemd[1]: Starting PostgreSQL 13 database server...
May 23 12:38:22 test-machine02 postmaster[127116]: 2021-05-23 12:38:22.201 +03 [127116] LOG:  redirecting log output to logging collector process
May 23 12:38:22 test-machine02 postmaster[127116]: 2021-05-23 12:38:22.201 +03 [127116] HINT:  Future log output will appear in directory "log".
May 23 12:38:22 test-machine02 systemd[1]: Started PostgreSQL 13 database server.
[root@test-machine02 system]#


[root@test-machine02 system]#
[root@test-machine02 system]# su - postgres
Last login: Sun May 23 12:34:45 +03 2021 on pts/1
-bash-4.2$
-bash-4.2$
-bash-4.2$ psql -p 5433
psql (13.2)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5433".
postgres=#
postgres=#

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article
!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Leave a Reply

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