postgresql clustering and Debian ================================ Anand Kumria 07 Mar 2006 Introduction ------------ What ~~~~ Each postgresql installation can contain multiple, independent, databases. This is known as a postgresql cluster. The databases within the cluster all share configuration (such as how authentication occurs, what port the postmaster is listening on) as well as any configured storage areas (e.g. tablespaces). Why ~~~ You may want to have the set of databases but in different instances (e.g. 'production', 'test', 'dev'). Rather than having to manipulate the database name across your application, you can connect to each cluster just by specifying a particular port. This kind of setup can also allow you to use the same binaries for testing as you are for production and also know that the hardware you have set aside for development is also capable of handling the load that a testing places on it. Source or Distribution? ----------------------- Invariably when installing postgresql at a client's site you'll need to choose between a source-based approach, or using what the distribution provides. Source ~~~~~~ Positives - You can choose what version to deploy - You can choose when (if) to upgrade - You can choose which optional languages should be supported Negatives - You have to keep up to date with security issues - It is a custom installation which needs to be documented for others - You might mis-specify compilation options with effects not apparent for a while Binary Distributions ~~~~~~~~~~~~~~~~~~~~ Positives - Known to work (reasonably) well - Integrates well with other parts of distribution - Security updates are 'free' - Generally all optional languages are available Negatives - Have to deploy available version of distribution - Required to update / upgrade when the distribution does - May have too many unfamiliar components (e.g. TLS, IPv6) How --- All (future) versions of Debian (testing, unstable) have the package 'postgresql-common', which is installed when postgresql-8.1 is brought in. So `apt-get install -`, where version is either `7.4` or `8.1` to get access to the clustering system. Commands ~~~~~~~~ All standard binary commands are wrapped with 'pg_cluster'/PgCommon.pm This is a perl script (library), which interprets a pseudo argument to the standard commands `--cluster [version]/cluster`. For example, to connect to postgresql 7.4's main cluster: -------- psql --cluster 7.4/main -l ------- If no cluster is specified, the version of postgresql listening on 5432 and the 'main' cluster are the defaults. As well as wrapping the logical commands (such as psql, createdb, etc.), some additional commands are provided to manage things at the cluster level. For example, on a machine with both 7.4 and 8.1 installed: ------ $ pg_lsclusters Version Cluster Port Status Owner Data directory Log file 7.4 main 5432 online postgres /var/lib/postgresql/7.4/main /var/log/postgresql/postgresql-7.4-main.log 8.1 main 5433 online postgres /var/lib/postgresql/8.1/main /var/log/postgresql/postgresql-8.1-main.log ------ As you would expect, clusters can be created, upgraded, dropped and control with the commands `pg_createcluster`, `pg_upgradecluster`, `pg_dropcluster` and `pg_ctlcluster` respectively. Paths ~~~~~ Configuration information and the actual data need to be stored per-cluster instance. Since we are storing both version and cluster name, the path generated is, unsurprisingly enough, composed of both. For configuration files: ------- /etc/postgresql///start.conf /etc/postgresql///pg_hba.conf ------- and likewise, for data areas: ---- /var/lib/postgresql///PG_VERSION /var/lib/postgresql///base ---- What about Right Now? --------------------- While you could the testing and unstable versions of Debian on your production machine -- you probably don't want to. For that you can turn to http://www.backports.org/[backports.org]. http://www.backports.org/[backports.org] only allows uploads only from Debian developers and it rebuilds packages across all the supported architectures of a stable release. Thus by using http://www.backports.org/[backports.org] you can get the features of testing with the stability (security updates, etc.) of stable. Place into `/etc/apt/source.list` the following: ---- deb http://www.backports.org/debian/ sarge-backports main ---- And then into `/etc/apt/preferences`: ---- Package: * Pin: release a=sarge-backports Pin-Priority: 200 ---- Then perform an `apt-get update`, followed by `apt-get install ` which will download and install the package for you. If you are running Debian 3.1 and want to migrate to the clustering system herein, then you would do `apt-get install postgresql-7.4 postgresql-client-7.4 postgresql-contrib-7.4`. If you want to utilise either Perl, Python, R, Ruby or TCL then you would also do `apt-get install postgresql-plperl-7.4 postgresql-plpython-7.4 postgresql-7.4-plr postgresql-7.4-plruby postgresql-pltcl7.4`. You can then upgrade to 'postgresql-8.1' at your own pace by installing the same packages but substituting 8.1 instead of 7.4.