Migrating from a containerized database to an external PostgreSQL database

By default, PostgreSQL database is installed with the product. However, BMC recommends that you do not use the containerized database for your production environments. This topic provides the steps to migrate from a containerized database to an external PostgreSQL database, installed on a different host. 

Use this topic for the following scenarios: 

• If you are migrating from a containerized database to an external PostgreSQL database.
• If you upgrade your external PostgreSQL database to a later version.

Before you begin

Complete the following tasks:

• Install a compatible PostgreSQL database version.
  For details, see System-requirements.

• Verify whether the PostgresSQL contrib modules are also installed. These are optional utilities shipped with the standard package, which may not installed by default.

  To verify whether the contrib modules are installed, run the following commands based on your version:

  For PostgreSQL 12

  yum list installed | grep postgresql12-contrib

  For PostgreSQL 11.2

  yum list installed | grep postgresql11-contrib

Migrating to the external PostgreSQL database

You first back up the database and then connect to the external one. 

1. Perform the following steps to back up the internal database:

   1. Open a terminal and SSH into the host where the database container is available by running the following command. 

      docker exec -it database-infra-ext-postgres sh

   2. Back up the internal database by running the following commands: 

      su postgres

      pg_dumpall  > /var/lib/postgresql/data/dump.sql
   3. Copy dump.sql to the host where the external PostgreSQL database is installed. 

2. On the host where the external PostgreSQL database is installed, run the following commands to create relevant folders where the database needs to be restored: 

   mkdir -p /var/lib/postgresql/data/tsac_tablespace/itil
   mkdir -p /var/lib/postgresql/data/tsac_tablespace/<default tablespace>dat (The dump.sql file contains the default tablespace name.)
   mkdir -p /var/lib/postgresql/data/tsac_tablespace/<default tablespace>_idx (The dump.sql file contains the default tablespace name.)
   mkdir -p /var/lib/postgresql/data/tsac_tablespace/truesight_assetstate
   mkdir -p /var/lib/postgresql/data/tsac_tablespace/tsac_database_catalog_db
   mkdir -p /var/lib/postgresql/data/tsac_tablespace/exceptions_service_db
   mkdir -p /var/lib/postgresql/data/tsac_tablespace/tsac_database_operation_db
   mkdir -p /var/lib/postgresql/data/tsac_tablespace/tsac_database_resource_db
   mkdir -p /var/lib/postgresql/data/tsac_tablespace/tsac_database_user_dat
   mkdir -p /var/lib/postgresql/data/tsac_tablespace/tsac_database_user_idx
   chown -R postgres:postgres /var/lib/postgresql/

3. Restore the database.

   su postgres
   
   psql -f <directory containing dump.sql>/dump.sql postgres

   After successfully restoring the database, the database links still point to the internal containerized DB. You must delete the links and create a new dblink. 

4. To create new dblinks, run the following commands:

   SELECT dblink_connect('host=<database hostname> user=<username> password=<password> dbname=<resource service db> port=<resource service db port>');               
   DROP FOREIGN DATA WRAPPER IF EXISTS FDW CASCADE;    
   CREATE SERVER IF NOT EXISTS resdb FOREIGN DATA WRAPPER FDW OPTIONS (host '<database hostname>', dbname '<resource service db>', port '<resource service db port>');                              
   CREATE USER MAPPING FOR postgres SERVER resdb OPTIONS (user '<username>', password '<password>');
   SELECT dblink_connect('resdb');
   GRANT USAGE ON FOREIGN SERVER resdb TO postgres;

   Here, specify the user as postgres.

5. Now, run these commands to connect the application to the external database:

   /opt/bmc/stackmanager inputs-file edit

   Enter 1 and the provide the host name, port, username, and password for the external database. 

6. Restart the application by running the following command: 

   /opt/bmc/stackmanager restart --deployment=application
   

Where to go next?

Now that you have installed the product successfully, administrators can start setting up the application for your patch and vulnerability management needs. You can start using the features based on your role and requirements.