Chapter 4. Deploying Apicurio Registry storage in a PostgreSQL database This chapter explains how to install, configure, and manage Apicurio Registry data storage in a PostgreSQL database. Section 4.1, "Installing a PostgreSQL database from the OpenShift OperatorHub" Section 4.2, "Configuring Apicurio Registry with PostgreSQL database storage on OpenShift" Section 4.3, "Backing up Apicurio Registry PostgreSQL storage" Section 4.4, "Restoring Apicurio Registry PostgreSQL storage" Prerequisites Chapter 2, Installing Apicurio Registry on OpenShift 4.1. Installing a PostgreSQL database from the OpenShift OperatorHub If you do not already have a PostgreSQL database Operator installed, you can install a PostgreSQL Operator on your OpenShift cluster from the OperatorHub. The OperatorHub is available from the OpenShift Container Platform web console and provides an interface for cluster administrators to discover and install Operators. For more details, see Understanding OperatorHub . Prerequisites You must have cluster administrator access to an OpenShift cluster. Procedure In the OpenShift Container Platform web console, log in using an account with cluster administrator privileges. Change to the OpenShift project in which you want to install the PostgreSQL Operator. For example, from the Project drop-down, select my-project . In the left navigation menu, click Operators and then OperatorHub . In the Filter by keyword text box, enter PostgreSQL to find an Operator suitable for your environment, for example, Crunchy PostgreSQL for OpenShift . Read the information about the Operator, and click Install to display the Operator subscription page. Select your subscription settings, for example: Update Channel : stable Installation Mode : A specific namespace on the cluster and then my-project Approval Strategy : Select Automatic or Manual Click Install , and wait a few moments until the Operator is ready for use. Important You must read the documentation from your chosen PostgreSQL Operator for details on how to create and manage your database. Additional resources Adding Operators to an OpenShift cluster Crunchy PostgreSQL Operator QuickStart 4.2. Configuring Apicurio Registry with PostgreSQL database storage on OpenShift This section explains how to configure storage for Apicurio Registry on OpenShift using a PostgreSQL database Operator. You can install Apicurio Registry in an existing database or create a new database, depending on your environment. This section shows a simple example using the PostgreSQL Operator by Dev4Ddevs.com. Prerequisites You must have an OpenShift cluster with cluster administrator access. You must have already installed Apicurio Registry. See Chapter 2, Installing Apicurio Registry on OpenShift . You must have already installed a PostgreSQL Operator on OpenShift. For example, see Section 4.1, "Installing a PostgreSQL database from the OpenShift OperatorHub" . Procedure In the OpenShift Container Platform web console, log in using an account with cluster administrator privileges. Change to the OpenShift project in which Apicurio Registry and your PostgreSQL Operator are installed. For example, from the Project drop-down, select my-project . Create a PostgreSQL database for your Apicurio Registry storage. For example, click Installed Operators , PostgreSQL Operator by Dev4Ddevs.com , and then Create database . Click YAML and edit the database settings as follows: name : Change the value to registry image : Change the value to centos/postgresql-12-centos7 Edit any other database settings as needed depending on your environment, for example: apiVersion: postgresql.dev4devs.com/v1alpha1 kind: Database metadata: name: registry namespace: my-project spec: databaseCpu: 30m databaseCpuLimit: 60m databaseMemoryLimit: 512Mi databaseMemoryRequest: 128Mi databaseName: example databaseNameKeyEnvVar: POSTGRESQL_DATABASE databasePassword: postgres databasePasswordKeyEnvVar: POSTGRESQL_PASSWORD databaseStorageRequest: 1Gi databaseUser: postgres databaseUserKeyEnvVar: POSTGRESQL_USER image: centos/postgresql-12-centos7 size: 1 Click Create , and wait until the database is created. Click Installed Operators > Red Hat Integration - Service Registry > ApicurioRegistry > Create ApicurioRegistry . Paste in the following custom resource definition, and edit the values for the database url and credentials to match your environment: apiVersion: registry.apicur.io/v1 kind: ApicurioRegistry metadata: name: example-apicurioregistry-sql spec: configuration: persistence: 'sql' sql: dataSource: url: 'jdbc:postgresql://<service name>.<namespace>.svc:5432/<database name>' # e.g. url: 'jdbc:postgresql://acid-minimal-cluster.my-project.svc:5432/registry' userName: 'postgres' password: '<password>' # Optional Click Create and wait for the Apicurio Registry route to be created on OpenShift. Click Networking > Route to access the new route for the Apicurio Registry web console. For example: Additional resources Crunchy PostgreSQL Operator QuickStart Apicurio Registry Operator QuickStart 4.3. Backing up Apicurio Registry PostgreSQL storage When using storage in a PostgreSQL database, you must ensure that the data stored by Apicurio Registry is backed up regularly. SQL Dump is a simple procedure that works with any PostgreSQL installation. This uses the pg_dump utility to generate a file with SQL commands that you can use to recreate the database in the same state that it was in at the time of the dump. pg_dump is a regular PostgreSQL client application, which you can execute from any remote host that has access to the database. Like any other client, the operations that can perform are limited to the user permissions. Procedure Use the pg_dump command to redirect the output to a file: USD pg_dump dbname > dumpfile You can specify the database server that pg_dump connects to using the -h host and -p port options. You can reduce large dump files using a compression tool, such as gzip, for example: USD pg_dump dbname | gzip > filename.gz Additional resources For details on client authentication, see the PostgreSQL documentation . For details on importing and exporting registry content, see Managing Apicurio Registry content using the REST API . 4.4. Restoring Apicurio Registry PostgreSQL storage You can restore SQL Dump files created by pg_dump using the psql utility. Prerequisites You must have already backed up your PostgreSQL datbase using pg_dump . See Section 4.3, "Backing up Apicurio Registry PostgreSQL storage" . All users who own objects or have permissions on objects in the dumped database must already exist. Procedure Enter the following command to create the database: USD createdb -T template0 dbname Enter the following command to restore the SQL dump USD psql dbname < dumpfile Run ANALYZE on each database so the query optimizer has useful statistics.

Commands:
apiVersion: postgresql.dev4devs.com/v1alpha1 kind: Database metadata: name: registry namespace: my-project spec: databaseCpu: 30m databaseCpuLimit: 60m databaseMemoryLimit: 512Mi databaseMemoryRequest: 128Mi databaseName: example databaseNameKeyEnvVar: POSTGRESQL_DATABASE databasePassword: postgres databasePasswordKeyEnvVar: POSTGRESQL_PASSWORD databaseStorageRequest: 1Gi databaseUser: postgres databaseUserKeyEnvVar: POSTGRESQL_USER image: centos/postgresql-12-centos7 size: 1
apiVersion: registry.apicur.io/v1 kind: ApicurioRegistry metadata: name: example-apicurioregistry-sql spec: configuration: persistence: 'sql' sql: dataSource: url: 'jdbc:postgresql://<service name>.<namespace>.svc:5432/<database name>' # e.g. url: 'jdbc:postgresql://acid-minimal-cluster.my-project.svc:5432/registry' userName: 'postgres' password: '<password>' # Optional
http://example-apicurioregistry-sql.my-project.my-domain-name.com/
pg_dump dbname > dumpfile
pg_dump dbname | gzip > filename.gz
createdb -T template0 dbname
psql dbname < dumpfile