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 AUTHENTICATION” uncomment 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.
