"invalid locale name" error received when attempting to restore the Cloud Director database dump from the external PostgreSQL server to embedded database

Products

• VMware Cloud Director

Issue/Introduction

Symptoms:

• When restoring a Cloud Director database, you receive an error could not execute query: ERROR: invalid locale name
• "invalid locale name" error received when attempting to restore the Cloud Director database dump from the external PostgreSQL server to embedded database
• Using pg_restore to restore a Cloud Director database results in an error invalid locale name

Environment

VMware Cloud Director 10.x

Cause

This issue occurs when the server encoding of the database is not set to en_US.UTF-8.
This is a requirement for Cloud Director databases as specified in the Cloud Director Database documentation.

Resolution

To resolve this issue, you will need to modify the server encoding to match the Cloud Director requirement of  en_US.UTF-8.
This will require a new Cloud Director database to be created on the External PostgreSQL server.

To achieve this, follow the steps below:

1. Take a backup of the Cloud Director database
   1. pg_dump vcloud > /tmp/vcloud.sql
2. Create a new Database using the expected server encoding.
   1. CREATE DATABASE vcddb ENCODING 'UTF-8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';
3. Restore the backup data to the new database.
   1. sudo -u postgres /opt/vmware/vpostgres/current/bin/pg_restore -d vcddb  -f /tmp/vcloud.sql
4. Rename the databases so that the newly restored database is called 'vcloud'
   1. ALTER DATABASE vcloud RENAME TO vcloud_old;
   2. ALTER DATABASE vcddb RENAME TO vcloud;

Once complete, the backup and restore process should no longer fail due to server encoding issues.