Руководство по миграции на Postgres 15

В данной инструкции мы рассмотрим пример обновления СУБД PostgreSQL с версии 11,13 на версию 15. В качестве исходной операционной системы будет использоваться CentOS 7 Postgres 11, а так же Astra Linux SE 1.7 PostgresPro 13.
В качестве целевой ОС будет использована Rocky 9 PostgreSQL 15.
За исключением мелких различий ОС, на процесс миграции данных ни происхождение PostgreSQL ни версия ОС не влияет.

Руководство по миграции на Postgres 15

1. На целевом хосте (в нашем случае это Rocky 9 PostgreSQL15) должна быть установлена БД, а так же установлен пакет luxmsbi-pg15, который в свою очередь установит необходимые пакеты расширений.
2. Требуется посмотреть список расширений, которые мы используем в текущем PostgreSQL, и сравнить их со списком расширений на исходном хосте. При необходимости установить недостающие пакеты расширений.

su - postgres -c "psql mi"
mi=# \dx
                                         Список установленных расширений
     Имя      | Версия |   Схема    |                                  Описание                                  
--------------+--------+------------+----------------------------------------------------------------------------
 btree_gin    | 1.3    | public     | support for indexing common datatypes in GIN
 btree_gist   | 1.7    | public     | support for indexing common datatypes in GiST
 hstore       | 1.8    | public     | data type for storing sets of (key, value) pairs
 http         | 1.5    | public     | HTTP client for PostgreSQL, allows web page retrieval inside the database.
 intarray     | 1.5    | public     | functions, operators, and index support for 1-D arrays of integers
 pgcrypto     | 1.3    | public     | cryptographic functions
 plpgsql      | 1.0    | pg_catalog | PL/pgSQL procedural language
 plv8         | 3.2.1  | pg_catalog | PL/JavaScript (v8) trusted procedural language
 postgres_fdw | 1.1    | public     | foreign-data wrapper for remote PostgreSQL servers
 redis        | 0.0.1  | public     | Redis
 redis_fdw    | 1.0.8  | public     | foreign-data wrapper for Redis
(11 строк)

Не поддерживаемые расширения

Если в списках расширений на исходной БД есть расширения недоступные для целевой БД, необходимо отключить их и удалить связанные с ними объекты БД (схемы, таблицы, функции и т.д.)

осторожно

Мы отказались от использования расширения AGE после версии 8.9.3.

Поэтому если у Вас в списке есть запись про это расширение, Вам необходимо его удалить.

Вам нужно выполнить обновление Luxms BI БД, до версии 8.9.3, в соответсвии с Документацией по обновлению (см. раздел 10 Установка обновлений в Руководстве системного администратора).

Как проверить версию БД Luxms BI - можно посмотреть по ссылке “О программе” в Web-приложении

Если Ваша версия БД уже обновлена до этой или более поздней версии необходимо убедиться в отсутствии конфигурационных параметров БД, связанных с этим расширением.

1. Роль bi не предполагает использование shared library. Безусловно требуется выполнить команду:

su - postgres -c 'psql -c "ALTER USER bi reset shared_preload_libraries;"'

2. Далее проверяем конфигурационные файлы на наличие age

[root@ups03 ~]# grep 'shared_preload_libraries' /var/lib/pgsql/11/data/postgresql*.conf 
/var/lib/pgsql/11/data/postgresql.auto.conf:shared_preload_libraries = 'age'
/var/lib/pgsql/11/data/postgresql.conf:#shared_preload_libraries = ''   # (change requires restart)

• postgresql.auto.conf. Если есть age в shared_preload_libraries то выполняем команду:

su - postgres -c 'psql -c "ALTER system reset shared_preload_libraries;"'

У вас может быть помимо age еще какие-либо библиотеки, тогда выполняем команду:

su - postgres -c 'psql -c "ALTER system set shared_preload_libraries=<your_libraries>;"'

• postgresql.conf. Если есть age в shared_preload_libraries то удаляем его, у вас может быть несколько библиотек их удалять не требуется.

3. Перезагрузка сервера.

systemctl restart postgresql-11

Дополнительные шаги подготовки

1. Так как дамп может занять много дискового пространства, то нам нужно убедиться в наличие дискового пространства. Посмотреть свободное место можно командой df -h
2. Проверить размер БД на исходном хосте.

sudo -iu postgres psql mi -c "SELECT pg_size_pretty(pg_database_size(current_database()));"
 pg_size_pretty 
----------------
 253 MB
(1 строка)

3. До снятия дампа БД необходимо остановить все сервисы Luxms BI, подключающихся к существующей БД. В том числе и на самом хосте с БД:

sudo systemctl stop luxms\*

6. Так же стоит сравнить настройки для файла pg_hba.conf. Некоторые настройки, которые явно менялись для исходной версии СУБД, стоит перенести в конфигурационные файлы нового postgresql.

Снятие дампа с исходного хоста

1. Мы предлагаем запускать команду для снятия дампа непосредственно на новом сервере. При этом, мы подключимся к старому серверу. Для этого вводим:

pg_dump -d postgresql://bi:bi@192.168.0.10/mi -c -C -Z 9 --quote-all-identifiers -f "/path/to/backup/mi_"`date +%Y%m%d`".dump.gz"

• где 192.168.0.10 - адрес сервера, где находится старая версия PostgreSQL. bi:bi - логин и пароль для подклчюения.

Если нет возможности удаленного подключения к старой БД, снять дамп можно локально, и безопасным способом передать дамп на целевой хост.

Загрузка дампа на целевой хост

1. В случае если на целевом хосте будет присутствовать база данных mi для исключения конфликтов требуется её удалить. Для этого нам требуется остановить сервисы Luxmsbi
2. Теперь переходим непосредственно к шагу загрузки дампа в базу данных, для этого требуется выполнить команду:

su - postgres -c 'zcat /tmp/mi_20240719.dump.gz | psql -U postgres &> "`date +%Y%m%d`".dump.log'

3. Следующим шагом будем просмотр лога загрузки дампа. В Случае непонятных или подозрительных команд для разъяснения, необходимо обратиться в техническую поддержку.
4. Проверка версии базы данных. Бывают такие случаи когда дамп мы снимаем с bi версии 9.х.х, а загружаем в версию 10.х.х как на скриншоте ниже, или даже в рамках одной мажорной версии, нам требуется запустить скрипт обновления базы данных mi.

migration_postgres_1.png

Для этого нам требуется выполнить команду:

su - postgres -c "/usr/share/luxmsbi-pg/luxmsbi-setupdb.sh --upgrade"

После выполнения данной процедуры мы увидим изменения в версии luxmsbi-pg.

migration_postgres_2.png

5. При установке БД Luxms BI из пакета luxmsbi-pg15 устанавливаются параметры сервера БД, расчитанные для минимальных ресурсов:

-- Generated by PGConfig 2.0 beta
---- http://pgconfig.org

-- Memory Configuration
ALTER SYSTEM SET shared_buffers TO '1GB';
ALTER SYSTEM SET effective_cache_size TO '3GB';
ALTER SYSTEM SET work_mem TO '20MB';
ALTER SYSTEM SET maintenance_work_mem TO '512MB';

-- Checkpoint Related Configuration
ALTER SYSTEM SET min_wal_size TO '2GB';
ALTER SYSTEM SET max_wal_size TO '6GB';
ALTER SYSTEM SET checkpoint_completion_target TO '0.9';
ALTER SYSTEM SET wal_buffers TO '16MB';

-- Network Related Configuration
ALTER SYSTEM SET listen_addresses TO '*';
ALTER SYSTEM SET max_connections TO '150';

-- Storage Configuration
ALTER SYSTEM SET random_page_cost TO '4.0';
ALTER SYSTEM SET effective_io_concurrency TO '2';

-- Worker Processes
ALTER SYSTEM SET max_worker_processes TO '2';
ALTER SYSTEM SET max_parallel_workers_per_gather TO '1';
ALTER SYSTEM SET max_parallel_workers TO '2';

После установки рекомендуем рассчитать параметры под ваши ресурсы. Наши рекомендации:

1) Используйте для изменения конфигурации сервера команды ALTER SYSTEM, это позволяет избежать ошибок при редактировании postgresql.conf. При этом конфигурационные параметры применяются при каждом рестарте экземпляров БД из конфигурационного файла postgresql.auto.conf.

2) Для генерации конфигурационных команд можно использовать любой калькулятор, мы рекомендуем PGConfig.

Запуск ПО Luxms BI

1. Запускаем остановленные, находящиеся на текущем хосте сервисы командой:

sudo systemctl --all start luxms\*

Проблема подключения к СУБД после обновления

После завершения работ по загрузке дампа, мы можем подключиться к СУБД от пользователя postgres, но не можем подключиться по сети или из приложения.

Причина: как правило, проблема в конфигурации pg_hba.

Решение: файл pg_hba.conf регламентирует условия подключения к СУБД — с каких узлов, для каких учетных записей, к каким базам и с помощью какого метода аутентификации. Необходимо привести в соответствие наши файлы. Данный файл находится в том же каталоге, что и основной файл конфигурации, в нашем примере в Rocky9 PostgreSQL 15 это:

vi /var/lib/pgsql/data/pg_hba.conf

Также необходимо обратить внимание на методы шифрования паролей. Например, в 11 версии по умолчанию используется md5, например:

host    all             all             127.0.0.1/32            md5

В то время, как в 15 версии уже используется scram-sha-256:

host    all             all             127.0.0.1/32            scram-sha-256

Таким образом, при миграции данных в новую базу были перенесены и пароли с алгоритмом шифрования md5, а при подключении система пытается использовать scram-sha-256. Полученная таким образом последовательность не соответствует записанной, что приводит к ошибкам аутентификации. Для решения проблемы можно поменять scram-sha-256 на md5 в файле pg_hba.conf.