Install and Set Up Self-Service Analytics 's Metadata Store

Provides an overview about the your software’s metadata store.

Before the Self-Service Analytics components can be installed in the target server, you must install and set up the metadata store that is used in the environment. This must occur regardless of the technique used to install the components (using the installation script or an alternative installation method).

Self-Service Analytics uses a packaged PostgreSQL database instance to store its metadata. Use the provided instance due to the specific configuration and version combination:

• Self-Service Analytics 26.3 and later: PostgreSQL 16

If you would like to use another PostgreSQL instance, contact Technical Support for further guidance.

New installations of Self-Service Analytics use PostgreSQL 16. If you are upgrading your environment to Self-Service Analytics, you can retain your existing PostgreSQL version.

Before you attempt to upgrade to Self-Service Analytics, be sure to back up your metadata store. See Back Up the Metadata Store.

If you are upgrading Self-Service Analytics, consider the following possible PostgreSQL migration issues:

New installations of Self-Service Analytics use PostgreSQL 16. If you are upgrading your environment to Self-Service Analytics, you can retain your existing PostgreSQL version.

• No check for available disk space is performed during the upgrade. The PostgreSQL automatic upgrade might fail if there is not enough available disk space to accommodate it. The Bootstrap installation procedure cannot predict the required amount of free disk space it will need.
• If your installation uses an external PostgreSQL instance for Self-Service Analytics’s metadata store, you must disable the automatic PostgreSQL upgrade in the Bootstrap procedure before you upgrade Self-Service Analytics.

If either of these migration issues is true for your installation, you can disable the automatic PostgreSQL upgrade using the Bootstrap ZOOMDATA_POSTGRES_DISABLE_UPGRADE option. This option must be exported before running the Bootstrap procedure (for example by running export ZOOMDATA_POSTGRES_DISABLE_UPGRADE=TRUE). After running the Bootstrap procedure with the PostgreSQL upgrade disabled, you must manually upgrade your PostgreSQL metadata store to the appropriate version before you can use Self-Service Analytics.

Configuring PostgreSQL as the metadata store involves defining authentication and connection parameters, restarting PostgreSQL, establishing login credentials for the user, and creating metadata space for the server and microservices. Complete the following steps to install and set up the PostgreSQL metadata store:

1. Set Up the PostgreSQL Metadata Store
2. Change Metadata Store Authentication to MD5 — not necessary in Ubuntu environments
3. Create the Metadata Store User & Stores
4. Configure the Metadata Store for SSL

In addition, insightsoftware highly recommends that you optimize the PostgreSQL database instance after every upgrade. See Optimize Self-Service Analytics’s Metadata Store Performance.

If you have performance problems with your PostgreSQL metadata store, examine the database settings related to automatic vacuuming, automatic analyzing, and the write-ahead log (WAL). See Optimize Self-Service Analytics’s Metadata Store Performance.

Optionally, if the target server on which will be installed does not have Internet access, read Obtain the Installation Package Without Internet Access.

Set Up the PostgreSQL Metadata Store

The instructions to set up PostgreSQL as Self-Service Analytics’s metadata store differ depending on the Linux operating system used by the target server. Select a topic below:

• PostgreSQL Setup for CentOS Environments
• PostgreSQL Setup for Ubuntu Environments

PostgreSQL Setup for CentOS Environments

New installations of Self-Service Analytics use PostgreSQL 16. If you are upgrading your environment to Self-Service Analytics, you can retain your existing PostgreSQL version.

1. Add the PostgreSQL Yum repository to CentOS by running this command calling the appropriate PostgreSQL version:

   sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
   

2. Install the PostgreSQL client and server packages by running these commands:

   sudo yum -y install epel-release yum-utils sudo yum-config-manager --enable pgdg12 sudo yum install postgresql12-server postgresql12
   

3. After installation, initialize the PostgreSQL database:

   sudo /usr/pgsql-12/bin/postgresql12-setup initdb
   

4. Start and enable the PostgreSQLmicroservice:

   sudo systemctl enable --now postgresql-12
   

5. Confirm that the service started without errors:

   sudo systemctl status postgresql-12
   

   If necessary, start it:

   sudo systemctl start postgresql-12
   

6. If you have a running firewall and remote clients should be able to connect to the PostgreSQL metadata store, modify the firewall to allow the PostgreSQL service:

   sudo firewall-cmd --add-service=postgresql --permanent sudo firewall-cmd --reload
   

7. If the PostgreSQL database is operating in a cluster, repeat steps 3-6 for each instance of the database.

8. Set up the PostgreSQL Admin user and password:

   sudo su - postgres ~]$ psql -c "alter user postgres with password 'StrongPassword'" ALTER ROLE
   

PostgreSQL Setup for Ubuntu Environments

New installations of Self-Service Analytics use PostgreSQL 16. If you are upgrading your environment to Self-Service Analytics, you can retain your existing PostgreSQL version.

1. If this is a new server instance, update your current system packages:

   sudo apt update sudo apt -y install vim bash-completion wget sudo apt -y upgrade
   

   A reboot is necessary after an upgrade.

   sudo reboot
   

2. Import the GPG key and add the appropriate PostgreSQL version repository to your Ubuntu machine. Run the following commands:

   wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee /etc/apt/sources.list.d/pgdg.list
   

   The added repository contains many different packages and third-party add-ons, including: postgresql-client, postgresql, libpq-dev, postgresql-server-dev, and pgadmin packages.

3. Update the package list and install the PostgreSQL server and client packages:

   sudo apt update sudo apt -y install postgresql-12 postgresql-client-12
   

   The PostgreSQL microservice is started and will start with every system reboot.

4. If you have a running firewall and remote clients should be able to connect to the PostgreSQL metadata store, modify the firewall to allow the PostgreSQL service port:

   sudo ufw allow 5432/tcp
   

5. Test the PostgreSQL connection.

   1. During installation, a user named postgres is created automatically with full superadmin access to your entire PostgreSQL instance. Before you switch to this account, your logged in system user should have sudo privileges:

      sudo su - postgres
      

   2. Replace the postgres password with a strong password:

      psql -c "alter user postgres with password 'StrongAdminP@ssw0rd'"
      

   3. Start PostgreSQL using this command.

      $ psql
      

   4. Get connection details as shown below.

      postgres=# \conninfo You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
      

   5. Create a test database called mytestdb to see if everything is working.

      postgres=# CREATE DATABASE mytestdb; CREATE DATABASE postgres=# CREATE USER mytestuser WITH ENCRYPTED PASSWORD 'MyStr0ngP@SS'; CREATE ROLE postgres=# GRANT ALL PRIVILEGES ON DATABASE mytestdb to mytestuser; GRANT
      

      You can list the created databases by running:

      postgres=# \l
      

   6. Connect to your test database.

      postgres-# \c mytestdb You are now connected to database "mytestdb" as user "postgres".
      

Change Metadata Store Authentication to MD5

If you installed Self-Service Analytics’s metadata store on a server running CentOS or RedHat, complete the configuration steps below. If the server is running Ubuntu, ignore these instructions.

New installations of Self-Service Analytics use PostgreSQL 16. If you are upgrading your environment to Self-Service Analytics, you can retain your existing PostgreSQL version.

Change authentication for your metadata store to MD5

1. Edit the pg_hba.conf file for the appropriate version of PosgreSQL.

   sudo vi /var/lib/pgsql/12/data/pg_hba.conf
   

2. Change METHOD to MD5.

   # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5
   

3. Restart PostgreSQL. In CentOS environments, run:

   sudo systemctl restart postgresql-12
   

Create the Metadata Store User & Stores

A Self-Service Analytics user must be established for the Postgres metadata store.

To create the Self-Service Analytics user for the Postgres metadata store, complete the following steps:

1. For all Linux operating systems, create the Self-Service Analytics user in PostgreSQL. Run the following command:

   sudo -u postgres -H psql -c "CREATE USER <db_username> WITH PASSWORD '<db_password>'"
   

   Substitute the PostgreSQL user name and password for <db_username> and <db_password>.

2. Create the stores that will hold the Self-Service Analytics metadata, upload data, keyset, and query engine data. Run the following series of commands, substituting the user name for <db_username>:

   sudo -u postgres -H psql -c "CREATE DATABASE\"zoomdata\" WITH OWNER <db_username>" sudo -u postgres -H psql -c "CREATE DATABASE\"zoomdata-upload\" WITH OWNER <db_username>" sudo -u postgres -H psql -c "CREATE DATABASE\"zoomdata-keyset\" WITH OWNER <db_username>" sudo -u postgres -H psql -c "CREATE DATABASE\"zoomdata-qe\" WITH OWNER <db_username>"
   

Configure the Metadata Store for SSL

If you have specified SSL connections for the metadata store JDBC connections in zoomdata.properties file, the root CA certificate that is used for the PostgreSQL database must be added to the /opt/zoomdata/.postgresql directory. This directory does not exist by default and will need to be created. Complete the following steps.

1. Change to the /opt/zoomdata directory as a superuser:

   sudo cd /opt/zoomdata
   

2. Create a .postgresql subdirectory.

   sudo mkdir .postgresql
   

3. Copy the root CA certificate for the PostgreSQL database into the new directory:

   sudo cp root.ca /opt/zoomdata/.postgresql/root.ca
   

Optimize Self-Service Analytics’s Metadata Store Performance

If you have performance problems with your PostgreSQL metadata store, examine the database settings related to automatic vacuuming, automatic analyzing, and the write-ahead log (WAL).

Automatic Vacuuming (VACUUM)

In PostgreSQL, whenever rows in a table are deleted, the existing row (or tuple) is marked as “dead,” but it is not physically removed. During an update, PostgreSQL marks the existing tuple as dead and inserts a new tuple. So a PostgreSQL UPDATE operation is a combination of a delete and an insert operation (DELETE + INSERT).

Dead tuples consume unnecessary storage and eventually, your PostgreSQL database is bloated with them. The VACUUM procedure reclaims the storage occupied by dead tuples. Bear in mind that the reclaimed storage space is never given back to the resident operating system. Instead it is just defragmented within the same database page, and the storage is available for reuse by future data inserts in the same table.

Bloating seriously affects PostgreSQL query performance. PostgreSQL tables and indexes are stored as an array of fixed-size pages (usually 8 KB in size). When a query request for rows is processed, the PostgreSQL instance loads these pages into the memory and the dead rows cause expensive disk I/O during data loading.

To check for dead tuples and the latest vacuum run, use the following query:

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup
/ (n_live_tup
current_setting('autovacuum_vacuum_scale_factor')::float8
+ current_setting('autovacuum_vacuum_threshold')::float8)
DESC;

To check your automatic vacuuming settings, use the following query:

select
from pg_settings
where
name like '%autovacuum%';

For more information about adjusting automatic VACUUM settings, see:

• https://www.postgresql.org/docs/current/runtime-config-autovacuum.html
• https://habr.com/en/company/postgrespro/blog/486104/
• https://dzone.com/articles/tuning-postgresql-autovacuum-to-prevent-table-bloa

For information about vacuuming your Self-Service Analytics PostgreSQL metadata store, see Automatic Vacuuming (VACUUM).

Automatic Analyzing (ANALYZE)

The PostgreSQL query planner relies on statistical information about the contents of tables to generate good plans for queries. These statistics are gathered using the ANALYZE command, which can be invoked by itself or as an optional step in VACUUM. It is important to have reasonably accurate statistics, or poor planning choices might degrade database performance. The PostgreSQL autovacuum daemon, if enabled, automatically issues ANALYZE commands whenever the content of a table has changed sufficiently. The daemon schedules ANALYZE strictly as a function of the number of rows inserted or updated; it has no knowledge of whether that will lead to meaningful statistical changes.

As with vacuuming for space recovery, frequent updates of statistics are more useful for heavily updated tables. But even for a heavily updated table, there might be no need for statistics updates if the statistical distribution of the data has not changing much. A simple rule of thumb is to consider how much the minimum and maximum values of the columns in the table change. For example, a time stamp column that contains the time of row update will have a constantly-increasing maximum value as rows are added and updated; such a column will probably need more frequent statistic updates than a column containing URLs for pages accessed on a website. The URL column might receive changes just as often, but the statistical distribution of its values probably changes relatively slowly.

For more information about the use of automatic ANALYZE processing, see:

• https://www.postgresql.org/docs/12/sql-analyze.html
• https://habr.com/en/company/postgrespro/blog/486104/

Write-Ahead Log (WAL)

PostgreSQL databases rely on a write-ahead log (WAL). All databases changes and transactions are written to the WAL first, and then to the data files. This provides durability, because if the database crashes, it can use the WAL to recover. It can read the changes from the WAL and reapply them to the data files. While this may double the number of writes, it may actually improve performance. Users only have to wait for the WAL (to be flushed to disk), while the data files are modified only in memory and then flushed later in the background. PostgreSQL uses checkpoints in its sequence of transactions to identify points at which the data files and the heap have been updated fully with all the data written before the checkpoint. Checkpoints are points in the transaction stream before which the WAL is no longer needed for recovery, reducing disk space requirements and recovery time.

For more information about the WAL, see:

• https://habr.com/en/company/postgrespro/blog/494464/
• https://www.postgresql.org/docs/current/runtime-config-wal.html
• https://postgreshelp.com/postgresql-checkpoint/
• https://www.postgresql.org/docs/9.5/wal-configuration.html

Vacuum Self-Service Analytics’s Metadata Store

insightsoftware highly recommends that you vacuum the Self-Service Analytics PostgreSQL metadata store after every upgrade. Vacuuming the metadata store optimizes it by maximizing database performance and minimizing the disk space it uses. The following simple procedure recollects metadata store statistics and “vacuums” the unused or dead rows in the database.

This procedure blocks writing to the database. Consequently, work with the database is impossible until the procedure completes.

This procedure only works for a PostgreSQL database.

If you have performance problems with your PostgreSQL metadata store, examine the database settings related to automatic vacuuming, automatic analyzing, and the write-ahead log (WAL). See Optimize Self-Service Analytics’s Metadata Store Performance.

Vacuum your PostgreSQL metadata store

1. Upgrade your version of Self-Service Analytics (if you have not already done so). This automatically upgrades the PostgreSQL metadata store.

2. Stop Self-Service Analytics and any process connecting to its PostgreSQL metadata store. See Stop Microservices.

3. Back up the PostgreSQL metadata store. See Back Up the Metadata Store.

4. Connect to the PostgreSQL database.

5. Run the following command in the console for the PostgreSQL database:

   VACUUM (FULL, ANALYZE);
   

   For more information about VACUUM, see https://www.postgresql.org/docs/12/sql-vacuum.html.

6. After vacuuming completes, start Self-Service Analytics. See Start Microservices.

Restore the Metadata From the Metadata Store Backup

This topic describes how to restore the metadata store from a backup copy. For information about backing up the metadata store, see Back Up the Metadata Store.

Prior to restoring the metadata, ensure that the target PostgreSQL data store is clean.

Restore the metadata store

1. From your terminal, SSH to your Self-Service Analytics server.

2. Stop all Self-Service Analytics microservices. For appropriate commands based on your OS, see Stop Microservices.

3. Navigate to your backup directory and enter the following commands:

   sudo -u postgres psql zoomdata < zoomdata
   sudo -u postgres psql zoomdata-upload < zoomdata-upload
   sudo -u postgres psql zoomdata-keyset < zoomdata-keyset
   sudo -u postgres psql zoomdata-qe < zoomdata-qe
   

4. Restart all Self-Service Analytics microservices. For appropriate commands based on your OS, see Restart Microservices.

For more information on backup and restore processes for PostgreSQL, refer to the PostgreSQL documentation.

Back Up the Metadata Store

Before upgrading your Self-Service Analytics server, insightsoftware recommends that you back up your PostgreSQL metadata store. The metadata includes refresh schedule data, object information for your environment (such as sources, dashboards, and visual definitions), and aggregated result sets. After the metadata store is backed up, perform the upgrade. If problems arise, you can restore the metadata store from your backup copy, if necessary.

This topic describes how to back up the metadata store. For information about restoring the metadata store, see Restore the Metadata From the Metadata Store Backup.

Back up the metadata store

1. From your terminal, SSH to your server.

2. Stop all microservices. For appropriate commands based on your operating system, see Stop Microservices.

3. Navigate to the /etc/zoomdata directory and create a backup folder:

   mkdir backups
   

4. Navigate to the backups directory.

5. Perform an SQL dump of the databases by entering the following commands:

   sudo -u postgres pg_dump zoomdata > zoomdata
   sudo -u postgres pg_dump zoomdata-upload > zoomdata-upload
   sudo -u postgres pg_dump zoomdata-keyset > zoomdata-keyset
   sudo -u postgres pg_dump zoomdata-qe > zoomdata-qe
   

6. Restart all microservices. For appropriate commands based on your OS, see Start Microservices.

For more information on backup and restore processes for PostgreSQL, refer to the PostgreSQL documentation.