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-*- 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-.