yury@u2004s01:~$ sudo -i -u postgres
postgres@u2004s01:~$ /usr/lib/postgresql/14/bin/initdb -D /usr/local/pgsql/data
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.
creating directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
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/lib/postgresql/14/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
postgres@u2004s01:~$ logout
yury@u2004s01:~$
Start PostgreSQL server
for u2004s01, u2004s02, u2004s03
we got the error
yury@u2004s01:~$ sudo -i -u postgres
postgres@u2004s01:~$ /usr/lib/postgresql/14/bin/pg_ctl start -l mylogfile.log -D /usr/local/pgsql/data
waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.
here is a log
2022-01-21 11:43:30.965 UTC [7671] LOG: starting PostgreSQL 14.1 (Ubuntu 14.1-2.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
2022-01-21 11:43:30.966 UTC [7671] LOG: could not bind IPv4 address "127.0.0.1": Address already in use
2022-01-21 11:43:30.966 UTC [7671] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2022-01-21 11:43:30.966 UTC [7671] WARNING: could not create listen socket for "localhost"
2022-01-21 11:43:30.966 UTC [7671] FATAL: could not create any TCP/IP sockets
2022-01-21 11:43:30.968 UTC [7671] LOG: database system is shut down
UNIT LOAD ACTIVE SUB DESCRIPTION
...
postgresql.service loaded active exited PostgreSQL RDBMS
[email protected] loaded active running PostgreSQL Cluster 14-main
...
Stop the service and start the server
yury@u2004s01:~$ sudo systemctl stop postgresql.service
yury@u2004s01:~$ sudo -i -u postgres
postgres@u2004s01:~$ /usr/lib/postgresql/14/bin/pg_ctl start -l mylogfile.log -D /usr/local/pgsql/data
waiting for server to start.... done
server started
postgres@u2004s01:~$ /usr/lib/postgresql/14/bin/pg_ctl stop -l mylogfile.log -D /usr/local/pgsql/data
waiting for server to shut down.... done
server stopped
Modify PostgreSQL systemd config
After Creating Database Storage Area(or Database Cluster) we need to reset systemd config file for postgresql.service, since it is not very good idea to start the server with a command postgres@u2004s01:~$ /usr/lib/postgresql/14/bin/pg_ctl stop -l mylogfile.log -D /usr/local/pgsql/data
to find the path to the config file we run
yury@u2004s01:~$ systemctl status postgresql.service
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: inactive (dead) since Fri 2022-01-21 12:01:49 UTC; 1h 8min ago
...
# systemd service for managing all PostgreSQL clusters on the system. This
# service is actually a systemd target, but we are using a service since
# targets cannot be reloaded.
[Unit]
Description=PostgreSQL RDBMS
[Service]
Type=oneshot
ExecStart=/bin/true
ExecReload=/bin/true
RemainAfterExit=on
[Install]
WantedBy=multi-user.target
So the new version of the /lib/systemd/system/postgresql.service is as follows
postgresql.service
# systemd service for managing all PostgreSQL clusters on the system. This
# service is actually a systemd target, but we are using a service since
# targets cannot be reloaded.
[Unit]
Description=PostgreSQL RDBMS
[Service]
Type=notify
User=postgres
ExecStart=/usr/lib/postgresql/14/bin/postgres -D /usr/local/pgsql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0
[Install]
WantedBy=multi-user.target
It does not work
sudo systemctl daemon-reload
sudo systemctl disable postgresql.service
sudo systemctl enable postgresql.service
sudo systemctl start postgresql.service
yury@u2004s01:~$ sudo systemctl start postgresql.service
Job for postgresql.service failed because the control process exited with error code.
See "systemctl status postgresql.service" and "journalctl -xe" for details.
so we return initial version of the /lib/systemd/system/postgresql.service-file and
We should modify /etc/postgresql/14/main/postgresql.conf
we have data_directory of postgresql.service
data_directory of postgresql.service
yury@u2004s01:~$ sudo -u postgres psql
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.
postgres=# SHOW data_directory;
data_directory
-----------------------------
/var/lib/postgresql/14/main
(1 row)
postgres=# \q
yury@u2004s01:~$
we have data_directory of pg_ctl start -l mylogfile.log -D /usr/local/pgsql/data
data_directory of pg_ctl start ...
yury@u2004s01:~$ sudo -i -u postgres
postgres@u2004s01:~$ /usr/lib/postgresql/14/bin/pg_ctl start -l mylogfile.log -D /usr/local/pgsql/data
waiting for server to start.... done
server started
postgres@u2004s01:~$ psql
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.
postgres=# SHOW data_directory;
data_directory
-----------------------
/usr/local/pgsql/data
(1 row)
postgres=# \q
postgres@u2004s01:~$ /usr/lib/postgresql/14/bin/pg_ctl stop -l mylogfile.log -D /usr/local/pgsql/data
waiting for server to shut down.... done
server stopped
postgres@u2004s01:~$ logout
yury@u2004s01:~$
modify /etc/postgresql/14/main/postgresql.conf
sudo nano /etc/postgresql/14/main/postgresql.conf
according to we set data_directory as shown below:
data_directory = '/usr/local/pgsql/data' # use data in another directory
with sudo nano /etc/postgresql/14/main/postgresql.conf set the following values
wal_level = replica # minimal, replica, or logical
max_wal_senders = 10 # max number of walsender processes
max_replication_slots = 10 # max number of replication slots
synchronous_commit = on # synchronization level;
synchronous_standby_names = 'FIRST 2 (u2004s02, u2004s03)' # standby servers that provide sync rep
for u2004s01
with sudo nano /etc/postgresql/14/main/pg_hba.conf set the following values