Migrating a Database Cluster to QHB

Migration to QHB is currently supported only starting from PostgreSQL version 12. If you are using an earlier version of PostgreSQL, you should first upgrade it to version 12. Migration from older versions of QHB is also supported. This guide only covers the main migration points that you need to pay attention to.

This example considers the migration from PostgreSQL version 12 to current QHB version. The migration procedure is described in more detail on the help page of the qhb_upgrade utility.

Preparing for Migration

Example of Source Data

PostgreSQL data
Directory containing executable files: /usr/pgsql-12/bin
Directory containing data: /var/lib/pgsql/12/data/
Superuser: postgres
Port: 5432
Service: postgresql-12

QHB data
Directory containing executable files: /usr/local/qhb/bin
Directory containing data: /opt/qhb/data
Superuser: qhb
Port: 5432
Service: qhb

If you want the postgresql-12 and qhb services to run simultaneously (e.g. for testing the QHB functionality), you need to change the port number for QHB, i.e. change the value of the port parameter in the qhb.conf configuration file, for example, to 5433. This is not necessary for updating via qhb_upgrade, since the services are started in turn.

WARNING!
If, while installing QHB on a machine where PostgreSQL is already located, the system displays a notification about the need to rollback the versions of some packages that were installed by PostgreSQL (for example, llvm or llvm-libs), it is recommended to cancel the installation and then install the llvm-compat-libs-12.0.1 package separately. Only after successful package installation you can repeat QHB installation (in this case, the installation will no longer require a version rollback, since 2 versions of llvm will be installed at once).

Language Settings

You should check the parameter values with the language settings (locale, lc-collate and lc-ctype are the locale, string sort order and character classification, respectively) of the new QHB database cluster in advance. Normally, these parameters have the same value and should match the settings of the original PostgreSQL database cluster. You can find out the values of these parameters in PostgreSQL using the \l command in psql, listing databases with their parameters. In this example, the parameter values are the same and are en_US.UTF-8.

Renaming the Superuser postgres to qhb in a PostgreSQL Database Cluster

qhb_upgrade must run under the same user with both PostgreSQL and QHB. It is recommended to use qhb as such a user. Since the superuser qhb is not present in the PostgreSQL database by default, you should rename the user postgres to qhb. To do this, log in to PostgreSQL as a different superuser. If such a user does not exist, you can create it, and after renaming the user postgres — delete it.

Let's log in as the superuser postgres:

/usr/pgsql-12/bin/psql -d postgres -U postgres

Let's create a new superuser temp_su, log in as it, rename the user postgres to qhb, connect as qhb and delete the no longer needed user temp_su:

CREATE ROLE temp_su LOGIN SUPERUSER;
\c postgres temp_su
ALTER USER postgres RENAME TO qhb;
\c postgres qhb
DROP ROLE temp_su;

Creating a Test Database and a Table in It

In order to visually verify that the data transfer was successful, especially during a test run, you can create a separate database in PostgreSQL and a table in it, filling it with at least one row.

CREATE DATABASE test;
\c test
CREATE TABLE test (id int, txt text);
INSERT INTO test VALUES(1, "test1");

Stopping Services and Changing the Owner of the QHB Directory

To stop services postgres-12 and qhb (if service qhb was running):

sudo systemctl stop postgresql-12
sudo systemctl stop qhb

In order for qhb_upgrade to work normally under the system user qhb, we will change the owner for the data directory PostgreSQL to qhb. For top-level directories, it is necessary to temporary grant permissions allowing other users to enter them and read data.

sudo chmod o+rx /var/lib/pgsql
sudo chmod o+rx /var/lib/pgsql/12
sudo chown -R qhb:qhb /var/lib/pgsql/12/data

Initializing a QHB Database Cluster

Then, it is recommended to switch to the system user qhb and execute the following commands as it:

sudo su - qhb

Clearing the QHB Data Directory

It is assumed that the /opt/qhb/data directory is the root directory of the QHB data that you selected when installing and configuring the database. If the directory is not empty at this point, you must clear it completely. Otherwise, the data migration will not start.

rm -rf /opt/qhb/data/*

Initializing a New Cluster

By default, a superuser is created in the database cluster with the name of the operating system user who ran the installation, i.e. qhb.

/usr/local/qhb/bin/qhb_bootstrap \
--locale=en_US.UTF-8 \
--lc-collate=en_US.UTF-8 \
--lc-ctype=en_US.UTF-8 \
--encoding=UTF8 \
--data-dir /opt/qhb/data

Verifying the Possibility of Database Migration from PostgreSQL to QHB

It is better to run qhb_upgrade from a separate temporary directory, for example /tmp/qhb_upgrade. The logs of the utility's work will be saved in the directory, in particular, the qhb_upgrade_internal.log file, in which the results of the migration capability check or error messages during the migration are recorded. It is advisable to prohibit access to this directory to other users.

mkdir /tmp/qhb_upgrade
chmod 700 /tmp/qhb_upgrade
cd /tmp/qhb_upgrade

WARNING!
The qhb_upgrade version when migrating from PostgreSQL to QHB must be at least 1.3.1. The check is performed by executing /usr/local/qhb/bin/qhb_upgrade --version.

The following command checks the migration capability using the --check option.

/usr/local/qhb/bin/qhb_upgrade \
--new-bindir=/usr/local/qhb/bin \
--new-datadir=/opt/qhb/data \
--old-bindir=/usr/pgsql-12/bin \
--old-datadir=/var/lib/pgsql/12/data \
-U qhb -v --check

The results of the check will be displayed on the screen, as well as in the file qhb_upgrade_internal.log. If the check is successful, the message "Clusters are compatible" will be displayed on the screen as the penultimate string.

Performing a Migration

To perform migration, you need to remove the --check option in the previous command.

/usr/local/qhb/bin/qhb_upgrade \
--new-bindir=/usr/local/qhb/bin \
--new-datadir=/opt/qhb/data \
--old-bindir=/usr/pgsql-12/bin \
--old-datadir=/var/lib/pgsql/12/data \
-U qhb -v

If the migration is successful, the penultimate string on the screen will display "Upgrade Complete". The results of the utility will also be recorded in the qhb_upgrade_internal.log log file.

Reverting the Directory to its Original Permissions

In general, the /var/lib/pgsql directory may contain several database clusters. Let's restore the original access rights for the top-level directories and return the previous owner for the PostgreSQL data directory /var/lib/pgsql/12/data. This may be necessary if you cancel the migration and return to using the PostgreSQL databases.

sudo chmod o-rx /var/lib/pgsql
sudo chmod o-rx /var/lib/pgsql/12
sudo chown -R postgres:postgres /var/lib/pgsql/12/data

Checking Data Transfer

All settings in the corresponding configuration files (qhb.conf, qhb_hba.conf and qhb_ident.conf) must be edited beforehand.

To run QHB and check the transferred data:

sudo systemctl start qhb
/usr/local/qhb/bin/psql -U qhb -d test -c "select * from test"