Creating Cluster using initdb

Database Cluster :
A cluster of databases under one instance of a database server in operation is called a database cluster. A Postgres database, intended to serve as the default database for use by users, utilities, and outside programs, will be present in a database cluster following initialization.

PostgreSQL is a command-line tool used to initialize a new PostgreSQL database cluster. It's typically one of the first steps in setting up a PostgreSQL database system.

When you install PostgreSQL, it doesn't automatically create a database for you; instead, it provides you with this utility to create a new database cluster.

A cluster is defined by the port number. The default DB cluster in postgres loaction is “/var/lib/pgsql/13/data”.
Here we can see all the configuration files and parameters.

  -bash-4.2$ cd /var/lib/pgsql/13/data
  -bash-4.2$ ls -lrt
  total 76
  -rw-------. 1 postgres postgres    88 Nov 14 11:48 postgresql.auto.conf
  drwx------. 2 postgres postgres    18 Nov 14 11:48 pg_xact
  -rw-------. 1 postgres postgres     3 Nov 14 11:48 PG_VERSION
  drwx------. 2 postgres postgres     6 Nov 14 11:48 pg_twophase
  drwx------. 2 postgres postgres     6 Nov 14 11:48 pg_tblspc
  drwx------. 2 postgres postgres     6 Nov 14 11:48 pg_snapshots
  drwx------. 2 postgres postgres     6 Nov 14 11:48 pg_serial
  drwx------. 2 postgres postgres     6 Nov 14 11:48 pg_notify
  drwx------. 4 postgres postgres    36 Nov 14 11:48 pg_multixact
  -rw-------. 1 postgres postgres  1636 Nov 14 11:48 pg_ident.conf
  drwx------. 2 postgres postgres     6 Nov 14 11:48 pg_dynshmem
  drwx------. 2 postgres postgres     6 Nov 14 11:48 pg_commit_ts
  -rw-------. 1 postgres postgres  4631 Nov 15 10:30 pg_hba.conf
  drwx------. 6 postgres postgres    54 Nov 15 10:39 base
  -rw-------. 1 postgres postgres     0 Nov 15 10:39 tablespace_map.old
  drwx------. 2 postgres postgres     6 Nov 15 10:39 pg_replslot
  -rw-------. 1 postgres postgres   224 Nov 15 10:39 backup_label.old
  -rw-------. 1 postgres postgres 28165 Nov 15 10:55 postgresql.conf
  drwx------. 2 postgres postgres    18 Nov 15 10:55 pg_subtrans
  drwx------. 2 postgres postgres     6 Nov 15 10:55 pg_stat
  drwx------. 3 postgres postgres  4096 Nov 15 11:05 pg_wal
  -rw-r--r--. 1 postgres postgres     0 Nov 15 12:43 backup.sql
  drwx------. 2 postgres postgres  4096 Nov 16 10:32 global
  drwx------. 2 postgres postgres  4096 Nov 20 09:41 log
  -rw-------. 1 postgres postgres    30 Nov 23 10:10 current_logfiles
  -rw-------. 1 postgres postgres    58 Nov 23 10:10 postmaster.opts
  drwx------. 4 postgres postgres    68 Nov 23 10:10 pg_logical
  -rw-------. 1 postgres postgres    95 Nov 23 10:10 postmaster.pid
  drwx------. 2 postgres postgres    25 Nov 23 12:02 pg_stat_tmp

Steps to create a Database cluster :
Step 1 : cd /var/lib/pgsql

Step 2 : Create a folder where the db is to be initiated

Step 3 : The owner and group of the created folder should be postgres and also the folder should have full access.

  -bash-4.2$ cd /var/lib/pgsql
  -bash-4.2$ mkdir tesdb
  -bash-4.2$ ls -lrt
  total 4
  drwxr-xr-x. 2 postgres postgres    6 Aug 22 10:14 perl5
  drwxr-xr-x. 7 root     root     4096 Aug 31 15:27 pgbadger-11.6
  drwx------. 2 postgres postgres    6 Nov 14 10:46 tbs
  drwx------. 4 postgres postgres   51 Nov 14 11:48 13
  drwxr-xr-x. 2 postgres postgres    6 Nov 23 12:18 tesdb

Step 4 : cd /usr/pgsql-13/bin

Step 5 : ./initdb -D /var/lib/pgsql/tesdb

In the above step we are initialising the db by mentioning the location of the directory we created

    -bash-4.2$ cd /usr/pgsql-13/bin
    -bash-4.2$  ./initdb -D /var/lib/pgsql/tesdb
  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 /var/lib/pgsql/tesdb ... 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/Kolkata
  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:
      ./pg_ctl -D /var/lib/pgsql/tesdb -l logfile start

  now start the new cluster

Step 6 : ./pg_ctl -D /var/lib/pgsql/tesdb start
When we try to start the new DB , it will end in error as it is trying to connect via port 5432 as same as the postgres DB(default cluster) which is already running in the default port 5432.

Two cluster cannot run on same port. so we have to change the port number of the new DB cluster(or default cluster) and then start again or Another method for starting server is by stopping the running server and starting the new server.

-bash-4.2$ ./pg_ctl -D /var/lib/pgsql/tesdb start waiting for server to start....2023-11-23 12:22:47.769 IST [14584] LOG: redirecting log output to logging collector process 2023-11-23 12:22:47.769 IST [14584] HINT: Future log output will appear in directory "log".

stopped waiting pg_ctl: could not start server
Examine the log output.

« Previous Next Topic » (Postgresql - Changing portnumber of new cluster)