Using Crunchy PostgreSQL for PCF

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

Crunchy PostgreSQL Service

Crunchy PostgreSQL for Pivotal Cloud Foundry (PCF) Tile 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 Plans

The postgresql-9.5-odb service comes with several plans:

  • small
  • medium
  • large
  • extra-large

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

Review each plan offering and choose the correct database size for the application.

Creating PostgreSQL Service

Service Creation Parameters

The following service configuration parameters are supported during service creation.

Required Parameters

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

  • db_name: Name of the service database. Value must begin with a lowercase alpha character and may contain only lowercase alpha, numeric, and underscore characters.
  • db_username: Name of the service database user. This user will be the owner of the service database and be granted all privileges on the service database. Value must begin with a lowercase alpha character and may contain only lowercase alpha, numeric, and underscore characters.
  • owner_name: Contact name for the service database.
  • owner_email: Contact email for the service database.
Optional Parameters
  • bloat_check_schedule: Cron-specification for performing database bloat checking. Default is @weekly. Only valid if monitoring is true
  • db_encoding: Database encoding for the service database. It is not recommended to change this value unless specifically required. Default is UTF8.
  • 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.
  • monitoring: Installs the monitoring extensions and Prometheus data store, true or false. Default is false.
  • plr: Enables PL/R extensions, true or false. Default is false.
  • postgis: Enable PostGIS 2.3 extensions, true or false. Extensions include postgis, postgis_tiger_geocoder, postgis_topology, and fuzzystrmatch. Default is false.
  • postgres_fdw: Enables the PostgreSQL Foreign Data Wrapper, true or false. Default is false.
  • redis_fdw: Enables the Redis Foreign Data Wrapper, true or false. Default is false.
Optional PostgreSQL Settings Parameters

The default configuration settings for PostgreSQL attempt to apply the most tuned settings for each plan and service instance.

However, if further configuration is found to be necessary, the following settings can be overridden manually:

  • pgc_autovacuum_analyze_scale_factor
  • pgc_autovacuum_analyze_threshold
  • pgc_autovacuum_freeze_max_age
  • pgc_autovacuum_max_workers
  • pgc_autovacuum_vacuum_cost_delay
  • pgc_autovacuum_vacuum_scale_factor
  • pgc_autovacuum_vacuum_threshold
  • pgc_checkpoint_completion_target
  • pgc_data_directory
  • pgc_effective_io_concurrency
  • pgc_external_pid_file
  • pgc_hba_file
  • pgc_hot_standby
  • pgc_ident_file
  • pgc_listen_addresses
  • pgc_log_checkpoints
  • pgc_log_connections
  • pgc_log_destination
  • pgc_log_directory
  • pgc_log_disconnections
  • pgc_log_duration
  • pgc_log_filename
  • pgc_log_hostname
  • pgc_log_line_prefix
  • pgc_log_min_duration_statement
  • pgc_log_min_error_statement
  • pgc_log_min_messages
  • pgc_log_rotation_age
  • pgc_log_rotation_size
  • pgc_log_timezone
  • pgc_logging_collector
  • pgc_maintenance_work_mem
  • pgc_max_locks_per_transaction
  • pgc_max_wal_senders
  • pgc_max_wal_size
  • pgc_max_worker_processes
  • pgc_random_page_cost
  • pgc_ssl
  • pgc_stats_temp_directory
  • pgc_timezone
  • pgc_unix_socket_directories
  • pgc_wal_buffers
  • pgc_wal_keep_segments
  • pgc_wal_level
  • pgc_work_mem

Creating a New Service

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 may check the status of your service from the space dashboard in the Services tab. Ensure that the status of the service is create succeeded before proceeding.

  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": "example", "owner_name": "Firstname Lastname", "owner_email": "email@example.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 restage <APP_NAME>

The service will then be available for use within any bound application.

Integrating Applications

The following sections provide information on integrating an application with a newly created service.

Service Bindings and Service Keys

Service bindings are used for Cloud Foundry applications and services. When an application is bound to a service, the service credentials are automatically added to the VCAP_SERVICES environment variable within the application container. Unique credentials are generated for each service binding and are removed when the binding is deleted.

WARNING: Applications must obtain the service binding credentials dynamically from the application container environment. Do not use service binding credentials outside of a Cloud Foundry application. Instead, use service keys for this use case.

Service keys are used for external applications that need to use Cloud Foundry services. Creating a service key returns the service credentials JSON object, which can be used by the external application as appropriate.

Service Credentials

The following keys are available in the service credentials JSON object:

  • db_host: IP address for database write commands
  • db_name: database name
  • db_port: TCP port for database write commands
  • jdbc_read_uri: JDBC connection URI for database read commands; jdbc:postgresql://db_host:db_port/db_name
  • jdbc_uri: JDBC connection URI for database write commands; jdbc:postgresql://read_host:read_port/db_name
  • password: password for database user
  • read_host: IP address for database read commands
  • read_port: TCP port for database read commands
  • read_uri: standard postgresql connection URI for database read commands; postgresql://username:password@db_host:db_port/db_name
  • uri: standard postgresql connection URI for database write commands; postgresql://username:password@read_host:read_port/read_name
  • username: database user

The following keys are available if monitoring parameter was set to true during provisioning:

  • prometheus_host: host of the Prometheus server for monitoring data
  • prometheus_port: connection port for the Prometheus server
  • prometheus_username: username for accessing Prometheus
  • prometheus_password: password for accessing Prometheus

Accessing the Primary Database Server

To access the primary database server, which should be used for database write operations, use the db_host and db_port keys to construct a connection string. The uri and jdbc_uri may also be used to connect to the primary database server when supported by the client application or library.

Accessing the Replica Database Server(s)

To access the replica database server(s), which should be used for database read operations, use the read_host and read_port keys to construct a connection string. The read_uri and jdbc_read_uri may also be used to connect to the replica database server(s) when supported by the client application or library.

Binding a PCF App to a Service

Using Cloud Foundry CLI

When the status of your service is create succeeded you can bind an app to the service by running cf bind-service APP_NAME SERVICE_INSTANCE, substituting APP_NAME with the name of your PCF app and SERVICE_INSTANCE with the name of your service. For example:

cf bind-service myApp myService
cf restage myApp

You must restage or restart your app before it will have access to the service credentials.

Using Pivotal Apps Manager

  1. From the space dashboard, select the Services tab, then select your service.
  2. Select the Overview tab, then click the Bind Apps button.
  3. Under the Bound Apps section, tick the checkbox for the app you would like to bind to your service, then click Save.

Unbinding a PCF App from a Service

Using Cloud Foundry CLI

To unbind your app from your service, run cf unbind-service APP_NAME SERVICE_INSTANCE, substituting APP_NAME with the name of your PCF app and SERVICE_INSTANCE with the name of your service. For example:

cf unbind-service myApp myService

Using Pivotal Apps Manager

  1. From the space dashboard, select the Services tab, then select your service.
  2. Select the Overview tab, then click the Bind Apps button.
  3. Under the Bound Apps section, click the Edit Bindings button, untick the checkbox for the app you would like to unbind from your service, then click Save.

Managing Service Keys

Creating a Service Key

To create a service key for an external application, run cf create-service-key SERVICE_NAME SERVICE_KEY_NAME, substituting SERVICE_NAME with the name of your service and SERVICE_KEY_NAME with a name for the service key, e.g. SERVICE_NAME_sk. The service credentials JSON object can be viewed by running cf service-key SERVICE_NAME SERVICE_KEY_NAME. For example:

cf create-service-key myService myService_sk
cf service-key myService myService_sk

Deleting a Service Key

To delete a service key, run cf delete-service-key SERVICE_NAME SERVICE_KEY_NAME, substituting SERVICE_NAME with the name of your service and SERVICE_KEY_NAME with the name of the service key. For example:

cf delete-service-key myService myService_sk

Deleting a Service

WARNING: This is a destructive process and will destroy the entire service instance, including your database and backups. Ensure that you export any data you need before performing this step.

Using Cloud Foundry CLI

Note: You are required to unbind a service before the service can be deleted.

To delete your service instance, run cf delete-service SERVICE_INSTANCE, substituting SERVICE_INSTANCE with the name of your service. For example:

cf delete-service myService

Using Pivotal Apps Manager

  1. From the space dashboard, select the Services tab, then select your service.
  2. Select the Settings tab, then click the Delete Service Instance button.

Backup and Restore

Crunchy PostgreSQL for Pivotal Cloud Foundry (PCF) Tile uses pgBackrest as a dedicated backup and archiving host. The tile comes pre-configured with nightly physical backups of the database server:

Day Backup Type Time
Sunday Full 1 am UTC
Monday Incremental 1 am UTC
Tuesday Incremental 1 am UTC
Wednesday Incremental 1 am UTC
Thursday Incremental 1 am UTC
Friday Incremental 1 am UTC
Saturday Incremental 1 am UTC

Although backups only happen once a day, PostgreSQL is continuously shipping the Write-Ahead-Logs (WAL) to the pgBackrest server. This means that point-in-time recovery is possible, regardless of the schedule.

These backups not only offer peace of mind, but are used frequently by the tile. Crunchy PostgreSQL for PCF uses backups to create replicas in the stack. By using backups in operations, we can ensure that backups and restores work.

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

Note: Pivotal recommends that you use BOSH CLI v2 when running PCF v1.11 or higher.

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

    bosh ssh consul-server 0
    
    If using the BOSH v2 Client:
    bosh -e $ENV 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 
    
    If using the BOSH v2 Client:
    bosh -e $ENV ssh postgresql 
    

  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
    

Point in Time Recovery

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

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.

Note: Pivotal recommends that you use BOSH CLI v2 when running PCF v1.11 or higher.

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

    bosh ssh consul-server 0
    
    If using the BOSH v2 Client:
    bosh -e $ENV 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 
    
    If using the BOSH v2 Client:
    bosh -e $ENV ssh postgresql 
    

  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, please refer to the official PGBackRest documentation.

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