Chapter 3. Migrating from an embedded PostgreSQL 10 database to an external PostgreSQL 10 database

Procedure

1. 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>

   Copy to Clipboard Toggle word wrap

2. Verify that the pods are scaled down:

   $ oc get deploymentconfig system-app -o jsonpath='{.status.availableReplicas}{"\n"}'
   0

   Copy to Clipboard Toggle word wrap
   1. Wait for all the 3scale pods to have the status of Terminated before proceeding with the PostgreSQL migration.

3. Make a backup of the existing PostgreSQL database, including all data, configurations, and user accounts:

   $ DB_USER=$(oc get secret system-database -o jsonpath="{.data.DB_USER}" | base64 --decode)
   $ DATABASE_NAME=$(oc get secret system-database -o jsonpath="{.data.URL}" | base64 --decode | cut -d '/' -f4)

   Copy to Clipboard Toggle word wrap
   Important

   Do not pipe to stdout. Binary files get corrupted.

4. Dump with custom format:

   $ oc rsh $(oc get pods -l 'deploymentConfig=system-postgresql' -o json | jq -r '.items[0].metadata.name') bash -c "pg_dump -U $DB_USER -F c $DATABASE_NAME -f /tmp/<backupfilename>.backup"

   Copy to Clipboard Toggle word wrap

5. Download the backup:

   $ oc cp $(oc get pods -l 'deploymentConfig=system-postgresql' -o json | jq -r '.items[0].metadata.name'):/tmp/<backupfilename>.backup <backupfilename>.backup

   Copy to Clipboard Toggle word wrap
6. 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.
7. Copy and restore the backup you made of the existing PostgreSQL database, including all data, configurations, and user accounts to the target external system.

8. Create a new database in PostgreSQL:

   $ createdb -U <username> <databasename>

   Copy to Clipboard Toggle word wrap

9. Import the data from the backup file into the new PostgreSQL database.

   • Restore with custom format:

     $ pg_restore [--host <databasehostname>] -U <username> -d <databasename> --verbose -F c <backupfilename>.backup

     Copy to Clipboard Toggle word wrap

10. 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>

    Copy to Clipboard Toggle word wrap

11. Update system-database secret:

    $ 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

    Copy to Clipboard Toggle word wrap

12. Update APImanager CR to enable external database and scale up system:

    $ oc patch apimanager <apimanager_sample> --type=merge --patch '{"spec": {"system": {"database": null, "appSpec": {"replicas": 1}}, "externalComponents": {"system": {"database": true}}}}'

    Copy to Clipboard Toggle word wrap

13. Remove local postgresql deployment:

    $ oc delete service system-postgresql
    $ oc delete deploymentconfig system-postgresql
    $ oc delete pvc postgresql-data

    Copy to Clipboard Toggle word wrap

14. Verify that the pods are scaled up:

    $ oc wait --for=condition=available apimanager/<apimanager_sample> --timeout=-1s

    Copy to Clipboard Toggle word wrap