
==================
PostgreSQL Formula
==================

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.


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


Single deployment
-----------------

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:
          databasename:
            encoding: 'UTF8'
            locale: 'cs_CZ'
            users:
              - name: 'username'
                password: 'password'
                host: 'localhost'
                rights: 'all privileges'

Single database server with initial data

.. 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:
          databasename:
            encoding: 'UTF8'
            locale: 'cs_CZ'
            initial_data:
              engine: backupninja
              source: backup.host
              host: original-host-name
              database: original-database-name
            users:
            - 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:
          databasename:
            encoding: 'UTF8'
            locale: 'cs_CZ'
            users:
              - name: 'username'
                password: 'password'
                host: 'localhost'
                createdb: true
                rights: 'all privileges'

Database 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:
          databasename:
            encoding: 'UTF8'
            locale: 'cs_CZ'
            users:
              - 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


Master-slave cluster
--------------------

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 nodes

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

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

Client
------

.. code-block:: yaml

    postgresql:
      client:
        server:
          server01:
            admin:
              host: database.host
              port: 5432
              user: root
              password: password
            database:
              mydb:
                enabled: true
                encoding: 'UTF8'
                locale: 'en_US'
                users:
                - name: test
                  password: test
                  host: localhost
                  createdb: true
                  rights: all privileges
                init:
                  maintenance_db: mydb
                  queries:
                  - INSERT INTO login VALUES (11, 1) ;
                  - INSERT INTO device VALUES (1, 11, 42);


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

Init database cluster with given locale

.. code-block:: bash

    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

.. code-block:: bash

    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

.. code-block:: bash

    sudo pg_createcluster 9.3 main --start


More information
================

* 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