5
3
*excerpt from http://www.postgresql.org/about/*
7
PostgreSQL is a powerful, open source object-relational database system.
8
It has more than 15 years of active development and a proven
9
architecture that has earned it a strong reputation for reliability,
10
data integrity, and correctness. It is fully ACID compliant, has full
11
support for foreign keys, joins, views, triggers, and stored procedures
12
(in multiple languages). It includes most SQL:2008 data types, including
13
INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP.
14
It also supports storage of binary large objects, including pictures,
15
sounds, or video. It has native programming interfaces for C/C++, Java,
16
.Net, Perl, Python, Ruby, Tcl, ODBC, among others, and [exceptional
17
documentation](http://www.postgresql.org/docs/manuals/).
19
An enterprise class database, PostgreSQL boasts sophisticated features
20
such as Multi-Version Concurrency Control (MVCC), point in time
21
recovery, tablespaces, asynchronous replication, nested transactions
22
(savepoints), online/hot backups, a sophisticated query
23
planner/optimizer, and write ahead logging for fault tolerance. It
24
supports international character sets, multibyte character encodings,
25
Unicode, and it is locale-aware for sorting, case-sensitivity, and
26
formatting. It is highly scalable both in the sheer quantity of data it
27
can manage and in the number of concurrent users it can accommodate.
28
There are active PostgreSQL systems in production environments that
29
manage in excess of 4 terabytes of data.
5
PostgreSQL is a powerful, open source object-relational database system. It
6
has more than 15 years of active development and a proven architecture that has
7
earned it a strong reputation for reliability, data integrity, and correctness.
8
It is fully ACID compliant, has full support for foreign keys, joins, views,
9
triggers, and stored procedures (in multiple languages). It includes most
10
SQL:2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE,
11
INTERVAL, and TIMESTAMP. It also supports storage of binary large objects,
12
including pictures, sounds, or video. It has native programming interfaces for
13
C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and
14
[exceptional documentation](http://www.postgresql.org/docs/manuals/).
16
An enterprise class database, PostgreSQL boasts sophisticated features such as
17
Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces,
18
asynchronous replication, nested transactions (savepoints), online/hot backups,
19
a sophisticated query planner/optimizer, and write ahead logging for fault
20
tolerance. It supports international character sets, multibyte character
21
encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and
22
formatting. It is highly scalable both in the sheer quantity of data it can
23
manage and in the number of concurrent users it can accommodate. There are
24
active PostgreSQL systems in production environments that manage in excess of 4
36
31
- A single service containing one unit. This provides a 'standalone'
39
- A service containing multiple units. One unit will be a 'master',
40
and every other unit is a 'hot standby'. The charm sets up and
41
maintains replication for you, using standard PostgreSQL streaming
34
- A service containing multiple units. One unit will be a 'master', and every
35
other unit is a 'hot standby'. The charm sets up and maintains replication
36
for you, using standard PostgreSQL streaming replication.
45
38
To setup a single 'standalone' service::
47
40
juju deploy postgresql pg-a
50
45
To replicate this 'standalone' database to a 'hot standby', turning the
51
46
existing unit into a 'master'::
57
52
juju deploy -n 3 postgresql pg-b
59
You can remove units as normal. If the master unit is removed, failover
60
occurs and the most up to date 'hot standby' is promoted to 'master'.
61
The 'db-relation-changed' and 'db-admin-relation-changed' hooks are
62
fired, letting clients adjust::
54
You can remove units as normal. If the master unit is removed, failover occurs
55
and the most up to date 'hot standby' is promoted to 'master'. The
56
'db-relation-changed' and 'db-admin-relation-changed' hooks are fired, letting
64
59
juju remove-unit pg-b/0
67
To setup a client using a PostgreSQL database, in this case a vanilla
68
Django installation listening on port 8080::
70
juju deploy postgresql
71
juju deploy python-django
73
juju add-relation python-django postgresql:db
74
juju add-relation python-django gunicorn
75
juju expose python-django
80
- Do not attempt to relate client charms to a PostgreSQL service
81
containing multiple units unless you know the charm supports
62
To setup a client using a PostgreSQL database, in this case a vanilla Django
63
installation listening on port 8080::
65
juju deploy postgresql juju deploy python-django juju deploy gunicorn juju
66
add-relation python-django postgresql:db juju add-relation python-django
67
gunicorn juju expose python-django
70
## Known Limitations and Issues
72
- Do not attempt to relate client charms to a PostgreSQL service containing
73
multiple units unless you know the charm supports a replicated service.
84
75
- You cannot host multiple units in a single juju container. This is
85
problematic as some PostgreSQL features, such as tablespaces, use
86
user specified absolute paths.
76
problematic as some PostgreSQL features, such as tablespaces, use user
77
specified absolute paths.
88
79
# Interacting with the Postgresql Service
90
81
At a minimum, you just need to join a the `db` relation, and a user and
91
database will be created for you. For more complex environments,
92
you can provide the `database` name allowing multiple services to share
93
the same database. A client may also wish to defer its setup until the
94
unit name is listed in `allowed-units`, to avoid attempting to connect
95
to a database before it has been authorized.
82
database will be created for you. For more complex environments, you can
83
provide the `database` name allowing multiple services to share the same
84
database. A client may also wish to defer its setup until the unit name is
85
listed in `allowed-units`, to avoid attempting to connect to a database before
86
it has been authorized.
97
The `db-admin` relation may be used similarly to the `db` relation.
98
The automatically generated user for `db-admin` relations is a
88
The `db-admin` relation may be used similarly to the `db` relation. The
89
automatically generated user for `db-admin` relations is a PostgreSQL
101
92
## During db-relation-joined
103
94
### the client service provides:
105
- `database`: Optional. The name of the database to use. The postgresql
106
service will create it if necessary.
107
- `roles`: Optional. A comma separated list of database roles to grant
108
the database user. Typically these roles will have been granted
109
permissions to access the tables and other database objects.
110
Do not grant permissions directly to juju generated database
111
users, as the charm may revoke them.
96
- `database`: Optional. The name of the database to use. The postgresql service
97
will create it if necessary.
98
- `roles`: Optional. A comma separated list of database roles to grant the
99
database user. Typically these roles will have been granted permissions to
100
access the tables and other database objects. Do not grant permissions
101
directly to juju generated database users, as the charm may revoke them.
113
103
## During db-relation-changed
132
122
- `user`: a created super user
133
123
- `password`: the password for `user`
134
124
- `state`: 'standalone', 'master' or 'hot standby'
135
- `allowed-units`: space separated list of allowed clients (unit name).
136
You should check this to determine if you can connect to the database yet.
125
- `allowed-units`: space separated list of allowed clients (unit name). You
126
should check this to determine if you can connect to the database yet.
138
### For clustered support
139
In order for client charms to support replication the client will need to be
140
aware when relation-list reports > 1 unit of postgresql related:
128
### For clustered support In order for client charms to support replication the
129
client will need to be aware when relation-list reports > 1 unit of postgresql
141
131
- When > 1 postgresql units are related:
142
- if the client charm needs database write access, they will ignore
143
all "standalone", "hot standby" and "failover" states as those will
144
likely come from a standby unit (read-only) during standby install,
132
- if the client charm needs database write access, they will ignore all
133
"standalone", "hot standby" and "failover" states as those will likely
134
come from a standby unit (read-only) during standby install, setup or teardown
146
135
- If read-only access is needed for a client, acting on
147
136
db-admin-relation-changed "hot standby" state will provide you with a
148
readonly replicated copy of the db
137
readonly replicated copy of the db
149
138
- When 1 postgresql unit is related:
150
- watch for updates to the db-admin-relation-changed with "standalone" state
139
- watch for updates to the db-admin-relation-changed with "standalone"
142
# Contact Information
146
- [PostgreSQL website](http://www.postgresql.org/)
147
- [PostgreSQL bug submission
148
guidelines](http://www.postgresql.org/docs/9.2/static/bug-reporting.html)
149
- [PostgreSQL Mailing List](http://www.postgresql.org/list/)