Postgres Services

To integrate your apps with PostgreSQL database, Apcera provides the postgres serivce type and gateway.

This section describes how to create Postgres providers, services, and bindings, including:

Using Postgres services

Connecting jobs to a PostgreSQL database involves registering the PostgreSQL server as a provider, creating a service of type postgres, and binding one or more jobs to it.

The typical use case for a Postgres service is to connect apps to an external PostgreSQL database. External in this context means that the PostgreSQL database server is running outside of the Apcera Platform. When registering an external Postgres provider you upload a PEM file that contains the root certificate authorities for your Postgres server. The root certificate is used by the Postgres service gateway and semantic pipeline to authenticate with the Postgres server. See Registering Postgres providers.

You can also use the Postgres service to connect apps to an internal Postgres database provider. In this case, the PostgreSQL database runs as a job in a container in the Apcera Platform. Because of the ephemeral (stateless) nature of containers, you can bind the PosgreSQL database job to an NFS service for persistence.

Semantic pipeline for Postgres protocol

The postgres service type uses a semantic pipeline that lets you write rules to monitor, inject logic, and control behavior within the communication channel in real time.

drawing

The semantic pipeline for the postgres service type operates at the Postgres protocol level to accept client connections, authenticate the clients locally, and establish a connection to the backend server using separate credentials. The client cannot communicate directly with the server using the credentials it receives from the semantic pipeline.

Requirements for setting up Postgres providers

This section describes the requirements for setting up Postgres providers.

User permissions

For PostgreSQL database providers, we require the following users:

  • One user, specified in the URI for the provider connection string, that has enough privileges to let us create a user for each service.
  • A second user, for each service, that is restricted to that tablespace or DB for that user.

TLS certificate

The postgres service type uses a semantic pipeline to broker the connection between bound applications and the PostgreSQL provider database service. The Postgres semantic pipeline expects the PostgreSQL database to have a TLS certificate installed. For external Postgres databases (that is, running outside the Apcera cluster) you can provide a root certificate when registering the Postgres provider.

See Semantic Pipeline Types for more information.

Other requirements

To allow access to a Postgres DB server from any location other than "localhost," you may have to modify the Postgres server configuration file. See the PostgreSQL documentation for details. See also the troubleshooting PostgreSQL services section.

Registering Postgres providers

To implement Postgres services, you register a Postgres provider, create a service and binding, and test the service implementation. If the Postgres server you want to use is running outside the Apcera cluster, you must provide a root certificate when creating the provider. The Postgres service gateway and semantic pipeline uses the root certificate you provide to authenticate with the external database when provisioning services and creating bindings, and to broker the connection between the app and the Postgres database.

Syntax:

apc provider register <provider name> --type postgres --url URL \
[--cacert <path-to-root-cert>] \
[--description]

For example, the following registers a new Postgres provider on

apc provider register postgres --type postgres \
--url postgres://user:password@example.us-west-2.rds.amazonaws.com:5432/template1 \
--description 'PostgreSQL (RDS)'

To register an external Postgres database as a provider, use the --cacert option specify a path to a root certificate for the external database, for example:

apc provider register external-postgres --type postgres \
--url postgres://user:pass@db.example.com:5432 \
--cacert ~/certs/cacert.pem

Also see Adding root certificates to a provider.

Creating Postgres services and bindings

The following example demonstrates how to create a Postgres service.

apc service create postgres-service --provider /apcera/providers::postgres

The following example demonstrates how to create a Postgres service and bind a job to it using a single command:

apc service create postgres-service --provider /apcera/providers::postgres --job postgres-client

Apcera establishes the binding and sets up a PostgreSQL semantic pipeline between the job and the service. The job finds the URI of the semantic pipeline in its environment variable named POSTGRES_URI and uses it exactly as if it went directly to the PostgreSQL database.

Testing Postgres services

To test a Postgres service you have created as described above, you can use a capsule:

apc capsule create postgres-client --image ubuntu --allow-egress --batch

Then, bind the capsule to the Postgres service:

apc service bind postgres-service --job postgres-client

Once the service binding is created, note the binding URI envar, for example: $POSTGRESQLSERVICE_URI.

Now, connect to the capsule:

apc capsule connect postgresql-client

Update the capsule and install the Postgres client:

env
apt-get update
apt-get install postgresql-client

Test access to the Postgres DB using the binding URI:

psql $POSTGRESQLSERVICE_URI

You should see that you can connect to the service from the client app. The job can now interact with the PostgreSQL database using the binding URI found in its environment variable POSTGRESQLSERVICE_URI. Because the system provides a semantic pipeline for the postgres service type, the URI goes to a capsule that implements the semantic pipeline for the postgres service type. To the job, this looks like a direct connection to the PostgreSQL database server when in fact it is connecting to the proxy.

Create Postgres service from package

The following example shows you how to create a PostgreSQL DB provider and service from a Postgres package (version 9.4.4) that you have imported to your cluster in the /apcera/pkg/packages namespace.

Step 1. Create postgres app.

apc app from package postgres-app --package /apcera/pkg/packages::postgres-9.4.4 --memory 256MB --disable-routes --start-cmd "sudo -u postgres /opt/apcera/postgres-9.4.4/bin/postgres -D /opt/apcera/postgres-9.4.4/data"

NOTE: If you are using Linux or Mac, you can use single quotes surrounding the start command. If you are using Windows, you must use double-quotes around the start command.

Step 2. Set the port number.

apc app update postgres-app --port-add 5432

Step 3. Start the Postgres app.

apc app start postgres-app

Step 4. Create the PostgreSQL DB provider.

apc provider register postgres-provider --type postgres --job postgres-app --port 5432 --url postgres://postgres:postgres@postgres-app

Step 5. Create a service for the provider.

apc service create sample-todo-db --provider postgres-provider --job sample-todo

Troubleshooting Postgres services

If you sucessfully register a Postgres provider and create a service and binding but cannot read/write from/to the PostgreSQL database, make sure the PostgreSQL server is configured for TLS.

If you receive an error similar to the following when you try to create a service, make sure you have configured PostgreSQL database with the proper access.

no pg_hba.conf entry for host "198.51.100.54", user "pgdb", database "pgdb"

In this case, update pg_hba.conf to the following and restart the PostgreSQL database:

host    all             all             198.51.100.0/24             md5