[LINUX] [PostgreSQL] Create multiple instances

Settings when you want to create an instance (database cluster) for each of multiple users in PostgreSQL installed on one server. Version is 10

In PostgreSQL, the OS user has one instance, so first create the OS user who will be the instance owner. Also, let's set the group to postgres. (It should have been created when Postgres was installed)

User created

adduser dangouser1

### Create a database directory for each user 
mkdir /database/data_dango1

Location is arbitrary

Change the user and group that created the ownership of the directory to postgres

chown dangouser1 /database/data_dango1
chgrp postgres /database/data_dango1
chmod 700 /database/data_dango1

### Set the environment variable of the created user 
su - dangousr1
$vi ./bashrc

Add the following

./bashrc


export PATH=$PATH:/usr/local/pgsql/bin
export PGDATA=/database/data_dango1
export PATH
export PGDATA

※ $PATH:/usr/local/pgsql/bin Is common (because the commands are stored in the default instance directory created during deployment) You may want to go through / etc / profile </ font>.
Reflect settings

source bashrc

### Instance creation 
initdb --pgdata=/database/data_dango1

Upon completion, the instance configuration data will be created in / database / data_dango1. Like this

dangousr1@dangosvr:/database/data_dango1$ ls -lh

-rw-------1 dangousr1 postgres 3 July 30 23:50 PG_VERSION
drwxr-xr-x 2 dangousr1 postgres 12K August 27 23:50 archive_log
drwx------ 6 dangousr1 postgres 4.0K July 30 23:50 base
-rw-------1 dangousr1 postgres 63 August 28 00:00 current_logfiles
drwx------ 2 dangousr1 postgres 4.0K August 25 00:31 global
drwx------ 2 dangousr1 postgres 4.0K July 30 23:50 pg_commit_ts
drwx------ 2 dangousr1 postgres 4.0K July 30 23:50 pg_dynshmem
-rw------- 1 dangousr1 postgres 4.5K July 30 23:50 pg_hba.conf
-rw------- 1 dangousr1 postgres 1.6K July 30 23:50 pg_ident.conf
drwx------ 2 dangousr1 postgres 4.0K August 28 00:00 pg_log
drwx------ 4 dangousr1 postgres 4.0K August 27 23:55 pg_logical
drwx------ 4 dangousr1 postgres 4.0K July 30 23:50 pg_multixact
drwx------ 2 dangousr1 postgres 4.0K August 25 00:30 pg_notify
drwx------ 2 dangousr1 postgres 4.0K August 27 23:50 pg_replslot
drwx------ 2 dangousr1 postgres 4.0K July 30 23:50 pg_serial
drwx------ 2 dangousr1 postgres 4.0K July 30 23:50 pg_snapshots
drwx------ 2 dangousr1 postgres 4.0K August 25 00:30 pg_stat
drwx------ 2 dangousr1 postgres 4.0K August 28 11:51 pg_stat_tmp
drwx------ 2 dangousr1 postgres 4.0K July 31 23:50 pg_subtrans
drwx------ 2 dangousr1 postgres 4.0K July 30 23:50 pg_tblspc
drwx------ 2 dangousr1 postgres 4.0K July 30 23:50 pg_twophase

drwxr-xr-x 3 dangousr1 postgres 4096 August 27 23:55 pg_wal
drwx------ 2 dangousr1 postgres 4.0K July 30 23:50 pg_xact
-rw-------1 dangousr1 postgres 88 July 30 23:50 postgresql.auto.conf
-rw-------1 dangousr1 postgres 23K July 30 23:50 postgresql.conf
-rw-------1 dangousr1 postgres 62 August 25 00:30 postmaster.opts
-rw-------1 dangousr1 postgres 80 August 25 00:30 postmaster.pid
dangousr1@dangosvr:/database/data_dango1$ 

In this, postgresql.conf </ font> is the configuration file, so edit it.

postgresql.conf


listen_addresses = '*'
port=Set to other than 5432

The port is initially 5432, but It is used for the default instance, so let's change it.

Instance launch

pg_ctl -D /database/data_dango1 start

### Connect to default DB (postgres) ``` psql --port=5433 -d postgres ``` Specify the port number set in postgresql.conf.

You can create multiple instances by preparing users, directories, and instances separately for each port in this way.
I'm fine.

Recommended Posts