Change the Multi-Cloud Object Gateway Database's Collation Locale to C

Issue

Under certain circumstances, listing the objects in a Multi-Cloud Object Gateway bucket results in an endless loop that continuously prints a specific entry, e.g.:

$ aws s3 ls s3://mcg.bucket
> PRE prefix/
    PRE prefix/
    PRE prefix/
    PRE prefix/
...

Or when using S3-compatible tools or cURL:

<CommonPrefixes>
  <Prefix>prefix/</Prefix>
  <Prefix>prefix/</Prefix>
  <Prefix>prefix/</Prefix>
...

Resolution

The issue is caused by the Multi-Cloud Object Gateway database being set to use the wrong type of collation locale (often UTF-8).

⚠️ Please note that all Multi-Cloud Object Gateway services need to be taken down and will be inactive until the process is over. Testing these instructions in a staging environment is advised.

Please note that all subsequent oc commands should be run in the namespace in which MCG resides; the default namespace when using the OpenShift Data Foundation is openshift-storage.

In order to check what is the collation locale that the database currently uses:

1. Start a shell session in the noobaa-db-pg-0 pod (oc rsh noobaa-db-pg-0)
2. Access the Postgres command-line management interface by running psql
3. Type \l+ (note that this is a lower case L) and press Return

You should now see a list of databases - the relevant one is nbcore. If its Collate value is not C, the product might malfunction.

The issue can be fixed by changing the database's LC_COLLATE property to C - but since it is not possible to change this after the creation of a database, the existing database needs to be migrated into a new one with the correct LC_COLLATE setting.

In order to do so, follow the next steps:

1. Stop pods that may be utilizing the database

• Note down the number of currently deployed MCG endpoints. This number can be retrieved by running the following command:

oc get deployment <deployment_name> -o=jsonpath='{.spec.replicas}'

• Stop all operator, core and endpoint pods by running the following commands:

oc patch deployment noobaa-operator -p '{"spec":{"replicas":0}}'
oc patch deployment noobaa-endpoint -p '{"spec":{"replicas":0}}'
oc patch statefulset noobaa-core -p '{"spec":{"replicas":0}}'

2. Back up the database with pg_dump

Start a shell session in the noobaa-db-pg-0 pod, and then use pg_dump to store the DB in a file. Note that the time of this operation depends on the size of the database that is backed up.

oc rsh -n openshift-storage noobaa-db-pg-<pod-name>
pg_dump nbcore > /var/lib/pgsql/data/mcg_db_backup.sql

NOTE: Because the database is so important to NooBaa, after the above command has been run to produce a database dump, validate that it was successful:

tail -n100 /var/lib/pgsql/data/mcg_db_backup.sql
-- PostgreSQL database dump complete <--------------- SHOULD SEEE THIS
--
-- PostgreSQL database cluster dump complete

$ exit

Once validated, and outside of the pod, copy the db outside of the pod and onto a local system for safekeeping. To do this, in a separate CLI terminal window, run the following:

$ oc cp -n openshift-storage noobaa-db-pg-0:/var/lib/pgsql/data/mcg_db_backup.sql /<directory-name>/mcg_db_backup.sql

3. Create a new database with the correct collation locale

With the shell from the previous step, access the Postgres command-line management interface by running psql.
Once the interface loads, run the following command to create a new database with the correct collation locale -

CREATE DATABASE new_mcg_db WITH LC_COLLATE='C' TEMPLATE template0;

4. Restore the backup of the old database into the new one

Quit psql by typing \q and pressing Return, and run the following command. Note that the time of this operation depends on the size of the database that is backed up.

psql new_mcg_db < /var/lib/pgsql/data/mcg_db_backup.sql

5. Rename the databases

Return into psql, and connect to the "postgres" database by running the following command

psql postgres

Then, terminate all active connections to the old and new databases by running the following commands as they are:

SELECT pg_terminate_backend (pid) FROM pg_stat_activity WHERE datname = 'nbcore';

SELECT pg_terminate_backend (pid) FROM pg_stat_activity WHERE datname = 'new_mcg_db';

Once all connections have been terminated, rename the old database

ALTER DATABASE nbcore RENAME TO nbcore_old;

And then rename the new database so that the system will now access it instead of the old one

ALTER DATABASE new_mcg_db RENAME TO nbcore;

And then change the database owner to noobaa

ALTER DATABASE nbcore OWNER TO noobaa;

You can now exit the pod shell.

6. Restart the Multi-Cloud Object Gateway system

You can restart all the pods that were stopped in the first step by running the following commands. If you had more than one endpoint pod, please change "1" to the appropriate value that was noted in the first step.

oc patch deployment noobaa-operator -p '{"spec":{"replicas":1}}'
oc patch statefulset noobaa-core -p '{"spec":{"replicas":1}}'
oc patch deployment noobaa-endpoint -p '{"spec":{"replicas":1}}'

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.