On CentOS 7, the package that provides the server is postgresql-server, but only postgres is being installed. This means that the postgres user isn't being created, and the service isn't available to be enabled.

After #100 I can't run the formula on ubuntu 14.04

          ID: postgresql-cluster-prepared
    Function: cmd.run
        Name: pg_createcluster 9.5 main
      Result: False
     Comment: Command "pg_createcluster 9.5 main" run
     Started: 09:47:37.786713
    Duration: 269.437 ms
     Changes:   
              ----------
              pid:
                  11287
              retcode:
                  1
              stderr:
                  Error: cluster configuration already exists
              stdout:

There is no option to disable pg_createcluster on Ubuntu (?)

# make sure the data directory and contents have been initialized
postgresql-cluster-prepared:
  cmd.run:
    {% if postgres.initdb %}
    - name: {{ postgres.commands.initdb }} {{ postgres.initdb_args }} -D {{ postgres.data_dir }}
    {% elif grains.os_family == 'Debian' %}
    - name: pg_createcluster {{ postgres.version }} main
    {# else: TODO #}
    {% endif %}
    - cwd: /
    - user: {{ postgres.initdb_user }}
    - unless:
      - test -f {{ postgres.data_dir }}/PG_VERSION
    - require:
      - pkg: postgresql-installed
    - env:
      LC_ALL: C.UTF-8

I'm opening this for users looking to use this module. Until the following bug in the postgres salt state is merged and rolled out to package repos, this formula with fail with 9.5:

saltstack/salt#26845

A few weeks back I tested installation of this formula on Fedora/Debian/OpenSUSE without pillars and this worked good. Repeating this test "with pillars" using pillar.example as template was unsuccessful as many individual states failed. I solved by including following in pillar.example.

postgres:
use_upstream_repo: False
version: '9.6'
{% if salt'grains.get' == 'RedHat' or salt'grains.get' == 'SUSE' %}
pkgs_extra: [ postgresql-contrib, postgresql-plpython, postgresql-jdbc, postgresql-docs]
{% elif salt'grains.get' == 'Debian' %}
pkg: postgresql-9.6
pkgs_extra: [ postgresql-contrib, postgresql-plpython-9.6, libpostgresql-jdbc-java]
python: python-psycopg2
conf_dir: /var/lib/postgresql/data
postgresconf: ""
{% endif %}

prepare_cluster:
{% if salt'grains.get' == 'RedHat' %}
command: /usr/bin/initdb --pgdata=/var/lib/pgsql/data
test: test -f /var/lib/pgsql/data/PG_VERSION
{% elif salt'grains.get' == 'SUSE' %}
command: /usr/lib/postgresql/9.6/bin/initdb --pgdata=/var/lib/pgsql/data
test: test -f /var/lib/pgsql/data/PG_VERSION
{% elif salt'grains.get' == 'Debian' %}
command: /usr/lib/postgresql/9.6/bin/initdb --pgdata=/var/lib/postgresql/data
test: test -f /var/lib/postgresql/data/PG_VERSION
{% endif %}
user: postgres
env: {}

Should I raise PR to update pillar.example with same (perhaps commented out) as workaround to help others?

Using Vagrant to create a VirtualBox machine using a base Ubuntu 14.04 installation SLS postgres fails.

Without attempting to set LC_ALL or LANG the installation of the postgres package seemingly succeeds, but with a bunch of issues. For instance the /etc/postgres directory is not created and database initialisation fails.

There are a number of issues that appear related to this but I didn't become aware of these until I did some serious digging. See #4543, #19924 and #7190.

At the very least I think the documentation should mention this, and at best include an SLS to fix the issue.

The following worked for me;

# locale/init.sls
language-pack-en:
  pkg.installed:
    - names:
      - language-pack-en

locale-present:
  locale.present:
    - name: 'en_AU.UTF-8'

locale-system:
  locale.system:
    - name: 'en_AU.UTF-8'

configure-locale:
  cmd.run:
    - name: |
        dpkg-reconfigure locales
        update-locale LC_ALL=en_AU.UTF-8 LANG=en_AU.UTF-8
    - shell: /bin/bash

Note for me it wasn't enough to simply call locale.present and locale.system I had to specifically call dpkg-reconfigure locales and update-locale.

The formula's yaml files are carrying too much logic for files that must let you, at a quick glance, understand what values are being set as default or overriden in each of those files.

Even more, some of logic in these files, which should be loaded before pillars, are querying pillar values to construct variables or include values.

My understanding is that these files should just contain variables' assignments like here, have as little jinja as possible (like simple sets to avoid repetition) and then let map.jinja do all the magic.

On Darwin the POSIX user/group created by default is named "_postgres". An exception is thrown because salt tries to "runas: postgres" but this user does not exist.

OS: Darwin 15.6.0, Salt Version: saltstack: stable 2017.7.1 (bottled), HEAD

      ID: postgres_schema-uuid_ossp
Function: postgres_schema.present
    Name: uuid_ossp
  Result: False
 Comment: An exception occurred in this state: Traceback (most recent call last):
            File "/usr/local/Cellar/saltstack/2017.7.1/libexec/lib/python2.7/site-packages/salt/state.py", line 1837, in call
              **cdata['kwargs'])
            File "/usr/local/Cellar/saltstack/2017.7.1/libexec/lib/python2.7/site-packages/salt/loader.py", line 1794, in wrapper
              return f(*args, **kwargs)
            File "/usr/local/Cellar/saltstack/2017.7.1/libexec/lib/python2.7/site-packages/salt/states/postgres_schema.py", line 74, in present
              schema_attr = __salt__['postgres.schema_get'](dbname, name, **db_args)
            File "/usr/local/Cellar/saltstack/2017.7.1/libexec/lib/python2.7/site-packages/salt/modules/postgres.py", line 2108, in schema_get
              db_password=db_password)
            File "/usr/local/Cellar/saltstack/2017.7.1/libexec/lib/python2.7/site-packages/salt/modules/postgres.py", line 2160, in schema_list
              password=db_password)
            File "/usr/local/Cellar/saltstack/2017.7.1/libexec/lib/python2.7/site-packages/salt/modules/postgres.py", line 428, in psql_query
              password=password)
            File "/usr/local/Cellar/saltstack/2017.7.1/libexec/lib/python2.7/site-packages/salt/modules/postgres.py", line 367, in _psql_prepare_and_run
              rcmd, runas=runas, password=password, host=host, port=port, user=user)
            File "/usr/local/Cellar/saltstack/2017.7.1/libexec/lib/python2.7/site-packages/salt/modules/postgres.py", line 184, in _run_psql
              ret = __salt__['cmd.run_all'](cmd, python_shell=False, **kwargs)
            File "/usr/local/Cellar/saltstack/2017.7.1/libexec/lib/python2.7/site-packages/salt/modules/cmdmod.py", line 1699, in run_all
              **kwargs)
            File "/usr/local/Cellar/saltstack/2017.7.1/libexec/lib/python2.7/site-packages/salt/modules/cmdmod.py", line 409, in _run
              'User \'{0}\' is not available'.format(runas)
          CommandExecutionError: User 'postgres' is not available
 Started: 01:40:38.529985
Duration: 3.858 ms
 Changes: 

Looks like the formula can manage nearly every aspect of a PostgreSQL server installation, except server side languages, using the postgres_language state. Any chance to get this added?

Deploy of postfix 9.6 on Ubuntu fails.

Replication

• Fresh vanilla installation of Ubuntu 16/17 & SALT 2017.17
• Change version "9.3" to "9.6" in pillars populated from pillar.example
  https://github.com/saltstack-formulas/postgres-formula/blob/master/pillar.example

Output:

      ID: postgresql-client-libs
Function: pkg.installed
  Result: True
 Comment: 2 targeted packages were installed/updated.
 Started: 22:29:56.822971
Duration: 14458.556 ms
 Changes:   
          ----------
          libpq-dev:
              ----------
              new:
                  9.6.4-0ubuntu0.17.04.1
              old:
          libpq5:
              ----------
              new:
                  9.6.4-0ubuntu0.17.04.1
              old:
          postgresql-client:
              ----------
              new:
                  1
              old:
          postgresql-client-9.6:
              ----------
              new:
                  9.6.4-0ubuntu0.17.04.1
              old:
          postgresql-client-common:
              ----------
              new:
                  179
              old:

      ID: postgresql-server
Function: pkg.installed
  Result: True
 Comment: 3 targeted packages were installed/updated.
 Started: 22:30:11.289041
Duration: 31987.267 ms
 Changes:   
          ----------
          postgresql-9.6:
              ----------
              new:
                  9.6.4-0ubuntu0.17.04.1
              old:
          postgresql-common:
              ----------
              new:
                  179
              old:
          postgresql-contrib:
              ----------
              new:
                  9.6+179
              old:
          postgresql-contrib-9.6:
              ----------
              new:
                  9.6.4-0ubuntu0.17.04.1
              old:
          postgresql-plpython:
              ----------
              new:
                  1
              old:
          postgresql-plpython-9.6:
              ----------
              new:
                  9.6.4-0ubuntu0.17.04.1
              old:
          sysstat:
              ----------
              new:
                  11.4.3-1
              old:

      ID: postgresql-cluster-prepared
Function: cmd.run
    Name: initdb --pgdata=/var/lib/pgsql/data
  Result: False
 Comment: Command "initdb --pgdata=/var/lib/pgsql/data" run
 Started: 22:30:43.280835
Duration: 289.877 ms
 Changes:   
          ----------
          pid:
              13734
          retcode:
              127
          stderr:
              /bin/bash: initdb: command not found
          stdout:

When trying to use postgres-formula on openSUSE Leap 42.2 (which ships with 2016.3.4), I got the following error:

• Rendering SLS 'base:postgres.manage' failed: Jinja error: get() takes exactly 3 arguments (2 given)
  Traceback (most recent call last):
  File "/usr/lib/python2.7/site-packages/salt/utils/templates.py", line 368, in render_jinja_tmpl
  output = template.render(**decoded_context)
  File "/usr/lib/python2.7/site-packages/jinja2/environment.py", line 989, in render
  return self.environment.handle_exception(exc_info, True)
  File "/usr/lib/python2.7/site-packages/jinja2/environment.py", line 754, in handle_exception
  reraise(exc_type, exc_value, tb)
  File "", line 4, in top-level template code
  TypeError: get() takes exactly 3 arguments (2 given)

  ; line 4
  
  ---
  {%- from "postgres/map.jinja" import postgres with context -%}
  {%- from "postgres/macros.jinja" import format_state with context -%}
  
  {%- if not salt.get('postgres.user_create') %}    <======================
  
  # Salt states for managing PostgreSQL is not available,
  # need to provision client binaries first
  
  include:
  [...]
  

saltstack/salt#21314 has been merged into develop, it could be used instead of doing cmd.run

States should be arriving shortly.

I see this in your pg_hba.conf:

# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust

If I read this correctly, this means that any local user can connect as anyone else... the default value at least on Debian systems is to have "md5" instead of "trust" on those lines so that access over IP is password restricted. Please change this to a safer default and let people which want those unsafe rules add them themselves (through the postgres:acls pillar).

hi, there is issue with 'use_upstream_repo' pillar variable:
this time it used like:
{% if 'use_upstream_repo' in pillar.get('postgres') %} install-postgresql-repo: .... omit some strings .... {% endif %}
but such usage checks only existence of variable in pillar but not its value.
looks like it should be something like this:

{% if 'use_upstream_repo' in pillar.get('postgres') %} {% if salt['pillar.get']('postgres:use_upstream_repo', False) %} install-postgresql-repo: .... omit some strings .... {% endif %} {% endif %}

As going per example in pillar.example file:

https://github.com/saltstack-formulas/postgres-formula/blob/master/pillar.example#L142

This does not seem to do anything.

Current logic for postgresql-cluster-prepared state does not work on Debian OS family and other OSes most likely due to initdb and create_cluster being false by default. The flaws is that the id cover two boolean that create 4 different states at least 2 of which are invalid (both true and both false).

I will submit a PR to clean that up in a few minutes.

Deploy of postfix 9.6 on Ubuntu fails using upstream repo.

Replication

• Fresh vanilla installation of Ubuntu 16/17 & SALT 2017.17
• Pillars populated as per pillar.example but ...
  https://github.com/saltstack-formulas/postgres-formula/blob/master/pillar.example

1. • Change version "9.3" to "9.6"
2. • Change "use_upstream_repo: True"

Output:

      ID: postgresql-repo
Function: pkgrepo.managed
  Result: False
 Comment: Failed to configure repo 'postgresql-repo': E: Malformed line 1 in source list /etc/apt/sources.list.d/pgdg.list (type)
          E: The list of sources could not be read.
 Started: 23:03:16.792033
Duration: 2639.258 ms
 Changes:   

Hi,

on a clean CentOS 6.5 install, the service state fail if the database haven't been initialized.

The output is as follow:

[root@salt-test-centos ~]# /etc/init.d/postgresql start

/var/lib/pgsql/data is missing. Use "service postgresql initdb" to initialize the cluster first.
[FAILED]

After issueing that command the service starts up is successful.

Needs investigation. Pillars based on defaults in pillar.example. OpenSUSE Leap 42

[INFO  ]  Failed to create ext: uuid-ossp
[ERROR] Failed to install extension uuid-ossp

- postgresql-plpython line for pkgs_extra in pillar example causes error on vagrant with config.vm.box = "ubuntu/xenial64":

RuntimeError: maximum recursion depth exceeded

On Fedora26/Salt2016.11.3 the state ID 'postgres-python.pkg' fails unexpectedly.

    [ERROR   ] Made the following changes: 'python2-psycopg2' changed from 'absent' to '2.6.2-2.fc25'

      ID: postgresql-python
Function: pkg.installed
    Name: python-psycopg2
  Result: False
 Comment: The following packages failed to install/update: python-psycopg2
 Started: 00:37:00.792703
Duration: 72294.839 ms
 Changes:
          ----------
          python2-psycopg2:
              ----------
              new:
                  2.6.2-2.fc25
              old:

      $ sudo yum install python-psycopg2
      Redirecting to '/usr/bin/dnf install python-psycopg2' (see 'man yum2dnf')
      Last metadata expiration check: 0:09:43 ago on Thu Sep 14 00:33:25 2017.
      Package python2-psycopg2-2.6.2-2.fc25.x86_64 is already installed, skipping. 

Hi,
we're using barman (http://www.pgbarman.org/) as a backup tool for postgres? Where would the barman state go? Would you accept a postgres.barman inside the current formula?

Our states are broken at the moment because of some of the recent updates to this formula. Obviously this is not ideal.

I propose that in-development changes should probably be done on a separate branch until they can be properly vetted and tested more thoroughly.

Additionally, we're going to update our config files to depend on a fork of this repo so that updates have to be merged in by us manually. This is probably a good practice in general.

On Darwin, salt function service.running returns False despite pillar "postgres.service: postgres" having the correct value.

OS: Darwin 15.6.0
Version: saltstack: stable 2017.7.1 (bottled), HEAD

This is the code:

 postgresql-running:
    service.running:
     - name: {{ postgres.service }}
     - enable: True
     -  reload: True
    - watch:
       - file: postgresql-pg_hba

But salt-state returns false.

 [ERROR   ] The named service postgres is not available
       ...
      ID: postgresql-running
    Function: service.running
    Name: postgres
  Result: False
 Comment: The named service postgres is not available
 Started: 01:40:37.545185
  Duration: 1.969 ms
 Changes: 

The postgres services (daemon, chk, wal, wri, avac,stats) are definitely running.

$ pgrep postgres | wc -l
      6

Hi,

I recently attempted to use this formula and received an error on 14 and 29 of init.sls that there is something wrong with the syntax.

If I replace
---> {% if 'pg_hba.conf' in pillar['postgres'] %}
with
---> {% if salt'pillar.get' %}

and
---> {% if 'db' in pillar['postgres'] %}
with
---> {% if salt'pillar.get' %}

then it works 100%.

This is on CentOS 6.5 with Salt version "salt 2014.1.4" installed from EPEL repo.

Regards,
David Bezuidenhout

Hi,

Testing this formula on a clean CentOS 6.5 minimal install, I found that it installed postgresql client utilities, but not the actual service, and as such the service state fail starting up,

Changing line 9 in map.jinja
from:
'pkg': 'postgresql',
to:
'pkg': 'postgresql-server',

seems to do the trick.

For '14.04' and '14.10' there are some entries missing. Without two of them (init_db and pkg_libpq_dev) the formula is not working on Ubuntu 14.04/14.10 systems.

Additionally I would like to know why this formula installs two versions of the database (pkg and pkg_dev)? How can I prevent to install the development version?
Shouldn't there be a setting in the pillar file?

it looks like tablespaces are missing from the example pillar but required by the template

Noticed that you have "conf_dir" set in the defaults.yaml file, but this isn't overridden in the pillar.example file. I was getting errors related to the postgres.conf file not being found when I called state.highstate until I realized this had to be set in the postgres.sls pillar. In the interest of having sane defaults in pillar.example I'm suggesting this setting be added:

conf_dir: '/etc/postgresql/9.3/main'

While deploying postgres-formula on debian jessie I'm getting a problem. The postgresql-server and postgresql-client-libs don't get installed because of apt pinning.

# apt-cache policy postgresql-common
postgresql-common:
  Installed: (none)
  Candidate: 165+deb8u1
  Version table:
  177.pgdg80+1 0
        500 http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg/main amd64 Packages
     165+deb8u1 0
        990 http://httpredir.debian.org/debian/ jessie/main amd64 Packages

You can see 990 for the jessie pkg and 500 for the jessie-pgdg package (less priority).

To get it to work we either have to add some pinning configuration or use fromrepo on the pkg.installed.

This is the quick hack I did to get it to work (which if necessary should be adapted to work on all distros) :

# git diff
diff --git a/postgres/client.sls b/postgres/client.sls
index c51ca11..6657cb9 100644
--- a/postgres/client.sls
+++ b/postgres/client.sls
@@ -19,6 +19,7 @@ include:
 postgresql-client-libs:
   pkg.installed:
     - pkgs: {{ pkgs }}
+    - fromrepo: jessie-pgdg
 {%- if postgres.use_upstream_repo %}
     - refresh: True
     - require:
diff --git a/postgres/server.sls b/postgres/server.sls
index a553dae..37ad460 100644
--- a/postgres/server.sls
+++ b/postgres/server.sls
@@ -14,6 +14,7 @@ include:
 postgresql-server:
   pkg.installed:
     - pkgs: {{ pkgs }}
+    - fromrepo: jessie-pgdg
 {%- if postgres.use_upstream_repo %}
     - refresh: True
     - require:

Hi!

It seems that this formula installs postgresql-server-9.1, and yet it installs postgresql-9.3-dev. That doesn't really make a whole lot of sense, as OSes that have postgresql-server-9.1 don't necessarily have a package for postgresql-9.3-dev, and vice versa.

Additionally, there doesn't seem to be a way to install another version of PostgreSQL, and this formula seems to be pinned to 9.1. This is a problem for me, as I'm trying to use Ubuntu 14.04, which only ships with postgresql-9.3.

I'll try to fix this and deliver a pull request. This might take a bit longer though, as there are structural differences between 9.1 and 9.3, such as the requirement of running postgresql_createcluster before you end up with anything usable.

https://github.com/saltstack-formulas/postgres-formula/blob/master/postgres/upstream.sls#L11-L12

postgres.upstream references the state defined by the variable postgres.pkg. This state does not exist when installing postgres.client.

Cannot extend ID 'postgresql-9.4' in 'base:postgres.upstream'. It is not part of the high state.
This is likely due to a missing include statement or an incorrectly typed ID.
Ensure that a state with an ID of 'postgresql-9.4' is available
in environment 'base' and to SLS 'postgres.upstream'

Replicated on ManjaroLinux (Arch distribution 17.0.5). Should detect systemd.

[TRACE ] Error loading module.upstart: The upstart execution module failed to load: this system was booted with systemd.
...
[ERROR ] The named service postgresql is not available
[INFO ] Completed state [postgresql] at time 23:40:01.128941 duration_in_ms=0.858
[DEBUG ] File /var/cache/salt/minion/accumulator/139911285947728 does not exist, no need to cleanup.
[DEBUG ] LazyLoaded highstate.output
[TRACE ] data = {'local': {'cmd_|-postgresql-cluster-prepared_|-initdb -D /var/lib/postgres/data_|-run': {'comment': u'unless execution succeeded', 'id': 'postgresql-cluster-prepared', 'sls': u'postgres.server', 'name': 'initdb -D /var/lib/postgres/data', 'duration': u'100.863 ms', 'run_num': 1, 'start_time': '23:40:00.603802', 'skip_watch': True, 'changes': {}, 'result': True}, 'file_|-postgresql-config-dir_|-/var/lib/postgres/data_|-directory': {'comment': u'Directory /var/lib/postgres/data is in the correct state\nDirectory /var/lib/postgres/data updated', 'pchanges': {}, 'name': '/var/lib/postgres/data', 'start_time': '23:40:00.708662', 'result': True, 'duration': u'1.77 ms', 'run_num': 2, 'sls': u'postgres.server', 'changes': {}, 'id': 'postgresql-config-dir'}, 'file_|-postgresql-pg_hba_|-/var/lib/postgres/data/pg_hba.conf_|-managed': {'comment': u'File /var/lib/postgres/data/pg_hba.conf is in the correct state', 'name': '/var/lib/postgres/data/pg_hba.conf', 'start_time': '23:40:00.710801', 'result': True, 'duration': u'416.799 ms', 'run_num': 3, 'sls': u'postgres.server', 'changes': {}, 'id': 'postgresql-pg_hba'}, 'pkg_|-postgresql-server_|-postgresql-server_|-installed': {'comment': u'All specified packages are already installed', 'name': 'postgresql', 'start_time': '23:40:00.546436', 'result': True, 'duration': u'55.777 ms', 'run_num': 0, 'sls': u'postgres.server', 'changes': {}, 'id': 'postgresql-server'}, 'service_|-postgresql-running_|-postgresql_|-running': {'comment': u'The named service postgresql is not available', 'name': 'postgresql', 'start_time': '23:40:01.128083', 'result': False, 'duration': u'0.858 ms', 'run_num': 4, 'sls': u'postgres.server', 'changes': {}, 'id': 'postgresql-running'}}}

Needs investigation. Replicated with default pillar.example. These all fail.

postgresql-running
postgres_user-localUser
postgres_user-remoteUser
postgres_tablespace-my_space
postgres_database-db1
postgres_database-db2
postgres_schema-uuid_ossp
postgres_extension-uuid-ossp

For Arch the values "command" and "pkg_client" have incorrect values. The correct values are "/var/lib/postgres/data" and "postgresql-libs.

5 Arch:
6 conf_dir: /var/lib/postgres/data
7 prepare_cluster:
8 command: initdb -D /var/lib/postgresql/data
9 test: test -f /var/lib/postgres/data/PG_VERSION
10 pkg_client: postgresql
11 pkg_dev: postgresql

Note 'postgresql-libs' package provides 'postgresql-client' on Arch but state works because postgresql-libs is installed as dependency.

17baaa9 breaks backward compatibility by forcing a new key to be added to the pillar called tablespaces. This should definitely be defaulted to an empty dict in the state.

Using variation of pillar.example causes formula failure (fedora 26/Salt 2017.7)

   [ERROR   ] Command '['/bin/psql', '--no-align', '--no-readline', '--no-psqlrc', '--no-password', '--dbname', 'postgres', '-c', 'CREATE TABLESPACE "my_space" OWNER "localUser" LOCATION \'/srv/my_tablespace\' ']' failed with return code: 1
    [ERROR   ] stderr: ERROR:  could not set permissions on directory "/srv/my_tablespace": Permission denied
   [ERROR   ] retcode: 1
   [ERROR   ] Error connecting to Postgresql server
   [ERROR   ] An exception occurred in this state: Traceback (most recent call last): File"/usr/lib/python2.7/site-packages/salt/state.py", line 1837, in call  **cdata['kwargs'])  File "/usr/lib/python2.7/site-packages/salt/loader.py", line 1794, in wrapper return f(*args, **kwargs) File "/usr/lib/python2.7/site-packages/salt/states/postgres_tablespace.py", line 121, in present if tblspaces[name]['Location'] != directory and not __opts__['test']:
   KeyError: 'my_space'

      ID: postgres_user-localUser
Function: postgres_user.present
    Name: localUser
  Result: True
 Comment: The user localUser has been created
 Started: 00:36:50.125273
Duration: 2517.922 ms
 Changes: 
          ----------
          localUser:
              Present

      ID: postgresql-tablespace-dir-my_space
Function: file.directory
    Name: /srv/my_tablespace
  Result: True
 Comment: Directory /srv/my_tablespace updated
 Started: 00:36:47.685616
Duration: 2.077 ms
 Changes:
          ----------
          /srv/my_tablespace:
              New Dir

      ID: postgres_tablespace-my_space
Function: postgres_tablespace.present
    Name: my_space
  Result: False
 Comment: An exception occurred in this state: Traceback (most recent call last):
            File "/usr/lib/python2.7/site-packages/salt/state.py", line 1837, in call
              **cdata['kwargs'])
            File "/usr/lib/python2.7/site-packages/salt/loader.py", line 1794, in wrapper
              return f(*args, **kwargs)
            File "/usr/lib/python2.7/site-packages/salt/states/postgres_tablespace.py", line 121, in present
              if tblspaces[name]['Location'] != directory and not __opts__['test']:
          KeyError: 'my_space'
 Started: 00:36:55.939996
Duration: 697.953 ms
 Changes:

The directory /srv/my_dablespace/ is owned by POSIX user/group "postgres:postgres" with 0700 permissions. There is no POSIX user named localUser

Updating the pillar.example changing user to postgres does not help.

      ID: postgresql-tablespace-dir-my_space
Function: file.directory
    Name: /srv/my_tablespace
  Result: False
 Comment: The following requisites were not found
                           require: postgres_user: postgres_user_postgres
 Changes:

I see warning that schemas and extensions will not work in Nitrogen version of SaltStack. I made a naive attempt to squash this error by moving the per database extensions and schemas under a context key, but that just changed the warning to context being an invalid keyword for postgres_database.present. I don't know enough about salt yet to know exactly where the postgres_database_present expects to find the context parameter.

 Warnings: 'extensions' and 'schemas' are invalid keyword arguments for
              'postgres_database.present'. If you were trying to pass additional
              data to be used in a template context, please populate 'context'
              with 'key: value' pairs. Your approach will work until Salt
              Nitrogen is out. Please update your state files.

It would be nice to have this formula deal with the mapping of which postgis package to use depending on the os family and pg version.

we've started using the postgres formula and encountered an issue when highstating. The database server does a restart instead of a reload which leads to problems with apps using the database, which also need a restart.

I guess that the reason is inside of the run-postgresql state.

run-postgresql:
  service.running:
    - enable: true
    - name: {{ postgres.service }}
    - require:
      - pkg: install-postgresql

Are there any things done by the formula which require a restart of the service, or may this enhanced by reload: True?

Most Postgres servers do not need the development headers installed. On the contrary, installing those packages will bring a bunch of other unwanted packages (including a compiler).

One can work around this by setting the pillar "postgres:lookup:pkg_dev" to False but this does not look like the proper interface to make such a decision. Either you need a pillar "postgres:install_dev_packages" that would default to False or you put those states in a separate sls file (postgres/dev.sls).

Thank you for considering!

Here is the output of state.highstate:

----------
          ID: /etc/postgresql/9.4/main
    Function: file.directory
      Result: False
     Comment: User postgres is not available Group postgres is not available
     Started: 15:54:34.472581
    Duration: 428.798 ms
     Changes:   
----------

The issue is that the following block is not requiring postgres to be already installed so the file does not exist yet:
https://github.com/saltstack-formulas/postgres-formula/blob/master/postgres/init.sls#L56

Most formulas currently use markdown as markup language.

I would open a pull request converting this formula if there is no one against that.

Creating a user and assigning a database (or the other way around) doesn't work properly. Probably because users are fetched and cached?

      ID: postgres_user-provisioner_local
Function: postgres_user.present
    Name: provisioner_local
  Result: True
 Comment: The user provisioner_local has been created
 Started: 15:13:29.747481
Duration: 519.459 ms
 Changes:   
          ----------
          provisioner_local:
              Present

      ID: postgres_database-provisioner_local
Function: postgres_database.present
    Name: provisioner_local
  Result: False
 Comment: An exception occurred in this state: Traceback (most recent call last):
            File "/usr/lib/python2.7/dist-packages/salt/state.py", line 1746, in call
              **cdata['kwargs'])
            File "/usr/lib/python2.7/dist-packages/salt/loader.py", line 1704, in wrapper
              return f(*args, **kwargs)
            File "/usr/lib/python2.7/dist-packages/salt/states/postgres_database.py", line 98, in present
              dbs = __salt__['postgres.db_list'](**db_args)
            File "/usr/lib/python2.7/dist-packages/salt/modules/postgres.py", line 461, in db_list
              password=password)
            File "/usr/lib/python2.7/dist-packages/salt/modules/postgres.py", line 417, in psql_query
              password=password)
            File "/usr/lib/python2.7/dist-packages/salt/modules/postgres.py", line 364, in _psql_prepare_and_run
              rcmd, runas=runas, password=password, host=host, port=port, user=user)
            File "/usr/lib/python2.7/dist-packages/salt/modules/postgres.py", line 181, in _run_psql
              ret = __salt__['cmd.run_all'](cmd, python_shell=False, **kwargs)
            File "/usr/lib/python2.7/dist-packages/salt/modules/cmdmod.py", line 1662, in run_all
              **kwargs)
            File "/usr/lib/python2.7/dist-packages/salt/modules/cmdmod.py", line 394, in _run
              'User \'{0}\' is not available'.format(runas)
          CommandExecutionError: User 'provisioner_local' is not available
 Started: 15:13:30.268091
Duration: 13.714 ms
 Changes:   `

Pillar:
`

postgres:
  #pg_hba.conf: salt://postgres/pg_hba.conf
  conf_dir: /etc/postgresql/9.4/main
  lookup:
    pkg: 'postgresql-9.4'
    pkg_client: 'postgresql-client-9.4'
    pkg_dev: 'postgresql-server-dev-9.4'
    pkg_contrib: 'postgresql-contrib-9.4'
    pg_hba: '/etc/postgresql/9.4/main/pg_hba.conf'
  version: 9.4

  users:
      provisioner_local:
        password: 'dfsjkle45980dfgkjl'
        createdb: False

  acls:
    - ['host', 'provisioner_local', 'provisioner_local', '0.0.0.0/0', 'md5']


  databases:
    provisioner_local:
      owner: 'provisioner_local'
      user: 'provisioner_local'
      template: 'template0'
      lc_ctype: 'C.UTF-8'
      lc_collate: 'C.UTF-8'
   
  postgresconf: |
    listen_addresses = '0.0.0.0'

`

I use salt-call and vagrant for various testing which I perform as I am developing salt states. I am fairly new to the salt world, so occasionally I misunderstand what is happening. When I tried to use this formula, from 'salt-call' in my salty vagrant, I get the following error:

[ERROR   ] Failed to read the virtual function for module: postgres_ext
Traceback (most recent call last):
  File "/usr/lib/python2.7/site-packages/salt/loader.py", line 1072, in process_virtual
    virtual = mod.__virtual__()
  File "/var/cache/salt/minion/extmods/modules/postgres_ext.py", line 19, in __virtual__
    if 'postgres.psql_query' in __salt__:
NameError: global name '__salt__' is not defined

I found a few references to this, but saltstack/salt#15616 appears to address this issue.

Comments / Suggestions?

Edit: Version
salt 2014.7.3 (Helium)
Python 2.7.5
CentOS Linux release 7.0.1406 (Core)

Just upgraded to v2014.7.5 - still broken.

Hey all,

This is a fun one. When I would run state.apply, I get the following error:

Improperly formatted top file matcher in saltenv OrderedDict([('refresh', False)]): False file

I investigated heavily, and it all seem to come down to the refresh states being used here:

• https://github.com/saltstack-formulas/postgres-formula/blob/master/postgres/init.sls#L15
• https://github.com/saltstack-formulas/postgres-formula/blob/master/postgres/client.sls#L11

I tinkered with these to no success. Finally, I outright removed them from the state files and I was able to successfully complete my Postgres state.

For the record, here's my very slim Pillar:

# See example: https://github.com/saltstack-formulas/postgres-formula/blob/master/pillar.example

postgres:
  pg_hba.conf: salt://postgres/pg_hba.conf

  use_upstream_repo: False

  lookup:
    pkg: 'postgresql-9.4'
    pkg_client: 'postgresql-client-9.4'
    pg_hba: '/etc/postgresql/9.4/main/pg_hba.conf'

  # ACL management of pg_hba.conf
  # FIXME: Needs to use a specific database/Postgres users Pillar.
  acls:
    - ['local', 'db1', 'localUser']

Thank you for your help!

This is caused by #167.

I can create a custom tablespace and it is created successfully. However when I rerun state.apply the tablespace state fails complaining that the tablespace is in the wrong place. This prevents any other changes that are applied to the database definition in the pillar from being updated because they all require the postgres_tablespace-cutom_space

From my pillar.sls

    postgres:                                                                   
       ensure: present                                                           
                                                                                 
   # tablespaces to be created                                                   
   tablespaces:                                                                  
     custom_space:                                                               
       # format is /mnt/${server_identifier}/${application_owner}                
       directory: /mnt/cha-dev-play03/postgres/                                  
       owner: postgres

The first time I run the state.

----------
          ID: postgres_tablespace-custom_space
    Function: postgres_tablespace.present
        Name: custom_space
      Result: True
     Comment: The tablespace custom_space has been created
     Started: 16:14:37.791460
    Duration: 733.579 ms
     Changes:   
              ----------
              custom_space:
                  Present

Run the state again without changing anything

----------
          ID: postgres_tablespace-custom_space
    Function: postgres_tablespace.present
        Name: custom_space
      Result: False
     Comment: Tablespace custom_space is not at the right location. This is
                          unfixable without dropping and recreating the tablespace.
     Started: 16:15:28.623210
    Duration: 422.103 ms
     Changes: 

Fedora operating system is not currently not supported because it uses a different upstream package repository than RedHat.

Structure of RedHat upstream repository

base_url=http://yum.postgresql.org/9.5/redhat/rhel-7-x86_64/

vs

base_url=https://download.postgresql.org/pub/repos/yum/9.6/fedora/fedora-24-x86_64/

As it currently stands, there's no way to update the PostgreSQL configuration. That would be a cool feature to have, as it's not uncommon to change (for instance) the listen_addresses directive in postgresql.conf.

I've already sent in a pull request - #20 - to add this functionality.