
==========
PostgreSQL
==========

PostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, Microsoft Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software. PostgreSQL is developed by the PostgreSQL Global Development Group, consisting of a handful of volunteers employed and supervised by companies such as Red Hat and EnterpriseDB.

Support
=======

* required by: [redmine](../master/redmine)
* service versions: 9.1
* operating systems: Ubuntu 12.04

Sample pillars
==============

Single database server with empty database
------------------------------------------

.. code-block:: yaml

    postgresql:
      server:
        enabled: true
        version: 9.1
        bind:
          address: 127.0.0.1
          port: 5432
          protocol: tcp
        clients:
        - 127.0.0.1
        database:
          name: 'databasename'
          encoding: 'UTF8'
          locale: 'cs_CZ'
          user:
            name: 'username'
            password: 'password'
            host: 'localhost'
            rights: 'all privileges'

Single database server with prepopulated database
-------------------------------------------------

.. code-block:: yaml

    postgresql:
      server:
        enabled: true
        version: 9.1
        bind:
        - address: 127.0.0.1
          port: 5432
          protocol: tcp
        clients:
        - 127.0.0.1
        database:
          name: 'databasename'
          encoding: 'UTF8'
          locale: 'cs_CZ'
          initial_data:
            engine: backupninja
            source: backup.host
            host: original-host-name
            database: original-database-name
          user:
            name: 'username'
            password: 'password'
            host: 'localhost'
            rights: 'all privileges'

User with createdb privileges
-----------------------------

.. code-block:: yaml

    postgresql:
      server:
        enabled: true
        version: 9.1
        bind:
          address: 127.0.0.1
          port: 5432
          protocol: tcp
        clients:
        - 127.0.0.1
        database:
          name: 'databasename'
          encoding: 'UTF8'
          locale: 'cs_CZ'
          user:
            name: 'username'
            password: 'password'
            host: 'localhost'
            createdb: true
            rights: 'all privileges'


PostgreSQL extensions
---------------------

.. code-block:: yaml

    postgresql:
      server:
        enabled: true
        version: 9.1
        bind:
          address: 127.0.0.1
          port: 5432
          protocol: tcp
        clients:
        - 127.0.0.1
        database:
          name: 'databasename'
          encoding: 'UTF8'
          locale: 'cs_CZ'
          user:
            name: 'username'
            password: 'password'
            host: 'localhost'
            createdb: true
            rights: 'all privileges'
          extension:
            postgis_topology:
              enabled: true
            fuzzystrmatch:
              enabled: true
            postgis_tiger_geocoder:
              enabled: true
            postgis:
              enabled: true
              pkgs:
              - postgresql-9.1-postgis-2.1

Cluster
=======

Basic streaming replication.

Master node
-----------

.. code-block:: yaml

  postgresql:
    server:
      enabled: true
      version: 9.6
      bind:
        address: 0.0.0.0
      database:
        mydb: ...
    cluster:
      enabled: true
      role: master
      mode: hot_standby
      members:
      - host: "172.16.10.101"
      - host: "172.16.10.102"
      - host: "172.16.10.103"
      replication_user:
        name: repuser
        password: password
  keepalived:
    cluster:
      enabled: True
      instance:
        VIP:
          notify_action:
            master:
              - 'if [ -f /root/postgresql/flags/failover ]; then touch /var/lib/postgresql/${postgresql:server:version}/main/trigger; fi'
            backup:
              - 'if [ -f /root/postgresql/flags/failover ]; then service postgresql stop; fi'
            fault:
              - 'if [ -f /root/postgresql/flags/failover ]; then service postgresql stop; fi'

Slave node
----------

.. code-block:: yaml

  postgresql:
    server:
      enabled: true
      version: 9.6
      bind:
        address: 0.0.0.0
    cluster:
      enabled: true
      role: slave
      mode: hot_standby
      master:
        host: "172.16.10.100"
        port: 5432
        user: repuser
        password: password
  keepalived:
    cluster:
      enabled: True
      instance:
        VIP:
          notify_action:
            master:
              - 'if [ -f /root/postgresql/flags/failover ]; then touch /var/lib/postgresql/${postgresql:server:version}/main/trigger; fi'
            backup:
              - 'if [ -f /root/postgresql/flags/failover ]; then service postgresql stop; fi'
            fault:
              - 'if [ -f /root/postgresql/flags/failover ]; then service postgresql stop; fi'

Multi-master cluster with 2ndQuadrant bi-directional replication plugin

Master node
-----------

.. code-block:: yaml

  postgresql:
    server:
      enabled: true
      version: 9.4
      bind:
        address: 0.0.0.0
      database:
        mydb:
          extension:
            bdr:
              enabled: true
            btree_gist:
              enabled: true
        ...
    cluster:
      enabled: true
      mode: bdr
      role: master
      members:
      - host: "172.16.10.101"
      - host: "172.16.10.102"
      - host: "172.16.10.101"
      local: "172.16.10.101"
      replication_user:
        name: repuser
        password: password

Slave node
----------

.. code-block:: yaml

  postgresql:
    server:
      enabled: true
      version: 9.4
      bind:
        address: 0.0.0.0
      database:
        mydb:
          extension:
            bdr:
              enabled: true
            btree_gist:
              enabled: true
        ...
    cluster:
      enabled: true
      mode: bdr
      role: master
      members:
      - host: "172.16.10.101"
      - host: "172.16.10.102"
      - host: "172.16.10.101"
      local: "172.16.10.102"
      master: "172.16.10.101"
      replication_user:
        name: repuser
        password: password

Sample usage
============

Init database cluster with given locale

    sudo su - postgres -c "/usr/lib/postgresql/9.3/bin/initdb /var/lib/postgresql/9.3/main --locale=C"

Convert PostgreSQL cluster from 9.1 to 9.3

    sudo su - postgres -c '/usr/lib/postgresql/9.3/bin/pg_upgrade -b /usr/lib/postgresql/9.1/bin -B /usr/lib/postgresql/9.3/bin -d /var/lib/postgresql/9.1/main/ -D /var/lib/postgresql/9.3/main/ -O "-c config_file=/etc/postgresql/9.3/main/postgresql.conf" -o "-c config_file=/etc/postgresql/9.1/main/postgresql.conf"'

Ubuntu on 14.04 on some machines won't create default cluster

    sudo pg_createcluster 9.3 main --start

Read more
=========

* http://www.postgresql.org/
* http://www.postgresql.org/docs/9.1/interactive/index.html
* http://momjian.us/main/writings/pgsql/hw_performance/
* https://gist.github.com/ibussieres/11262268 - upgrade instructions for ubuntu
Documentation and Bugs
======================

To learn how to install and update salt-formulas, consult the documentation
available online at:

    http://salt-formulas.readthedocs.io/

In the unfortunate event that bugs are discovered, they should be reported to
the appropriate issue tracker. Use Github issue tracker for specific salt
formula:

    https://github.com/salt-formulas/salt-formula-postgresql/issues

For feature requests, bug reports or blueprints affecting entire ecosystem,
use Launchpad salt-formulas project:

    https://launchpad.net/salt-formulas

You can also join salt-formulas-users team and subscribe to mailing list:

    https://launchpad.net/~salt-formulas-users

Developers wishing to work on the salt-formulas projects should always base
their work on master branch and submit pull request against specific formula.

    https://github.com/salt-formulas/salt-formula-postgresql

Any questions or feedback is always welcome so feel free to join our IRC
channel:

    #salt-formulas @ irc.freenode.net