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
- Semantic pipeline for Postgres protocol
- Requirements for setting up Postgres providers
- Registering Postgres providers
- Creating Postgres services and bindings
- Testing Postgres services
- Create Postgres service from package
- Troubleshooting Postgres services
Using Postgres services
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 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.
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.
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.
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.
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:email@example.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:firstname.lastname@example.org: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:
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:
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