Для того, чтобы обновить пароли до SCRAM нужно убедиться в 2 вещах:

1. Используется PostgreSQL 10 или выше.

2. Драйвера, используемые для подключения к БД поддерживают SCRAM.

Само обновление проходит так:

1. В файле postgresql.conf установить параметр password_encryption в scram-sha-256.

2. Определиться с учетными записями, которым нужно обновить пароли. У меня это пользователь zabbix.

3. Перехешировать пароли, по сути просто ввести новый (естественно, никто не мешает ввести текущий) пароль с помощью команды \password в psql.

4. Заменить md5 в файле pg_hba.conf на scram-sha-256.

# Установить password_encryption = scram-sha-256
vim /var/lib/pgsql/14/data/postgresql.conf
# Перезагрузить конфигурацию PostgreSQL
service postgresql-14 reload
# Под пользователем postgres запустить psql
su - postgres
psql
# Выбрать учетные записи, которым требуется обновление
SELECT
    rolname, rolpassword ~ '^SCRAM-SHA-256\$' AS has_upgraded
FROM pg_authid
WHERE rolcanlogin;
# У меня такой результат:
 rolname  | has_upgraded
----------+--------------
 postgres | t
 zabbix   | f
(2 rows)
# Если в выборке у пользователя параметр has_upgraded установлен в false (f), то ему требуется перехешировать пароль.
# Вводим пароль для пользователя zabbix
\password zabbix
# Проверяем что для всех пользователей возвращается TRUE (t)
SELECT rolname, rolpassword ~ '^SCRAM-SHA-256\$' AS has_upgraded FROM pg_authid WHERE rolcanlogin;
# Теперь у всех true
 rolname  | has_upgraded
----------+--------------
 postgres | t
 zabbix   | t
(2 rows)
# Выход из psql
\q
# Осталось заменить md5 в файле pg_hba.conf на scram-sha-256
vim /var/lib/pgsql/14/data/pg_hba.conf
# Возвращаемся в УЗ root
exit
# И еще раз перезагрузить конфигурацию PostgreSQL
service postgresql-14 reload

 -- Посмотрим, сколько места на диске займут несжатые данные после выгузки в CSV.
select sum(before_compression_total_bytes)/1024/1024 as before_compression_total_mbytes, sum(after_compression_total_bytes)/1024/1024 as after_compression_total_mbytes FROM chunk_compression_stats('history_old');

-- Выгрузить данные в файл
\copy (select * from history_old) TO '/tmp/history.csv' DELIMITER ',' CSV

CREATE TEMP TABLE temp_history (
    itemid     bigint NOT NULL,
    clock      integer  DEFAULT '0' NOT NULL,
    value      DOUBLE PRECISION DEFAULT '0.0000'   NOT NULL,
    ns  integer  DEFAULT '0' NOT NULL
);
-- Загрузить данные из файла
\copy temp_history FROM '/tmp/history.csv' DELIMITER ',' CSV

-- Создадим hypertable и заполним её данными
select create_hypertable('history', 'clock', chunk_time_interval => 86400, migrate_data => true);
INSERT INTO history SELECT * FROM temp_history ON CONFLICT (itemid,clock,ns) DO NOTHING;

-- Включаем сжатие
select set_integer_now_func('history', 'zbx_ts_unix_now', true);
alter table history set (timescaledb.compress,timescaledb.compress_segmentby='itemid',timescaledb.compress_orderby='clock,ns');

-- В hypertable_schema стоит заменить public на свою схему, если она отличается
-- Возвращенный ID задачи (<JOB_ID>) нужно будет подставить в run_job
select add_compression_policy('history', (
    select extract(epoch from (config::json->>'compress_after')::interval) from timescaledb_information.jobs where application_name like 'Compression%%' and hypertable_schema='public' and hypertable_name='history_old'
    )::integer
);

select alter_job((select job_id from timescaledb_information.jobs where hypertable_schema='public' and hypertable_name='history'), scheduled => true);

-- Запустить задачу сжатия данных (вместо <JOB_ID> нужно поставить ID задачи из предыдущего шага)
call run_job(<JOB_ID>);
-- Может появиться уведомление 'NOTICE:  no chunks for hypertable public.history_uint that satisfy compress chunk policy', это нормально.

 -- Verify that there is enough space to allow export of uncompressed data
select sum(before_compression_total_bytes)/1024/1024 as before_compression_total_mbytes, sum(after_compression_total_bytes)/1024/1024 as after_compression_total_mbytes FROM chunk_compression_stats('history_uint_old');

-- Export data
\copy (select * from history_uint_old) TO '/tmp/history_uint.csv' DELIMITER ',' CSV

CREATE TEMP TABLE temp_history_uint (
    itemid     bigint NOT NULL,
    clock      integer  DEFAULT '0' NOT NULL,
    value      numeric(20)     DEFAULT '0' NOT NULL,
    ns  integer  DEFAULT '0' NOT NULL
);
-- Import data
\copy temp_history_uint FROM '/tmp/history_uint.csv' DELIMITER ',' CSV

-- Create hypertable and populate it
select create_hypertable('history_uint', 'clock', chunk_time_interval => 86400, migrate_data => true);
INSERT INTO history_uint SELECT * FROM temp_history_uint ON CONFLICT (itemid,clock,ns) DO NOTHING;

-- Enable compression
select set_integer_now_func('history_uint', 'zbx_ts_unix_now', true);
alter table history_uint set (timescaledb.compress,timescaledb.compress_segmentby='itemid',timescaledb.compress_orderby='clock,ns');

-- Substitute your schema in hypertable_schema
-- Job id will returned, it should be passed to run_job
select add_compression_policy('history_uint', (
    select extract(epoch from (config::json->>'compress_after')::interval) from timescaledb_information.jobs where application_name like 'Compression%%' and hypertable_schema='public' and hypertable_name='history_uint_old'
    )::integer
);

select alter_job((select job_id from timescaledb_information.jobs where hypertable_schema='public' and hypertable_name='history_uint'), scheduled => true);

-- Run compression job
call run_job(<JOB_ID>);
-- May show 'NOTICE:  no chunks for hypertable public.history_uint that satisfy compress chunk policy', it is fine.

 -- Verify that there is enough space to allow export of uncompressed data
select sum(before_compression_total_bytes)/1024/1024 as before_compression_total_mbytes, sum(after_compression_total_bytes)/1024/1024 as after_compression_total_mbytes FROM chunk_compression_stats('history_str_old');

-- Export data
\copy (select * from history_str_old) TO '/tmp/history_str.csv' DELIMITER ',' CSV

CREATE TEMP TABLE temp_history_str (
    itemid     bigint NOT NULL,
    clock      integer  DEFAULT '0' NOT NULL,
    value      varchar(255)    DEFAULT ''  NOT NULL,
    ns  integer  DEFAULT '0' NOT NULL
);
-- Import data
\copy temp_history_str FROM '/tmp/history_str.csv' DELIMITER ',' CSV

-- Create hypertable and populate it
select create_hypertable('history_str', 'clock', chunk_time_interval => 86400, migrate_data => true);
INSERT INTO history_str SELECT * FROM temp_history_str ON CONFLICT (itemid,clock,ns) DO NOTHING;

-- Enable compression
select set_integer_now_func('history_str', 'zbx_ts_unix_now', true);
alter table history_str set (timescaledb.compress,timescaledb.compress_segmentby='itemid',timescaledb.compress_orderby='clock,ns');

-- Substitute your schema in hypertable_schema
-- Job id will returned, it should be passed to run_job
select add_compression_policy('history_str', (
    select extract(epoch from (config::json->>'compress_after')::interval) from timescaledb_information.jobs where application_name like 'Compression%%' and hypertable_schema='public' and hypertable_name='history_str_old'
    )::integer
);

select alter_job((select job_id from timescaledb_information.jobs where hypertable_schema='public' and hypertable_name='history_str'), scheduled => true);

-- Run compression job
call run_job(<JOB_ID>);
-- May show 'NOTICE:  no chunks for hypertable public.history_uint that satisfy compress chunk policy', it is fine.

-- Verify that there is enough space to allow export of uncompressed data
select sum(before_compression_total_bytes)/1024/1024 as before_compression_total_mbytes, sum(after_compression_total_bytes)/1024/1024 as after_compression_total_mbytes FROM chunk_compression_stats('history_log_old');

-- Export data
\copy (select * from history_log_old) TO '/tmp/history_log.csv' DELIMITER ',' CSV

CREATE TEMP TABLE temp_history_log (
    itemid     bigint NOT NULL,
    clock      integer  DEFAULT '0' NOT NULL,
    timestamp  integer  DEFAULT '0' NOT NULL,
    source     varchar(64)     DEFAULT ''  NOT NULL,
    severity   integer  DEFAULT '0' NOT NULL,
    value      text     DEFAULT ''  NOT NULL,
    logeventid integer  DEFAULT '0' NOT NULL,
    ns  integer  DEFAULT '0' NOT NULL
);
-- Import data
\copy temp_history_log FROM '/tmp/history_log.csv' DELIMITER ',' CSV

-- Create hypertable and populate it
select create_hypertable('history_log', 'clock', chunk_time_interval => 86400, migrate_data => true);
INSERT INTO history_log SELECT * FROM temp_history_log ON CONFLICT (itemid,clock,ns) DO NOTHING;

-- Enable compression
select set_integer_now_func('history_log', 'zbx_ts_unix_now', true);
alter table history_log set (timescaledb.compress,timescaledb.compress_segmentby='itemid',timescaledb.compress_orderby='clock,ns');

-- Substitute your schema in hypertable_schema
-- Job id will returned, it should be passed to run_job
select add_compression_policy('history_log', (
    select extract(epoch from (config::json->>'compress_after')::interval) from timescaledb_information.jobs where application_name like 'Compression%%' and hypertable_schema='public' and hypertable_name='history_log_old'
    )::integer
);

select alter_job((select job_id from timescaledb_information.jobs where hypertable_schema='public' and hypertable_name='history_log'), scheduled => true);

-- Run compression job
call run_job(<JOB_ID>);
-- May show 'NOTICE:  no chunks for hypertable public.history_uint that satisfy compress chunk policy', it is fine. 

 -- Verify that there is enough space to allow export of uncompressed data
select sum(before_compression_total_bytes)/1024/1024 as before_compression_total_mbytes, sum(after_compression_total_bytes)/1024/1024 as after_compression_total_mbytes FROM chunk_compression_stats('history_text_old');

-- Export data
\copy (select * from history_text_old) TO '/tmp/history_text.csv' DELIMITER ',' CSV

CREATE TEMP TABLE temp_history_text (
    itemid     bigint NOT NULL,
    clock      integer  DEFAULT '0' NOT NULL,
    value      text     DEFAULT ''  NOT NULL,
    ns  integer  DEFAULT '0' NOT NULL
);
-- Import data
\copy temp_history_text FROM '/tmp/history_text.csv' DELIMITER ',' CSV

-- Create hypertable and populate it
select create_hypertable('history_text', 'clock', chunk_time_interval => 86400, migrate_data => true);
INSERT INTO history_text SELECT * FROM temp_history_text ON CONFLICT (itemid,clock,ns) DO NOTHING;

-- Enable compression
select set_integer_now_func('history_text', 'zbx_ts_unix_now', true);
alter table history_text set (timescaledb.compress,timescaledb.compress_segmentby='itemid',timescaledb.compress_orderby='clock,ns');

-- Substitute your schema in hypertable_schema
-- Job id will returned, it should be passed to run_job
select add_compression_policy('history_text', (
    select extract(epoch from (config::json->>'compress_after')::interval) from timescaledb_information.jobs where application_name like 'Compression%%' and hypertable_schema='public' and hypertable_name='history_text_old'
    )::integer
);

select alter_job((select job_id from timescaledb_information.jobs where hypertable_schema='public' and hypertable_name='history_text'), scheduled => true);

-- Run compression job
call run_job(<JOB_ID>);
-- May show 'NOTICE:  no chunks for hypertable public.history_uint that satisfy compress chunk policy', it is fine.

touch history_text.sql
vi history_text.sql
sudo -u zabbix psql zabbix < /tmp/history_text.sql
# Дальше вывод работы скрипта
 before_compression_total_mbytes | after_compression_total_mbytes
---------------------------------+--------------------------------
            292.0625000000000000 |            70.7031250000000000
(1 row)

COPY 1359548
CREATE TABLE
COPY 1359548
     create_hypertable
----------------------------
 (15,public,history_text,t)
(1 row)

INSERT 0 1359548
 set_integer_now_func
----------------------

(1 row)

ALTER TABLE
 add_compression_policy
------------------------
                   1007
(1 row)

                                               alter_job
-------------------------------------------------------------------------------------------------------
 (1007,"1 day",00:00:00,-1,01:00:00,t,"{""hypertable_id"": 15, ""compress_after"": 612000}",-infinity)
(1 row)

# Вызываем run_job с Job ID = 1007
echo "call run_job(1007);" |sudo -u zabbix psql zabbix
CALL