postgres-release

This is a BOSH release for PostgreSQL.

Deploying
Customizing
Contributing
Known Limitation
Upgrading

Deploying

In order to deploy the postgres-release you must follow the standard steps for deploying software with BOSH.

Deploy and run a BOSH director. Please refer to BOSH documentation for instructions on how to do that. Bosh-lite specific instructions can be found here.
Install the BOSH command line Interface (CLI) v2+. Please refer to BOSH CLI documentation. Use the CLI to target your director.

Upload the desired stemcell directly to bosh. bosh.io provides a resource to find and download stemcells.

# Example for bosh-lite
bosh upload-stemcell https://bosh.io/d/stemcells/bosh-warden-boshlite-ubuntu-trusty-go_agent

Upload the latest release from bosh.io:

bosh upload-release https://bosh.io/d/github.com/cloudfoundry/postgres-release

or create and upload a development release:

cd ~/workspace/postgres-release
bosh -n create-release --force && bosh -n upload-release

Generate the manifest. You can provide in input an operation file to customize the manifest:
```
~/workspace/postgres-release/scripts/generate-deployment-manifest-v2 \
-o OPERATION-FILE-PATH > OUTPUT_MANIFEST_PATH
```
You can use the operation file to specify postgres properties or to override the configuration if your BOSH director cloud-config is not compatible.

This example operation file is a great starting point. Note: when using this operation file, you will need to inject pgadmin_database_password at bosh deploy-time, which is a good pattern for keeping credentials out of manifests.

For deployment on bosh-lite, the VM- and disk types defined by the postgres-release can be changed to default (as specified by the cloud-config that comes with bosh-lite) using the following ops file:
```
scripts/generate-deployment-manifest-v2 -o templates/v2/operations/use-lite-types.yml > /tmp/pg.yml
```
You are also provided with options to enable ssl in the PostgreSQL server or to use static ips.

Deploy:

bosh -d DEPLOYMENT_NAME deploy OUTPUT_MANIFEST_PATH

Example, injecting the pgadmin_database_password variable:

bosh -d DEPLOYMENT_NAME deploy -v pgadmin_database_password=foobarbaz OUTPUT_MANIFEST_PATH

Customizing

The table below shows the most significant properties you can use to customize your postgres installation. The complete list of available properties can be found in the spec.

Property	Description
databases.port	The database port. Default: 5432
databases.databases	A list of databases and associated properties to create when Postgres starts
databases.databases[n].name	Database name
databases.databases[n].citext	If `true` the citext extension is created for the db
databases.databases[n].run_on_every_startup	A list of SQL commands run at each postgres start against the given database as `vcap` (DEPRECATED!)
databases.roles	A list of database roles and associated properties to create
databases.roles[n].name	Role name
databases.roles[n].password	Login password for the role. If not provided, SSL certificate authentication is assumed for the user.
databases.roles[n].common_name	The cn attribute of the certificate for the user. It only applies to SSL certificate authentication.
databases.roles[n].permissions	A list of attributes for the role. For the complete list of attributes, refer to ALTER ROLE command options.
databases.tls.certificate	PEM-encoded certificate for secure TLS communication
databases.tls.private_key	PEM-encoded key for secure TLS communication
databases.tls.ca	PEM-encoded certification authority for secure TLS communication. Only needed to let users authenticate with SSL certificate.
databases.max_connections	Maximum number of database connections
databases.log_line_prefix	The postgres `printf` style string that is output at the beginning of each log line. Default: `%m:`
databases.collect_statement_statistics	Enable the `pg_stat_statements` extension and collect statement execution statistics. Default: `false`
databases.additional_config	A map of additional key/value pairs to include as extra configuration properties
databases.monit_timeout	Monit timout in seconds for the postgres job start. Default: `90`.
databases.trust_local_connection	Whether or not postgres must trust local connections. `vcap` is always trusted. It defaults to `true`.
databases.skip_data_copy_in_minor	Whether or not a copy of the data directory is created during PostgreSQL minor upgrades. A copy is created by default.

Note

Removing a database from databases.databases list and deploying again does not trigger a physical deletion of the database in PostgreSQL.
Removing a role from databases.roles list and deploying again does not trigger a physical deletion of the role in PostgreSQL.

Enabling SSL on the PostgreSQL server

PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security. You can enable it by setting the databases.tls.certificate and the databases.tls.private_key properties.

A script is provided that creates a CA, generates a keypair, and signs it with the CA:

./scripts/generate-postgres-certs -n HOSTNAME_OR_IP_ADDRESS

The common name for the server certificate must be set to the DNS hostname if any or to the ip address of the PostgreSQL server. This because in ssl mode 'verify-full', the hostname is matched against the common-name. Refer to PostgreSQL documentation for more details.

You can also use BOSH variables to generate the certificates. See by way of example the operation file used by the manifest generation script.

~/workspace/postgres-release/scripts/generate-deployment-manifest-v2 \
   -s -h HOSTNAME_OR_IP_ADDRESS \
   -o OPERATION-FILE-PATH > OUTPUT_MANIFEST_PATH

Enabling SSL certificate authentication

In order to perform authentication using SSL client certificates, you must not specify a user password and you must configure the following properties:

databases.tls.certificate
databases.tls.private_key
databases.tls.ca

The cn (Common Name) attribute of the certificate will be compared to the requested database user name, and if they match the login will be allowed.

Optionally you can map the common_name to a different database user by specifying property databases.roles[n].common_name.

A script is provided that creates a client certificates:

./scripts/generate-postgres-client-certs --ca-cert <PATH-TO-CA-CERT> --ca-key <PATH-TO-CA-KEY> --client-name <USER_NAME>

Contributing

Contributor License Agreement

Contributors must sign the Contributor License Agreement before their contributions can be merged. Follow the directions here to complete that process.

Developer Workflow

Fork the repository and make a local clone

Create a feature branch from the development branch

cd postgres-release
git checkout develop
git checkout -b feature-branch

Make changes on your branch
Test your changes by running acceptance tests
Push to your fork (git push origin feature-branch) and submit a pull request selecting develop as the target branch. PRs submitted against other branches will need to be resubmitted with the correct branch targeted.

Known Limitations

The postgres-release does not directly support high availability. Even if you deploy more instances, no replication is configured.

Upgrading

Refer to versions.yml in order to assess if you are upgrading to a new PostgreSQL version.

Considerations before deploying

A copy of the database is made for the upgrade, you may need to adjust the persistent disk capacity of the postgres job.
- For major upgrades the copy is always created
- For minor upgrades the copy is created unless the databases.skip_data_copy_in_minor is set to true.
The upgrade happens as part of the pre-start and its duration may vary basing on your env.
- In case of a PostgreSQL minor upgrade a simple copy of the old data directory is made.
- In case of a PostgreSQL major upgrade the pg_upgrade utility is used.
Postgres will be unavailable during this upgrade.

Considerations after a successfull deployment

In case a copy of the old database is kept (see considerations above), the old database is moved to /var/vcap/store/postgres/postgres-previous. The postgres-previous directory will be kept until the next postgres upgrade is performed in the future. You are free to remove this if you have verified the new database works and you want to reclaim the space.

Recovering a failure during deployment

In case of a long upgrade, the deployment may time out; anyway bosh would not stop the actual upgrade process. In this case you can just wait for the upgrade to complete and, only when postgres is up and running, rerun the bosh deploy.

If the upgrade fails:

The old data directory is still available at /var/vcap/store/postgres/postgres-x.x.x where x.x.x is the old PostgreSQL version
The new data directory is at /var/vcap/store/postgres/postgres-y.y.y where y.y.y is the new PostgreSQL version
If the upgrade is a PostgreSQL major upgrade:
- A marker file is kept at /var/vcap/store/postgres/POSTGRES_UPGRADE_LOCK to prevent the upgrade from happening again.
- pg_upgrade logs that may have details of why the migration failed can be found in /var/vcap/sys/log/postgres/postgres_ctl.log

If you want to attempt the upgrade again or to rollback to the previous release, you should remove the new data directory and, if present, the marker file.

chirangaalwis/postgres-release