Description

PostgreSQL is a powerful, open source object-relational database system.
It has more than 15 years of active development and a proven
architecture that has earned it a strong reputation for reliability,
data integrity, and correctness. It is fully ACID compliant, has full
support for foreign keys, joins, views, triggers, and stored procedures
(in multiple languages). It includes most SQL:2008 data types, including
INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP.
It also supports storage of binary large objects, including pictures,
sounds, or video. It has native programming interfaces for C/C++, Java,
.Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional
documentation (http://www.postgresql.org/docs/manuals/).

Overview

excerpt from http://www.postgresql.org/about/

PostgreSQL is a powerful, open source object-relational database system. It
has more than 15 years of active development and a proven architecture that has
earned it a strong reputation for reliability, data integrity, and correctness.
It is fully ACID compliant, has full support for foreign keys, joins, views,
triggers, and stored procedures (in multiple languages). It includes most
SQL:2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE,
INTERVAL, and TIMESTAMP. It also supports storage of binary large objects,
including pictures, sounds, or video. It has native programming interfaces for
C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and
exceptional documentation.

An enterprise class database, PostgreSQL boasts sophisticated features such as
Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces,
asynchronous replication, nested transactions (savepoints), online/hot backups,
a sophisticated query planner/optimizer, and write ahead logging for fault
tolerance. It supports international character sets, multibyte character
encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and
formatting. It is highly scalable both in the sheer quantity of data it can
manage and in the number of concurrent users it can accommodate. There are
active PostgreSQL systems in production environments that manage in excess of 4
terabytes of data.

Usage

This charm supports several deployment models:

  • A single service containing one unit. This provides a 'standalone'
    environment.

  • A service containing multiple units. One unit will be a 'master', and every
    other unit is a 'hot standby'. The charm sets up and maintains replication
    for you, using standard PostgreSQL streaming replication.

To setup a single 'standalone' service::

juju deploy postgresql pg-a

Scale Out Usage

To replicate this 'standalone' database to a 'hot standby', turning the
existing unit into a 'master'::

juju add-unit pg-a

To deploy a new service containing a 'master' and two 'hot standbys'::

juju deploy -n 3 postgresql pg-b

You can remove units as normal. If the master unit is removed, failover occurs
and the most up to date 'hot standby' is promoted to 'master'. The
'db-relation-changed' and 'db-admin-relation-changed' hooks are fired, letting
clients adjust::

juju remove-unit pg-b/0

To setup a client using a PostgreSQL database, in this case a vanilla Django
installation listening on port 8080::

juju deploy postgresql juju deploy python-django juju deploy gunicorn juju

add-relation python-django postgresql:db juju add-relation python-django
gunicorn juju expose python-django

Known Limitations and Issues

  • Do not attempt to relate client charms to a PostgreSQL service containing
    multiple units unless you know the charm supports a replicated service.

  • You cannot host multiple units in a single juju container. This is
    problematic as some PostgreSQL features, such as tablespaces, use user
    specified absolute paths.

Interacting with the Postgresql Service

At a minimum, you just need to join a the db relation, and a user and
database will be created for you. For more complex environments, you can
provide the database name allowing multiple services to share the same
database. A client may also wish to defer its setup until the unit name is
listed in allowed-units, to avoid attempting to connect to a database before
it has been authorized.

The db-admin relation may be used similarly to the db relation. The
automatically generated user for db-admin relations is a PostgreSQL
superuser.

During db-relation-joined

the client service provides:

  • database: Optional. The name of the database to use. The postgresql service
    will create it if necessary. If your charm sets this, then it must wait
    until a matching database value is presented on the PostgreSQL side of
    the relation (ie. relation-get database returns the value you set).
  • roles: Optional. A comma separated list of database roles to grant the
    database user. Typically these roles will have been granted permissions to
    access the tables and other database objects. Do not grant permissions
    directly to juju generated database users, as the charm may revoke them.

During db-relation-changed

the postgresql service provides:

  • host: the host to contact.
  • database: a regular database.
  • port: the port PostgreSQL is listening on.
  • user: a regular user authorized to read the database.
  • password: the password for user.
  • state: 'standalone', 'master' or 'hot standby'.
  • allowed-units: space separated list of allowed clients (unit name). You
    should check this to determine if you can connect to the database yet.

During db-admin-relation-changed

the postgresql service provides:

  • host: the host to contact
  • port: the port PostgreSQL is listening on
  • user: a created super user
  • password: the password for user
  • state: 'standalone', 'master' or 'hot standby'
  • allowed-units: space separated list of allowed clients (unit name). You
    should check this to determine if you can connect to the database yet.

For replicated database support

A PostgreSQL service may contain multiple units (a single master, and
optionally one or more hot standbys). The client charm can tell which
unit in a relation is the master and which are hot standbys by
inspecting the 'state' property on the relation, and it needs to be
aware of how many units are in the relation by using the 'relation-list'
hook tool.

If there is a single PostgreSQL unit related, the state will be
'standalone'. All database connections of course go to this unit.

If there is more than one PostgreSQL unit related, the client charm
must only use units with state set to 'master' or 'hot standby'.
The unit with 'master' state can accept read and write connections. The
units with 'hot standby' state can accept read-only connections, and
any attempted writes will fail. Units with any other state must not be
used and should be ignored ('standalone' units are new units joining the
service that are not yet setup, and 'failover' state will occur when the
master unit is being shutdown and a new master is being elected).

The client charm needs to watch for state changes in its
relation-changed hook. New units may be added to a single unit service,
and the client charm must stop using existing 'standalone' unit and wait
for 'master' and 'hot standby' units to appear. Units may be removed,
possibly causing a 'hot standby' unit to be promoted to a master, or
even having the service revert to a single 'standalone' unit.

Example client hooks

Python::

import sys
from charmhelpers.core.hookenv import (
    Hooks, config, relation_set, relation_get,
    local_unit, related_units, remote_unit)

hooks = Hooks()
hook = hooks.hook

@hook
def db_relation_joined():
    relation_set('database', config('database'))  # Explicit database name
    relation_set('roles', 'reporting,standard')  # DB roles required

@hook
def db_relation_changed():
    db_changed(related_units())

@hook
def db_relation_departed():
    # Note we currently have to explicitly filter the dying unit
    # from our list of database units due to LP Bug #1192433
    db_changed(
        unit for unit in related_units() if unit != remote_unit())

@hook
def db_relation_broken():
    db_changed([])

def db_changed(active_db_units):
    # Rather than try to merge in just this particular database
    # connection that triggered the hook into our existing connections,
    # it is easier to iterate over all active related databases and
    # reset the entire list of connections.
    conn_str_tmpl = "dbname={dbname} user={user} host={host} port={port}"
    master_conn_str = None
    slave_conn_strs = []
    for db_unit in active_db_units:
        if relation_get('database', db_unit) != config('database'):
            continue  # Not yet acknowledged requested database name.

        allowed_units = relation_get('allowed-units') or ''  # May be None
        if local_unit() not in allowed_units.split():
            continue  # Not yet authorized.

        conn_str = conn_str_tmpl.format(**relation_get(unit=db_unit)
        remote_state = relation_get('state', db_unit)

        if remote_state == 'standalone' and len(active_db_units) == 1:
            master_conn_str = conn_str
        elif relation_state == 'master':
            master_conn_str = conn_str
        elif relation_state == 'hot standby':
            slave_conn_strs.append(conn_str)

    update_my_db_config(master=master_conn_str, slaves=slave_conn_strs)

if __name__ == '__main__':
    hooks.execute(sys.argv)

Contact Information

PostgreSQL

Configuration

autovacuum_vacuum_scale_factor
(float) Fraction of table size before vacuum
0.2
log_disconnections
(boolean) Log disconnections
version
(string) Version of PostgreSQL that we want to install
os_username
(string) EXPERIMENTAL. OpenStack Swift username.
encoding
(string) Default encoding used to store text in this service. Can only be set when deploying the first unit of a service.
UTF-8
locale
(string) Locale of service, defining language, default collation order, and default formatting of numbers, currency, dates & times. Can only be set when deploying the first unit of a service.
C
log_min_duration_statement
(int) -1 is disabled, 0 logs all statements and their durations, > 0 logs only statements running at least this number of milliseconds
-1
nagios_context
(string) Used by the nrpe-external-master subordinate charm. A string that will be prepended to instance name to set the host name in nagios. So for instance the hostname would be something like: juju-postgresql-0 If you're running multiple environments with the same services in them this allows you to differentiate between them.
juju
os_tenant_name
(string) EXPERIMENTAL. OpenStack Swift tenant name.
standard_conforming_strings
(boolean) Standard conforming strings
True
log_lock_waits
(boolean) log lock waits >= deadlock_timeout
streaming_replication
(boolean) EXPERIMENTAL. Enable streaming replication. Normally, streaming replication is always used, and any log shipping configured is used as a fallback. Turning this off without configuring log shipping is an error.
True
archive_mode
(boolean) Enable archiving of WAL files using the command specified by archive_command. If archive_mode is enabled and archive_command not set, then archiving is deferred until archive_command is set and the WAL files will accumulate.
os_password
(string) EXPERIMENTAL. OpenStack Swift password.
checkpoint_segments
(int) in logfile segments, min 1, 16MB each
3
max_connections
(int) Maximum number of connections to allow to the PG database
100
hot_standby
(boolean) Hot standby or warm standby. When True, queries can be run against the database when in recovery or standby mode (ie. replicated). Overridden by juju when master/slave relations are used.
volume-ephemeral-storage
(boolean) If false, a configure-error state will be raised if volume-map[$JUJU_UNIT_NAME] is not set (see "volume-map" below) - see "volume-map" below. If true, service units won't try to use "volume-map" (and related variables) to mount and use external (EBS) volumes, thus storage lifetime will equal VM, thus ephemeral. YOU'VE BEEN WARNED.
True
listen_port
(int) Port to listen on
5432
backup_retention_count
(int) Number of recent backups to retain.
7
performance_tuning
(string) Possible values here are "auto" or "manual". If we set "auto" then the charm will attempt to automatically tune all the performance parameters for kernel_shmall, kernel_shmmax, shared_buffers and effective_cache_size below, unless those config values are explicitly set. If manual, then it will use the defaults below unless set. "auto" gathers information about the node on which you are deployed and tries to make intelligent guesses about what tuning parameters to set based on available RAM and CPU under the assumption that it's the only significant service running on this node.
auto
advisory_lock_restart_key
(int) An advisory lock key used internally by the charm. You do not need to change it unless it happens to conflict with an advisory lock key being used by your applications.
765
volume-dev-regexp
(string) Block device for attached volumes as seen by the VM, will be "scanned" for an unused device when "volume-map" is valid for the unit.
/dev/vd[b-z]
wal_keep_segments
(int) Number of old WAL files to keep, providing a larger buffer for streaming hot standbys to catch up from when lagged. Each WAL file is 16MB in size. The WAL files are the buffer of how far a hot standby can lag behind the master, and replication fails if this buffer is overrun. When this service is replicated, the larger value of wal_keep_segments and replicated_wal_keep_segments is used.
autovacuum_analyze_threshold
(int) min number of row updates before analyze
50
archive_command
(string) Command used to archive WAL files when archive_mode is set and wal_level > minimal.
cluster_name
(string) Name of the cluster we want to install the DBs into
main
log_connections
(boolean) Log connections
swiftwal_log_shipping
(boolean) EXPERIMENTAL. Archive WAL files into Swift. If swiftwal_backup_schedule is set, this allows point-in-time recovery and WAL files are removed automatically with old backups. If swiftwal_backup_schedule is not set then WAL files are never removed. Enabling this option will override the archive_mode and archive_command settings.
autovacuum_analyze_scale_factor
(float) Fraction of table size before analyze
0.1
log_checkpoints
(boolean) Log checkpoints
wal_buffers
(string) min 32kB, -1 sets based on shared_buffers (change requires restart)
-1
maintenance_work_mem
(string) Maintenance working memory
1MB
extra-packages
(string) Extra packages to install on the postgresql service units.
replicated_wal_keep_segments
(int) Value of wal_keep_segments used when this service is replicated. This setting only exists to provide a sane default when replication is requested (so it doesn't fail) and nobody bothered to change the wal_keep_segments setting.
5000
log_timezone
(string) Log timezone
UTC
swiftwal_backup_retention
(int) EXPERIMENTAL. Number of recent base backups to retain. You need enough space in Swift for this many backups plus one more, as an old backup will only be removed after a new one has been successfully made to replace it.
2
search_path
(string) Comma separated list of schema names for the default SQL search path.
"$user",public
temp_buffers
(string) The maximum number of temporary buffers used by each database session.
1MB
hot_standby_feedback
(boolean) Hot standby feedback, informing a master about in progress transactions on a streaming hot standby and allowing the master to defer cleanup and avoid query cancelations on the hot standby.
backup_schedule
(string) Cron-formatted schedule for database backups.
13 4 * * *
os_auth_url
(string) EXPERIMENTAL. OpenStack Swift authentication URL.
max_wal_senders
(int) Maximum number of hot standbys that can connect using streaming replication. Set this to the expected maximum number of hot standby units to avoid unnecessary blocking and database restarts. Overridden by juju if necessary.
volume-map
(string) YAML map as e.g. "{ postgres/0: vol-0000010, postgres/1: vol-0000016 }". Service units will raise a "configure-error" condition if no volume-map value is set for it - it's expected a human to set it properly to resolve it.
dumpfile_location
(string) Path to a dumpfile to load into DB when service is initiated.
None
backup_dir
(string) Directory to place backups in
/var/lib/postgresql/backups
extra_archives
(string) Extra archives to add, space separated. Supports ppa:, http:, cloud: URIs, as well as other schemes and keywords supported by charmhelpers.fetch.add_source() such as "proposed".
wal_level
(string) 'minimal', 'archive' or 'hot_standby'. Defines how much information is written to the WAL. Set to 'minimal' for stand alone databases and 'hot_standby' for replicated setups. Overridden by juju when replication s used.
minimal
swiftwal_container_prefix
(string) EXPERIMENTAL. Swift container prefix for SwiftWAL to use. Must be set if any SwiftWAL features are enabled.
listen_ip
(string) IP to listen on
*
autovacuum
(boolean) Autovacuum should almost always be running.
True
ssl
(string) Whether PostgreSQL should talk SSL
True
swiftwal_backup_schedule
(string) EXPERIMENTAL. Cron-formatted schedule for SwiftWAL database backups.
kernel_shmmax
(int) The maximum size, in bytes, of a shared memory segment.
kernel_shmall
(int) Total amount of shared memory available, in bytes.
config_change_command
(string) The command to run whenever config has changed. Accepted values are "reload" or "restart" - any other value will mean neither is executed after a config change (which may be desired, if you're running a production server and would rather handle these out of band). Note that postgresql will still need to be reloaded whenever authentication and access details are updated, so disabling either doesn't mean PostgreSQL will never be reloaded.
reload
shared_buffers
(string) The amount of memory the database server uses for shared memory buffers. This string should be of the format '###MB'.
log_autovacuum_min_duration
(int) -1 disables, 0 logs all actions and their durations, > 0 logs only actions running at least this number of milliseconds.
-1
random_page_cost
(float) Random page cost
4
effective_cache_size
(string) Effective cache size is an estimate of how much memory is available for disk caching within the database. (50% to 75% of system memory). This string should be of the format '###MB'.
work_mem
(string) Working Memory
1MB
autovacuum_vacuum_cost_delay
(string) Default vacuum cost delay for autovacuum, in milliseconds; -1 means use vacuum_cost_delay
20ms
log_line_prefix
(string) special values: %a = application name %u = user name %d = database name %r = remote host and port %h = remote host %p = process ID %t = timestamp without milliseconds %m = timestamp with milliseconds %i = command tag %e = SQL state %c = session ID %l = session line number %s = session start timestamp %v = virtual transaction ID %x = transaction ID (0 if none) %q = stop here in non-session processes %% = '%' e.g. '<%u%%%d> '
%t
admin_addresses
(string) A comma-separated list of IP Addresses (or single IP) admin tools like pgAdmin3 will connect from, this is most useful for developers running juju in local mode who need to connect tools like pgAdmin to a postgres. The IP addresses added here will be included in the pg_hba.conf file allowing ip connections to all databases on the server from the given using md5 password encryption.