Skip to content

Using the a9s PostgreSQL

This section 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, postgresql and its service plans appear in your CF marketplace. Run cf marketplace to see the service listing:

1
2
3
4
5
6
$ cf marketplace
Getting services from marketplace in org test / space test as admin...
OK

service        plans                         description
postgresql     postgresql-xs, postgresql-m   This is the anynines 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.

1
cf create-service {service_name} {service_plan} {instance_name}
1
cf create-service postgresql postgresql-xs my-postgresql-service

Service and Service Plan Naming

Service and service plans may be named differently in your Cloud Foundry environment. Find the correct names of available services using the cf marketplace command.

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 {instance_name}.

Bind an Application to a Service Instance

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

1
cf bind-service {app_name} {instance_name}
1
cf bind-service 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.

1
cf env {app_name}
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
$ cf env postgresql-app
Getting env variables for app postgresql-app in org test / space test as admin...
OK

System-Provided:
{
 "VCAP_SERVICES": {
  "postgresql": [
   {
    "credentials": {
     "host": "EXAMPLE-HOST",
     "name": "d92e2bd",
     "password": "EXAMPLE-PASSWORD",
     "port": 5432,
     "uri": "EXAMPLE-URI",
     "username": "EXAMPLE-USERNAME"
    },
    "label": "postgresql",
    "name": "my-postgresql-service",
    "plan": "postgresql-xs",
    "tags": [
     "sql",
     "database"
    ]
   }
  ]
 }
}
...

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

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.

1
cf services
1
2
3
4
5
6
7
$ cf services

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

name                    service          plan                      bound apps           last operation
my-postgresql-service   postgresql   postgresql-xs   postgresql-app   create succeeded

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

Unbind a Service Instance

Run cf unbind to unbind the service from your app.

1
cf unbind-service {app_name} {instance_name}
1
cf unbind-service 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:

1
cf delete-service {instance_name}
1
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, refer to Update a Service Instance.

1
cf update-service {instance_name} -p {service_plan}
1
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-xs plan, you can upgrade to the postgresql-m 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.

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

1
cf update-service my-postgresql-service -c '{ "syslog": ["logs4.your-syslog-endpoint.com:54321"], "interval": "5" }'

Cloud Foundry Application Security Groups

This section 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, refer to Understanding Application Security Groups.

Get Service Instance GUID

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

1
cf service {instance_name} --guid
1
2
$ cf service my-postgresql --guid
ca16f111-5073-40b7-973a-156c75dd3028

Check available Security Groups

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
$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

If the connection between the application and the service instance cannot be established, check if a security group was created.

Backup and Restore Service Instances, Download Backups

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

Get Dashboard Address, Login and Authorize

  1. Get the dashboard URL with cf service {instance_name}, e.g.:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    $cf service my-postgresql
    
    Service instance: my-postgresql
    Service: postgresql
    Bound apps:
    Tags:
    Plan: postgresql-xs
    Description: This is a service creating and managing dedicated PostgreSQL service instances and clusters, powered by the anynines Service Framework
    Documentation url:
    Dashboard: https://postgresql-dashboard.aws.ie.apps.eu1.mindsphere.io/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
    
  2. Enter the dashboard URL into your browser and authenticate on the redirected page with the "Login with WebKey" method:
    authentication-page

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

As for the backup, depending on the size of the data, the restore will take some time.

service-dashboard

Download a Backup

To be able to download a performed backup, you first have to set a personal encryption key. Otherwise a performed backup is not downloadable.

To set a personal encryption key, open the Service Dashboard for the appropriate Service Instance as shown above. Click the Edit button.

service-dashboard

Set the encryption key and click the Save button. The minimum length for the encryption key is 8 characters.

service-dashboard

The newly created backup will now show a Download button. Click on this button to download a backup.

service-dashboard

Note

If you change your personal encryption key, you will no longer be able to download performed backups encrypted with your old personal encryption key.

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

Obtain the Service Instance Access Credentials to get the host name of the service and the user credentials:

1
cf env {app_name}
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
$ cf env postgresql-app
Getting env variables for app postgresql-app in org test / space test as admin...
OK

System-Provided:
{
 "VCAP_SERVICES": {
  "postgresql": [
   {
    "credentials": {
     "host": [
        "d67901c.service.dc1.a9svs"
       ],
     "username": "brk-usr",
     "password": "password",
     "port": 5432
    },
    "label": "postgresql",
    "name": "my-postgresql-service",
    "plan": "postgresql-xs"
   }
  ]
 }
}
...

The host and the port (in the example: d67901c.service.dc1.a9svs and 5432) are required in the next step.

Create a Tunnel to the Service

With the cf ssh you can create a SSH forward tunnel to the management dashboard. Use the host and port from the previous step to connect to the a9s PostgreSQL instance:

1
cf ssh {app_name} -L {local_port}:{host}:{port}
1
2
$ cf ssh postgresql-app -L 5432:d67901c.service.dc1.a9svs:5432
vcap@956aaf4e-6da9-4f69-4b1d-8e631a403312:~$

When the SSH tunnel is open you can access the instance in the example via the address localhost:5432.

Note

Don't forget to close the session using exit.

PostgreSQL Extensions

Create or Drop Extensions

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

1
2
3
cf create-service {service_name} {service_plan} {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

Default Extensions

The following PostgreSQL extensions are installed by default and cannot be deleted, because they are needed to have a functional replication:

  • pgcrypto
  • citext

Any questions left?

Ask the community


Except where otherwise noted, content on this site is licensed under the MindSphere Development License Agreement.