-
Notifications
You must be signed in to change notification settings - Fork 1
U1.59 Ubuntu Quick Start (QS): PostgreSQL HA Cluster on premises
chempkovsky edited this page Jan 21, 2022
·
12 revisions
- Installation
- The PostgreSQL User Account
- Listing databases
- Creating a Database Cluster or Database Storage Area
- It was done for all virtual machines
- Preparing the Primary
- Preparing the StandBy
- Testing Replication
- read the article Chapter 27. High Availability, Load Balancing, and Replication
- read the article Patroni
- read the article Consul
- read the article Ubuntu: PostgreSQL
- Pre-installed DHCP in the virtual environment (for example, a hardware implementation of a DHCP server in a modem)
- Go to the page Ubuntu 20.04.3 LTS (Focal Fossa)
- Download ubuntu-20.04.3-live-server-amd64.iso
- Deploy three virtual machines with default settings (i.e. openssh is ON)
- u2004s01 192.168.100.41
- u2004s02 192.168.100.42
- u2004s03 192.168.100.43
- Sudo-enabled User
- yury
- with /etc/hosts-file as follows
127.0.0.1 localhost
192.168.100.61 u2004s01
192.168.100.62 u2004s02
192.168.100.63 u2004s03
# The following lines are desirable for IPv6 capable hosts
::1 ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
- u2004s01 Primary
- u2004s02 Standby
- u2004s03 Standby
- read the article Chapter 16. Installation from Binaries
- read the article Linux downloads (Ubuntu)
- for u2004s01, u2004s02, u2004s03
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql
Click to show the response of 'sudo apt-get -y install postgresql'
...
After this operation, 132 MB of additional disk space will be used.
Get:1 http://by.archive.ubuntu.com/ubuntu focal/main amd64 libcommon-sense-perl amd64 3.74-2build6 [20.1 kB]
Get:2 http://by.archive.ubuntu.com/ubuntu focal/main amd64 libjson-perl all 4.02000-2 [80.9 kB]
Get:3 http://by.archive.ubuntu.com/ubuntu focal/main amd64 libtypes-serialiser-perl all 1.0-1 [12.1 kB]
Get:4 http://by.archive.ubuntu.com/ubuntu focal/main amd64 libjson-xs-perl amd64 4.020-1build1 [83.7 kB]
Get:5 http://by.archive.ubuntu.com/ubuntu focal/main amd64 libllvm9 amd64 1:9.0.1-12 [14.8 MB]
Get:6 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 libpq5 amd64 14.1-2.pgdg20.04+1 [170 kB]
Get:7 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 pgdg-keyring all 2018.2 [10.7 kB]
Get:8 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-client-common all 232.pgdg20.04+1 [91.6 kB]
Get:9 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-client-14 amd64 14.1-2.pgdg20.04+1 [1,602 kB]
Get:10 http://by.archive.ubuntu.com/ubuntu focal/main amd64 libsensors-config all 1:3.6.0-2ubuntu1 [6,092 B]
Get:11 http://by.archive.ubuntu.com/ubuntu focal/main amd64 libsensors5 amd64 1:3.6.0-2ubuntu1 [27.4 kB]
Get:12 http://by.archive.ubuntu.com/ubuntu focal/main amd64 ssl-cert all 1.0.39 [17.0 kB]
Get:13 http://by.archive.ubuntu.com/ubuntu focal-updates/main amd64 sysstat amd64 12.2.0-2ubuntu0.1 [448 kB]
Get:14 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-common all 232.pgdg20.04+1 [225 kB]
Get:15 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-14 amd64 14.1-2.pgdg20.04+1 [15.7 MB]
Get:16 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql all 14+232.pgdg20.04+1 [66.2 kB]
...
- read the article 19.1. The PostgreSQL User Account
- check if user postgres exists
- for u2004s01, u2004s02, u2004s03
- check if user postgres exists
yury@u2004s01:~$ sudo groups postgres
postgres : postgres ssl-cert
- check if psql is available
yury@u2004s01:~$ sudo -i -u postgres
postgres@u2004s01:~$ psql
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.
postgres=# \q
postgres@u2004s01:~$ logout
yury@u2004s01:~$
yury@u2004s01:~$ sudo -u postgres psql
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=# \q
# Using SQL
yury@u2004s01:~$ sudo -u postgres psql
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.
postgres=# SELECT * FROM pg_database;
oid | datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl
-------+-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+-------------------------------------
13726 | postgres | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 13725 | 726 | 1 | 1663 |
1 | template1 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | t | -1 | 13725 | 726 | 1 | 1663 | {=c/postgres,postgres=CTc/postgres}
13725 | template0 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | f | -1 | 13725 | 726 | 1 | 1663 | {=c/postgres,postgres=CTc/postgres}
(3 rows)
postgres=# \q
- read the article 19.2. Creating a Database Cluster
- for u2004s01, u2004s02, u2004s03
sudo mkdir /usr/local/pgsql
sudo chown postgres /usr/local/pgsql
sudo -i -u postgres
/usr/lib/postgresql/14/bin/initdb -D /usr/local/pgsql/data
logout
Click to show the response
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:~$
- 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
- As we saw above server is already running
UNIT LOAD ACTIVE SUB DESCRIPTION
...
postgresql.service loaded active exited PostgreSQL RDBMS
postgresql@14-main.service 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
- 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
...
yury@u2004s01:~$ sudo nano /lib/systemd/system/postgresql.service
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=oneshot
ExecStart=/bin/true
ExecReload=/bin/true
RemainAfterExit=on
[Install]
WantedBy=multi-user.target
- Under 19.3. Starting the Database Server they recommend to add the line below into postgresql.service file
ExecStart=/usr/lib/postgresql/14/bin/postgres -D /usr/local/pgsql/data
- 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
sudo systemctl daemon-reload
sudo systemctl start postgresql.service
- 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
- start the service
sudo systemctl daemon-reload
sudo systemctl start postgresql.service
- test
click to show the test
yury@u2004s01:~$ sudo -u postgres psql
[sudo] password for yury:
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
yury@u2004s01:~$
sudo mkdir /usr/local/pgsql
sudo chown postgres /usr/local/pgsql
sudo -i -u postgres
/usr/lib/postgresql/14/bin/initdb -D /usr/local/pgsql/data
logout
-
Modification of /etc/postgresql/14/main/postgresql.conf
- set
data_directory = '/usr/local/pgsql/data' # use data in another directory
with
- set
sudo nano /etc/postgresql/14/main/postgresql.conf
- Restart the service
sudo systemctl stop postgresql.service
sudo systemctl daemon-reload
sudo systemctl start postgresql.service
- set
listen_addresses = '*' # what IP address(es) to listen on;
with
sudo nano /etc/postgresql/14/main/postgresql.conf
- set identical password for postgres-user with
sudo -u postgres psql
ALTER USER postgres with encrypted password 'your_password';
\q
- After configuring the password, edit the file /etc/postgresql/14/main/pg_hba.conf and set
- set
local all postgres md5
- set
- read the article 20.6.1. Sending Servers
- read the article 20.6.2. Primary Server
- for u2004s01
- with
sudo nano /etc/postgresql/14/main/postgresql.conf
set the following values
- with
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
- with
host replication replicator u2004s02 md5
host replication replicator u2004s03 md5
- for u2004s01
- Restart the service
sudo systemctl restart postgresql.service
- for u2004s02 and u2004s03
- stop the service
sudo systemctl stop postgresql.service
- for u2004s02 and u2004s03
- with
sudo nano /etc/postgresql/14/main/postgresql.conf
set the following values
- with
hot_standby = on # "off" disallows queries during recovery
- for u2004s02 and u2004s03
- run pg_basebackup
sudo -i -u postgres
cp -R /usr/local/pgsql/data /usr/local/pgsql/data_bak
rm -rf /usr/local/pgsql/data/*
pg_basebackup -h u2004s01 -D /usr/local/pgsql/data -U replicator -P -v -R
click to show pg_basebackup
postgres@u2004s02:~$ pg_basebackup -h u2004s01 -D /usr/local/pgsql/data -U replicator -P -v -R
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_4805"
26216/26216 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/2000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
- for u2004s02 and u2004s03
- start the service
sudo systemctl start postgresql.service
- for u2004s01
yury@u2004s01:~$ sudo -u postgres psql -c "select * from pg_stat_replication;"
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
------+----------+------------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
4814 | 16384 | replicator | 14/main | 192.168.100.62 | u2004s02 | 44494 | 2022-01-21 18:51:02.623047+00 | | streaming | 0/5000060 | 0/5000060 | 0/5000060 | 0/5000060 | | | | 0 | async | 2022-01-21 18:57:48.397214+00
4833 | 16384 | replicator | 14/main | 192.168.100.63 | u2004s03 | 45886 | 2022-01-21 18:56:15.632458+00 | | streaming | 0/5000060 | 0/5000060 | 0/5000060 | 0/5000060 | | | | 0 | async | 2022-01-21 18:57:45.848155+00
(2 rows)
- for u2004s01
yury@u2004s01:~$ sudo -i -u postgres psql
postgres=# CREATE DATABASE mytestdb;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
----------------+----------+----------+-------------+-------------+-----------------------
mytestdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
- for u2004s02(or u2004s03)
yury@u2004s02:~$ sudo -i -u postgres psql
[sudo] password for yury:
Password for user postgres:
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
----------------+----------+----------+-------------+-------------+-----------------------
mytestdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=#