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 comes with one service:

  • postgresql-9.5

PostgreSQL 9.5 Service

The postgresql-9.5 service comes with two plans:

  • small-15
  • large-30

The small-15 plan includes the following:

  • PostgreSQL 9.5
  • Automated Backups
  • Load Balanced Read Replicas
  • Maximum Connections: 15

The large-30 plan includes the following:

  • PostgreSQL 9.5
  • Automated Backups
  • Load Balanced Read Replicas
  • Maximum Connections: 30

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 will install PostGIS 2.2 extensions.
  • db_encoding will change the default decoding of the database. This is not recommended.

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

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

    cf create-service postgresql-9.5 small-15 my_pgsql -c '
    {
        "db_name": "crunchydb",
        "db_username": "crunchy",
        "owner_name": "Crunchy Data",
        "owner_email": "admin@crunchydata.com"
    }'
    
  2. Bind the service instance to your application:

    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 will take incremental backups, while Sunday will create 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 will be 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 postgresql-master server. For guidance on SSHing to servers in the service, see the Pivotal documentation.

  1. First, ssh into the postgresql-master server using the bosh tool:

    bosh ssh postgresql-master
    

  2. Switch to the root user:

    sudo -i 
    

  3. Stop postgresql services:

    monit stop postgresql
    

  4. Switch to the vcap user:

    su - vcap
    

  5. Verify that backups are available:

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

  6. Restore the database:

    pgbackrest --config=/var/vcap/store/pgbackrest/config/pgbackrest.conf \
      --stanza=main \
      --delta restore
    

  7. Switch to root:

    sudo -i
    

  8. Start the database:

    monit start postgresql
    

Restore Point In Time

These instructions will 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 SSHing to servers in the service, see the Pivotal documentation.

  1. First, ssh into the postgresql-master server using the bosh tool:

    bosh ssh postgresql-master
    

  2. Switch to the root user:

    sudo -i
    

  3. Stop postgresql services:

    monit stop postgresql
    

  4. Switch to the vcap user:

    su - vcap
    

  5. Verify that backups are available:

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

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

  7. Switch to root:

    sudo -i
    

  8. Start the database:

    monit start postgresql
    

pgBackrest Additional Information

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

Was this helpful?
What can we do to improve?
Create a pull request or raise an issue on the source for this page in GitHub