01. Устанавливаем пакеты, необходимые для работы
[root]#
apt -y update
apt -y install gnupg2 wget nano mc vim less git zip gpg wget zstd iputils-ping build-essential
apt-get -y install make gcc
02. Добавляем новый репозитарий
[root]#
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
03. импортируем ключ нового репозитария:
[root]#
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
04. Выполняем обновление установленных пакетов:
[root]#
apt update
05. Выполняем установку сервера БД Postgres и создание дефолтной БД:
[root]#
apt install postgresql-17 postgresql-contrib-17 postgresql-server-dev-17
06. Выполняем рестарт сервиса БД:
[root]#
systemctl stop postgresql
systemctl enable postgresql
systemctl status postgresql
07. Выключить firewall на сервере
[root]#
systemctl disable ufw
systemctl stop ufw
08. Получить текущую конфигурацию дефолтного кластера
[root]#
pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
17 main 5432 down postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log
09. удалить дефолтный кластер
[root]#
pg_dropcluster --stop 17 main
10. Создать каталоги для БД нового кластера
[root]#
mkdir -p /pgarchivewal
mkdir -p /pgbackup
mkdir -p /pgdata/main
mkdir -p /pgdata/log
chown -R postgres:postgres /pgdata
chown -R postgres:postgres /pgarchivewal
chown -R postgres:postgres /pgbackup
11. Создать новый кластер в правильных каталогах
[postgres]$
pg_createcluster --datadir=/pgdata/main --logfile=/pgdata/log/postgresql.log --start 17 main
12. Выполнить рестарт сервера БД
[postgres]$
pg_ctlcluster 17 main stop
pg_ctlcluster 17 main start
13. Добавить файл параметров для пользователя postgres
[postgres]$
cd ~
vi ~/.profile
### ENV
export PATH=/usr/lib/postgresql/17/bin/:/usr/sbin:/usr/bin:/usr/sbin:/usr/bin:/sbin:/bin
export PGDATA=/etc/postgresql/17/main/
export EDITOR=vi
cd $PGDATA
14. Скачать модули для расширений:
15.1 pg_cron
[root]#
apt-get -y install postgresql-17-cron
15.2. pg_profile. Расширение для записи журналов
[root]#
cd /tmp
git clone https://github.com/zubkov-andrei/pg_profile.git
cd ./pg_profile
make USE_PGXS=1
make USE_PGXS=1 install
15.3. pg_store_plans. Расширение для записи истории выполения планов запросов
[root]#
cd /tmp
git clone https://github.com/ossc-db/pg_store_plans.git
cd ./pg_store_plans
make USE_PGXS=1
make USE_PGXS=1 install
15.4. pg_wait_sampling. Расширение для записи истории ожиданий
[root]#
cd /tmp
git clone https://github.com/postgrespro/pg_wait_sampling.git
cd ./pg_wait_sampling
make USE_PGXS=1
make USE_PGXS=1 install
15.5. pg_stat_kcache. Расширение собирает статистику о фактическом использовании системных ресурсов отдельными запросами клиентских процессов.
[root]#
cd /tmp
git clone https://github.com/powa-team/pg_stat_kcache.git
cd ./pg_stat_kcache
make USE_PGXS=1
make USE_PGXS=1 install
16. Установить EXTENSION в БД postgres
[postres]$
psql>
CREATE SCHEMA ext;
CREATE SCHEMA profile;
CREATE EXTENSION pg_cron;
CREATE EXTENSION dblink SCHEMA ext;
CREATE EXTENSION pg_stat_statements SCHEMA ext;
CREATE EXTENSION pg_profile SCHEMA profile;
CREATE EXTENSION pg_store_plans SCHEMA ext;
CREATE EXTENSION pg_wait_sampling SCHEMA ext;
CREATE EXTENSION pg_stat_kcache SCHEMA ext;
17. Создание прикладной БД и схемы
[postres]$
psql>
create database <ИМЯ_БД>;
\c <ИМЯ_БД>
create schema main;
-- проверить установку EXTENSION
\dx
18. Внести изменения в файл pg_hba.conf (добавить правило)
[postgres]$
vi /etc/postgresql/17/main/pg_hba.conf
##
host all all 0.0.0.0/0 scram-sha-256
19. Добавить параметры для начала работа сервера БД
[postgres]$
vi /etc/postgresql/17/main/postgresql.conf
cron.database_name = 'postgres'
shared_preload_libraries = 'pg_cron, pg_stat_statements, pg_stat_kcache, pg_store_plans, pg_wait_sampling'
# Connectivity
max_connections = 300
listen_addresses = '*'
log_destination = 'stderr'
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,client=%h ' ###'[%t u=%u d=%d h=%r p=%p c=%c l=%l s=%s v=%v x=%x] '
# pg_wait_sampling
pg_wait_sampling.history_period = 5000 # 5с
pg_wait_sampling.history_size = 86400 # срезов за 5 дней
pg_wait_sampling.profile_period = 5000 # 5с
pg_wait_sampling.profile_pid = true
pg_wait_sampling.sample_cpu = true
pg_wait_sampling.profile_queries = all
# pg_store_plans
pg_store_plans.max = 1000
pg_store_plans.max_plan_length = 5000
pg_store_plans.min_duration = 5000
pg_store_plans.track = all
pg_store_plans.save = on
pg_store_plans.log_timing = on
pg_store_plans.log_analyze = on
pg_store_plans.log_buffers = off
# pg_profile
pg_profile.topn = 20
pg_profile.max_sample_age = 7
20. Выполнить рестарт сервера БД
[postgres]$
pg_ctlcluster 17 main stop
pg_ctlcluster 17 main start
------------------------------------------------------
21. Создание ролевой модели
[postgres]$
21.1. добавление роли администратора БД (групповая роль)
drop role if exists db_admin;
CREATE ROLE db_admin WITH NOLOGIN CREATEDB CREATEROLE SUPERUSER REPLICATION;
21.2. добавление роли администратора приложения (групповая роль)
drop role if exists as_admin;
CREATE ROLE as_admin WITH NOLOGIN NOCREATEDB NOCREATEROLE NOSUPERUSER NOREPLICATION;
22. Предоставление прав групповым ролям
--
alter database <ИМЯ_БД> owner to db_admin;
--
GRANT CONNECT ON DATABASE <ИМЯ_БД> TO db_admin;
GRANT CONNECT ON DATABASE <ИМЯ_БД> TO as_admin;
--
\c <ИМЯ_БД>
alter schema public owner to db_admin;
alter schema main owner to as_admin;
23. Прикладные и административные роли (с правом авторизации)
23.1. ТУЗ администратора БД, выдана групповая роль db_admin
drop OWNED BY dba_tuz;
drop role if exists dba_tuz;
CREATE ROLE dba_tuz WITH LOGIN password '***';
grant db_admin to dba_tuz;
ALTER ROLE dba_tuz set role db_admin;
23.2. ТУЗ для подключений прикладных сервисов, выдана групповая роль as_admin
drop OWNED BY web_tuz;
drop role if exists web_tuz;
CREATE ROLE web_tuz WITH LOGIN password '***';
grant as_admin to web_tuz;
ALTER ROLE web_tuz set role as_admin;
23.3. ТУЗ для работы механизма сбора диагностической информации (for extention profile)
\c postgres
drop OWNED BY profile_tuz;
drop role if exists profile_tuz;
CREATE ROLE profile_tuz WITH LOGIN password '***';
ALTER ROLE profile_tuz SET search_path = profile,cron,ext;
grant pg_monitor to profile_tuz;
grant pg_read_all_settings to profile_tuz;
grant pg_read_all_stats to profile_tuz;
grant pg_stat_scan_tables to profile_tuz;
GRANT EXECUTE ON FUNCTION pg_stat_statements_reset TO profile_tuz;
-- database
GRANT CONNECT ON DATABASE postgres TO profile_tuz;
-- схема profile
GRANT USAGE ON SCHEMA profile TO profile_tuz;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA profile TO profile_tuz;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA profile TO profile_tuz;
GRANT execute ON ALL FUNCTIONS IN SCHEMA profile TO profile_tuz;
-- схема cron
GRANT USAGE ON SCHEMA cron TO profile_tuz;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA cron TO profile_tuz;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA cron TO profile_tuz;
GRANT execute ON ALL FUNCTIONS IN SCHEMA cron TO profile_tuz;
-- схема ext
GRANT USAGE ON SCHEMA ext TO profile_tuz;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA ext TO profile_tuz;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA ext TO profile_tuz;
GRANT execute ON ALL FUNCTIONS IN SCHEMA ext TO profile_tuz;
GRANT EXECUTE ON FUNCTION ext.pg_stat_statements_reset TO profile_tuz;
24. Создание файла паролей c необходимыми правами для работы pg_cron (для pg_cron)
где,
[profile_tuz]$
vi ~/.pgpass
localhost:5432:postgres:profile_tuz:заменить_на пароль
chmod 0600 ~/.pgpass
примечание: данный пункт выполнить на всех узлах кластера Postgres!
25. Изменение информации о подключении profile к базе данных (для работы pg_stat_statements)
[profile_tuz]$
psql>
SELECT profile.set_server_connstr('local','host=127.0.0.1 dbname=postgres port=5432 user=profile_tuz password=pasword123');
-- проверка
select * from profile.servers;
26. Создаем и конфигурирование задания сбора диагностической информации (новый отчет каждые 5 мин)
[profile_tuz]$
psql>
select cron.schedule(job_name := 'AWR',schedule := '*/5 * * * *',command := $$SELECT profile.take_sample();$$);
27. Проверка задания и журнал работы задания
[profile_tuz]$
psql>
select * from cron.job;
select * from cron.job_run_details order by runid desc limit 10;
дополнения:
-- удаление задания
select cron.unschedule(1);
-- изменение расписания
SELECT cron.alter_job(
job_id := (select jobid from cron.job where jobname = 'AWR'),
database := 'postgres',
username := 'profile_tuz'
);
No comments:
Post a Comment