Обновление базы данных Postgresql с 10-ой до 11-ой версии


Я буду проводить обновление базы данных реального проекта с довольно большой базой данных которая использует табличные пространства. Для начала нам потребуется провести анализ существующей базы данных.

Начнем со сбора данных о существующих базах данных и их размеров, что позволит в первом приближении оценить, что обновление прошло успешно:

# SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname)/1024/1024 AS size_in_mb FROM 
pg_database ORDER by size_in_mb DESC;

В результате вы получите примерно такой результат который вам необходимо сохранить для сравнения с результатом обновления:

     database_name      | size_in_mb 
------------------------+------------
 Dev.GM00               |     557875
 Dev.Hyw.201803         |     314131
 ....
 LexcomVinProxy         |          8
 postgres               |          7
 template1              |          7
 template0              |          7
 BmwVinProxy            |          7
(30 rows)

Следующим этапом мы должны получить сведения о существующих табличных пространствах (на базе данных Postgresql версии 11 нам потребуется создать их в том же виде):

# \db+

У меня есть только одно дополнительное табличное пространство:

                                           List of tablespaces
     Name      |  Owner   |        Location        | Access privileges | Options |  Size   | Description
---------------+----------+------------------------+-------------------+---------+---------+-------------
 pg_default    | postgres |                        |                   |         | 545 GB  |
 pg_global     | postgres |                        |                   |         | 645 kB  |
 tblspace_slow | postgres | /mnt/2TB/tblspace_slow |                   |         | 1106 GB |
(3 rows)

Исходим мы из того, что Postgresql у нас подключен из репозитария я могу установить "рядом" с десятым Postgresql одинадцатый используя только одну команду:

# aptitude install postgresql-11

Теперь у нас в системе установлено два Postgresql разных версий и соответственно две службы сервера баз данных:

  postgresql@10-main.service                                                                loaded active running   PostgreSQL Cluster 10-main                                                 
● postgresql@11-main.service                                                                loaded failed failed    PostgreSQL Cluster 11-main

Обратите внимание, что теперь вызовы команд psql и т.п. вызывают врапер который работает с 11-ой версией:

$ psql --version
psql (PostgreSQL) 11.2 (Debian 11.2-1.pgdg90+1)

А для работы с psql именно 10-ой версии необходимо указывать полный путь к бинарному файлу десятки:

/usr/lib/postgresql/10/bin/psql

Останавливаем десятку и запускаем 11-ый:

# systemctl stop postgresql@10-main.service
# systemctl start postgresql@11-main.service

Начинаем мы с создания необходимого табличного пространства. Создаем каталог для хранения табличного пространства и задаем его владельца:

# mkdir /mnt/4TB/tblspace_slow
# chown postgres:postgres /mnt/4TB/tblspace_slow

Немного уличной магии:

$ ln -s /var/run/postgresql/.s.PGSQL.5433 /var/run/postgresql/.s.PGSQL.5432

В противном случае мы получим ошибку:

psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Можно конечно и переопределить путь к сокету, но это единичная операция работы с табличными пространствами и дальше нам это уже не понадобится. Создаем табличное пространство:

# CREATE TABLESPACE tblspace_slow LOCATION '/mnt/4TB/tblspace_slow/';

Проверяем, что все прошло успешно:

postgres=# \db+
                                           List of tablespaces
     Name      |  Owner   |        Location        | Access privileges | Options |  Size   | Description
---------------+----------+------------------------+-------------------+---------+---------+-------------
 pg_default    | postgres |                        |                   |         | 22 MB   |
 pg_global     | postgres |                        |                   |         | 574 kB  |
 tblspace_slow | postgres | /mnt/4TB/tblspace_slow |                   |         | 0 bytes |
(3 rows)

И наконец мы переходим к собственно обновлению данных при помощи утилиты pg_upgrade и первым делом мы останавливаем базы данных:

# su postgres
$ /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main/ stop
$ /usr/lib/postgresql/11/bin/pg_ctl -D /var/lib/postgresql/11/main/ stop

Сразу обращаю ваше внимание, что в случае работы с СУБД Postgresql установленной из репозитория файлы postgresql.conf pg_hba.conf и прочие находятся в каталоге /etc/postgresql соответственно в подкаталогах 10 и 11.

При запуске миграции вы получите ошибку:

could not connect to source postmaster started with the command:
"/usr/lib/postgresql/10/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/10/main/" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/tmp'" start

Но на самом деле ошибка несколько глубже и в логе миграции вы найдете сообщение:

postgres: could not access the server configuration file 
"/var/lib/postgresql/10/main/postgresql.conf": No such file or directory

Следовательно как вы догадались необходимо сделать симлинки недостающих файлов из /etc/postgresql/ в каталоги данных СУБД:

$ ln -s /etc/postgresql/10/main/postgresql.conf  /var/lib/postgresql/10/main/postgresql.conf
$ ln -s /etc/postgresql/10/main/conf.d  /var/lib/postgresql/10/main/conf.d
...

И т.п. все каталоги конфигурации. После того как вы создали необходимые симлинки можно производить миграцию (синтаксис этой команды можно найти в документации).

$ /usr/lib/postgresql/11/bin/pg_upgrade --old-datadir "/var/lib/postgresql/10/main/" --new-datadir "/var/lib/postgresql/11/main/"
--old-bindir "/usr/lib/postgresql/10/bin/" --new-bindir "/usr/lib/postgresql/11/bin/"

По завершении миграции запускаем новый сервер:

# systemctl start postgresql@11-main.service
# ln -s /var/run/postgresql/.s.PGSQL.5433 /var/run/postgresql/.s.PGSQL.5432

Проверяем, что база на месте и сверяем размеры (станет даже больше), после того как вы проверили, что все работает и прибираемся:

Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh