This is not a config file. Just a few notes to allow proper configuration
of the PostgreSQL database server.

1.1) Disable the "main" DB cluster:
    systemctl stop postgresql@9.6-main.service
    systemctl disable postgresql@9.6-main.service

1.2) Create a new DB cluster per stage (as root):
    pg_createcluster --datadir=/srv/postgresql/data/9.6/devl --locale=fr_FR.UTF-8 9.6 devl
    pg_createcluster --datadir=/srv/postgresql/data/9.6/test --locale=fr_FR.UTF-8 9.6 test
    pg_createcluster --datadir=/srv/postgresql/data/9.6/prod --locale=fr_FR.UTF-8 9.6 prod
    pg_lsclusters
        Ver Cluster Port Status Owner    Data directory                Log file
        9.6 devl    5433 down   postgres /srv/postgresql/data/9.6/devl /var/log/postgresql/postgresql-9.6-devl.log
        9.6 main    5432 down   postgres /var/lib/postgresql/9.6/main  /var/log/postgresql/postgresql-9.6-main.log
        9.6 prod    5435 down   postgres /srv/postgresql/data/9.6/prod /var/log/postgresql/postgresql-9.6-prod.log
        9.6 test    5434 down   postgres /srv/postgresql/data/9.6/test /var/log/postgresql/postgresql-9.6-test.log

2) Configure Postgres authorization (pg_hba.conf):
* each cluster must be configured : /etc/postgresql/?.?/*/pg_hba.conf
    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # >>> Doloforge administrative login by UNIX sockets: members of the Postgres group "doloforge" are allowed
    # Be sure that /var/lib/postgres/data/global/pg_group contains the users of the group (may not)
    # If not : "GRANT "doloforge_adm" TO "nhuillard";"
    local   all         +doloforge_adm                                  peer
    # All other connections by UNIX sockets : doloforge denied
    local   all         all                                             reject
    # All IPv4/6 connections from localhost (standalone)
    host    all         all         127.0.0.1/32                        ident
    host    all         all         ::1/128                             ident
    # All IPv4 connections from the application servers (devl, test, prod)
    host    all         all         172.16.0.0/13                       ident
    # <<< End Doloforge config

    # "local" is for Unix domain socket connections only
* authorize TCP/IP connections in (/etc/postgresql/?.?/*/postgresql.conf:listen_addresses = '*'), if more than 127.0.0.1 is needed
    listen_addresses = '*'  # Doloforge setup
    port = 5434             # (change requires restart)
* restart every DB cluster
    systemctl start postgresql@9.6-devl.service
    systemctl start postgresql@9.6-test.service
    systemctl start postgresql@9.6-prod.service 
    pg_lsclusters
        Ver Cluster Port Status Owner    Data directory                Log file
        9.6 devl    5433 online postgres /srv/postgresql/data/9.6/devl /var/log/postgresql/postgresql-9.6-devl.log
        9.6 main    5432 down   postgres /var/lib/postgresql/9.6/main  /var/log/postgresql/postgresql-9.6-main.log
        9.6 prod    5435 online postgres /srv/postgresql/data/9.6/prod /var/log/postgresql/postgresql-9.6-prod.log
        9.6 test    5434 online postgres /srv/postgresql/data/9.6/test /var/log/postgresql/postgresql-9.6-test.log

3) Add Doloforge user/group and admins in each Postgres cluster:
* doloforge system users and groups (to enable backups, create DBs, etc.):
    sudo -u postgres -s bash
    psql --cluster 9.6/devl template1
        CREATE ROLE "doloforge_adm" CREATEDB CREATEROLE;
        CREATE ROLE "doloforge" SUPERUSER LOGIN CREATEDB CREATEROLE IN GROUP "doloforge_adm";
        CREATE ROLE "root" SUPERUSER LOGIN CREATEDB CREATEROLE IN GROUP "doloforge_adm";
* web-apps log-in as www-data:
        CREATE ROLE "www-data" LOGIN;
* for each doloforge administrator (SUPERUSER because he must add extensions to DBs):
        CREATE ROLE "nhuillard" SUPERUSER LOGIN IN GROUP "doloforge_adm";
        CREATE DATABASE "nhuillard" WITH OWNER "nhuillard";
* dolodrome shared group for multi-client DB:
        CREATE ROLE dolodrome_shr WITH NOLOGIN;
        GRANT dolodrome_shr TO dolomede WITH ADMIN OPTION;
        GRANT dolodrome_shr TO nhuillard;

4) various Postgres admin commands:
    pg_dropcluster --stop 9.6 main
    pg_createcluster --datadir=/srv/postgresql/data/9.6/main --locale=fr_FR.UTF-8 9.6 main
    pg_ctlcluster 9.6 main start|restart|reload
    pg_lsclusters



------
Old notes...

1) Create a new database cluster:
* move "main" DB cluster from /var to /srv    mkdir -p /srv/postgresql/data
        or, on a standby cluster configuration (/etc/postgresql/ is csync2'ed; /srv/.db/ is drbd'ed)
            mkdir -p /srv/.db/postgresql/data
            ln -s .db/postgresql /srv/postgresql
            chown postgres /var/log/postgresql/
    pg_dropcluster --stop 8.1 main
    pg_createcluster --datadir=/srv/postgresql/data/8.1/main --locale=fr_FR.UTF-8 8.1 main
    pg_ctlcluster 8.1 main start
    pg_lsclusters
        Version Cluster   Port Status Owner    Data directory                     Log file
        7.4     main      5432 online postgres /srv/postgresql/data/7.4/main      /var/log/postgresql/postgresql-7.4-main.log
        8.1     main      5433 online postgres /srv/postgresql/data/8.1/main      /var/log/postgresql/postgresql-8.1-main.log


2) Configure Postgres authorization (pg_hba.conf):
* each cluster must be configured : /etc/postgresql/8.1/main/pg_hba.conf
    # >>> Doloforge administrative login by UNIX sockets: members of the Postgres group "doloforge" are allowed
    # Be sure that /var/lib/postgres/data/global/pg_group contains the users of the group (may not)
    # If not : "GRANT "doloforge_adm" TO "nhuillard";"
    local   all         +doloforge_adm                                  peer
    # All other connections by UNIX sockets : doloforge denied
    local   all         all                                             reject
    # All IPv4 connections from localhost (devl) / APs (test+prod)
    host    all         all         127.0.0.1/32                        ident
    host    all         all         ::1/128                             ident
    host    all         all         10.0.1.32/27                        ident
    host    all         all         10.0.1.64/27                        ident
    # <<< End Doloforge config
* recharger Postgres après avoir modifié les autorisations
    pg_ctlcluster 8.1 main reload
* authorize TCP/IP connections in (listen_addresses=...), if more than 127.0.0.1 is needed
    vi /etc/postgresql/8.1/main/postgresql.conf
    pg_ctlcluster 8.1 main restart

3) Add Doloforge user/group and admins in the Postgres instance (or cluster, in Debian etch):
* doloforge system users and groups
    sudo -u postgres -s bash
    psql --cluster 9.6/standalone template1
        CREATE ROLE "doloforge_adm" CREATEDB CREATEROLE;
        -- to enable backups
        CREATE ROLE "doloforge" SUPERUSER LOGIN CREATEDB CREATEROLE IN GROUP "doloforge_adm";
        -- for the doloforge command to create DBs, etc.
        CREATE ROLE "root" SUPERUSER LOGIN CREATEDB CREATEROLE IN GROUP "doloforge_adm";
        CREATE ROLE "www-data" LOGIN;
* for each doloforge administrator
    sudo -u postgres -s bash
    psql --cluster 9.6/standalone template1
        CREATE ROLE "nhuillard" LOGIN IN GROUP "doloforge_adm";
        -- main administrator must add extensions to DBs
        ALTER ROLE "nhuillard" WITH SUPERUSER;
        CREATE DATABASE "nhuillard" WITH OWNER "nhuillard";
* nearly-one-liner :
    postgres@macaret:/root$ psql --cluster 9.6/devl template1
    template1=# CREATE ROLE "doloforge_adm" CREATEDB CREATEROLE; CREATE ROLE "doloforge" SUPERUSER LOGIN CREATEDB CREATEROLE IN GROUP "doloforge_adm"; CREATE ROLE "root" SUPERUSER LOGIN CREATEDB CREATEROLE IN GROUP "doloforge_adm"; CREATE ROLE "www-data" LOGIN; CREATE ROLE "nhuillard" LOGIN IN GROUP "doloforge_adm"; ALTER ROLE "nhuillard" WITH SUPERUSER; CREATE DATABASE "nhuillard" WITH OWNER "nhuillard";


4) Create projects databases
* simply let Doloforge do...
    doloforge config --update
* or, for each new project (connect as an administrator)
    psql --cluster 8.1/main template1
        CREATE ROLE "internal_adm";
        CREATE ROLE "internal" LOGIN IN GROUP "internal_adm";
        CREATE DATABASE "internal_sandbox" WITH OWNER "internal_adm" ENCODING 'UNICODE';
    psql --cluster 8.1/main internal_sandbox
        COMMENT ON DATABASE "internal_sandbox" IS 'Sandbox project for developer training';
* FIXME : ROLE and permissions on 8.1 (USER/GROUP -> ROLE ; "le rôle «www-data» n'est pas autorisé à se connecter")




From /usr/share/doc/postgresql-7.4/README.Debian.gz
    66 Cluster management
    67 ------------------
    68 For managing clusters, the following commands are provided (each with its own
    69 manual page):
    70
    71    pg_createcluster  - Create a new cluster or integrate an existing one into
    72                        the postgresql-common architecture.
    73    pg_dropcluster    - Completely remove a cluster.
    74    pg_ctlcluster     - Control the server process of a cluster (start, stop,
    75                        restart).
    76    pg_lsclusters     - Show a list of all existing clusters and their status.
    77    pg_upgradecluster - Migrate a cluster from one major version to another one.

