Creating and Managing Cluster with native commands

PostgreSQL has 2 important native binary executables or commands, initdb and pg_ctl to create and manage the PostgreSQL clusters. Then, there is another executable, pg_controldata, to view a lot of internal information initialized during initdb, such as the catalog version.

The command initdb is used to create a new cluster. The command pg_ctl is used to create and manage (initdb/start/stop/restart etc) the cluster. So, a new cluster can be created using the command initdb as well as using the command pg_ctl with init or initdb option. If these commands are not in the executable path, then you can add these to the profile file of the Postgres or required operating system user. An example profile might look like this.

$ cat .profile
PATH=$PATH:/usr/lib/postgresql/13/bin
export PATH
$

Multiple PostgreSQL clusters belonging to the same or multiple PostgreSQL versions can be created on a single server. Each cluster will have its own data directory and each cluster will need a separate port to run on so that clients can connect to a specific cluster.

Creating a cluster using initdb

Running just the command initdb with the option --help will display the usage of the command.

$ initdb --help

$ initdb --help
initdb initializes a PostgreSQL database cluster.

Usage:
  initdb [OPTION]... [DATADIR]

Options:
  -A, --auth=METHOD         default authentication method for local connections
      --auth-host=METHOD    default authentication method for local TCP/IP connections
      --auth-local=METHOD   default authentication method for local-socket connections
 [-D, --pgdata=]DATADIR     location for this database cluster
  -E, --encoding=ENCODING   set default encoding for new databases
  -g, --allow-group-access  allow group read/execute on data directory
      --locale=LOCALE       set default locale for new databases
      --lc-collate=, --lc-ctype=, --lc-messages=LOCALE
      --lc-monetary=, --lc-numeric=, --lc-time=LOCALE
                            set default locale in the respective category for
                            new databases (default taken from environment)
      --no-locale           equivalent to --locale=C
      --pwfile=FILE         read password for the new superuser from file
  -T, --text-search-config=CFG
                            default text search configuration
  -U, --username=NAME       database superuser name
  -W, --pwprompt            prompt for a password for the new superuser
  -X, --waldir=WALDIR       location for the write-ahead log directory
      --wal-segsize=SIZE    size of WAL segments, in megabytes

Less commonly used options:
  -d, --debug               generate lots of debugging output
  -k, --data-checksums      use data page checksums
  -L DIRECTORY              where to find the input files
  -n, --no-clean            do not clean up after errors
  -N, --no-sync             do not wait for changes to be written safely to disk
  -s, --show                show internal settings
  -S, --sync-only           only sync data directory

Other options:
  -V, --version             output version information, then exit
  -?, --help                show this help, then exit

If the data directory is not specified, the environment variable PGDATA
is used.

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
$

One of the most used options of initdb is -D or --pgdata which defines the data directory for the cluster.

Features of initdb command:

• It creates a new cluster only if data directory given via option -D is empty or doesn’t exist, else it will error out.
• It creates postgresql.conf, pg_hba.conf and other configuration files inside the data directory itself (unlike the Ubuntu command pg_createcluster which creates the configuration files inside /etc/var/postgresql/<version>/<cluster name>).
• It doesn’t maintain any catalog of clusters it creates (unlike the Ubuntu command pg_createcluster which maintains a catalog of the clusters).
• It doesn’t specify any port in the configuration file postgresql.conf. After the cluster creation, one can manually specify the port in that file or provide the port while starting the cluster later (unlike the Ubuntu command pg_createcluster which automatically assigns next available port number starting from default value of 5432).
• Starting the cluster without specifying the port will try to start the cluster on default port of 5432 and the cluster will fail to start if port of 5432 is in use by any other cluster or any process.

Let’s create a new cluster with this option. At the end of the output, it also displays the command to start the cluster.

$ initdb -D /var/lib/postgresql/13/mars

$ initdb -D /var/lib/postgresql/13/mars
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 "C.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.

creating directory /var/lib/postgresql/13/mars ... 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/Calcutta
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/postgresql/13/mars -l logfile start

$

For now, we will not start the cluster. Let’s create more clusters will different options.

Here, we will create a new cluster with custom auth options so that password authentication for host-based connections is in place from the start, rather than need to be set up later. Also, we will assign a separate location for wal (write-ahead-log) files by using option --waldir which effectively creates a soft link on the directory pg_wal pointing to the new location.

$ export PGDATA=/var/lib/postgresql/13/sun
$ initdb --waldir /var/lib/postgresql/13/waldir/sun --auth-local peer --auth-host md5

$ export PGDATA=/var/lib/postgresql/13/sun
postgres@MKM-HP-ENVY:~/13/mars$ initdb --waldir /var/lib/postgresql/13/waldir/sun --auth-local peer --auth-host md5
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 "C.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.

creating directory /var/lib/postgresql/13/sun ... ok
creating directory /var/lib/postgresql/13/waldir/sun ... 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/Calcutta
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/13/sun -l logfile start

$

At this point in time, you can check the directory /var/lib/postgresql/13/sun to see the database files created for the cluster. You can also check pg_hba.conf file in the same path to verify that the password method is set up host-based authentication.

$ ls -lh /var/lib/postgresql/13/sun/

$ ls -lh /var/lib/postgresql/13/sun/
total 45K
-rw------- 1 postgres postgres    3 Aug  7 20:57 PG_VERSION
drwx------ 1 postgres postgres  512 Aug  7 20:57 base
drwx------ 1 postgres postgres  512 Aug  7 20:57 global
-rw------- 1 postgres postgres  572 Aug  7 21:08 logfile
drwx------ 1 postgres postgres  512 Aug  7 20:57 pg_commit_ts
drwx------ 1 postgres postgres  512 Aug  7 20:57 pg_dynshmem
-rw------- 1 postgres postgres 4.5K Aug  7 20:57 pg_hba.conf
-rw------- 1 postgres postgres 1.6K Aug  7 20:57 pg_ident.conf
drwx------ 1 postgres postgres  512 Aug  7 20:57 pg_logical
drwx------ 1 postgres postgres  512 Aug  7 20:57 pg_multixact
drwx------ 1 postgres postgres  512 Aug  7 20:57 pg_notify
drwx------ 1 postgres postgres  512 Aug  7 20:57 pg_replslot
drwx------ 1 postgres postgres  512 Aug  7 20:57 pg_serial
drwx------ 1 postgres postgres  512 Aug  7 20:57 pg_snapshots
drwx------ 1 postgres postgres  512 Aug  7 20:57 pg_stat
drwx------ 1 postgres postgres  512 Aug  7 20:57 pg_stat_tmp
drwx------ 1 postgres postgres  512 Aug  7 20:57 pg_subtrans
drwx------ 1 postgres postgres  512 Aug  7 20:57 pg_tblspc
drwx------ 1 postgres postgres  512 Aug  7 20:57 pg_twophase
lrwxrwxrwx 1 postgres postgres   33 Aug  7 20:57 pg_wal -> /var/lib/postgresql/13/waldir/sun
drwx------ 1 postgres postgres  512 Aug  7 20:57 pg_xact
-rw------- 1 postgres postgres   88 Aug  7 20:57 postgresql.auto.conf
-rw------- 1 postgres postgres  28K Aug  7 20:57 postgresql.conf
$

$ cat /var/lib/postgresql/13/sun/pg_hba.conf | grep ^host

$ cat /var/lib/postgresql/13/sun/pg_hba.conf | grep ^host
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
$

Now, run pg_controldata to view the cluster information.

$ pg_controldata -D /var/lib/postgresql/13/sun

$ pg_controldata -D /var/lib/postgresql/13/sun
pg_control version number:            1300
Catalog version number:               202007201
Database system identifier:           6993710173075214578
Database cluster state:               shut down
pg_control last modified:             Sat Aug  7 20:57:23 2021
Latest checkpoint location:           0/15C99E8
Latest checkpoint's REDO location:    0/15C99E8
Latest checkpoint's REDO WAL file:    000000010000000000000001
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:485
Latest checkpoint's NextOID:          13415
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        478
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Sat Aug  7 20:57:23 2021
Fake LSN counter for unlogged rels:   0/3E8
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    replica
wal_log_hints setting:                off
max_connections setting:              100
max_worker_processes setting:         8
max_wal_senders setting:              10
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float8 argument passing:              by value
Data page checksum version:           0
Mock authentication nonce:            0094aa3f7e77e13b752311ec7d102116762d09a0c28e0b920ed76d33ec146294
$

Creating and Managing a cluster using pg_ctl

Running just the command pg_ctl with the option --help will display the usage of the command.

$ pg_ctl --help

$ pg_ctl --help
pg_ctl is a utility to initialize, start, stop, or control a PostgreSQL server.

Usage:
  pg_ctl init[db]   [-D DATADIR] [-s] [-o OPTIONS]
  pg_ctl start      [-D DATADIR] [-l FILENAME] [-W] [-t SECS] [-s]
                    [-o OPTIONS] [-p PATH] [-c]
  pg_ctl stop       [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
  pg_ctl restart    [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
                    [-o OPTIONS] [-c]
  pg_ctl reload     [-D DATADIR] [-s]
  pg_ctl status     [-D DATADIR]
  pg_ctl promote    [-D DATADIR] [-W] [-t SECS] [-s]
  pg_ctl logrotate  [-D DATADIR] [-s]
  pg_ctl kill       SIGNALNAME PID

Common options:
  -D, --pgdata=DATADIR   location of the database storage area
  -s, --silent           only print errors, no informational messages
  -t, --timeout=SECS     seconds to wait when using -w option
  -V, --version          output version information, then exit
  -w, --wait             wait until operation completes (default)
  -W, --no-wait          do not wait until operation completes
  -?, --help             show this help, then exit
If the -D option is omitted, the environment variable PGDATA is used.

Options for start or restart:
  -c, --core-files       allow postgres to produce core files
  -l, --log=FILENAME     write (or append) server log to FILENAME
  -o, --options=OPTIONS  command line options to pass to postgres
                         (PostgreSQL server executable) or initdb
  -p PATH-TO-POSTGRES    normally not necessary

Options for stop or restart:
  -m, --mode=MODE        MODE can be "smart", "fast", or "immediate"

Shutdown modes are:
  smart       quit after all clients have disconnected
  fast        quit directly, with proper shutdown (default)
  immediate   quit without complete shutdown; will lead to recovery on restart

Allowed signal names for kill:
  ABRT HUP INT KILL QUIT TERM USR1 USR2

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
$

Some of the frequently used options are described below:

• -D (create/stop/start the cluster with give data directory location)
• -s (Work in silent mode, show only errors)
• -w (Wait for the operation to finish before giving back the prompt. This is the defaut behaviour.)
• -W (Do not wait for the operation to finish)
• -o (Options for the postgres or initdb executable)
• -m (Modes for stop or restart. smart/fast/immediate)

We can create a cluster using the command pg_ctl with the option of --init or --initdb. We also need to give data directory location. A few other options are also possible. Essentially, it uses the same options as the command initdb but in a different format. Let’s create a new cluster now.

$ pg_ctl initdb -D /var/lib/postgresql/13/moon -o "--auth-local peer --auth-host md5"

$ pg_ctl initdb -D /var/lib/postgresql/13/moon -o "--auth-local peer --auth-host md5"
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 "C.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.

creating directory /var/lib/postgresql/13/moon ... 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/Calcutta
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    /usr/lib/postgresql/13/bin/pg_ctl -D /var/lib/postgresql/13/moon -l logfile start

$

Now, we can start the cluster using the command displayed at the end of the cluster creation console output. If the start fails, then you can look into the file logfile to find and resolve the errors. It will try to start the cluster on the default port of 5432.

$ pg_ctl -D /var/lib/postgresql/13/moon -l logfile start

$ pg_ctl -D /var/lib/postgresql/13/moon -l logfile start
waiting for server to start.... done
server started
$

Using status option of pg_ctl, we can see what options the cluster was started with.

$ pg_ctl -D /var/lib/postgresql/13/moon status

$ pg_ctl -D /var/lib/postgresql/13/moon status
pg_ctl: server is running (PID: 1331)
/usr/lib/postgresql/13/bin/postgres "-D" "/var/lib/postgresql/13/moon"
$

Also, we can connect to the new cluster and verify the cluster details using the command psql with -c option and few arguments. For now, we will use psql just to check few basic details as we will look into full psql capabilities later. The output will show the database, the user, the data directory, and the port.

$ psql -c "\conninfo" -c "show data_directory"

$ psql -c "\conninfo" -c "show data_directory"
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
       data_directory
-----------------------------
 /var/lib/postgresql/13/moon
(1 row)

$

In the above case, if the default port of 5432 is being used already, then the start will fail. In such a case, one can start the cluster on a different port using -o option of pg_ctl. So, let’s first stop the cluster.

The cluster can be stopped with stop option of pg_ctl. Optionally, a flag -m can be passed to control the behavior of the cluster when stopping.

• -m smart (allow transactions to complete and stop normally)
• -m fast (skill all the connections instantly and stop the cluster) – This the default behaviour.
• -m immediate (kill operating system processes and stop abruptly)

$ pg_ctl -D /var/lib/postgresql/13/moon stop -m fast

$ pg_ctl -D /var/lib/postgresql/13/moon stop -m fast
2021-08-08 09:07:54.446 IST [1272] LOG:  received fast shutdown request
waiting for server to shut down....2021-08-08 09:07:54.453 IST [1272] LOG:  aborting any active transactions
2021-08-08 09:07:54.457 IST [1272] LOG:  background worker "logical replication launcher" (PID 1279) exited with exit code 1
2021-08-08 09:07:54.457 IST [1274] LOG:  shutting down
2021-08-08 09:07:54.493 IST [1272] LOG:  database system is shut down
 done
server stopped
$

Now, start the cluster on another port, say 5454. Similarly, we can specify an alternate file name for the logfile and an alternate startup configuration file as well as shown below.

$ pg_ctl -D /var/lib/postgresql/13/moon -o "-c config_file=/var/lib/postgresql/13/moon/postgresql.conf" -o "-p 5454" -l logfile.13.moon.txt start

$ pg_ctl -D /var/lib/postgresql/13/moon -o "-c config_file=/var/lib/postgresql/13/moon/postgresql.conf" -o "-p 5454" -l logfile.13.moon.txt start
waiting for server to start.... done
server started
$

Let’s again check the cluster details and verify that the cluster is running on a new port and with a custom configuration file.

$ pg_ctl -D /var/lib/postgresql/13/moon status

$ pg_ctl -D /var/lib/postgresql/13/moon status
pg_ctl: server is running (PID: 1290)
/usr/lib/postgresql/13/bin/postgres "-D" "/var/lib/postgresql/13/moon" "-c" "config_file=/var/lib/postgresql/13/moon/postgresql.conf" "-p" "5454"
$

To connect to a cluster running on non-default port, we need to specify the port with -p option of psql. This method is just one of the many methods to connect to a cluster running on non-default port.

$ psql -p 5454 -c "\conninfo" -c "show data_directory"

$ psql -p 5454 -c "\conninfo" -c "show data_directory"
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5454".
       data_directory
-----------------------------
 /var/lib/postgresql/13/moon
(1 row)

$

Now, run pg_controldata to view the cluster information.

$ pg_controldata -D /var/lib/postgresql/13/moon

$ pg_controldata -D /var/lib/postgresql/13/moon
pg_control version number:            1300
Catalog version number:               202007201
Database system identifier:           6993735661211656680
Database cluster state:               in production
pg_control last modified:             Sun Aug  8 09:31:44 2021
Latest checkpoint location:           0/15CA210
Latest checkpoint's REDO location:    0/15CA1D8
Latest checkpoint's REDO WAL file:    000000010000000000000001
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:485
Latest checkpoint's NextOID:          13415
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        478
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  485
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Sun Aug  8 09:31:44 2021
Fake LSN counter for unlogged rels:   0/3E8
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    replica
wal_log_hints setting:                off
max_connections setting:              100
max_worker_processes setting:         8
max_wal_senders setting:              10
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float8 argument passing:              by value
Data page checksum version:           0
Mock authentication nonce:            bdafae6934588b9b020ca27b6e80c32b8de6fe916e8bddc2cfd7738b41c2b49e
$

Dropping the cluster

There is no native PostgreSQL command to remove or drop the cluster. You simply stop the cluster and remove the data directory to get rid of the cluster.