Using the a9s PostgreSQL

This chapter describes how to use a9s PostgreSQL.

Use a9s PostgreSQL with an App

To use the a9s PostgreSQL with with an app, follow the procedures in this section to create a service instance and bind the service instance to your app. For more information on managing service instances, see Managing Service Instances with the cf CLI.

View the a9s PostgreSQL Service

After the service is installed, a9s-postgresql and its service plans appear in your CF marketplace. Run cf marketplace to see the service listing:

$ cf marketplace
Getting services from marketplace in org test / space test as admin...

service            plans                                                                                              description
a9s-postgresql     postgresql-single-small, postgresql-cluster-small, postgresql-single-big, postgresql-cluster-big   This is the a9s PostgreSQL 9.4 service.

See the next section for instructions on creating PostgreSQL service instances based on the plans listed in the cf marketplace output.

Create a Service Instance

To provision a PostgreSQL database, run cf create-service. For example:

$ cf create-service a9s-postgresql postgresql-single-small my-postgresql-service

Depending on your infrastructure and service broker usage, it may take several minutes to create the service instance.

Check the creation status using cf services. This displays a list of all your service instances. To check the status of a specific service instance, run cf service NAME-OF-YOUR-SERVICE.

Bind an Application to a Service Instance

After you create your database, run cf bind-service to bind the service to your application:

$ cf bind-service a9s-postgresql-app my-postgresql-service

Restage or Restart Your Application

To enable your app to access the service instance, run cf restage or cf restart to restage or restart your app.

Obtain Service Instance Access Credentials

After a service instance is bound to an application, the credentials of your PostgreSQL database are stored in the environment variables of the application. Run cf env APP-NAME to display the environment variables.

You can find the credentials in the VCAP_SERVICES key.

$ cf env a9s-postgresql-app
Getting env variables for app a9s-postgresql-app in org test / space test as admin...

  "a9s-postgresql": [
    "credentials": {
     "host": "EXAMPLE-HOST",
     "hosts": ["EXAMPLE-HOST"],
     "name": "d92e2bd",
     "password": "EXAMPLE-PASSWORD",
     "port": 5432,
     "uri": "EXAMPLE-URI",
     "username": "EXAMPLE-USERNAME"
    "label": "a9s-postgresql",
    "name": "my-postgresql-service",
    "plan": "postgresql-cluster-small",
    "tags": [

You can use the host, username and password values to connect to your database with a PostgreSQL client.

Best Practices

There are some best practices for using service binding information in apps in a separate document.

Delete an a9s PostgreSQL Service Instance

WARNING: Before deleting a service instance, you must backup data stored in your database. This operation cannot be undone and all the data is lost when the service is deleted.

Before you can delete a service instance, you must unbind it from all apps.

List Available Services

Run cf services to list your available services.

$ cf services

Getting services in org test / space test as admin...

name                    service          plan                      bound apps           last operation
my-postgresql-service   a9s-postgresql   postgresql-single-small   a9s-postgresql-app   create succeeded

This example shows that my-postgresql-service is bound to the a9s-postgresql-app app.

Unbind a Service Instance

Run cf unbind to unbind the service from your app.

$ cf unbind-service a9s-postgresql-app my-postgresql-service

Delete a Service Instance

After unbinding the service, it is no longer bound to an application. Run cf delete-service to delete the service:

  $ cf delete-service my-postgresql-service

It may take several minutes to delete the service. Deleting a service deprovisions the corresponding infrastructure resources. Run the cf services command to view the deletion status.

Upgrade the Service Instance to another Service Plan

Once created, you can upgrade your service instance to another, larger service plan. A larger service plan provides more CPU, RAM and storage. For more information, see the Update a Service Instance of the Managing Service Instances with the cf CLI topic.

$ cf update-service my-postgresql-service -p a-bigger-plan

Here are the plans you can upgrade to depending on the one you are currently using:

  • If you are currently using the postgresql-single-small plan, you can upgrade to the postgresql-single-big plan.

  • If you are currently using the postgresql-cluster-small plan, you can upgrade to the postgresql-cluster-big plan.

Configuring metrics intervals

It is possible to configure the collection of metrics groups to take place at specific intervals. Metrics groups are collections of related metrics. Each metric groups has a custom parameter that can be used to set its collection interval.

Intervals are in seconds and if the interval is set to 0 then the metric group is disabled.

Metric groups

Custom parameter nameSuggested (interval in seconds)Default value (interval in seconds)Description
metric_pg_database_size300300This group lets you see how quickly your database is growing.
metric_pg_stat_all_indexes300This group provides status information on all indexes.
metric_pg_statio_all_indexes600This group provides information on index usage for all indexes.
metric_pg_statio_all_tables300This group provides information on table usage for all tables.
metric_pg_stat_all_tables1200This group provides the status of all tables.
metric_pg_stat_archiver_table3000This group provides the archival status. Useful with continuous archiving enabled.
metric_pg_stat_database1200This group provides the general status of each database.
metric_pg_stat_database_conflicts3000This group provides insights on database conflicts.
metric_pg_replication1010This group provides the status of replication. Useful when the service instance is a replica set.

Metric group patterns

Each metric group has a set of patterns that describe the metrics contained in each group.


Configuring metrics databases

By setting the metric_databases custom parameter on a service instance it is possible to control which databases metrics are generated for. The metric_databases parameter expects an array of database names. If the parameter is not set or the array is set to empty then metrics will be collected for all databases.

Add a Graphite Endpoint

If you want to monitor your service with Graphite, you can set an endpoint to where to information will be sent with the cf update-service command. This command expects the -c flag and a JSON string containing the graphite and metrics_prefix keys. Depending on your graphite provider the metrics_prefix might require that each metrics must start with an API key in their name.

You can also change the intervals of different metric groups and decide from which databases you require metrics from. To do this look in the Configuring metrics intervals and Configuring metrics databases sections.

$ cf update-service my-postgresql-service -c '{ "graphite": [""], "metrics_prefix": "" }'

Add a Syslog Endpoint

The cf update-service command used with the -c flag can let you stream your syslog to a third-party service. In this case, the command expects a JSON string containing the syslog key. You can also change the interval for the syslog with the same key than for the graphite endpoint interval.

$ cf update-service my-postgresql-service -c '{ "syslog": [""], "interval": "5" }'

Cloud Foundry Application Security Groups

This topic describes how to check whether a security group was created.

Each a9s Data Service will automatically create and update Cloud Foundry security groups in order to protected service instances to be accessed by applications not running in the same Cloud Foundry applications space. To get a better understanding about Security Groups you can have a look on the Understanding Application Security Groups topic.

Get Service Instance GUID

Run cf service INSTANCE_NAME --guid to get the guid of the service instance.

$ cf service my-postgresql --guid

Check available Security Groups

To see all available security groups use cf security-groups.

$cf security-groups
Getting security groups as

     Name                                         Organization     Space
#0   public_networks
#1   dns
#2   tcp_open
#3   guard_432fb752-876d-443b-a311-a075f4df2237   demonstrations   demo
#4   guard_ca16f111-5073-40b7-973a-156c75dd3028   demonstrations   demo

There we can see a security group with the named guard_ca16f111-5073-40b7-973a-156c75dd3028 was successfully created.

NOTE: in some circumstances the connection between the application and the service instance is not possible, in this case check if a security group was created.

Backup and Restore Service Instances

a9s PostgreSQL provides an easy way to create backups and restore if needed. For a more detailed description, please see the a9s Service Dashboard documentation.

Make a Service Instance Locally Available

It is possible to access any of the a9s Data Services locally. That means you can connect with a local client to the service for any purpose such as debbuging. CF provides a smart way to create SSH forward tunnels via a pushed application. For more information about this feature see the Accessing Apps with SSH section of the CF documentation.

First of all you must have an application bound to the service. How to do this see Bind an Application to a Service Instance.

NOTE: cf ssh support must be enabled in the platform. Ask your administrator if you are not sure.

Get The Service Url and Credentials

When you follow this instructions Obtain Service Instance Access Credentials you will get the hostname of the service and the user credentials.

$ cf env a9s-postgresql-app
Getting env variables for app a9s-postgresql-app in org test / space test as admin...

   "a9s-postgresql": [
      "credentials": {
       "host": "d67901c-psql-master-alias.node.dc1.consul",
       "hosts": [
       "password": "a9s-brk-usr",
       "username": "a9s-password"
     "label": "a9s-postgresql",
     "name": "my-postgresql-service",
     "plan": "postgresql-cluster-small"

Notice the host d67901c-psql-master-alias.node.dc1.consul, the username a9s-brk-usr and the password a9s-password. You will need this in the next step.

Create a Tunnel to The Service

With the cf ssh as mentioned before you can create a ssh forward tunnel to the management dashboard. Use port 5432 to connect to the a9s PostgreSQL Instance.

$ cf ssh a9s-postgresql-app -L 5432:d67901c-psql-master-alias.node.dc1.consul:5432

When the ssh tunnel is open you can access the instance over the address localhost:5432.

NOTE: Don't forget to close the session with exit.

PostgreSQL Extensions

Create or Drop Extensions

It is possible to install PostgreSQL extensions with the Cloud Foundry CLI and additional configuration parameters:

$ cf create-service a9s-postgresql PLAN_NAME INSTANCE_NAME -c '{"install_plugins": ["<plugin_name>"]}'
$ cf update-service INSTANCE_NAME -c '{"install_plugins": ["<plugin_name>"]}'
$ cf update-service INSTANCE_NAME -c '{"delete_plugins": ["<plugin_name>"]}'

Available Extensions

The following PostgreSQL extensions are available:

  • postgis
  • uuid-ossp
  • ltree
  • pgcrypto
  • citext
  • pg_stat_statements

Default Extensions

There are PostgreSQL extensions that are always installed and it's also not possible to delete them. The following PostgreSQL extensions are set as default extensions:

  • pgcrypto
  • citext

These two extensions are needed to have a functional replication.

PostgreSQL statistics collector

You can enable support for the statistics collector feature (please note that this feature is PostgreSQL version specific). This feature allows you to retrieve information about the current status of the PostgreSQL cluster. It includes information about currently active queries and other useful information. For each statistics collector view another one is created by a PostgreSQL super user (with the super users' access privileges) that has the same name with a prefix prepended. Example: For 'pg_stat_activity' the associated view is called 'ds_pg_stat_activity'. You can set the value of the prefix as you prefer. Read-only access to the views created by this feature is given to every user allowed to login on the cluster via the "public" schema.

You need to connect to the postgres database with your service bindings in order to use those views.

This feature is disabled by default. You can ask your operator to enable this feature for your service instance.

Enable AppDynamics support

Enable AppDynamics support to create a view called 'pg_stat_activity_allusers' which is accessible to all users as described here.

This feature is disabled by default. You can ask your operator to enable this feature for your service instance.

PostgreSQL Custom Parameters

As an end user you have the opportunity to customize your service instance by using custom parameters. Custom parameters are passed on to a service instance by using the -c switch of the cf CLI commands cf create-service and cf update-service. For example

cf update-service mypg -c '{"temp_file_limit": "-1"}'

would set the temporary file limit to unlimited for the service instance mypg.

You don't have to utilize those settings. There are sane defaults in place that fit your service plan well.

Every parameter corresponds to a property in the configuration file for the respective PostgreSQL version.


Use checksums on data pages to help detect corruption by the I/O system that would otherwise be silent. This option can only be set during creation of the service instance, and cannot be changed later. Enabling checksums may incur a noticeable performance penalty.

The default value is enabled. The allowed values are enabled and disabled.


Determines the maximum number of concurrent connections to the database server.

Note: You cannot decrease max_connections as this will break clustering.

The default behaviour for all a9s PostgreSQL versions is to calculate the number of max_connections automatically based on the size of shared_buffers and work_mem (in MB) with the following formula:

max_connections = 2 * (shared_buffers / work_mem)

It is expected that not all connections will use most of the allocated work memory for the process, to avoid resource underusage the number of max allowed connections is doubled. Little performance degradation can happen as long as most of the connections do not use more than half of the allocated work memory (work_mem).

We recommend to use a different approach for a9s-pg and configure this value manually based on the usage of the clients. To configure this parameter, use the iaas.a9s_pg.max_connections property on your IaaS File.

This setup will be enough for most cases but if a different value is needed for a service instance, you can configure it with:

cf create-service <service> <plan> <service-instance-name> -c '{"max_connections": "<max_connections_value>"}'

When creating a service.

When updating an existing service instance, you can use the following:

cf update-service mypg -c '{"max_connections": "<max_connections_value>"}'

Note that during an update to a greater value than the current one this situation happens for the last standby node not updated yet, since the master will have a greater value. Because the node comes back with the correct value the update goes one with success. As a side effect, during the update of the second node in a 3 node cluster the cluster will not have majority and the master node will be blocked causing a down time, the situation is solved when the second node comes back.

An update to a smaller value than the current one is not allowed. Once you set the value for max_connections, you cannot reset it to the default value using the null value.

The max_connections of an existing cluster deployment can not be updated to a smaller value than the current one due to the fact that a standby node can not connect to a master that has a greater value for this property.


Sets the planner's assumption about the effective size of the disk cache that is available to a single query.

Please note, that our BOSH release includes an algorithm which calculates a usable value for this property on the fly based on the resources of the VM running.

The property effective_cache_size can be specified (e.g. '2GB') or be omitted. If effective_cache_size is not set, it's value is 75% of the total memory. If the total memory is less than 4GB, it will use 50% of the total memory with a floor of 256MB. When specifying this value make sure you set a value that leads to a configuration that is higher than shared_buffers because shared buffers are included in the PostgreSQL query execution planner.

An integer value greater than 0 is expected. The units of the property are MB and no unit should be appended to the value. For example, to set 1GB of shared buffers the parameter value should be 1024, and for 512MB the value would be 512.


Sets the amount of memory the database server uses for shared memory buffers.

The property shared_buffers can be specified (e.g. 2048) or be omitted If shared_buffers is not set, its value is 25% of the total memory, if the total memory if greater than 1GB and 15% if less with a floor of 128MB.

An integer value greater than 0 is expected. The units of the property are MB and no unit should be appended to the value. For example, to set 1GB of shared buffers the parameter value should be 1024, and for 512MB the value would be 512.


Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files.

The default unit is kilobytes but you can also specify MB or GB. The default value is 8 MB. For example, to set 1 MB of work_mem the parameter value can be 1024 or "1 MB".


Specifies the maximum amount of disk space that a process can use for temporary files, such as sort and hash temporary files, or the storage file for a held cursor.

An integer value greater than 0 is expected. Alternatively "-1" means "no limit". The default value is "-1".


Enables timing of database I/O calls.

By default this parameter is off.

WARNING: When it is on it will repeatedly query the operating system for the current time, which may cause significant overhead on some platforms.


Enforces the server to switch to a new WAL segment file periodically.

By default the value is 1h. The following values are allowed:

  • null: reset custom value and use default one, so 1h
  • 0: disables
  • integer bigger than 59 seconds


Abort any statement that takes more than the specified number of milliseconds, starting from the time the command arrives at the server from the client..

The default value is 0 (off). Only integer values bigger or equal 0 are allowed.


Terminate any session with an open transaction that has been idle for longer than the specified duration in milliseconds.

The default value is 0 (disabled). Only integer values bigger or equal 0 are allowed.

Not available for a9s PostgreSQL 9.4.


Configures default privileges for the credentials.

By default, it is not possible to specify a value for this parameter. However, default and permitted values are configured on the SPI by the platform operator. Check with him which privileges are available.

Possible configurable values are:

  • CREATEDB: Gives the user permission to create and drop new databases. NOCREATEDB is the default.
  • CREATEROLE: Gives the user permission to create, delete, and alter the attributes of a role. NOCREATEROLE is the default.
  • REPLICATION: Gives the role permission to create and drop replication slots and connect to the service in replication mode. NOREPLICATION is the default.

This parameter receives values in the following format:

  • null: reset custom values to the default specified by the platform operator.
  • []: removes all privileges.
  • ['CREATEDB', 'CREATEROLE', 'REPLICATION']: an array of privileges. The default and permitted values are defined by the platform operator. Any privilege not specified is disabled.

This parameter can be specified during instance creation (cf create-service and cf update-service) and credentials creation (cf bind-service and cf create-service-key).

When specifying during instance creation, the privileges are applied to the cfuser role. All other users inherit this role by default. For example:

$ cf create-service a9s-postgresql postgresql-single-small my-postgresql-service -c '{ "role_privileges": ["CREATEROLE"]}'

If specified during credentials creation, the privileges are applied to the created user only and are enabled when using the user role instead of the cfuser role.

$ cf create-service-key my-postgresql-service -c '{ "role_privileges": ["CREATEROLE"]}'

The role can be set using either SET ROLE or ALTER ROLE. ALTER ROLE can only be executed if CREATEROLE privilege is given.

CAUTION: Be careful when configuring privileges in your service instances. CREATEROLE, for example, gives a user permission to create new users with a weak password, avoid doing this in favor of using the CF CLI. CREATEDB gives permission also to delete databases, and misusing REPLICATION can cause issues with the storage space, continuous archiving, and the node replication of your cluster.

To know more about the a9s PostgreSQL user configuration, see the Overview.

Logging parameters

Logging in a9s PostgreSQL is reduced to a minimum by default. This is, to give most of the performance and especially diskspace, to the customer. Additionally it reduces the problems that can appear if queries are failing for any reasons.

The parameters that can be configured are the following:

client_min_messages: Controls which message levels are sent to the client. Valid values (in order of details) are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, LOG, NOTICE, WARNING, ERROR, FATAL, and PANIC. The default for a9s PostgreSQL is NOTICE.

pg_log_min_messages: Controls which message levels are written to the server log. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC. Each level includes all the levels that follow it. The later the level, the fewer messages are sent to the log. The default for a9s PostgreSQL is WARNING.

pg_log_min_error_statement: Controls which SQL statements that cause an error condition are recorded in the server log. The current SQL statement is included in the log entry for any message of the specified severity or higher. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC. The default for a9s PostgreSQL is ERROR, which means statements causing errors, log messages, fatal errors, or panics will be logged. To effectively turn off logging of failing statements, set this parameter to PANIC.

repmgr_loglevel: Log level: possible values are DEBUG, INFO, NOTICE, WARNING, ERR, ALERT, CRIT or EMERG. The default for a9s PostgreSQL is NOTICE.

loglevel: Configures the overall loglevel for the control scripts and components from anynines. Valid levels are INFO or DEBUG. The default for a9s PostgreSQL is DEBUG.

temp_files: Controls logging of temporary file names and sizes. Temporary files can be created for sorts, hashes, and temporary query results. A log entry is made for each temporary file when it is deleted. A value of zero logs all temporary file information, while positive values log only files whose size is greater than or equal to the specified number of kilobytes. The default for a9s PostgreSQL is -1, which disables such logging.

log_statement: Controls which SQL statements are logged. Valid values are none (off), ddl, mod, and all (all statements). ddl logs all data definition statements, such as CREATE, ALTER, and DROP statements. mod logs all ddl statements, plus data-modifying statements such as INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. PREPARE, EXECUTE, and EXPLAIN ANALYZE statements are also logged if their contained command is of an appropriate type. For clients using extended query protocol, logging occurs when an Execute message is received, and values of the Bind parameters are included (with any embedded single-quote marks doubled). The default for a9s PostgreSQL is none.

log_error_verbosity: Controls the amount of detail written in the server log for each message that is logged. Valid values are TERSE, DEFAULT, and VERBOSE, each adding more fields to displayed messages. TERSE excludes the logging of DETAIL, HINT, QUERY, and CONTEXT error information. VERBOSE output includes the SQLSTATE error code (see also Appendix A) and the source code file name, function name, and line number that generated the error. The default for a9s PostgreSQL is TERSE.

More information about logging can be found in Documentation

Create a fork of a service instance

The procedure of forking a service instance involves creating a backup of a service instance, modifying the backup a bit and restoring it to a different service instance.

Having two service instances is a prerequisite for the process:

$ cf s
Getting services in org my_org / space my_space as

name    service            plan                       bound apps   last operation
psql1   a9s-postgresql94   postgresql-replica-small                create succeeded
psql2   a9s-postgresql94   postgresql-replica-small                create succeeded

Having a service key for each of the service instances comes in handy later when we need info about the database and credentials to connect to the database. Create one for each service instance using e.g.: cf create-service-key psql1 mykey. You can then have a look at the keys contents:

$ cf service-key psql1 mykey
Getting key mykey for service instance psql1 as

 "host": "pcsd85301a-psql-master-alias.node.dc1.consul",
 "hosts": [
 "name": "pcsd85301a",
 "password": "a9spass",
 "port": 5432,
 "uri": "postgres://a9suser:a9spass@pcsd85301a-psql-master-alias.node.dc1.consul:5432/pcsd85301a",
 "username": "a9suser"
$ cf service-key psql2 mykey
Getting key mykey for service instance psql2 as

 "host": "pcsd59c00c-psql-master-alias.node.dc1.consul",
 "hosts": [
 "name": "pcsd59c00c",
 "password": "a9spass",
 "port": 5432,
 "uri": "postgres://a9suser:a9spass@pcsd59c00c-psql-master-alias.node.dc1.consul:5432/pcsd59c00c",
 "username": "a9suser"

We need the database names for later use as well as the credentials for psql2. The database name is the trailing part of the URI. For psql1 the database name is pcsd85301a and for psql2 the database name is pcsd59c00c

Additional prerequisites regarding command line tools:

  • BASH (some shell)
  • cat
  • sed
  • openssl
  • psql (the command line client for psql)

Open the service dashboard of the service instance you want to fork. We use psql1 for this example. You can find the dashboard URL like this:

$ cf service psql1
Showing info of service psql1 in org my_org / space my_space as

name:            psql1
service:         a9s-postgresql94
bound apps:      
plan:            postgresql-replica-small
description:     Dedicated PostgreSQL service instances and clusters powered by the anynines Service Framework


Make sure you set a encryption password for the backups using the service instance dashboard (Change Backup Settings). Create a backup using the dashboard. Download the backup to your local machine. The filename will be something like racsd92baee-1522222422893. Decrypt the backup and write its contents to a file:

$ cat racsd92baee-1522222422893 | openssl enc -aes256 -d -pass 'pass:mytopsecretpassword' | gunzip -c > backup.sql

We have to replace the database name of the original instance in the backup with the database name of the other instance:

$ cat backup.sql | sed -e 's/pcsd85301a/pcsd59c00c/g' > backup.fork.sql

Create a tunnel to the admin interface (like explained in a previous chapter) of the PostgreSQL instance that will be the fork of the original instance. I used psql1 as the original instance and psql2 as the fork instance. I assume you're using something like this to set up the tunnel to psql2:

$ cf ssh someapp -L

Finally restore the data:

psql -h -p 5432 -U a9s27ae5c9bfee2937a8dc04b1d7d9b9c9690c97704 pcsd59c00c < backup.fork.sql

Don't get confused if you see error messages. The backup contains data that we lack the necessary permissions to restore. In the end our data gets restored and that's our primary goal.

Setup Disk Usage Alerts

Each service comes with the a9s Parachute. This component monitors ephemeral and persistent disk usage. See the a9s Parachute documentation how to configure the component.

Continuous Archiving and Point-in-Time Recovery (PITR)

Enabled Continuous Archiving

When creating a service you need to pass the custom paramter continuous_archiving in order to create an a9s PostgreSQL instance with Continuous Archiving enabled:

cf cs a9s-postgresql11 postgresql-replica-small mypsql -c '{"continuous_archiving": "enabled"}'

Currently, you cannot update a service instance to change the custom paramter continuous_archiving.