crunchy-postgres-appdev

PostgreSQL (pronounced “post-gress-Q-L”) is an open source, ACID compliant, relational database management system (RDBMS) developed by a worldwide team of volunteers. The crunchy-postgres-appdev container image is unmodified, open source PostgreSQL packaged and maintained by professionals.

This image is identical to the crunchy-postgres-gis image except it is built specifically for ease of use for application developers. To achieve that we have set reasonable default for some environment variables, remove some functionality needed for a production usage (such as replication and backup). The goal for this image is to get application developers up and going as soon as possible with PostgreSQL with most of the useful extensions and features pre-installed.

THIS IMAGE COMES WITH NO SUPPORT FROM CRUNCHY DATA. Support on this image is through community work and on a good faith basis. If you need support for your containers please contact Crunchy Data to become a customer.

This image should NOT be used for production deployment. It shares most of the same configuration as the crunchy-postgres and the crunchy-postgres-gis image. Therefore, you can use this as a test bed for developing your applications that will eventually be used in the supported containers.

Features

The following features are supported by the crunchy-postgres-appdev container:

  • Kubernetes and OpenShift secrets
  • Custom mounted configuration files (see below)
  • PostGIS
  • PL/R

Packages

The crunchy-postgres-ppdev Docker image contains the following packages (versions vary depending on PostgreSQL version):

  • Latest PostgreSQL
  • Latest PostGIS
  • CentOS7 - publicly available

Environment Variables

Required

Name Default Description
PG_PASSWORD None Set this value to specify the password of the user role, if PG_ROOT_PASSWORD is unset then it will share this password

Optional - Common

Name Default Description
PG_DATABASE None Set this value to create an initial database
PG_PRIMARY_PORT 5432 Set this value to configure the primary PostgreSQL port. It is recommended to use 5432.
PG_USER None Set this value to specify the username of the general user account
PG_ROOT_PASSWORD None Set this value to specify the password of the superuser role. If unset it is the same as the password PG_PASSWORD

Optional - Other

Name Default Description
ARCHIVE_MODE Off Set this value to on to enable continuous WAL archiving
ARCHIVE_TIMEOUT 60 Set to a number (in seconds) to configure archive_timeout in postgresql.conf
CHECKSUMS Off Enables data-checksums during initialization of the database. Can only be set during initial database creation.
CRUNCHY_DEBUG FALSE Set this to true to enable debugging in logs. Note: this mode can reveal secrets in logs.
LOG_STATEMENT none Sets the log_statement value in postgresql.conf
LOG_MIN_DURATION_STATEMENT 60000 Sets the log_min_duration_statement value in postgresql.conf
MAX_CONNECTIONS 100 Sets the max_connections value in postgresql.conf
PG_LOCALE UTF-8 Set the locale of the database
PGAUDIT_ANALYZE None Set this to enable pgaudit_analyze
PGBOUNCER_PASSWORD None Set this to enable pgBouncer support by creating a special pgbouncer user for authentication through the connection pooler.
PGDATA_PATH_OVERRIDE None Set this value to override the /pgdata directory name. By default /pgdata uses hostname of the container. In some cases it may be required to override this with a custom name (such as in a Statefulset)
SHARED_BUFFERS 128MB Set this value to configure shared_buffers in postgresql.conf
TEMP_BUFFERS 8MB Set this value to configure temp_buffers in postgresql.conf
WORK_MEM 4MB Set this value to configure work_mem in postgresql.conf
XLOGDIR None Set this value to configure PostgreSQL to send WAL to the /pgwal volume (by default WAL is stored in /pgdata)
PG_CTL_OPTS None Set this value to supply custom pg_ctl options (ex: -c shared_preload_libraries=pgaudit) during the initialization phase the container start.

Volumes

Name Description
/pgconf Volume used to store custom configuration files mounted to the container.
/pgdata Volume used to store the data directory contents for the PostgreSQL database.

Custom Configuration

The following configuration files can be mounted to the /pgconf volume in the crunchy-postgres container to customize the runtime:

Name Description
ca.crt Certificate of the CA used by the server when using SSL authentication
ca.crl Revocation list of the CA used by the server when using SSL authentication
pg_hba.conf Client authentication rules for the database
pg_ident.conf Mapping of external users (such as SSL certs, GSSAPI, LDAP) to database users
postgresql.conf PostgreSQL settings
server.key Key used by the server when using SSL authentication
server.crt Certificate used by the server when using SSL authentication
setup.sql Custom SQL to execute against the database. Note: only run during the first startup (initialization)

Verifying PL/R

In order to verify the successful initialization of the PL/R extension, the following commands can be run:

create extension plr;
SELECT * FROM plr_environ();
SELECT load_r_typenames();
SELECT * FROM r_typenames();
SELECT plr_array_accum('{23,35}', 42);
CREATE OR REPLACE FUNCTION plr_array (text, text)
RETURNS text[]
AS '$libdir/plr','plr_array'
LANGUAGE 'c' WITH (isstrict);
select plr_array('hello','world');