Using Crunchy PostgreSQL for PCF

This topic describes how to use Crunchy PostgreSQL for Pivotal Cloud Foundry (PCF).

Crunchy PostgreSQL Services and Plans

Crunchy PostgreSQL is an on-demand, single-tenant PostgreSQL database service.
Crunchy PostgreSQL comes with one service:

  • postgresql-9.5-odb

This service includes:

  • PostgreSQL cluster (primary and replicas)
  • Load balancer
  • pgBackrest dedicated backup
  • Consul cluster

PostgreSQL 9.5 Service

The postgresql-9.5 service comes with four plans:

  • Small
  • Medium
  • Large
  • Extra-Large

Each plan corresponds to different server size set up by the operator who deployed the service.

Review each plan offering and choose the right database size for the app.

Create PostgreSQL 9.5 Service

Creating a service requires four parameters used with the create-service request:

  • db_name is the database name being requested.
  • db_username is the username being requested.
  • owner_name is meta information about the owner of this database.
  • owner_email is the email address where the owner can be contacted.

Optional parameters:

  • postgis installs PostGIS 2.2 extensions.
  • db_encoding changes the default decoding of the database. Crunchy Data does not recommend this.
  • hba_addresses a space-separated list of IP addresses and/or networks in CIDR format that will be authorized to connect to the service database as the service user.

Follow the steps below to create and bind a service instance of Crunchy PostgreSQL to use with your app.

Note: With an on-demand service, new servers are built and configured when a new service is created. Therefore, this step takes some time to complete. You can check the status of your service from the space dashboard in the Services tab.

  1. Create a service instance using the following command as a template:

    cf create-service postgresql-9.5-odb small myService -c '
    {
        "db_name": "testdb",
        "db_username": "testrole",
        "owner_name": "Crunchy Data",
        "owner_email": "admin@crunchydata.com"
    }'
    
  2. Bind the service instance to your app:

    cf bind-service APP_NAME SERVICE_INSTANCE_NAME
    
  3. Run the following commands to restage your app so that it can use the service:

    cf bind-service APP_NAME SERVICE_NAME
    cf restage APP_NAME
    

Backups

Crunchy PostgreSQL uses pgBackrest for physical backups of the database.
Backups are taken every night at 1 am, 7 days a week. Monday through Saturday takes incremental backups, and Sunday creates a new full backup.

All archives from the database server are stored on the dedicated backup host. This means that databases can be restored to specific points in time.

Currently, individual databases cannot be restored. All databases are restored in the shared cluster.

Restore Deltas

These instructions demonstrate how the database can be restored using only deltas between the backup and the database.

The following requires an administrator to SSH to the primary postgresql server. For guidance on using SSH to connect to servers in the service, see Advanced Troubleshooting with the BOSH CLI.

  1. SSH into the consul server using the bosh tool:

    bosh ssh consul-server 0

  2. Identify the current primary by querying Consul:

    sudo su - vcap
    curl -sSL http://localhost:8500/v1/catalog/service/postgresql-zone1?tag=primary \
    | jq -r '.[] | .Node'
    

  3. SSH into the postgresql server (identified above) using the bosh tool:

    bosh ssh postgresql-zone1 
    

  4. Switch to the root user:

    sudo -i 
    

  5. Stop postgresql services:

    monit unmonitor postgresql
    monit stop postgresql
    

  6. Switch to the vcap user:

    su - vcap
    

  7. Verify that backups are available:

    pgbackrest --stanza=main info
    

  8. Restore the database:

    pgbackrest --stanza=main --delta restore
    

  9. Switch to root:

    sudo -i
    

  10. Start the database:

    monit start postgresql
    monit monitor postgresql
    

Restore Point in Time

These instructions demonstrate how the database can be restored using a point in time.

The following requires an administrator to SSH to the postgresql-master server. For guidance on using SSH to connect to servers in the service, see the Advanced Troubleshooting with the BOSH CLI.

  1. SSH into the consul server using the bosh tool:

    bosh ssh consul-server 0
    

  2. Identify the current primary by querying Consul:

    sudo su - vcap
    curl -sSL http://localhost:8500/v1/catalog/service/postgresql-zone1?tag=primary \
    | jq -r '.[] | .Node'
    

  3. SSH into the postgresql server (identified above) using the bosh tool:

    bosh ssh postgresql-zone1 
    

  4. Switch to the root user:

    sudo -i
    

  5. Stop postgresql services:

    monit stop postgresql
    

  6. Switch to the vcap user:

    su - vcap
    

  7. Verify that backups are available:

    pgbackrest --config=/var/vcap/store/pgbackrest/config/pgbackrest.conf --stanza=main info
    

  8. Restore the database:

    pgbackrest --config=/var/vcap/store/pgbackrest/config/pgbackrest.conf \
        --stanza=main \
        --delta \
        --type=time "--target=2016-12-13 00:11:34.531619+00" restore
    

  9. Switch to root:

    sudo -i
    

  10. Start the database:

    monit start postgresql
    

pgBackrest Documentation

For more information about pgBackrest restorations, see the official documentation.

Create a pull request or raise an issue on the source for this page in GitHub