Chapter 3. Migrating from an embedded PostgreSQL 10 database to an external PostgreSQL 10 database Important Before scaling system.appSpec.replicas to 1 , the database should be upgraded to the supported version, which is currently PostgreSQL 13. See Red Hat 3scale API Management Supported Configurations This documentation is about migrating from an embedded PostgreSQL 10 database to an external PostgreSQL 10 database. To upgrade from an external PostgreSQL 10 database to an external PostgreSQL 13 database, you must following the official PostgreSQL documentation . Disclaimer: Links contained herein to external website(s) are provided for convenience only. Red Hat has not reviewed the links and is not responsible for the content or its availability. The inclusion of any link to an external website does not imply endorsement by Red Hat of the website or their entities, products or services. You agree that Red Hat is not responsible or liable for any loss or expenses that may result due to your use of (or reliance on) the external site or content. The process to move from an embedded PostgreSQL database to and external PostgreSQL database should happen with the same DB version. In this migration guide, it should be PostgreSQL 10. You should use external databases for production environments. If you are using PostgreSQL as your system-database , use the supported version for external database installation with 3scale. Important These steps are general guidelines. Exact steps may vary depending on your operating system, version of PostgreSQL, and specific requirements of your database. Read the PostgreSQL documentation and release notes carefully before upgrading. Test this procedure in a non-production environment before applying it to a production deployment. This process disrupts the provision of the service until the procedure finishes. Due to this disruption, be sure to have a maintenance window. Procedure Use APIManager customer resource (CR) to scale down the system-app DeploymentConfig (DC): apiVersion: apps.3scale.net/v1alpha1 kind: APIManager metadata: name: <apimanager_sample> spec: system: appSpec: replicas: 0 wildcardDomain: <example.com> Verify that the pods are scaled down: USD oc get deploymentconfig system-app -o jsonpath='{.status.availableReplicas}{"\n"}' 0 Wait for all the 3scale pods to have the status of Terminated before proceeding with the PostgreSQL migration. Make a backup of the existing PostgreSQL database, including all data, configurations, and user accounts: USD DB_USER=USD(oc get secret system-database -o jsonpath="{.data.DB_USER}" | base64 --decode) USD DATABASE_NAME=USD(oc get secret system-database -o jsonpath="{.data.URL}" | base64 --decode | cut -d '/' -f4) Important Do not pipe to stdout . Binary files get corrupted. Dump with custom format: USD oc rsh USD(oc get pods -l 'deploymentConfig=system-postgresql' -o json | jq -r '.items[0].metadata.name') bash -c "pg_dump -U USDDB_USER -F c USDDATABASE_NAME -f /tmp/<backupfilename>.backup" Download the backup: USD oc cp USD(oc get pods -l 'deploymentConfig=system-postgresql' -o json | jq -r '.items[0].metadata.name'):/tmp/<backupfilename>.backup <backupfilename>.backup Install the same version of PostgreSQL 10 that you deployed on 3scale in your target external system. Download the installation package from the PostgreSQL website following the installation instructions. Copy and restore the backup you made of the existing PostgreSQL database, including all data, configurations, and user accounts to the target external system. Create a new database in PostgreSQL: USD createdb -U <username> <databasename> Import the data from the backup file into the new PostgreSQL database. Restore with custom format: USD pg_restore [--host <databasehostname>] -U <username> -d <databasename> --verbose -F c <backupfilename>.backup Verify that the data was successfully imported into the new PostgreSQL database by connecting to the database and running queries: postgresql://<username>:<password>@<databasehostname>/<databasename> Update system-database secret: USD oc apply -f - <<EOF --- apiVersion: v1 kind: Secret metadata: name: system-database stringData: DB_PASSWORD: <password> DB_USER: <username> URL: "postgresql://<username>:<password>@<databasehostname>:<databaseport>/<databasename>" type: Opaque EOF Update APImanager CR to enable external database and scale up system: USD oc patch apimanager <apimanager_sample> --type=merge --patch '{"spec": {"system": {"database": null, "appSpec": {"replicas": 1}}, "externalComponents": {"system": {"database": true}}}}' Remove local postgresql deployment: USD oc delete service system-postgresql USD oc delete deploymentconfig system-postgresql USD oc delete pvc postgresql-data Verify that the pods are scaled up: USD oc wait --for=condition=available apimanager/<apimanager_sample> --timeout=-1s Additional resources PostgreSQL documentation PostgreSQL Downloads

Commands:
apiVersion: apps.3scale.net/v1alpha1 kind: APIManager metadata: name: <apimanager_sample> spec: system: appSpec: replicas: 0 wildcardDomain: <example.com>
oc get deploymentconfig system-app -o jsonpath='{.status.availableReplicas}{"\n"}' 0
DB_USER=USD(oc get secret system-database -o jsonpath="{.data.DB_USER}" | base64 --decode) DATABASE_NAME=USD(oc get secret system-database -o jsonpath="{.data.URL}" | base64 --decode | cut -d '/' -f4)
oc rsh USD(oc get pods -l 'deploymentConfig=system-postgresql' -o json | jq -r '.items[0].metadata.name') bash -c "pg_dump -U USDDB_USER -F c USDDATABASE_NAME -f /tmp/<backupfilename>.backup"
oc cp USD(oc get pods -l 'deploymentConfig=system-postgresql' -o json | jq -r '.items[0].metadata.name'):/tmp/<backupfilename>.backup <backupfilename>.backup
createdb -U <username> <databasename>
pg_restore [--host <databasehostname>] -U <username> -d <databasename> --verbose -F c <backupfilename>.backup
postgresql://<username>:<password>@<databasehostname>/<databasename>
oc apply -f - <<EOF --- apiVersion: v1 kind: Secret metadata: name: system-database stringData: DB_PASSWORD: <password> DB_USER: <username> URL: "postgresql://<username>:<password>@<databasehostname>:<databaseport>/<databasename>" type: Opaque EOF
oc patch apimanager <apimanager_sample> --type=merge --patch '{"spec": {"system": {"database": null, "appSpec": {"replicas": 1}}, "externalComponents": {"system": {"database": true}}}}'
oc delete service system-postgresql oc delete deploymentconfig system-postgresql oc delete pvc postgresql-data
oc wait --for=condition=available apimanager/<apimanager_sample> --timeout=-1s