[LINUX] Launch multiple instances with Postgresql 11.x

PostgreSQL installation

Install PostgreSQL11.6 and start multiple instances. Also, create a startup script for each instance so that it can be started / stopped individually.

• Instance ①

• pgsql-5432

• Port: 5432

• /data/pgsql-5432

• Instance ②

• pgsql-5433

• Port 5433

• /data/pgsql-5433

Add Yum repository

This time, we will install version 11, so we will add a repository.

$ yum --disablerepo=\* --enablerepo=centos-media list
$ yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Run Yum

$ yum -y install postgresql11-server postgresql11-contrib
$ rpm -qa |grep postgres
$ ls -l /usr/pgsql-11/

Setting environment variables

Add PATH to postgres user.

$ su - postgres
$　vi ./bashrc
export PATH=$PATH:/usr/pgsql-11/bin
export PGDATA=/data/pgsql-5432
export PATH
export PGDATA

$ source bashrc

Instance ① (pgsql-5432)

Create the first instance pgsql-5432.

Instance creation

Create an instance in the directory "/ data / pgsql-5432" with the initdb command.

$ initdb --pgdata=/data/pgsql-5432
$ cat /data/pgsql-5432/PG_VERSION
11

Instance startup confirmation

Execute the pg_ctl command to confirm the startup of the instance.

$ su postgres
$ /usr/pgsql-11/bin/pg_ctl -D /data/pgsql-5432 start
$ /usr/pgsql-11/bin/pg_ctl -D /data/pgsql-5432 stop
$ exit

Create startup script

Duplicate the startup script created during installation and edit it for instance ①.

• If you do not plan to use the default postgresql.service, you may save it to another directory or edit it. Because TAB is completed when executing systemctl or the like.

$ cd /usr/lib/systemd/system/
$ cp -p postgresql.service postgresql-5432.service
$ vi /usr/lib/systemd/system/postgresql-5432.service
Environment=PGDATA=/data/pgsql-5432/

Check the startup script

Set the service stop / start of instance ①, and if necessary, automatic start.

• When creating instance ②, the service will be stopped because the port will be covered as it is.

$ systemctl daemon-reload
$ systemctl status postgresql-5432.service
$ systemctl start postgresql-5432.service
$ systemctl stop postgresql-5432.service
$ systemctl status postgresql-5432.service

$ systemctl enable postgresql-5432.service
$ systemctl list-unit-files |grep postgres

Instance ② (pgsql-5433)

Create the first instance pgsql-5433. Just change the directory path of the procedure executed in "instance ①".

Instance creation

Create an instance in the directory "/ data / pgsql-5433" with the initdb command.

$ initdb --pgdata=/data/pgsql-5433
$ cat /data/pgsql-5433/PG_VERSION
11

Instance startup confirmation

Execute the pg_ctl command to confirm the startup of the instance.

$ su postgres
$ /usr/pgsql-11/bin/pg_ctl -D /data/pgsql-5432 start
$ /usr/pgsql-11/bin/pg_ctl -D /data/pgsql-5432 stop
$ exit

Create startup script

Duplicate the startup script created during installation and edit it for instance ②.

$ cd /usr/lib/systemd/system/
$ cp -p postgresql.service postgresql-5433.service
$ vi /usr/lib/systemd/system/postgresql-5433.service
Environment=PGDATA=/data/pgsql-5432/

Check the startup script

Set the service stop / start of instance ②, and if necessary, automatic start.

• If this is left as it is, the service will be stopped because the instance ① and the port will be covered.

$ systemctl daemon-reload
$ systemctl status postgresql-5433.service
$ systemctl start postgresql-5433.service
$ systemctl stop postgresql-5433.service
$ systemctl status postgresql-5433.service

$ systemctl enable postgresql-5433.service
$ systemctl list-unit-files |grep postgres

Port settings

Change the port used by each instance. This time, only instance ② is changed.

$ vi /data/pgsql-5433/postgresql.conf
#port = 5432
port = 5433

Instance launch

Launch instances individually.

$ systemctl start postgresql-5432.service
$ systemctl status postgresql-5432.service

$ systemctl start postgresql-5433.service
$ systemctl status postgresql-5433.service

Process confirmation

Check the process of each instance.

$ ps -ef |grep pgsql
postgres 28435     1  0 Feb09 ?        00:00:02 /usr/pgsql-11/bin/postmaster -D /data/pgsql-5432/
postgres 28456     1  0 Feb09 ?        00:00:01 /usr/pgsql-11/bin/postmaster -D /data/pgsql-5433/

Port confirmation

Make sure each instance is running on the specified port.

$ ss -anp |grep 5432
～～～
tcp    LISTEN     0      128       *:5432                  *:*                   users:(("postmaster",pid=28435,fd=3))
～～～

$ ss -anp |grep 5433
～～～
tcp    LISTEN     0      128       *:5433                  *:*                   users:(("postmaster",pid=28456,fd=3))
～～～

All you have to do is set it for each instance!

$ ll /data/pgsql-5432
$ ll /data/pgsql-5433

that's all.