Make sure '/etc/hosts' contains:
192.168.88.11 node1
192.168.88.12 node2
192.168.88.13 node3
Install dependencies:
apt update && apt-get -y install postgresql postgresql-contrib watchdog nmap jq nfs-common nfs-kernel-server python3-dev python3-pip python3-testresources
Disable PosthreSQL service because we will manage DB engine through Patroni framework:
systemctl stop postgresql && systemctl disable postgresql
Sepcial directive to disable service for Debian/Ubuntu:
sed -i 's|^auto|disabled|' /etc/postgresql/12/main/start.conf && cat /etc/postgresql/12/main/start.conf
Refresh systemd definitions:
systemctl daemon-reload
Activate watchdog/softdog:
modprobe softdog
Active watchod at system startup:
echo 'modprobe softdog' | sudo tee -a /etc/rc.modules
Ensure users have read and write access to set the timer:
echo 'KERNEL=="watchdog", MODE="0666"' | sudo tee /etc/udev/rules.d/61-watchdog.rules
Allow postgres user to administer watchdog:
chown postgres /dev/watchdog
Create a directory for shared archive:
mkdir -p /wal_archive && chown -R postgres. /wal_archive && chmod 0750 -R /wal_archive
Install Access Controll List rule which servers can work with this directory:
echo '
/wal_archive 192.168.88.11(rw,sync,no_subtree_check)
/wal_archive 192.168.88.12(rw,sync,no_subtree_check)
/wal_archive 192.168.88.13(rw,sync,no_subtree_check)
/wal_archive 192.168.88.10(rw,sync,no_subtree_check)
' | sudo tee -a /etc/exports
Export all directories:
exportfs -a
Mount now:
mount -t nfs 192.168.88.10:/wal_archive /wal_archive
If it does not fail then add entry to fstab:
echo '192.168.88.10:/wal_archive /wal_archive nfs rw,bg,soft,intr,nosuid 0 0' | sudo tee -a /etc/fstab
Allow any user to use postgres utilities:
ln -s /usr/lib/postgresql/12/bin/* /usr/sbin/
Patroni has been writen in python, latast version can be used from direct python repository:
pip3 install --upgrade pip && pip3.8 install --upgrade setuptools && pip3.8 install psycopg2-binary python-etcd patroni
Check version. It should print 'patroni 2.0.2':
patroni --version
Patroni ctl should have 'patronictl version 2.0.2':
patronictl version
Make directory where patroni config will be kept:
mkdir -p /etc/patroni
Install new patroni config on node1:
echo "
scope: patronii_ha
namespace: /service/
name: node1
restapi:
listen: 0.0.0.0:8008
connect_address: 192.168.88.11:8008
etcd:
host: 127.0.0.1:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
parameters:
shared_buffers: '128MB'
work_mem: '4MB'
wal_level: hot_standby
hot_standby: 'on'
wal_keep_segments: 8
max_connections: 1000
max_wal_senders: 10
max_replication_slots: 10
wal_log_hints: 'on'
archive_mode: 'on'
archive_timeout: 1800s
archive_command: mkdir -p /wal_archive/archive/ && test ! -f /wal_archive/archive/%f && cp %p /wal_archive/archive/%f
restore_command: cp /wal_archive/archive/%f %p
initdb:
- encoding: UTF8
- data-checksums
pg_hba:
- host replication replicator 127.0.0.1/32 md5
- host all all 0.0.0.0/0 md5
- host replication postgres 127.0.0.1/32 md5
- host replication postgres 192.168.88.11/32 md5
- host replication postgres 192.168.88.12/32 md5
- host replication postgres 192.168.88.13/32 md5
- host all postgres all md5
- host replication postgres all md5
users:
admin:
password: admin
options:
- createrole
- createdb
postgresql:
listen: 0.0.0.0:5444
connect_address: 192.168.88.11:5444
data_dir: /postgresql/12/patroni/pg_data
authentication:
replication:
username: postgres
password: postgres
superuser:
username: postgres
password: postgres
rewind:
username: rewind_user
password: rewind_password
parameters:
unix_socket_directories: '/tmp'
watchdog:
mode: automatic
device: /dev/watchdog
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
" | sudo tee /etc/patroni/config.yml
Patroni service runs under user 'postgres'. Config will belong to 'postgres':
chown -R postgres:postgres /etc/patroni
Since we will not run PostgreSQL engine in a standart way over port 5432 and over /var/lib/postgresql dir, then let's create a different directory:
mkdir -p /postgresql/12/patroni && chown -R postgres. /postgresql && chmod -R 0750 /postgresql
Validate directory is empty:
ls -lh /postgresql/12/patroni
Check if etcd daemon is reachable:
nmap -p 2379 192.168.88.10
It should print:
Starting Nmap 7.80 ( https://nmap.org ) at 2021-04-05 12:37 EEST
Nmap scan report for 192.168.88.10
Host is up (0.00021s latency).
PORT STATE SERVICE
2379/tcp open etcd-client
MAC Address: FC:3F:DB:8D:51:8B (Hewlett Packard)
Nmap done: 1 IP address (1 host up) scanned in 0.06 seconds
In this stage we can launch patroni in foreground an observe if it's functional. Navigate to posthres user:
su - postgres
Sheck how many patroni related binaries and direcotires have been made:
whereis patroni
It should print only:
patroni: /etc/patroni /usr/local/bin/patroni
Validate config, if everything is fine, the output should be empty:
patroni --validate-config /etc/patroni/config.yml
patronictl --config-file /etc/patroni/config.yml list
It should print cluster is 'uninitialized':
+ Cluster: patronii_ha (uninitialized) ---------+
| Member | Host | Role | State | TL | Lag in MB |
+--------+------+------+-------+----+-----------+
+--------+------+------+-------+----+-----------+
If it's not unitalized and this is first node we are building then remove. This is because 'etcd' has remembered something:
$ patronictl --config-file /etc/patroni/config.yml remove patronii_ha
+ Cluster: patronii_ha (6947588788899999859) ---+
| Member | Host | Role | State | TL | Lag in MB |
+--------+------+------+-------+----+-----------+
+--------+------+------+-------+----+-----------+
Please confirm the cluster name to remove: patronii_ha
You are about to remove all information in DCS for patronii_ha, please type: "Yes I am aware": Yes I am aware
Launch in foreground:
patroni /etc/patroni/config.yml
It will print:
2021-04-05 15:36:47,030 INFO: No PostgreSQL configuration items changed, nothing to reload.
2021-04-05 15:36:47,035 INFO: Lock owner: None; I am node2
2021-04-05 15:36:47,039 INFO: trying to bootstrap a new cluster
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 text search configuration will be set to "english".
Data page checksums are enabled.
creating directory /postgresql/12/patroni/pg_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 ... Europe/Riga
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/12/bin/pg_ctl -D /postgresql/12/patroni/pg_data -l logfile start
2021-04-05 15:36:48,823 INFO: postmaster pid=13243
2021-04-05 15:36:48.828 EEST [13243] LOG: starting PostgreSQL 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
2021-04-05 15:36:48.829 EEST [13243] LOG: listening on IPv4 address "0.0.0.0", port 5444
2021-04-05 15:36:48.833 EEST [13243] LOG: listening on Unix socket "/tmp/.s.PGSQL.5444"
2021-04-05 15:36:48.856 EEST [13245] LOG: database system was shut down at 2021-04-05 15:36:47 EEST
2021-04-05 15:36:48.857 EEST [13246] FATAL: the database system is starting up
localhost:5444 - rejecting connections
2021-04-05 15:36:48.873 EEST [13243] LOG: database system is ready to accept connections
localhost:5444 - accepting connections
2021-04-05 15:36:48,895 INFO: establishing a new patroni connection to the postgres cluster
2021-04-05 15:36:48,903 INFO: running post_bootstrap
2021-04-05 15:36:48,931 WARNING: Could not activate Linux watchdog device: "Can't open watchdog device: [Errno 2] No such file or directory: '/dev/watchdog'"
2021-04-05 15:36:48,943 INFO: initialized a new cluster
2021-04-05 15:36:58,934 INFO: Lock owner: node2; I am node2
2021-04-05 15:36:58,946 INFO: no action. i am the leader with the lock
At this point some content will be made at directory:
ls -lh /postgresql/12/patroni
Exit user 'postgres':
exit
Let's install 'patroni' application as systemd service:
echo 'W1VuaXRdCkRlc2NyaXB0aW9uPVJ1bm5lcnMgdG8gb3JjaGVzdHJhdGUgYSBoaWdoLWF2YWlsYWJpbGl0eSBQb3N0Z3JlU1FMCkFmdGVyPW5ldHdvcmsudGFyZ2V0CkNvbmRpdGlvblBhdGhFeGlzdHM9L2V0Yy9wYXRyb25pL2NvbmZpZy55bWwKCltTZXJ2aWNlXQpUeXBlPXNpbXBsZQoKVXNlcj1wb3N0Z3JlcwpHcm91cD1wb3N0Z3JlcwoKIyBSZWFkIGluIGNvbmZpZ3VyYXRpb24gZmlsZSBpZiBpdCBleGlzdHMsIG90aGVyd2lzZSBwcm9jZWVkCkVudmlyb25tZW50RmlsZT0tL2V0Yy9wYXRyb25pX2Vudi5jb25mCgpXb3JraW5nRGlyZWN0b3J5PX4KCiMgUHJlLWNvbW1hbmRzIHRvIHN0YXJ0IHdhdGNoZG9nIGRldmljZQojIFVuY29tbWVudCBpZiB3YXRjaGRvZyBpcyBwYXJ0IG9mIHlvdXIgcGF0cm9uaSBzZXR1cAojRXhlY1N0YXJ0UHJlPS0vdXNyL2Jpbi9zdWRvIC9zYmluL21vZHByb2JlIHNvZnRkb2cKI0V4ZWNTdGFydFByZT0tL3Vzci9iaW4vc3VkbyAvYmluL2Nob3duIHBvc3RncmVzIC9kZXYvd2F0Y2hkb2cKCiMgU3RhcnQgdGhlIHBhdHJvbmkgcHJvY2VzcwpFeGVjU3RhcnQ9L3Vzci9sb2NhbC9iaW4vcGF0cm9uaSAvZXRjL3BhdHJvbmkvY29uZmlnLnltbAoKIyBTZW5kIEhVUCB0byByZWxvYWQgZnJvbSBwYXRyb25pLnltbApFeGVjUmVsb2FkPS9iaW4va2lsbCAtcyBIVVAgJE1BSU5QSUQKCiMgb25seSBraWxsIHRoZSBwYXRyb25pIHByb2Nlc3MsIG5vdCBpdCdzIGNoaWxkcmVuLCBzbyBpdCB3aWxsIGdyYWNlZnVsbHkgc3RvcCBwb3N0Z3JlcwpLaWxsTW9kZT1wcm9jZXNzCgojIEdpdmUgYSByZWFzb25hYmxlIGFtb3VudCBvZiB0aW1lIGZvciB0aGUgc2VydmVyIHRvIHN0YXJ0IHVwL3NodXQgZG93bgpUaW1lb3V0U2VjPTMwCgojIERvIG5vdCByZXN0YXJ0IHRoZSBzZXJ2aWNlIGlmIGl0IGNyYXNoZXMsIHdlIHdhbnQgdG8gbWFudWFsbHkgaW5zcGVjdCBkYXRhYmFzZSBvbiBmYWlsdXJlClJlc3RhcnQ9bm8KCltJbnN0YWxsXQpXYW50ZWRCeT1tdWx0aS11c2VyLnRhcmdldAoK' | base64 --decode | sudo tee /usr/lib/systemd/system/patroni.service && systemctl daemon-reload
Start again:
systemctl start patroni
Check status:
systemctl status patroni
We can reach new postgres server on port 5444:
PGHOST=127.0.0.1 PGUSER=postgres PGPASSWORD=postgres PGPORT=5444 psql
Exit:
exit
To see the role of this server, validate if it has replication active:
PGHOST=127.0.0.1 PGUSER=postgres PGPASSWORD=postgres PGPORT=5444 psql -c "select pg_is_in_recovery()"
In the output it will print 'f' which stands for false and means that this is first node which is a leader.
Ping the patroni API to see status:
curl -s http://localhost:8008/cluster | jq .
It will print:
{
"members": [
{
"name": "node1",
"role": "leader",
"state": "running",
"api_url": "http://:8008/patroni",
"timeline": 2
}
]
}
Troubleshootinh. If cluster was started in foreground with 'patroni /etc/patroni/config.yml' and it did print only:
2021-04-05 15:28:07,277 INFO: No PostgreSQL configuration items changed, nothing to reload.
2021-04-05 15:28:07,282 INFO: Lock owner: None; I am node2
2021-04-05 15:28:07,284 INFO: waiting for leader to bootstrap
Then need to list clusters:
patronictl --config-file /etc/patroni/config.yml list
It will print cluster ID (like 6947588788899999859) in parentacies. Now remove this:
$ patronictl --config-file /etc/patroni/config.yml remove patronii_ha
+ Cluster: patronii_ha (6947588788899999859) ---+
| Member | Host | Role | State | TL | Lag in MB |
+--------+------+------+-------+----+-----------+
+--------+------+------+-------+----+-----------+
Please confirm the cluster name to remove: patronii_ha
You are about to remove all information in DCS for patronii_ha, please type: "Yes I am aware": Yes I am aware
No comments:
Post a Comment