Using the PostgreSQL

This topic describes how to use PostgreSQL.

Use PostgreSQL with an App

To use the 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 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...
OK

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

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

Create a Service Instance

To provision a Posa9s PostgreSQLtgreSQL 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 a9s 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...
OK

System-Provided:
{
 "VCAP_SERVICES": {
  "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": [
     "sql",
     "database"
    ]
   }
  ]
 }
}
...

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

Delete an 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...
OK

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.

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 interval within the data is send to the endpoint. Do to this modify interval the default is 10s.

$ cf update-service my-postgresql-service -c '{ "graphite": ["yourspace.your-graphite-endpoint.com:12345"], "metrics_prefix": "your-api-key.my-cluster-postgresql", "interval": "5"}'

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": ["logs4.your-syslog-endpoint.com:54321"], "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
ca16f111-5073-40b7-973a-156c75dd3028

Check available Security Groups

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

$cf security-groups
Getting security groups as demo@anynines.com
OK

     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

PostgreSQL provides an easy way to create backups and restore if needed.

Get Dashboard Address, Login and Authorize

  1. Grap the dashboard URL with cf service SERVICE-NAME. “`bash $cf service my-postgresql

Service instance: my-postgresql Service: a9s-postgresql Bound apps: Tags: Plan: postgresql-single-small Description: This is a service creating and managing dedicated a9s PostgreSQL service instances and clusters, powered by the anynines Service Framework Documentation url: Dashboard: https://a9s-postgresql-dashboard.aws.ie.a9s.eu/service-instances/ca16f111-5073-40b7-973a-156c75dd3028

Last Operation Status: update succeeded Message: Started: 2017-10-26T08:28:38Z Updated: 2017-10-26T08:28:38Z ”`

  1. Browse to the dashboard URL and authenticate on the redirected page with your Cloud Foundry credentials: authentication-page

  2. Click Authorize to approve the authorization request: authorization-page

Perform a Backup

On the dashboard as shown above you can trigger a backup by clicking Trigger backup

service-dashboard

After a short period of time the backup will be queued. The backup process will start soon.

service-dashboard

NOTE: Depending on the size of the data the backup will take some time.

Restore a Backup

Open the dashboard again and select the backup you would like to restore. Click the Restore button of the backup. After a short period of time the restore will be triggered.

service-dashboard

NOTE: Same as the backup, depending on the size of the data, the restore will take some time.

service-dashboard

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

System-Provided:
{
  "VCAP_SERVICES": {
   "a9s-postgresql": [
    {
      "credentials": {
       "host": "d67901c-psql-master-alias.node.dc1.consul",
       "hosts": [
        "d67901c-pg-0.node.dc1.consul",
        "d67901c-pg-1.node.dc1.consul",
        "d67901c-pg-2.node.dc1.consul"
       ],
       "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 PostgreSQL Instance.

$ cf ssh a9s-postgresql-app -L 5432:d67901c-psql-master-alias.node.dc1.consul:5432
vcap@956aaf4e-6da9-4f69-4b1d-8e631a403312:~$

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.

a9s PostgreSQL Extensions

Create or Drop Extensions

It is possible to install a9s 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 a9s PostgreSQL extensions are available:

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

Default Extensions

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

  • pgcrypto
  • citext

These two extensions are needed to have a functional replication.

a9s 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 '{"max_connections": "100"}' would set the maximum number of concurrent connections to 100 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 a9s PostgreSQL version.

max_connections

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

The default in the service plans of anynines-deployment is as follows:

Plan Max Connections
big 100
small 50
nano 20

An integer value greater than 10 is expected.

effective_cache_size

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 ressources of the VM running.

The property effective_cache_size can be specified (e.g. ‘2GB’) or be ommited. 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 a9s PostgreSQL query execution planner.

An integer value greater than 0 is expected.

shared_buffers

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

The property shared_buffers can be specified (e.g. '2048MB’) or be ommited 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.

work_mem

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

An integer value greater than 0 is expected. The default is 8MB for anynines-deployment plans.

temp_file_limit

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

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: “`shell $ cf s Getting services in org my_org / space my_space as user@domain.com

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: “`shell $ cf service-key psql1 mykey Getting key mykey for service instance psql1 as user@domain.com

{ "host”: “pcsd85301a-psql-master-alias.node.dc1.consul”, “hosts”: [ “pcsd85301a-pg-0.node.dc1.consul”, “pcsd85301a-pg-1.node.dc1.consul”, “pcsd85301a-pg-2.node.dc1.consul” ], “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 user@domain.com

{ “host”: “pcsd59c00c-psql-master-alias.node.dc1.consul”, “hosts”: [ “pcsd59c00c-pg-0.node.dc1.consul”, “pcsd59c00c-pg-0.node.dc1.consul”, “pcsd59c00c-pg-0.node.dc1.consul” ], “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: ”`shell $ cf service psql1 Showing info of service psql1 in org my_org / space my_space as user@domain.com

name: psql1 service: a9s-postgresql94 bound apps: tags: plan: postgresql-replica-small description: Dedicated a9s PostgreSQL service instances and clusters powered by the anynines Service Framework documentation: dashboard: https://a9s-postgresql-dashboard.de.a9s.eu/service-instances/271ceaa0-eded-43ba-a305-aaecab326079

[…] “`

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: shell $ cat racsd92baee-1522222422893 | openssl enc -aes256 -d -pass 'pass:mytopsecretpassword' > backup.sql

We have to replace the database name of the original instance in the backup with the database name of the other instance: shell $ 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 a9s 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: shell $ cf ssh someapp -L 127.0.0.1:15672:racsd92baee.service.dc1.a9ssvc:15672

Finally restore the data: shell psql -h 127.0.0.1 -p 5432 -U a9s27ae5c9bfee2937a8dc04b1d7d9b9c9690c97704 pcsd59c00c &lt; 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.

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