Appendix A - Deploying Postgres

The Lagom shopping cart sample app uses Postgres as its database.

Database schema

Lagom will automatically create the database tables you need for you if they are not there. However, this is not the recommended way to run Lagom in production, since it’s generally considered bad practice to allow an application database account to perform DDL statements. Instead, we’re going to manually create the database schema needed.

The shopping cart application uses Akka persistence with the Akka persistence JDBC backend. This requires two tables, a journal table, which contains all the events for your entities, and a snapshot table, which contains snapshots of the state every so many events. The schema for these tables on Postgres can be found in the Akka persistence JDBC repository, and looks like this:

CREATE TABLE IF NOT EXISTS journal (
  ordering BIGSERIAL,
  persistence_id VARCHAR(255) NOT NULL,
  sequence_number BIGINT NOT NULL,
  deleted BOOLEAN DEFAULT FALSE,
  tags VARCHAR(255) DEFAULT NULL,
  message BYTEA NOT NULL,
  PRIMARY KEY(persistence_id, sequence_number)
);

CREATE UNIQUE INDEX journal_ordering_idx ON journal(ordering);

CREATE TABLE IF NOT EXISTS snapshot (
  persistence_id VARCHAR(255) NOT NULL,
  sequence_number BIGINT NOT NULL,
  created BIGINT NOT NULL,
  snapshot BYTEA NOT NULL,
  PRIMARY KEY(persistence_id, sequence_number)
);

In addition, Lagom also needs an offset table, which is used to track the progress of read side processors and Kafka publishers through the event log. That table looks like this:

CREATE TABLE read_side_offsets (
  read_side_id VARCHAR(255), tag VARCHAR(255),
  sequence_offset bigint, time_uuid_offset char(36),
  PRIMARY KEY (read_side_id, tag)
)

You can find this schema already saved in the project, in schemas/shopping-cart.sql. We’ll load this script using the psql client when we come to create the schema.

Deployment topology

Before we talk about how to deploy Postgres, we first need to decide how we’re going to structure our databases. Microservices should have isolated data stores, that is to say, they should not share the same database tables, and ideally, this should be enforced. To implement this with Postgres, there are three different levels of isolation.

  1. One database server per service - this is the highest level of isolation. In this setup, a new Postgres pod is deployed for each service that needs it. This pod can be sized exactly according to the services needs, and it shares no resources, CPU or memory, with Postgres pods for other services, and so is completely isolated.
  2. One database per service on a single database server. In this setup, a single Postgres pod is provisioned, and multiple databases are created on it, one for each service. Additionally, a user is created for each service, and this user is granted access only to the database for that service. This allows a moderate level of isolation, different databases can be configured to use different volume mounts and are reasonably portable between database services, however, they will share CPU and RAM and hence can impact each other.
  3. One database schema per service on a single database. In this setup, a single Postgres pod is provisioned, with a single database, and multiple schemas are created in that database. Additionally, a user is created for each service, and this user is granted access only to the schema for that service. This allows the least level of isolation, since being on the same database, there is no isolation other than access isolation between the stores.

Which setup is appropriate for you depends on your use case and organisation, and in most cases, a combination of multiple setups might be used. Sharing a single pod might be good to start with to minimise resource usage, but as load grows on the services, you might need to split out into multiple pods. A large organisation may also decide to isolate by teams, so each team uses at least one pod, with potentially many databases on each pod if the team manages multiple services.

For the purpose of this guide, we only have one service accessing the database, so it doesn’t really matter which option we go with. However, we’ll use a setup that makes it easy to add additional databases to the database pod.

We will need to generate a number of passwords. A simple way to generate a secure password, if you have openssl installed, is to use that. For example, to generate a secure 33 character password, you can use this command:

openssl rand -base64 24

Creating a Postgres pod

OpenShift provides images for deploying Postgres out of the box, making it very straight forward to run Postgres. Detailed documentation on using it can be found here. We’ll create an ephemeral database service called postgresql:

oc new-app postgresql
Note

The database we’ve just created is using ephemeral persistence, meaning that if the pod is restarted, all data will be lost. Read the documentation for details on how to deploy persistent databases.

The above database will fail to provision, because the Postgres image requires that an environment variable be set for the Postgres admin password. While we could have specified that when we created the app, this would have hard coded it in the spec for the pod, making it readable to anyone that could read pods. Instead, we’re going to create a Kubernetes secret containing it, and then we’ll update the deployment to use that secret.

First, create the secret with a random password:

oc create secret generic postgresql-admin-password --from-literal=password="$(openssl rand -base64 24)"

Now patch the deployment config just created to use the admin password configured in the service.

oc patch deploymentconfig postgresql --patch '{"spec": {"template": {"spec": {"containers": [
  {"name": "postgresql", "env": [
    {"name": "POSTGRESQL_ADMIN_PASSWORD", "valueFrom":
      {"secretKeyRef": {"name": "postgresql-admin-password", "key": "password"}}
    }
  ]}
]}}}}'

Now watch the database come up (you may see the old database terminate as the new deployment config is applied):

oc get pods -w

Creating the Postgres database

We now need to create the database, database user, database user password, and the schema. The first thing we’ll do is create the password, again using the secret API:

oc create secret generic postgres-shopping-cart --from-literal=username=shopping_cart --from-literal=password="$(openssl rand -base64 24)"

To create our database, we’ll need to access Postgres. There are two ways to do this, the first is using port forwarding, where you open a port on your local machine, and then use the psql client installed on your local machine to connect to it. The second is to shell into the Postgres pod using oc rsh, and use the psql client installed on the pod to connect to Postgres. The first approach is a little simpler, since the psql client on your local machine can access SQL scripts locally on your machine, whereas to run a script when you shell into the pod, you will first need to copy the script there using oc rsync.

First, start the port forward:

oc port-forward svc/postgresql 15432:5432 &

This has started it in the background, it will output some logs when the tunnel is established, and each time it receives a new connection.

Now we can just run the psql command to connect as the Postgres admin user. The Postgres image we’re using is configured to trust all connections from localhost, and since the port forward command results in connections to it being made on the database as localhost, we can connect as any user without a password. We’ll directly feed it a script to create a database, a user, and grant that user access to just read/write operations on the database, so they won’t be able to execute any DDL statements. Finally, we’ll connect to the database and run the database schema script mentioned before:

psql -h localhost -p 15432 -U postgres <<DDL
CREATE DATABASE shopping_cart;
REVOKE CONNECT ON DATABASE shopping_cart FROM PUBLIC;
CREATE USER shopping_cart WITH PASSWORD '$(oc get secret postgres-shopping-cart -o jsonpath='{.data.password}' | base64 --decode)';
GRANT CONNECT ON DATABASE shopping_cart TO shopping_cart;

\connect shopping_cart;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO shopping_cart;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO shopping_cart;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, USAGE ON SEQUENCES TO shopping_cart;

\include schemas/shopping-cart.sql;
DDL

In the above here document, you can see we’ve loaded the secret that we just created from the secrets API.

Once the schema has been created, you can terminate the port forwarding session by killing it:

kill %1