Using PostgreSQL with KUSANAGI 9

KUSANAGI 9 recently added support for PostgreSQL 14 and 15, making it compatible with PostgreSQL upgrades.

KUSANAGI 9 Supports PostgreSQL Upgrade
KUSANAGI 9 now supports PostgreSQL 15

WordPress is designed to run on MySQL (MariaDB), but other CMS, such as Drupal, can run on PostgreSQL.
As some of you may have learned from this news, KUSANAGI also supports the use of PostgreSQL.

The kusanagi dbinit psql / kusanagi upgrade psql commands introduced here allow you to switch between databases that you set up when you did kusanagi init or to increase the database version.

Let’s see what we can actually do with kusanagi dbinit psql / kusanagi upgrade psql We would like to go to ……, but first look here.

Back up the database.

kusanagi dbinit psql / kusanagi upgrade psql are both processes that change the entire database.
Of course, KUSANAGI has taken every precaution to ensure that there are no flaws, but we cannot say that it will never fail.

As a precautionary measure in case something goes wrong, be sure to back up your database before running kusanagi dbinit psql / kusanagi upgrade psql.

Now, we will describe two ways to actually make backups.

Take a snapshot

Cloud vendors provide the ability to take snapshots of disks associated with VMs.
Below are the steps for creating a snapshot with a typical cloud vendor.

AWS

Log in to AWS console
– > Open EC2 dashboard
– > Select volume from menu
– > Select target disk
– > Create snapshot from actions

Azure

GCP

Log in to GCP console
– > Select target project
– > Select disk from menu
– > Select target disk
– > Create snapshot from top menu

Thus, the cloud vendor provides the ability to take snapshots of disks, and this feature can be used to easily take snapshots.

If you want to recover, you can create a disk from the snapshot and attach the disk to the target VM to easily restore it to the state where the snapshot was taken.

Take a dump of the database

The database has a function called dump, which outputs the database data as a file.
Below are the instructions for dumping MariaDB and PostgreSQL.

MariaDB

MariaDB can dump the database using mysqldump just as MySQL does.

mysqldump -h localhost -u root -p (対象データベース名) > (出力するファイルパス)

When executing, you will be asked to enter the password of the root user (which corresponds to the administrator user) of the database.

Data from the database specified by the target database name is output to the path specified in the output file path.

To recover, use the following commands

mysql -u root -p (対象データベース名) < ( mysqldump で出力したファイルパス)

When executing, you will be asked to enter the password of the root user (which corresponds to the administrator user) of the database.

This will restore the data in the database specified by the target database name to the data at the time mysqldump was run.

PostgreSQL

PostgreSQL allows you to dump the database using pg_dump.

pg_dump -h localhost -U postgres -d (対象データベース名) > (出力するファイルパス)

When executing, you will be asked to enter the password of the database postgres user (which corresponds to the administrator user).

Data from the database specified by the target database name is output to the path specified in the output file path.

To recover, use the following commands

psql -h localhost -U postgres -d (対象データベース名) -f ( pg_dump で出力したファイルパス)

When executing, you will be asked to enter the password of the database postgres user (which corresponds to the administrator user).

Please refer to the above procedure and be sure to make backups when changing the DB environment.

So now let’s see what you can do with kusanagi dbinit psql / kusanagi upgrade psql.

Switching from MariaDB to PostgreSQL

If your current database setup is MariaDB and you want to switch it to PostgreSQL, use kusanagi dbinit psql.

Please note that switching databases using kusanagi dbinit psql does not involve data migration.
If you are already using MariaDB and want to change your database to PostgreSQL, please do the data migration yourself, as switching databases with kusanagi dbinit psql will not migrate your data.

This is also true for the kusanagi dbinit mariadb command.

As a practical example, let’s assume that you already have kusanagi init as your database for MariaDB.

# kusanagi status
KUSANAGI Version 9.4.2-1.el8
(クラウド名)

：
（中略）
：
*** (active) mariadb : mariadb10.5 ***
* mariadb.service - MariaDB 10.5.22 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2023-10-12 14:24:59 JST; 1min 3s ago

*** (inactive) psql :  ***


*** (inactive) pgpool-II :  ***

：
（中略）
：
status completed.
#

As you can see, MariaDB 10.5 is installed and PostgreSQL is not.
Now we will switch the database to PostgreSQL using kusanagi dbinit psql.

# kusanagi dbinit psql --use psql13 --dbrootpass (DB 管理者ユーザーパスワード)
Last metadata expiration check: 0:09:31 ago on Thu Oct 12 14:23:56 2023.
pgdg-redhat-repo-latest.noarch.rpm               12 kB/s |  13 kB     00:01
Dependencies resolved.
================================================================================
 Package                Architecture Version           Repository          Size
================================================================================
Installing:
 pgdg-redhat-repo       noarch       42.0-35PGDG       @commandline        13 k
：
：
dbinit psql completed.
dbinit completed.
#

dbinit psql completed. and dbinit completed. are displayed, the switchover was successful.

Again, run kusanagi status to check the environment.

# kusanagi status
KUSANAGI Version 9.4.2-1.el8
(クラウド名)

：
（中略）
：
*** (inactive) mariadb : mariadb10.5 ***
* mariadb.service - MariaDB 10.5.22 database server
   Loaded: loaded (/usr/lib/systemd/system/./mariadb.service; disabled; vendor preset: disabled)
   Active: inactive (dead)

*** (active) psql : psql13 ***
* postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2023-10-12 14:34:23 JST; 1min 27s ago

*** (active) pgpool-II : pgpool44 ***
* pgpool.service - Pgpool-II
   Loaded: loaded (/usr/lib/systemd/system/pgpool.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2023-10-12 14:34:40 JST; 1min 11s ago

：
（中略）
：
status completed.
#

MariaDB is now inactive and PostgreSQL is active.
This confirms that the switch has been made.

Let’s actually provision a Drupal environment.

# kusanagi provision --drupal --fqdn (FQDN) --noemail --dbname drupal_test --dbuser drupal_user --dbpass (DB ユーザーパスワード) drupal_test
Target directory is /home/kusanagi/drupal_test.
CREATE ROLE
provision postgresql completed.
provision db completed.
Last metadata expiration check: 0:18:48 ago on Thu Oct 12 14:40:28 2023.
：
（中略）
：
Provisioning of drupal_test completed. Access (FQDN) and install drupal.
provision completed.
#

provision completed. and Drupal has been provisioned successfully.
Try to access the specified FQDN.

As you can see, we have confirmed that Drupal works properly with PostgreSQL.

The following commands were used to switch this time.

kusanagi dbinit psql --use psql13 --dbrootpass (DB 管理者ユーザーパスワード)

This --use psql13 specifies the PostgreSQL version to switch to.
In this case, it is PostgreSQL 13.

Please refer to the KUSANAGI 9 manual listed at the bottom for supported versions.

Optimizing PostgreSQL Configuration

kusanagi dbinit psql In addition to switching the database to be used, you can also perform database optimization.
If the database used is PostgreSQL and kusanagi dbinit psql is executed without any options, optimization can be performed.

# kusanagi dbinit psql
dbinit psql completed.
dbinit completed.
#

The PostgreSQL configuration is now optimized.

Increase PostgreSQL version

Let’s take a look at how to increase the PostgreSQL version, which has recently been supported.

In kusanagi dbinit psql, which we used earlier to “switch from MariaDB to PostgreSQL”, we specified --use psql13 as an option.
As mentioned earlier, this is a PostgreSQL 13 installation.

Let’s version up this PostgreSQL 13 to PostgreSQL 14 using kusanagi upgrade psql.

# kusanagi upgrade psql --use psql14
Last metadata expiration check: 0:44:35 ago on Thu Oct 12 14:40:28 2023.
Dependencies resolved.
Nothing to do.
Complete!
：
（中略）
：
upgrade psql completed.
upgrade completed.
#

upgrade psql completed. and upgrade completed. are displayed, the upgrade has been successful.

kusanagi status Let’s take a look at the environment in

# kusanagi status
KUSANAGI Version 9.4.2-1.el8
(クラウド名)

：
（中略）
：
*** (inactive) mariadb : mariadb10.5 ***
* mariadb.service - MariaDB 10.5.22 database server
   Loaded: loaded (/usr/lib/systemd/system/./mariadb.service; disabled; vendor preset: disabled)
   Active: inactive (dead)

*** (active) psql : psql14 ***
* postgresql-14.service - PostgreSQL 14 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2023-10-12 15:25:52 JST; 2min 42s ago

*** (active) pgpool-II : pgpool44 ***
* pgpool.service - Pgpool-II
   Loaded: loaded (/usr/lib/systemd/system/pgpool.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2023-10-12 15:26:15 JST; 2min 19s ago

：
（中略）
：
status completed.
#

psql : psql13 to psql : psql14.

Thus, we were able to easily increase the PostgreSQL version.

Please note that kusanagi upgrade psql allows you to increase the version, but not decrease it.

Please refer to the KUSANAGI 9 manual listed at the bottom of this page for the supported versions.

Use PostgreSQL from the initial setup

If you have already decided to install PostgreSQL at the time of initializing KUSANAGI, you can specify PostgreSQL at kusanagi init.

kusanagi init --passwd (kusanagi ユーザーパスワード) --nophrase --dbrootpass (DB 管理者ユーザーパスワード) --psql13

Thus, the last option --psql13 will use PostgreSQL 13.

Please also see our previous Tech Column at kusanagi init.

Performing KUSANAGI initialization easily with kusanagi init

kusanagi dbinit psql / kusanagi upgrade psql This is all that can be done with the command.

KUSANAGI is available not only for MariaDB but also for PostgreSQL, so we encourage everyone to take advantage of it.

KUSANAGI 9 Command Manual (dbinit psql): https://kusanagi.tokyo/document/commands/dbinit-psql/
KUSANAGI 9 Command Manual (upgrade psql): https: // kusanagi.tokyo/document/commands/upgrade-psql/

Effortlessly Manage PHP Versions with “kusanagi php” >>