postgresql #5

Description

PostgreSQL is a fully featured object-relational database management
system. It supports a large part of the SQL standard and is designed
to be extensible by users in many aspects. Some of the features are:
ACID transactions, foreign keys, views, sequences, subqueries,
triggers, user-defined types and functions, outer joins, multiversion
concurrency control. Graphical user interfaces and bindings for many
programming languages are available as well.


Overview

PostgreSQL

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.

  • Multiple services linked using 'master'/'slave' relationships. A
    single service can be the 'master', and multiple services connected
    to this master in a 'slave' role. Each service can contain multiple
    units; the 'master' service will contain a single 'master' unit and
    remaining units all 'hot standby'. The 'slave' services will only
    contain 'hot standby' units. 'Cascading replication is not
    supported', so do not attempt to relate an existing 'slave' service
    as a 'master' to another service.

To setup a single 'standalone' service::

juju deploy postgresql pg-a

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 a 'hot standby'::

juju deploy -n 2 postgresql pg-b

To relate a PostgreSQL service as a 'slave' of another PostgreSQL service.
Caution - this destroys the existing databases in the pg-b service::

juju add-relation pg-a:master pg-b:slave

To setup a client using a PostgreSQL database, in this case OpenERP and
its web front end. Note that OpenERP requires an administrative level
connection::

juju deploy postgresql
juju deploy postgresql pg-standby
juju deploy openerp-web
juju deploy openerp-server

juju add-relation postgresql:master pg-standby:slave
juju add-relation openerp-server:db postgresql:db-admin
juju add-relation openerp-web openerp-server

juju expose openerp-web
juju expose openerp-server

Restrictions

  • Do not attempt to relate client charms to a PostgreSQL service
    containing multiple units unless you know the charm supports
    a replicated service. You can use a 'master'/'slave' relationship
    to create a redundant copy of your database until the client charms
    are updated.

  • 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

Typically, you just need to join a the db relation, and a user and database
will be created for you. For more advanced uses, you can join the db-admin
relation, and a super user will be created. Using this account, you can
manipulate all other aspects of the database.

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-hosts: 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-hosts: space separated list of allowed clients (unit name).
    You should check this to determine if you can connect to the database yet.

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
9.1
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
standard_conforming_strings
(boolean) Standard conforming strings
True
log_lock_waits
(boolean) log lock waits >= deadlock_timeout
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.
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 is 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
kernel_shmmax
(int) Kernel/shmmax
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 paramaters as below. If manual, then it will use the defaults below unless overridden. "auto" gathers information about the node you're deployed on 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
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
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
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
search_path
(string)
"$user",public
temp_buffers
(string) Temp buffers
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 * * *
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 with add-apt-repository(1).
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
listen_ip
(string) IP to listen on
*
autovacuum
(boolean) Autovacuum should almost always be running.
True
ssl
(string) Whether PostgreSQL should talk SSL
True
backup_retention_count
(int) Number of recent backups to retain.
7
kernel_shmall
(int) Kernel/shmall
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) Shared buffers
8MB
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
128MB
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