05.02. Additional Configuration for Patroni & Postgres & pg_cron

                                                    <<< Back   Next >>>

Postgres cluster #1:

***************************

- db-pgsql01.oracak.ru - master

- db-pgsql02.oracak.ru - slave

***************************

1. Execute on the servers according to the following list:

- db-pgsql01.oracak.ru - master
- db-pgsql02.oracak.ru - slave 

 

1.1.Install the contrib and pg_cron packages
[root]#

yum -y install postgresql13-contrib.x86_64

yum -y install pg_cron_13.x86_64

1.2. Install the pg_profile package
-- Download the package.
[root]#
https://github.com/zubkov-andrei/pg_profile/releases/

-- Extract the archive.
[root]#

tar -xzvf ./pg_profile--4.3.tar.gz

-- Move the contents of the pg_profile archive to the directory with PostgreSQL extensions.
[root]#

mv ./pg_profile* /usr/pgsql-13/share/extension

-- Perform a restart of PostgreSQL.
[root]#

systemctl restart postgresql-13

2. Execute on the master server.
- db-pgsql01.oracak.ru

[postgres] $>

patronictl -c /etc/patroni/postgres.yml edit-config

wal_level: logical

track_commit_timestamp = on

max_replication_slots = 10

max_wal_senders = 10

Note:
Configuring PostgreSQL instance parameters (locate and replace the line with 'wal_level').
-- When executing the command, the vi editor opens.
-- Make the necessary changes and save following vi rules.


3. Execute on the servers according to the following list:

- db-pgsql01.oracak.ru

- db-pgsql02.oracak.ru

3.1. Install the pg_cron extension on the database servers in the cluster
[postgres] psql$>

\c dbquik1

CREATE EXTENSION pg_stat_statements;

CREATE EXTENSION pg_cron;

CREATE EXTENSION dblink;

CREATE SCHEMA profile;

CREATE EXTENSION pg_profile SCHEMA profile;

3.2. Configure PostgreSQL instance parameters (locate and replace the line with 'shared_preload_libraries')
[postgres] $>

vi /etc/patroni/postgres.yml

### In the 'parameters' block, find and replace the lines:
-- It was:

shared_preload_libraries: auto_explain,pg_stat_statements,pg_cron

-- It should become:

shared_preload_libraries: test_decoding,auto_explain,pg_stat_statements,pg_cron

-- It was:

cron.database_name = 'postgres'

-- It should become:

cron.database_name = 'dbquik1'

-- add

track_activities = 'on'

track_counts = 'on'

track_io_timing = 'on'

track_functions = 'all'

track_activity_query_size = '2048'

-- "In the 'pg_hba.conf' block, insert the line before replication lines with the name patroni (do not lose the '-'):

- host all ggadmin 0.0.0.0/0 scram-sha-256

- host all cron_tech 0.0.0.0/0 scram-sha-256

3.3. Make changes to the haproxy configuration.
In the file /etc/haproxy/haproxy.cfg, modify the 'timeout client' parameter to 24h."

-- It was:

timeout client 30m

-- It should become:

timeout client 24h

4. Execute on the master server of the cluster:
- db-pgsql01.oracak.ru

[postgres] $> psql

\c dbquik1

-- 4.1. Grant the replication role to the user ggadmin and create the ggadmin schema.

alter user ggadmin with replication;

create schema ggadmin;

alter schema ggadmin owner to ggadmin;

-- 4.2. Create the stock schema (a schema with application tables)

create schema stock;

alter schema stock owner to stock;

-- 4.3. Grant additional privileges to the user cron_tech.

grant usage on schema cron to cron_tech;

grant usage on schema profile to cron_tech;

alter user cron_tech with superuser;

5. Execute on the servers according to the following list (reload patroni, restart databases, and haproxy).

- db-pgsql01.oracak.ru

- db-pgsql02.oracak.ru

-- reload&restart patroni cluster
[postgres] $>

patronictl --config-file /etc/patroni/postgres.yml reload quik_test-n0qw71 --force

patronictl --config-file /etc/patroni/postgres.yml restart quik_test-n0qw71 --force

-- restert haproxy
[root]#

systemctl stop haproxy

systemctl start haproxy

-----------------------------------------------------------------------------------------

Postgres cluster #2:
***************************

- db-pgsql03.oracak.ru - master

- db-pgsql04.oracak.ru - slave

***************************

1. Execute on the servers according to the following list:

- db-pgsql01.oracak.ru - master

- db-pgsql02.oracak.ru - slave

1.1.Install the contrib and pg_cron packages
[root]#

yum -y install postgresql13-contrib.x86_64

yum -y install pg_cron_13.x86_64

1.2. Install the pg_profile package
-- Download the package.
[root]#
https://github.com/zubkov-andrei/pg_profile/releases/

-- Extract the archive.
[root]#

tar -xzvf ./pg_profile--4.3.tar.gz

-- Move the contents of the pg_profile archive to the directory with PostgreSQL extensions.
[root]#

mv ./pg_profile* /usr/pgsql-13/share/extension

-- Perform a restart of PostgreSQL.
[root]#

systemctl restart postgresql-13

2. Execute on the master server.
- db-pgsql03.oracak.ru

[postgres] $>

patronictl -c /etc/patroni/postgres.yml edit-config

wal_level: logical

track_commit_timestamp = on

max_replication_slots = 10

max_wal_senders = 10

cron.database_name = 'dbquik2'

Note:
Configuring PostgreSQL instance parameters (locate and replace the line with 'wal_level').
-- When executing the command, the vi editor opens.
-- Make the necessary changes and save following vi rules.

3.1. Install the pg_cron extension on the database servers in the cluster
[postgres] psql$>

\c dbquik1

CREATE EXTENSION pg_stat_statements;

CREATE EXTENSION pg_cron;

CREATE EXTENSION dblink;

CREATE SCHEMA profile;

CREATE EXTENSION pg_profile SCHEMA profile;

3.2. Configure PostgreSQL instance parameters (locate and replace the line with 'shared_preload_libraries')
[postgres] $>
vi /etc/patroni/postgres.yml

### In the 'parameters' block, find and replace the lines:
-- It was:

shared_preload_libraries: auto_explain,pg_stat_statements,pg_cron

-- It should become:

shared_preload_libraries: test_decoding,auto_explain,pg_stat_statements,pg_cron

-- It was:

cron.database_name = 'postgres'

-- It should become:

cron.database_name = 'dbquik1'

-- add

track_activities = 'on'

track_counts = 'on'

track_io_timing = 'on'

track_functions = 'all'

track_activity_query_size = '2048'

-- "In the 'pg_hba.conf' block, insert the line before replication lines with the name patroni (do not lose the '-'):

- host all ggadmin 0.0.0.0/0 scram-sha-256

- host all cron_tech 0.0.0.0/0 scram-sha-256

3.3. Make changes to the haproxy configuration.
In the file /etc/haproxy/haproxy.cfg, modify the 'timeout client' parameter to 24h."

-- It was:

timeout client 30m

-- It should become:

timeout client 24h

4. Execute on the master server of the cluster:
- db-pgsql03.oracak.ru

[postgres] $> psql

\c dbquik2

-- 4.1. Grant the replication role to the user ggadmin and create the ggadmin schema.

alter user ggadmin with replication;

create schema ggadmin;

alter schema ggadmin owner to ggadmin;

-- 4.2. Create the stock schema (a schema with application tables)

create schema stock;

alter schema stock owner to stock;

-- 4.3. Grant additional privileges to the user cron_tech.

grant usage on schema cron to cron_tech;

grant usage on schema profile to cron_tech;

alter user cron_tech with superuser;

5. Execute on the servers according to the following list (reload patroni, restart databases, and haproxy).
- db-pgsql03.oracak.ru
- db-pgsql04.oracak.ru

-- reload&restart patroni cluser
[postgres] $>
patronictl --config-file /etc/patroni/postgres.yml reload quik_test-20j0ow --force
patronictl --config-file /etc/patroni/postgres.yml restart quik_test-20j0ow --force

-- restart haproxy
[root]#
systemctl stop haproxy
systemctl start haproxy

                                                    <<< Back   Next >>>