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

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

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

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

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

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

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

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

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