1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
|
PostgreSQL for Debian
=====================
PostgreSQL is the successor to Postgres95, which in turn succeeded POSTGRES.
PostgreSQL is a relational database with object-oriented extensions. It
implements the greater part of SQL-92 and is intended to implement it in full;
in addition it supports its own extended facilities.
Since the on-disk data format of all major PostgreSQL versions (like 9.1,
9.4, etc.) is incompatible to each other, Debian's PostgreSQL packaging
architecture is designed to maintain clusters of different major versions in
parallel.
This postgresql-common package provides the common infrastructure and all
frontend programs that users and administrators use. The version specific
server and client programs are shipped in postgresql-*-<version> packages.
For a detailed description of the architecture, please see
/usr/share/doc/postgresql-common/architecture.html
First steps for the impatient
-----------------------------
Eventually you will not get around reading at least some parts of the manual,
but if you want to get straight into playing SQL, here are the steps to create
a database user and a database for the Unix user 'joe':
1. Install a database server with the major version of your choice
('postgresql-X.Y', e. g. 'postgresql-9.1'). Preferably the latest
version, which you can get by installing the metapackage
'postgresql'. This will automatically create a default cluster
'main' with the database superuser 'postgres'.
2. Get a shell for the database superuser 'postgres'. If your system
has an active root user, use su:
# su -s /bin/bash postgres
If your system uses sudo to get administrative rights, use sudo instead:
joe$ sudo -u postgres bash
3. In this postgres shell, create a database user with the same name as your
Unix login:
$ createuser -DRS joe
For details about the options, see createuser(1).
4. Create a database "joework" which is owned by "joe":
$ createdb -O joe joework
For details about the options, see createdb(1).
5. Exit the postgres shell.
6. As user joe, you should now be able to connect to your database with
$ psql joework
Cluster management
------------------
For managing clusters, the following commands are provided (each with its own
manual page):
pg_createcluster - Create a new cluster or integrate an existing one into
the postgresql-common architecture.
pg_dropcluster - Completely remove a cluster.
pg_ctlcluster - Control the server process of a cluster (start, stop,
restart).
pg_lsclusters - Show a list of all existing clusters and their status.
pg_upgradecluster - Migrate a cluster from one major version to another one.
pg_renamecluster - Rename a cluster.
Please note that you can of course also use the upstream tools for
creating clusters, such as initdb(1). However, please note that in
this case you cannot expect *any* of above pg_* tools to work, since
they use different configuration settings (SSL, data directories,
etc.) and file locations (e. g.
/etc/postgresql/9.1/main/postgresql.conf). If in doubt, then do *not*
use initdb, but only pg_createcluster. Since merely installing
postgresql-X.Y will already set up a default cluster which is ready to
work, most people do not need to bother about initdb or
pg_createcluster at all.
Port assignment
---------------
Please note that the pg_* tools automatically manage the server ports
unless you specify them manually. The first cluster which is ever
created (by any major version) will run on the default port 5432, and
each new cluster will use the next higher free one.
E. g. if you first install "postgresql-9.1" on a clean system, the
default 9.1/main cluster will run on port 5432. If you then create
another 9.1 cluster, or install the "postgresql-9.4" package, that new
one will run on 5433.
Please use "pg_lsclusters" for displaying the cluster <-> port
mapping, and please have a look at the pg_createcluster manpage (the
--port option) for details.
Default clusters and upgrading
------------------------------
When installing a postgresql-X.Y package from scratch, a default
cluster 'main' will automatically be created. This operation is
equivalent to doing 'pg_createcluster X.Y main --start'.
Due to this default cluster, an immediate attempt to upgrade an
earlier 'main' cluster to a new version will fail and you need to
remove the newer default cluster first. E. g., if you have
postgresql-9.1 installed and want to upgrade to 9.4, you first install
postgresql-9.4:
apt-get install postgresql-9.4
Then drop the default 9.4 cluster that was just created:
pg_dropcluster 9.4 main --stop
And then upgrade the 9.1 cluster to the latest installed version (e. g. 9.4):
pg_upgradecluster 9.1 main
SSL
---
The PostgreSQL server packages support SSL, which provides encrypted and
authenticated network communication. SSL should be used if you have an
untrusted network between a database server and a client and these exchange
security sensitive data like passwords or confidential database contents.
postgresql-common makes use of the 'snakeoil' SSL certificate that is
generated by the ssl-cert package, so that SSL works out of the box.
When a cluster is created with pg_createcluster, SSL support will
automatically be enabled in that cluster, and symlinks "server.key"
and "server.crt" are created in the data directory which point to the
snakeoil certificate in /etc/ssl; therefore all clusters use the same
certificate by default. Of course you can replace these symlinks by
cluster-specific certificates.
In addition, if /etc/postgresql-common/root.crt exists, a symbolic
link "root.crt" will be created in the data directory.
/etc/postgresql-common/root.crt is a dummy file by default, so that
client-side authentication is not performed. To enable it, you should
add some root certificates to it. A reasonable choice is to just
symlink the file to /etc/ssl/certs/ssl-cert-snakeoil.pem; in this
case, client certificates need to be signed by the snakeoil
certificate, which might be desirable in many cases. See
/usr/share/doc/postgresql-doc-9.1/html/ssl-tcp.html
for details (in package postgresql-doc).
Further documentation
---------------------
All commands shipped by postgresql-common have detailed manpages. See
postgresql-common(7) for the documentation of the database client program
wrapping, and user_clusters(5) and postgresqlrc(5) for the cluster
configuration.
The documentation of the database server and client functions, SQL commands,
modules, etc. documented is shipped in the per-version packages
postgresql-doc-<version>.
|
