geerlingguy / ansible-role-postgresql

Ansible Role - PostgreSQL

Ansible Role: PostgreSQL

Installs and configures PostgreSQL server on RHEL/CentOS or Debian/Ubuntu servers.

Requirements

No special requirements; note that this role requires root access, so either run it in a playbook with a global become: yes, or invoke the role in your playbook like:

- hosts: database
  roles:
    - role: geerlingguy.postgresql
      become: yes

Role Variables

Available variables are listed below, along with default values (see defaults/main.yml):

postgresql_enablerepo: ""

(RHEL/CentOS only) You can set a repo to use for the PostgreSQL installation by passing it in here.

postgresql_restarted_state: "restarted"

Set the state of the service when configuration changes are made. Recommended values are restarted or reloaded.

postgresql_python_library: python-psycopg2

Library used by Ansible to communicate with PostgreSQL. If you are using Python 3 (e.g. set via ansible_python_interpreter), you should change this to python3-psycopg2.

postgresql_user: postgres
postgresql_group: postgres

The user and group under which PostgreSQL will run.

postgresql_unix_socket_directories:
  - /var/run/postgresql

The directories (usually one, but can be multiple) where PostgreSQL's socket will be created.

postgresql_service_state: started
postgresql_service_enabled: true

Control the state of the postgresql service and whether it should start at boot time.

postgresql_global_config_options:
  - option: unix_socket_directories
    value: '{{ postgresql_unix_socket_directories | join(",") }}'
  - option: log_directory
    value: 'log'

Global configuration options that will be set in postgresql.conf. For PostgreSQL versions older than 9.3 you need to at least override this variable and set the option to unix_socket_directory. If you override the value of option: log_directory with another path, relative or absolute, then this role will create it for you.

postgresql_hba_entries:
  - { type: local, database: all, user: postgres, auth_method: peer }
  - { type: local, database: all, user: all, auth_method: peer }
  - { type: host, database: all, user: all, address: '127.0.0.1/32', auth_method: md5 }
  - { type: host, database: all, user: all, address: '::1/128', auth_method: md5 }

Configure host based authentication entries to be set in the pg_hba.conf. Options for entries include:

• type (required)
• database (required)
• user (required)
• address (one of this or the following two are required)
• ip_address
• ip_mask
• auth_method (required)
• auth_options (optional)

If overriding, make sure you copy all of the existing entries from defaults/main.yml if you need to preserve existing entries.

postgresql_locales:
  - 'en_US.UTF-8'

(Debian/Ubuntu only) Used to generate the locales used by PostgreSQL databases.

postgresql_databases:
  - name: exampledb # required; the rest are optional
    lc_collate: # defaults to 'en_US.UTF-8'
    lc_ctype: # defaults to 'en_US.UTF-8'
    encoding: # defaults to 'UTF-8'
    template: # defaults to 'template0'
    login_host: # defaults to 'localhost'
    login_password: # defaults to not set
    login_user: # defaults to 'postgresql_user'
    login_unix_socket: # defaults to 1st of postgresql_unix_socket_directories
    port: # defaults to not set
    owner: # defaults to postgresql_user
    state: # defaults to 'present'

A list of databases to ensure exist on the server. Only the name is required; all other properties are optional.

postgresql_users:
  - name: jdoe #required; the rest are optional
    password: # defaults to not set
    encrypted: # defaults to not set
    priv: # defaults to not set
    role_attr_flags: # defaults to not set
    db: # defaults to not set
    login_host: # defaults to 'localhost'
    login_password: # defaults to not set
    login_user: # defaults to '{{ postgresql_user }}'
    login_unix_socket: # defaults to 1st of postgresql_unix_socket_directories
    port: # defaults to not set
    state: # defaults to 'present'

A list of users to ensure exist on the server. Only the name is required; all other properties are optional.

postgres_users_no_log: true

Whether to output user data (which may contain sensitive information, like passwords) when managing users.

postgresql_version: [OS-specific]
postgresql_data_dir: [OS-specific]
postgresql_bin_path: [OS-specific]
postgresql_config_path: [OS-specific]
postgresql_daemon: [OS-specific]
postgresql_packages: [OS-specific]

OS-specific variables that are set by include files in this role's vars directory. These shouldn't be overridden unless you're using a version of PostgreSQL that wasn't installed using system packages.

Dependencies

None.

Example Playbook

- hosts: database
  become: yes
  vars_files:
    - vars/main.yml
  roles:
    - geerlingguy.postgresql

Inside vars/main.yml:

postgresql_databases:
  - name: example_db
postgresql_users:
  - name: example_user
    password: supersecure

License

MIT / BSD

Author Information

This role was created in 2016 by Jeff Geerling, author of Ansible for DevOps.

ansible-role-postgresql's Issues

Create users before databases

This section at tasks/main.yml:

# Configure PostgreSQL.                                                                                                                               
- import_tasks: databases.yml                                                                                                                         
- import_tasks: users.yml

should be

# Configure PostgreSQL.                                                                                                                               
- import_tasks: users.yml
- import_tasks: databases.yml                                                                                                                         

When I trying create user and database that he own like this:

postgresql_users:                                                                                                                                     
  - name: project                                                                                                                                     
    password: projectpassword                                                                                                                         
postgresql_databases:                                                                                                                                 
  - name: project                                                                                                                                     
    owner: project

I got error:

role "project" does not exist

postgresql_version ignored for package installation

The Debian variables files, for example (I haven't looked at the RedHat ones) specify meta packages like postgreql rather than postgresql-9.6. I assume that this is to allow the package repo to control the version.

But that also means that if I set postgresql_version: 11 (I have a custom apt repo for stretch with packages for 11), that version is completely ignored and 9.6 is installed instead.

I think the package names should reference postgresql-{{ postgresql_version }} so that the variable can actually control package version selection.

Missing required package when using python3 (Ubuntu 16.04)

When using role on Ubuntu Xenial with ansible_python_interpreter set to python3 the following error occurs due to missing package.

TASK [geerlingguy.postgresql : Ensure PostgreSQL databases are present.] *******
failed: [ubuntu-01] (item={u'name': u'app'}) => {"failed": true, "item": {"name": "app"}, "msg": "the python psycopg2 module is required"}

The task Ensure PostgreSQL Python libraries are installed installs python-psycopg2 when python3-psycopg2 is needed.

Workaround is to define postgresql_packages with inclusion of python3-psycopg2.

Error with versions on ubuntu server

TASK [geerlingguy.postgresql : Ensure PostgreSQL database is initialized.] *****
task path: /etc/ansible/roles/geerlingguy.postgresql/tasks/initialize.yml:22
<10.9.4.252> ESTABLISH SSH CONNECTION FOR USER: test
<10.9.4.252> SSH: EXEC sshpass -d14 ssh -C -vvv -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=22 -o User=test
 -o ConnectTimeout=10 -o ControlPath=/home/test/.ansible/cp/ansible-ssh-%h-%p-%r 10.9.4.252 '/bin/sh -c '"'"'sudo -H -S  -p "[sudo via ansible,
 key=ofxlibpjnnwlhvpuvxecdnwjthmqagiy] password: " -u postgres /bin/sh -c '"'"'"'"'"'"'"'"'echo BECOME-SUCCESS-ofxlibpjnnwlhvpuvxecdnwjthmqagiy
; LANG=en_US.UTF-8 LC_ALL=en_US.UTF-8 LC_MESSAGES=en_US.UTF-8 /usr/bin/python'"'"'"'"'"'"'"'"''"'"''
fatal: [10.9.4.252]: FAILED! => {"changed": false, "cmd": "/usr/lib/postgresql/9.5/bin/initdb -D /var/lib/postgresql/9.5/main", "failed": true,
 "invocation": {"module_args": {"_raw_params": "/usr/lib/postgresql/9.5/bin/initdb -D /var/lib/postgresql/9.5/main", "_uses_shell": false, "chd
ir": null, "creates": null, "executable": null, "removes": null, "warn": true}, "module_name": "command"}, "msg": "[Errno 2] No such file or di
rectory", "rc": 2}

Inside /usr/lib/postgresql there is only 9.6 version.

`templates/` prefix is superfluous

https://github.com/geerlingguy/ansible-role-postgresql/blob/master/tasks/configure.yml#L13
src should be just the path after templates.

How to change the PGDATA dir

Hello,

I'm trying to change the PGDATA default dir. I've set "postgresql_data_dir" to another dir (/var/postgresql/data for example). The initdb is working fine, but the service is not starting
because the systemd daemon is looking for /var/lib/pgsql/9.6/data

Is it possible to change the data dir using this role ?

Thanks.

OpenBSD

The role runs on OpenBSD 6.5 with the following changes.

vars:
- __postgresql_version: "11"
- __postgresql_data_dir: "/var/postgresql/data"
- __postgresql_bin_path: "/usr/local/bin"
- __postgresql_config_path: "/var/postgresql/data"
- __postgresql_daemon: "postgresql"
- __postgresql_packages:
- postgresql-server
- postgresql-contrib
- postgresql_user: _postgresql
- postgresql_group: _postgresql
- postgresql_unix_socket_directories: [ "/tmp" ]

And disable in tasks/initialize.yml
name: Set PostgreSQL environment variables.
template:
src: postgres.sh.j2
dest: /etc/profile.d/postgres.sh
mode: 0644
notify: restart postgresql

And disable in tasks/configure.yml
name: Ensure PostgreSQL unix socket dirs exist.
file:
path: "{{ item }}"
state: directory
owner: "{{ postgresql_user }}"
group: "{{ postgresql_group }}"
mode: 02775
with_items: "{{ postgresql_unix_socket_directories }}"

How to install extensions?

How make installation extension line

CREATE EXTENSION IF NOT EXISTS postgis;

pg_ident.conf support

Would be nice if it supported managing pg_ident.conf entries.

Does not work on Ubuntu 12.04

TASK [geerlingguy.postgresql : Include OS-specific variables (Debian).] ********
fatal: [drupalvm_php2]: FAILED! => {"failed": true, "msg": "Unable to find 'Ubuntu-12.yml' in expected paths."}

Ubuntu 18.04 support, missing vars/Ubuntu-18.yml

ASK [geerlingguy.postgresql : Include OS-specific variables (Debian).] **************************************************
fatal: [192.168.100.200]: FAILED! => {"ansible_facts": {}, "ansible_included_var_files": [], "changed": false, "message": "Could not find or access 'Ubuntu-18.yml'

restart postgresql handler fails

RUNNING HANDLER [geerlingguy.postgresql : restart postgresql] 
*******************************
fatal: [HOST]: FAILED! => {"msg": "The task includes an option with an undefined variable. The error was: 'postgresql_daemon' is undefined\n\nThe error appears to have been in 'PATH_TO_HOME/.ansible/roles/geerlingguy.postgresql/handlers/main.yml': line 2, column 3, but may\nbe elsewhere in the file depending on the exact syntax problem.\n\nThe offending line appears to be:\n\n---\n- name: restart postgresql\n  ^ here\n"}

One peculiarity is that role including geerlingguy.postgresql has default setting for postgresql_daemon.
Setting

postgresql_daemon: postgresql

in .ansible/roles/geerlingguy.postgresql/defaults/main.yml fixes this.

'postgresql_packages' is undefined with Ansible 2.2

Ansible 2.1.2+ (including 2.2) seems to be statically including role includes (see ansible/ansible#17687), which means some variable-defining tasks aren't run properly, but then later tasks which are skipped in a role that's ignored are still being run, but with undefined variables, which introduce an error like:

TASK [geerlingguy.firewall : Ensure the firewall is enabled and will start on boot.] ***
fatal: [drupalvm]: FAILED! => {"changed": false, "failed": true, "msg": "Could not find the requested service \"'firewall'\": "}

RUNNING HANDLER [geerlingguy.firewall : restart firewall] **********************

PLAY RECAP *********************************************************************
drupalvm                   : ok=10   changed=6    unreachable=0    failed=1

creating a read-only user

i am trying to create a read only user but i have been looking at the syntax and googling for hours and i still can't figure it out:

i have tried using:

priv: "public:USAGE/ALL:SELECT" #PostgreSQL privileges string in the format: table:priv1,priv2

but it errors out with: Invalid privs specified for database: USAGE\n')

i have looked at the postgresql_user and privs page, tried variations but nothing seems to work

can anyone point me in the right direction? just wanna figure out what i am doing wrong.
thanks.

add support for windows and mac

not sure this is a common case, but still.
as part of my setup - i need to configure postgres on multiple os types.

installation is pretty simple but the configuration process is cumbersome.
it would help a lot if you would add support for windows and mac.

Configuring pg_hba.conf is skipped though the var is set

I am using the Ansible local provisioner to set up a Vagrant test machine. The installed Postgresql database server should trust any incoming connection, so my playbook.yml looks something like this:

---
- name: Set up the Vagrant development server
  hosts: all
  become: yes
  vars:
    postgresql_hba_entries:
      - { type: local, database: all, user: all, auth_method: trust }
      - { type: host, database: all, user: all, address: '127.0.0.1/32', auth_method: trust }
      - { type: host, database: all, user: all, address: '::1/128', auth_method: trust }
  roles:
    - role: geerlingguy.postgresql

Installation works, but the postgresql role is skipping the step to configure the pg_hba.conf file, meaning I have to manually make my changes in pg_hba.conf to connect to the database server.

Is there a mistake in my playbook.yml or might this be a bug because my configuration is "too similar" to the defaults?

Debian-10 Buster support

Please support the upcoming stable.

Remove support for Ubuntu 14.04 Trusty

Role doesn't work with Debian 7 (Unable to find 'Debian-7.yml')

the role ansible-role-postgresql it's failing on debian Wheezy :

TASK [geerlingguy.postgresql : Include OS-specific variables (Debian).] ********
fatal: [wheezy]: FAILED! => {"ansible_facts": {}, "changed": false, "failed": true, "message": "Unable to find 'Debian-7.yml' in expected paths."}

i use the lastest version available :

cat roles/geerlingguy.postgresql/meta/.galaxy_install_info
{install_date: 'Thu Mar 16 16:43:53 2017', version: 1.0.2}

It's true, on the file directory, it's not exist this version :

ls roles/geerlingguy.postgresql/vars/
Debian-8.yml  RedHat-6.yml  RedHat-7.yml  Ubuntu-14.yml  Ubuntu-16.yml

password error when running playbook

I'm following your example, to no avail and am getting the following error. I'm using ansible 2.1.2.0, and my target is ubuntu/trusty64. Any clue what the issue could be?

TASK [setup] *******************************************************************
fatal: [localhost]: FAILED! => {"changed": false, "failed": true, "module_stderr": "sudo: a password is required\n", "module_stdout": "", "msg": "MODULE FAILURE"}

postgres.yml

---
- hosts: localhost
  become: true
  vars_files:
    - vars/postgresql.yml
  roles:
    - geerlingguy.postgresql

postgresql.yml

---
postgresql_group: postgres

postgresql_locales:
  - 'en_US.UTF-8'


ostgresql_databases:
 - name: testdb # required; the rest are optional
    # lc_collate: # defaults to 'en_US.UTF-8'
    # lc_ctype: # defaults to 'en_US.UTF-8'
    # encoding: # defaults to 'UTF-8'
    # template: # defaults to 'template0'
    # login_host: # defaults to 'localhost'
    # login_user: # defaults to 'postgresql_user'
    # login_unix_socket: # defaults to 1st of postgresql_unix_socket_directories
    # port: # defaults to not set
    # state: # defaults to 'present'
postgresql_users:
  - name: testdb #required; the rest are optional
    password: testdb
    # priv: # defaults to not set
    # role_attr_flags: # defaults to not set
    # db: # defaults to not set
    # login_host: # defaults to 'localhost'
    # login_user: # defaults to '{{ postgresql_user }}'
    # login_unix_socket: # defaults to 1st of postgresql_unix_socket_directories
    # port: # defaults to not set
    # state: # defaults to 'present'

postgresql daemon is not properly set for debian9 (9.6)

postgres (9.6 on debian 9) service should be enabled according to the version it is installed otherwise you would probably get an "empty" service template for systemd

the incorrect command is

vagrant@debhost:~$ sudo systemctl enable postgresql
Synchronizing state of postgresql.service with SysV service script with /lib/systemd/systemd-sysv-install.
Executing: /lib/systemd/systemd-sysv-install enable postgresql
vagrant@debhost:~$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Tue 2017-08-08 11:12:27 UTC; 3min 3s ago
 Main PID: 10028 (code=exited, status=0/SUCCESS)

Aug 08 11:12:27 debhost systemd[1]: Starting PostgreSQL RDBMS...
Aug 08 11:12:27 debhost systemd[1]: Started PostgreSQL RDBMS.
vagrant@debhost:~$ cat /lib/systemd/system/postgresql.service
# systemd service for managing all PostgreSQL clusters on the system. This
# service is actually a systemd target, but we are using a service since
# targets cannot be reloaded.

[Unit]
Description=PostgreSQL RDBMS

[Service]
Type=oneshot
ExecStart=/bin/true
ExecReload=/bin/true
RemainAfterExit=on

[Install]
WantedBy=multi-user.target

The correct command should be

vagrant@debhost:~$ sudo systemctl enable [email protected]
vagrant@debhost:~$ sudo systemctl status [email protected]
● [email protected] - PostgreSQL Cluster 9.6-main
   Loaded: loaded (/lib/systemd/system/[email protected]; enabled; vendor preset: enabled)
   Active: active (running) since Tue 2017-08-08 11:12:27 UTC; 4min 22s ago
 Main PID: 10010 (postgres)
   CGroup: /system.slice/system-postgresql.slice/[email protected]
           ├─10010 /usr/lib/postgresql/9.6/bin/postgres -D /var/lib/postgresql/9.6/main -c config_file=/etc/postgresql/9.6/main/postgresql.conf
           ├─10012 postgres: 9.6/main: checkpointer process
           ├─10013 postgres: 9.6/main: writer process
           ├─10014 postgres: 9.6/main: wal writer process
           ├─10015 postgres: 9.6/main: autovacuum launcher process
           ├─10016 postgres: 9.6/main: stats collector process
           └─10017 postgres: 9.6/main: user postgres 192.168.33.1(12512) idle

Aug 08 11:12:25 debhost systemd[1]: Stopped PostgreSQL Cluster 9.6-main.
Aug 08 11:12:25 debhost systemd[1]: Starting PostgreSQL Cluster 9.6-main...
Aug 08 11:12:27 debhost systemd[1]: Started PostgreSQL Cluster 9.6-main.
vagrant@debhost:~$ cat /lib/systemd/system/postgresql
postgresql.service   [email protected]
vagrant@debhost:~$ cat /lib/systemd/system/[email protected]
# systemd service template for PostgreSQL clusters. The actual instances will
# be called "postgresql@version-cluster", e.g. "[email protected]". The
# variable %i expands to "version-cluster", %I expands to "version/cluster".
# (%I breaks for cluster names containing dashes.)

[Unit]
Description=PostgreSQL Cluster %i
ConditionPathExists=/etc/postgresql/%I/postgresql.conf
PartOf=postgresql.service
ReloadPropagatedFrom=postgresql.service
Before=postgresql.service

[Service]
Type=forking
# @: use "postgresql@%i" as process name
ExecStart=@/usr/bin/pg_ctlcluster postgresql@%i --skip-systemctl-redirect %i start
ExecStop=/usr/bin/pg_ctlcluster --skip-systemctl-redirect -m fast %i stop
ExecReload=/usr/bin/pg_ctlcluster --skip-systemctl-redirect %i reload
PIDFile=/var/run/postgresql/%i.pid
SyslogIdentifier=postgresql@%i
# prevent OOM killer from choosing the postmaster (individual backends will
# reset the score to 0)
OOMScoreAdjust=-900
# restarting automatically will prevent "pg_ctlcluster ... stop" from working,
# so we disable it here. Also, the postmaster will restart by itself on most
# problems anyway, so it is questionable if one wants to enable external
# automatic restarts.
#Restart=on-failure
# (This should make pg_ctlcluster stop work, but doesn't:)
#RestartPreventExitStatus=SIGINT SIGTERM

[Install]
WantedBy=multi-user.target

the ansible role defines a handler to restart postgresql however it is using the postgres_daemon var which is set incorrectly therefore giving false indication for database service restart, see below:

RUNNING HANDLER [geerlingguy.postgresql : restart postgresql] ********************************************************************************************************************************


 [WARNING]: Ignoring "sleep" as it is not used in "systemd"

Using module file /usr/local/lib/python2.7/dist-packages/ansible/modules/system/systemd.py
<192.168.33.50> ESTABLISH SSH CONNECTION FOR USER: vagrant
<192.168.33.50> SSH: EXEC sshpass -d12 ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o User=vagrant -o ConnectTimeout=10 -o ControlPath=/home/vagrant/.ansib

le/cp/673d2d4c12 192.168.33.50 '/bin/sh -c '"'"'sudo -H -S -n -u root /bin/sh -c '"'"'"'"'"'"'"'"'echo BECOME-SUCCESS-dpuxjcyqgnwvajepusmhaselfsacbhku; /usr/bin/python'"'"'"'"'"'"'"'"' && sl

eep 0'"'"''
**<192.168.33.50> (0, '\n{"status": {"ExecStart": "{ path=/bin/true ; argv[]=/bin/true ; ignore_errors=no ; start_time=[Tue 2017-08-08 11:01:49 UTC] ; stop_time=[Tue 2017-08-08 11:01:49 UTC] ;**

 pid=9860 ; code=exited ; status=0 }", "TimeoutStopUSec": "1min 30s", "RuntimeDirectoryMode": "0755", "GuessMainPID": "yes", "ActiveExitTimestamp": "Tue 2017-08-08 11:01:46 UTC", "ExecMainCo

de": "1", "PropagatesReloadTo": "[email protected]", "ExecMainPID": "9860", "LimitSIGPENDING": "1878", "MemoryHigh": "18446744073709551615", "OOMScoreAdjust": "0", "LoadState": "lo

aded", "UID": "4294967295", "ProtectHome": "no", "RuntimeMaxUSec": "infinity", "TTYVHangup": "no", "WatchdogTimestampMonotonic": "0", "LimitMSGQUEUESoft": "819200", "LimitSTACK": "1844674407

3709551615", "MemorySwapMax": "18446744073709551615", "ActiveEnterTimestampMonotonic": "1763252353", "StandardError": "inherit", "CanIsolate": "no", "LimitDATASoft": "18446744073709551615",
"GID": "4294967295", "CPUSchedulingPriority": "0", "KillSignal": "15", "LimitFSIZE": "18446744073709551615", "IgnoreOnIsolate": "no", "LimitCPU": "18446744073709551615", "AssertTimestamp": "

Tue 2017-08-08 11:01:49 UTC", "RemoveIPC": "no", "CPUUsageNSec": "18446744073709551615", "LimitNICESoft": "0", "LimitMEMLOCKSoft": "65536", "UnitFileState": "enabled", "NoNewPrivileges": "no

", "MemoryLimit": "18446744073709551615", "MemoryDenyWriteExecute": "no", "InvocationID": "24928b8fde164fa8987ff78cfc47eb01", "CanStart": "yes", "IOAccounting": "no", "JobTimeoutAction": "no

ne", "Before": "multi-user.target exim4.service shutdown.target", "LimitAS": "18446744073709551615", "SyslogLevel": "6", "RootDirectoryStartOnly": "no", "InactiveExitTimestampMonotonic": "17

63248168", "SendSIGHUP": "no", "TimeoutStartUSec": "infinity", "Type": "oneshot", "FailureAction": "none", "SameProcessGroup": "no", "LimitNPROC": "1878", "UMask": "0022", "NonBlocking": "no

", "DevicePolicy": "auto", "ProtectKernelTunables": "no", "ExecMainStartTimestamp": "Tue 2017-08-08 11:01:49 UTC", "LimitRSSSoft": "18446744073709551615", "CapabilityBoundingSet": "184467440

73709551615", "TTYReset": "no", "FileDescriptorStoreMax": "0", "RefuseManualStart": "no", "KillMode": "control-group", "SyslogLevelPrefix": "yes", "LimitRSS": "18446744073709551615", "LimitR

TPRIO": "0", "StartupCPUWeight": "18446744073709551615", "Delegate": "no", "MemoryMax": "18446744073709551615", "ExecReload": "{ path=/bin/true ; argv[]=/bin/true ; ignore_errors=no ; start_

time=[n/a] ; stop_time=[n/a] ; pid=0 ; code=(null) ; status=0/0 }", "SyslogFacility": "3", "TasksCurrent": "18446744073709551615", "InactiveExitTimestamp": "Tue 2017-08-08 11:01:49 UTC", "Li

mitCORE": "18446744073709551615", "JobTimeoutUSec": "infinity", "TimerSlackNSec": "50000", "StateChangeTimestamp": "Tue 2017-08-08 11:01:49 UTC", "SubState": "exited", "CPUSchedulingResetOnF

ork": "no", "Result": "success", "CPUShares": "18446744073709551615", "LimitCORESoft": "0", "ConditionResult": "yes", "NFileDescriptorStore": "0", "LimitNOFILESoft": "1024", "TTYVTDisallocat

e": "no", "MainPID": "0", "StartupBlockIOWeight": "18446744073709551615", "ActiveEnterTimestamp": "Tue 2017-08-08 11:01:49 UTC", "FragmentPath": "/lib/systemd/system/postgresql.service", "Li

mitSIGPENDINGSoft": "1878", "ProtectControlGroups": "no", "StartupCPUShares": "18446744073709551615", "WatchdogUSec": "0", "ActiveState": "active", "Nice": "0", "LimitDATA": "184467440737095

51615", "UnitFilePreset": "enabled", "LimitNPROCSoft": "1878", "MemoryCurrent": "18446744073709551615", "LimitRTTIME": "18446744073709551615", "WantedBy": "multi-user.target", "SecureBits":
"0", "RestartUSec": "100ms", "ConditionTimestamp": "Tue 2017-08-08 11:01:49 UTC", "CPUAccounting": "no", "RemainAfterExit": "yes", "PrivateUsers": "no", "PrivateNetwork": "no", "StartupIOWei

ght": "18446744073709551615", "Restart": "no", "CPUSchedulingPolicy": "0", "LimitNOFILE": "4096", "SendSIGKILL": "yes", "StatusErrno": "0", "RefuseManualStop": "no", "SystemCallErrorNumber":

 "0", "MemoryLow": "0", "Perpetual": "no", "IOWeight": "18446744073709551615", "LimitCPUSoft": "18446744073709551615", "TasksAccounting": "yes", "NeedDaemonReload": "no", "ExecMainExitTimest

amp": "Tue 2017-08-08 11:01:49 UTC", "InactiveEnterTimestamp": "Tue 2017-08-08 11:01:46 UTC", "StandardInput": "null", "AssertTimestampMonotonic": "1763247726", "DefaultDependencies": "yes",

 "Requires": "sysinit.target system.slice", "UtmpMode": "init", "StateChangeTimestampMonotonic": "1763252353", "TasksMax": "4915", "CPUQuotaPerSecUSec": "infinity", "ExecMainStatus": "0", "L

imitMEMLOCK": "65536", "StopWhenUnneeded": "no", "LimitMSGQUEUE": "819200", "LimitRTPRIOSoft": "0", "LimitSTACKSoft": "8388608", "CPUWeight": "18446744073709551615", "AmbientCapabilities": "

0", "AllowIsolate": "no", "Slice": "system.slice", "ExecMainExitTimestampMonotonic": "1763252196", "ConsistsOf": "[email protected]", "LimitFSIZESoft": "18446744073709551615", "Sta

rtLimitIntervalSec": "10000000", "NotifyAccess": "none", "PermissionsStartOnly": "no", "BlockIOAccounting": "no", "CanStop": "yes", "PrivateTmp": "no", "OnFailureJobMode": "replace", "Assert

Result": "yes", "LimitLOCKS": "18446744073709551615", "ExecMainStartTimestampMonotonic": "1763248143", "StartLimitBurst": "5", "Wants": "[email protected]", "After": "systemd-journ

ald.socket system.slice basic.target [email protected] sysinit.target", "ProtectKernelModules": "no", "SyslogPriority": "30", "StartLimitAction": "none", "Conflicts": "shutdown.tar

get", "RestrictNamespace": "2114060288", "StandardOutput": "journal", "MountFlags": "0", "InactiveEnterTimestampMonotonic": "1760095277", "MemoryAccounting": "no", "IgnoreSIGPIPE": "yes", "T

ransient": "no", "IOScheduling": "0", "Description": "PostgreSQL RDBMS", "DynamicUser": "no", "ActiveExitTimestampMonotonic": "1760095277", "RestrictRealtime": "no", "CanReload": "yes", "Con

trolPID": "0", "LimitNICE": "0", "BlockIOWeight": "18446744073709551615", "LimitLOCKSSoft": "18446744073709551615", "LimitRTTIMESoft": "18446744073709551615", "Names": "postgresql.service",
"ProtectSystem": "no", "LimitASSoft": "18446744073709551615", "PrivateDevices": "no", "Id": "postgresql.service", "ConditionTimestampMonotonic": "1763247726"}, "invocation": {"module_args":
{"no_block": false, "name": "postgresql", "enabled": null, "daemon_reload": false, "state": "restarted", "user": false, "masked": null}}, "state": "started", "changed": true, "name": "postgr

esql"}\n', '')
changed: [postgresql001] => {
    "changed": true, 
    "invocation": {
        "module_args": {
            "daemon_reload": false, 
            "enabled": null, 
            "masked": null, 
            "name": "postgresql", 
            "no_block": false, 
            "state": "restarted", 
            "user": false
        }
    }, 
    "name": "postgresql", 
    "state": "started", 
    "status": {
        "ActiveEnterTimestamp": "Tue 2017-08-08 11:01:49 UTC", 
        "ActiveEnterTimestampMonotonic": "1763252353", 
        "ActiveExitTimestamp": "Tue 2017-08-08 11:01:46 UTC", 
        "ActiveExitTimestampMonotonic": "1760095277", 
        "ActiveState": "active", 
        "After": "systemd-journald.socket system.slice basic.target [email protected] sysinit.target", 
        "AllowIsolate": "no", 
        "AmbientCapabilities": "0", 
        "AssertResult": "yes", 
        "AssertTimestamp": "Tue 2017-08-08 11:01:49 UTC", 
        "AssertTimestampMonotonic": "1763247726", 
        "Before": "multi-user.target exim4.service shutdown.target", 
        "BlockIOAccounting": "no", 
        "BlockIOWeight": "18446744073709551615", 
        "CPUAccounting": "no", 
        "CPUQuotaPerSecUSec": "infinity", 
        "CPUSchedulingPolicy": "0", 
        "CPUSchedulingPriority": "0", 
        "CPUSchedulingResetOnFork": "no", 
        "CPUShares": "18446744073709551615", 
        "CPUUsageNSec": "18446744073709551615", 
        "CPUWeight": "18446744073709551615", 
        "CanIsolate": "no", 
        "CanReload": "yes", 
        "CanStart": "yes", 
        "CanStop": "yes", 
        "CapabilityBoundingSet": "18446744073709551615", 
        "ConditionResult": "yes", 
        "ConditionTimestamp": "Tue 2017-08-08 11:01:49 UTC", 
        "ConditionTimestampMonotonic": "1763247726", 
        "Conflicts": "shutdown.target", 
        "ConsistsOf": "[email protected]", 
        "ControlPID": "0", 
        "DefaultDependencies": "yes", 
        "Delegate": "no", 
        "Description": "PostgreSQL RDBMS", 
        "DevicePolicy": "auto", 
        "DynamicUser": "no", 
        "ExecMainCode": "1", 
        "ExecMainExitTimestamp": "Tue 2017-08-08 11:01:49 UTC", 
        "ExecMainExitTimestampMonotonic": "1763252196", 
        "ExecMainPID": "9860", 
        "ExecMainStartTimestamp": "Tue 2017-08-08 11:01:49 UTC", 
        "ExecMainStartTimestampMonotonic": "1763248143", 
        "ExecMainStatus": "0", 
        "ExecReload": "{ path=/bin/true ; argv[]=/bin/true ; ignore_errors=no ; start_time=[n/a] ; stop_time=[n/a] ; pid=0 ; code=(null) ; status=0/0 }", 
        "ExecStart": "{ path=/bin/true ; argv[]=/bin/true ; ignore_errors=no ; start_time=[Tue 2017-08-08 11:01:49 UTC] ; stop_time=[Tue 2017-08-08 11:01:49 UTC] ; pid=9860 ; code=exited ; s

tatus=0 }",
        "FailureAction": "none", 
        "FileDescriptorStoreMax": "0", 
        "FragmentPath": "/lib/systemd/system/postgresql.service", 
        "GID": "4294967295", 
        "GuessMainPID": "yes", 
        "IOAccounting": "no", 
        "IOScheduling": "0", 
        "IOWeight": "18446744073709551615", 
        "Id": "postgresql.service", 
        "IgnoreOnIsolate": "no", 
        "IgnoreSIGPIPE": "yes", 
        "InactiveEnterTimestamp": "Tue 2017-08-08 11:01:46 UTC", 
        "InactiveEnterTimestampMonotonic": "1760095277", 
        "InactiveExitTimestamp": "Tue 2017-08-08 11:01:49 UTC", 
        "InactiveExitTimestampMonotonic": "1763248168", 
        "InvocationID": "24928b8fde164fa8987ff78cfc47eb01", 
        "JobTimeoutAction": "none", 
        "JobTimeoutUSec": "infinity", 
        "KillMode": "control-group", 
        "KillSignal": "15", 
        "LimitAS": "18446744073709551615", 
        "LimitASSoft": "18446744073709551615", 
        "LimitCORE": "18446744073709551615", 
        "LimitCORESoft": "0", 
        "LimitCPU": "18446744073709551615", 
        "LimitCPUSoft": "18446744073709551615", 
        "LimitDATA": "18446744073709551615", 
        "LimitDATASoft": "18446744073709551615", 
        "LimitFSIZE": "18446744073709551615", 
        "LimitFSIZESoft": "18446744073709551615", 
        "LimitLOCKS": "18446744073709551615", 
        "LimitLOCKSSoft": "18446744073709551615", 
        "LimitMEMLOCK": "65536", 
        "LimitMEMLOCKSoft": "65536", 
        "LimitMSGQUEUE": "819200", 
        "LimitMSGQUEUESoft": "819200", 
        "LimitNICE": "0", 
        "LimitNICESoft": "0", 
        "LimitNOFILE": "4096", 
        "LimitNOFILESoft": "1024", 
        "LimitNPROC": "1878", 
        "LimitNPROCSoft": "1878", 
        "LimitRSS": "18446744073709551615", 
        "LimitRSSSoft": "18446744073709551615", 
        "LimitRTPRIO": "0", 
        "LimitRTPRIOSoft": "0", 
        "LimitRTTIME": "18446744073709551615", 
        "LimitRTTIMESoft": "18446744073709551615", 
        "LimitSIGPENDING": "1878", 
        "LimitSIGPENDINGSoft": "1878", 
        "LimitSTACK": "18446744073709551615", 
        "LimitSTACKSoft": "8388608", 
        "LoadState": "loaded", 
        "MainPID": "0", 
        "MemoryAccounting": "no", 
        "MemoryCurrent": "18446744073709551615", 
        "MemoryDenyWriteExecute": "no", 
        "MemoryHigh": "18446744073709551615", 
        "MemoryLimit": "18446744073709551615", 
        "MemoryLow": "0", 
        "MemoryMax": "18446744073709551615", 
        "MemorySwapMax": "18446744073709551615", 
        "MountFlags": "0", 
        "NFileDescriptorStore": "0", 
        "Names": "postgresql.service", 
        "NeedDaemonReload": "no", 
        "Nice": "0", 
        "NoNewPrivileges": "no", 
        "NonBlocking": "no", 
        "NotifyAccess": "none", 
        "OOMScoreAdjust": "0", 
        "OnFailureJobMode": "replace", 
        "PermissionsStartOnly": "no", 
        "Perpetual": "no", 
        "PrivateDevices": "no", 
        "PrivateNetwork": "no", 
        "PrivateTmp": "no", 
        "PrivateUsers": "no", 
        "PropagatesReloadTo": "[email protected]", 
        "ProtectControlGroups": "no", 
        "ProtectHome": "no", 
        "ProtectKernelModules": "no", 
        "ProtectKernelTunables": "no", 
        "ProtectSystem": "no", 
        "RefuseManualStart": "no", 
        "RefuseManualStop": "no", 
        "RemainAfterExit": "yes", 
        "RemoveIPC": "no", 
        "Requires": "sysinit.target system.slice", 
        "Restart": "no", 
        "RestartUSec": "100ms", 
        "RestrictNamespace": "2114060288", 
        "RestrictRealtime": "no", 
        "Result": "success", 
        "RootDirectoryStartOnly": "no", 
        "RuntimeDirectoryMode": "0755", 
        "RuntimeMaxUSec": "infinity", 
        "SameProcessGroup": "no", 
        "SecureBits": "0", 
        "SendSIGHUP": "no", 
        "SendSIGKILL": "yes", 
        "Slice": "system.slice", 
        "StandardError": "inherit", 
        "StandardInput": "null", 
        "StandardOutput": "journal", 
        "StartLimitAction": "none", 
        "StartLimitBurst": "5", 
        "StartLimitIntervalSec": "10000000", 
        "StartupBlockIOWeight": "18446744073709551615", 
        "StartupCPUShares": "18446744073709551615", 
        "StartupCPUWeight": "18446744073709551615", 
        "StartupIOWeight": "18446744073709551615", 
        "StateChangeTimestamp": "Tue 2017-08-08 11:01:49 UTC", 
        "StateChangeTimestampMonotonic": "1763252353", 
        "StatusErrno": "0", 
        "StopWhenUnneeded": "no", 
        "SubState": "exited", 
        "SyslogFacility": "3", 
        "SyslogLevel": "6", 
        "SyslogLevelPrefix": "yes", 
        "SyslogPriority": "30", 
        "SystemCallErrorNumber": "0", 
        "TTYReset": "no", 
        "TTYVHangup": "no", 
        "TTYVTDisallocate": "no", 
        "TasksAccounting": "yes", 
        "TasksCurrent": "18446744073709551615", 
        "TasksMax": "4915", 
        "TimeoutStartUSec": "infinity", 
        "TimeoutStopUSec": "1min 30s", 
        "TimerSlackNSec": "50000", 
        "Transient": "no", 
        "Type": "oneshot", 
        "UID": "4294967295", 
        "UMask": "0022", 
        "UnitFilePreset": "enabled", 
        "UnitFileState": "enabled", 
        "UtmpMode": "init", 
        "WantedBy": "multi-user.target", 
        "Wants": "[email protected]", 
        "WatchdogTimestampMonotonic": "0", 
        "WatchdogUSec": "0"
    }
}
META: ran handlers

Debian-9 Stretch support

please add support of Debian-9 Stretch support

actually it's crashing with :

TASK [geerlingguy.postgresql : Include OS-specific variables (Debian).] ***************************************************************
fatal: [stretch]: FAILED! => {"ansible_facts": {}, "changed": false, "failed": true, "message": "Unable to find 'Debian-9.yml' in expected paths."}

Initialize fails - Issue with "ansible_ssh_pipelining"

I am running in an issue with the task "Ensure PostgreSQL database is initialized." in initialize.yml. The task fails with

TASK [postgresql : Ensure PostgreSQL database is initialized.] *****************
fatal: [myhost]: FAILED! => {"changed": false, "failed": true, "module_stderr": "Failed to add the host to the list of known hosts (/root/.ssh/known_hosts).\r\nFailed to add the ho st to the list of known hosts (/root/.ssh/known_hosts).\r\nsudo: sorry, you must have a tty to run sudo\n", "module_stdout": "", "msg": "MODULE FAILURE"}

I use this role by installing it with ansible-galaxy, and in a playbook with
name: Install postgresql
hosts: myhost
become: yes
roles:
- postgresql
vars:
postgresql_global_config_options:
- option: listen_addresses
value: '*'
postgresql_version: 9.4
tags:
- hdp
- database
The playbook is run in a vagrant environment.

I suspect that this issue is coming from the fact that I am using a ssh config with a ProxyCommand to ssh to "myhost" through a bastion host.
"myhost" and the bastion host are both running CentOS 7.

The task runs successfully when I comment the line
ansible_ssh_pipelining: true

Would it be possible to make this "ansible_ssh_pipelining" value overridable ?

Changing listen port breaks user configuration

I'd like to configure my PostgreSQL server to listen on a different port, e.g. 5433. Here goes:

Given I have the following role vars:

        postgresql_global_config_options:
          - option: port
            value: 5433
        postgresql_users:
          - name: user
            port: 5433

When I run my playbook
Then I get an error about the user creation:

unable to connect to database: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5433"?

What happens AFAIU:

1. the postgresql.conf is changed with port = 5433
2. PostgreSQL is not restarted
3. To create the new user, Ansible connects to PostgreSQL with port: 5433 but cannot find the correct Unix socket, because PostgreSQL is still listening on 5432

Alternate scenario
If I don't specify the port option in postgresql_users:

Given I have the following role vars:

        postgresql_global_config_options:
          - option: port
            value: 5433
        postgresql_users:
          - name: user

When I run my playbook once
When I run my playbook twice
Then I get an error about the user creation:

unable to connect to database: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Given I work with Debian, I worked around the problem by configuring an additional postgresql_locales, forcing an early restart of PostgreSQL by triggering this task
Edit: Wrong, the restart is done too early, before postgresql.conf is changed.

Check for sudo dependency

When you run this role (on a Debian9) with no sudo package installed, you run into the following error message:

TASK [geerlingguy.postgresql : Ensure PostgreSQL users are present.]
[...]
MSG:

MODULE FAILURE
See stdout/stderr for the exact error

MODULE_STDERR:

/bin/sh: 1: sudo: not found

(assuming you set no_log: false)

Dynamically creating pg_hba.conf from postgresql_databases and postgresql_users?

I've set up my vars so that I can allow access to my database via the internet. (Filtering is handled elsewhere.)

- postgresql_databases:
   - name: testdb
- postgresql_users:
   - name: testuser
- postgresql_hba_entries:
   - {type: host, database: testdb, user: testuser, address: all, auth_method: md5}
- postgresql_global_config_options:
   - option: listen_addresses
     value: '*'

Since we already have postgresql_databases and postgresql_users values I would prefer to use a Cartesian product to create the pg_hba entries but don't see how to do it. list1|lookup('cartisian' list2) wouldn't have all of the required fields. Creating my own pg_hba.conf.j2 template would work but would I have to fork this project?

I'll be switching to LDAP authentication soon but would still prefer to iterate over the postgresql_databases list if possible.

Ideas?

Feature request: Implement version checking

Hi,

i think it would be nice to have a version checking functionnality if the "postgresql_version" variable did not match the content of PG_VERSION file.
then, remove existing packages and install new ones.

• support of postgresql yum repositories (by version)

TY

Shouldn't the service restart after pg_hba.conf?

I was getting an authentification error, it was because the service was not restarted.

Using postgresql_global_config_options is not reversible

If you make a mistake setting your listen address by specifying (note address not addresses):

postgresql_global_config_options:
  - option: listen_address
    value: "*"

Running a play, which then means postgresql does not start, then correct your play and rerun it:

postgresql_global_config_options:
  - option: listen_addresses
    value: "*"

The broken configuration option remains in the configuration file.

In addition if you manually correct the file and fix the listen_address option to say listen_addresses the playbook does not detect the duplicate options, and remove one of them.

I'd strongly recommend using templates rather than regex substitutions to create configuration files so that the results are deterministic and reversible.

Centos6 : failed to start due to default parameter

i'm using the roles without any specific parametres.

postgres refuse to start on Centos6 (it's starting on other OS)
the module don't give me information about the issue :

TASK [geerlingguy.postgresql : Ensure PostgreSQL is started and enabled on boot.] ***
fatal: [centos6]: FAILED! => {"changed": false, "failed": true, "msg": "Starting postgresql service: [FAILED]\r\n"}

Debuging manually the issue give me the error :

runuser -l postgres -c '/usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data'
FATAL:  unrecognized configuration parameter "unix_socket_directories"

remove the parameter is working :

diff /var/lib/pgsql/data/postgresql.conf /var/lib/pgsql/data/postgresql.conf.old
502c502
< #unix_socket_directories = '/var/run/postgresql'
---
> unix_socket_directories = '/var/run/postgresql'
[root@centos6 ~]# /etc/init.d/postgresql start
Démarrage du service postgresql :                          [  OK  ]
[root@centos6 ~]# /etc/init.d/postgresql status
postmaster (pid  9089) en cours d'exécution...

can you remove this new option on old OS/ old postgres version ?

[root@centos6 ~]# rpm -qa |grep gres
postgresql-server-8.4.20-7.el6.x86_64
postgresql-8.4.20-7.el6.x86_64
postgresql-contrib-8.4.20-7.el6.x86_64
postgresql-libs-8.4.20-7.el6.x86_64

Support latest version via official apt/rpm repos

See:

• https://wiki.postgresql.org/wiki/Apt
• https://wiki.postgresql.org/wiki/YUM_Installation

This might make the initial configuration a little easier, if I can target just one version (e.g. 9.5 or 9.6) instead of like 10 different versions :P

psycopg2 module is required (module present)

1. pip list shows psycopg2 (2.7.5)
2. yum shows this Package python-psycopg2-2.7.5-1.rhel7.x86_64 already installed and latest version
   2a: python --version: Python 2.7.5
3. Ansible yml has this:

  roles:
    - role: ansible-role-postgresql
      become: yes
      postgresql_enablerepo: "pgdg96"
      postgresql_restarted_state: "restarted"
      postgresql_python_library: python-psycopg2
      postgresql_bin_path: /usr/pgsql-9.6/bin
      postgresql_daemon: postgresql-9.6.service 

4. Error thrown is this:

failed: [clpaxd-iwh01.na.ad.rrd.com] (item={u'owner': u'manifold', u'login_user': u'manifold', u'name': u'manifoldcf', u'login_password': u'manifold'}) => {"changed": false, "item": {"login_password": "manifold", "login_user": "manifold", "name": "manifoldcf", "owner": "manifold"}, "msg": "the python psycopg2 module is required"}

Environment:

1. Centos 7
2. Python 2.7.5
3. ansible 2.7.0.dev0 (stable-2.6 28d0a173db) last updated 2018/06/25 12:12:39 (GMT -500)

Any idea how to fix this?

postgres is not able to login to verify existence of database

I'm not sure I understand how or why the postgres user is not able to login to verify the database exists or not, but the following Playbook...

---
- hosts: concourse_db
  roles:
    - role: geerlingguy.postgresql
      tags:
        - concourse_db
      vars:
        postgresql_restarted_state: "restarted"
        postgresql_service_state: started
        postgresql_service_enabled: true
        postgresql_hba_entries:
          - { type: local, database: all, user: postgres, auth_method: peer }
          - { type: local, database: all, user: all, auth_method: peer }
          - { type: host, database: all, user: all, address: '127.0.0.1/32', auth_method: md5 }
          - { type: host, database: all, user: all, address: '::1/128', auth_method: md5 }
        postgresql_locales:
          - 'en_US.UTF-8'
        postgresql_databases:
          - name: concourse
            owner: concourse
            login_user: postgres
            state: present
        postgresql_users:
          - name: concourse
            password: concoursepassword
            encrypted: true
            db: concourse
            login_user: postgres
            state: present
...

Give the following errors:

TASK [geerlingguy.postgresql : Ensure PostgreSQL is started and enabled on boot.] ***************************************************************************************************
ok: [10.144.0.137]

TASK [geerlingguy.postgresql : Ensure PostgreSQL databases are present.] ************************************************************************************************************
An exception occurred during task execution. To see the full traceback, use -vvv. The error was: ProgrammingError: role "concourse" does not exist
failed: [10.144.0.137] (item={'name': 'concourse', 'owner': 'concourse', 'login_user': 'postgres', 'state': 'present'}) => {"changed": false, "item": {"login_user": "postgres", "name": "concourse", "owner": "concourse", "state": "present"}, "msg": "Database query failed: role \"concourse\" does not exist\n"}
	to retry, use: --limit @/Users/pd028300/Documents/development/Ansible/concourse_ci_playbook/playbook_2.retry

I'm not sure if its supposed to be using postgres user or the user I created, concourse in this instance.

Ansible-Galaxy requirements file

---
# https://galaxy.ansible.com/geerlingguy/postgresql
- src: geerlingguy.postgresql
  version: 1.4.5
...

Target System Information

[email protected]:~ ( concourse-ci-db.novalocal )
12:38:42 $ uname -a
Linux concourse-ci-db.novalocal 3.10.0-957.1.3.el7.x86_64 #1 SMP Mon Nov 26 17:43:08 PST 2018 x86_64 x86_64 x86_64 GNU/Linux
[email protected]:~ ( concourse-ci-db.novalocal )
12:38:44 $ cat /etc/oracle-release
Oracle Linux Server release 7.6

Ansible and Ansible Playbook versions

$ ansible --version && ansible-playbook --version
ansible 2.7.6
  config file = /Users/pd028300/Documents/development/Ansible/concourse_ci_playbook/ansible.cfg
  configured module search path = ['/Users/pd028300/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /Users/pd028300/.pyenv/versions/3.7.2/lib/python3.7/site-packages/ansible
  executable location = /Users/pd028300/.pyenv/versions/3.7.2/bin/ansible
  python version = 3.7.2 (default, Jan 22 2019, 09:35:39) [Clang 9.0.0 (clang-900.0.39.2)]
ansible-playbook 2.7.6
  config file = /Users/pd028300/Documents/development/Ansible/concourse_ci_playbook/ansible.cfg
  configured module search path = ['/Users/pd028300/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /Users/pd028300/.pyenv/versions/3.7.2/lib/python3.7/site-packages/ansible
  executable location = /Users/pd028300/.pyenv/versions/3.7.2/bin/ansible-playbook
  python version = 3.7.2 (default, Jan 22 2019, 09:35:39) [Clang 9.0.0 (clang-900.0.39.2)]

Deprecation warning: The use of 'include' for tasks has been deprecated

On latest version (1.3.1) I receive the following message when running a playbook that includes this role:

[DEPRECATION WARNING]: The use of 'include' for tasks has been deprecated. Use 
'import_tasks' for static inclusions or 'include_tasks' for dynamic inclusions.
 This feature will be removed in a future release. Deprecation warnings can be 
disabled by setting deprecation_warnings=False in ansible.cfg.
[DEPRECATION WARNING]: include is kept for backwards compatibility but usage is
 discouraged. The module documentation details page may explain more about this
 rationale.. This feature will be removed in a future release. Deprecation 
warnings can be disabled by setting deprecation_warnings=False in ansible.cfg.

$ ansible --version
ansible 2.4.0.0

CentOS 7 fails to install rh-postgresql10

I'm using Postgresql 10 with CentOS 7. I'm setting the postgresql_* variables to get it working. Everything works fine besides two things:

• The “Ensure PostgreSQL database is initialized” task is skipped, because the PG_VERSION file already exists
• The initialization command is differnt and is called postgresql-setup --initdb.

Thank you for this great module
kalkin-

Support for database clusters

Hi,

While checking out this role I came to the conclusion that it only supports one PostgreSQL cluster - the main one.

Some summary information to help out supporting clusters:
https://www.postgresql.org/docs/11/creating-cluster.html

Each cluster has:

• it's own configuration dir /etc/postgresq/{version}/{cluster_name} -> /etc/postgresql/11/main
• it's own data directories /var/lib/postgresql/{version}/{cluster_name} /var/lib/postgresql/11/main
• The users/roles and databases are unique per each cluster

Usefull cluster commands:

• pg_lsclusters
• pg_ctlcluster -> pg_ctlcluster 10 main restart or pg_ctlcluster 11 testcluster stop

I don't think that clusters are hard to support. Most of the things are related to being able to specify the cluster name as a parameter that has a default value of main .

Allow configuring pg_hba without overriding default entries

The postgresql_hba_entries variable includes the default entries for pg_hba.conf which are bundled with PostgreSQL. In most cases, I want to add entries to this list, rather than override them. It should be possible to achieve this without breaking backwards-compatibility by introducing a new variable postgresql_extra_hba_entries. If set, this is appended to the value of postgresql_hba_entries when pg_hba.conf is generated.

Is this a feature change which is likely to be accommodated? I am happy to submit a pull request implementing this, if so.

Annoying chicken-and-egg problem from PR 15

#15 introduced a swap to the order of creating databases and users.

The way it was originally, databases would be created first, then user accounts which could have privileges for those databases.

This swap caused a breaking change for any playbooks relying on that ordering, because you could create a generic database, then add users that have privileges on that database. With the change, it doesn't always work as cleanly to create generic users, then assign ownership using that user to a particular database.

Therefore I'm going to revert that change and bump the minor version again in favor of the original order (which is also the convention I use for my MySQL role, so it's more consistent that way).

postgres\nsudo: unable to initialize policy plugin

TASK [postgre_install : Ensure PostgreSQL database is initialized.]
"sudo: unknown user: postgres\nsudo: unable to initialize policy plugin\n", "module_stdout": "", "msg": "MODULE FAILURE\nSee stdout/stderr for the exact error", "rc": 1}

Fix Debian build failures when adding Databases and Users

I get the message:

unable to connect to database: fe_sendauth: no password supplied

On Ubuntu 16, 14, and Debian 8; see https://travis-ci.org/geerlingguy/ansible-role-postgresql/builds/163833924

Things succeed on CentOS 6 and 7.

Feature request: Add connection limit parameter to postgresql_user

Hi,

User connection limit setting has been supported since Ansible 2.4: https://docs.ansible.com/ansible/latest/modules/postgresql_user_module.html?highlight=conn_limit

It would be great if this role includes that setting. Thanks.

Error when trying to initialize the DB

Using CentOS 7 with a Postgres 9.6 repo. Trying to get it up and running but I keep getting tripped up on this step:

TASK [geerlingguy.postgresql : Ensure PostgreSQL database is initialized.] *****
fatal: [dc1-devops-db]: FAILED! => {"changed": false, "failed": true, "module_stderr": "sudo: a password is required\n", "module_stdout": "", "msg": "MODULE FAILURE"}

Any idea what I'm doing wrong?

Using default pg OS user.

Change default port on Centos

I tried to change the default port of the database with :

postgresql_databases:
  - name: mydatabase # required; the rest are optional
    port: "5555"

But role failed on starting postgresql service. (I had another instance of postgresql already running on 5432).

On centos 7.6, solution is to add in initalize.yml :

name: "create systemd overriden config"
  template:
    src: postgres.service.j2
    dest: /etc/systemd/system/postgresql.service
    mode: 0644
  notify: restart postgresql

With file :

.include /lib/systemd/system/postgresql.service
[Service]
Environment=PGPORT={{ postgresql_port }}
Environment=PGDATA={{ postgresql_data_dir }}

Do not forget to activate system-reload on handlers :

name: restart postgresql
   service:
    name: "{{ postgresql_daemon }}"
    daemon_reload: yes
    state: "{{ postgresql_restarted_state }}"

And add postgresql_port in defaults

Switch exeucion of adding users with execution of creating db

Configure PostgreSQL.

• import_tasks: databases.yml
• import_tasks: users.yml

change to

Configure PostgreSQL.

• import_tasks: users.yml
• import_tasks: databases.yml

The reason for this is to create user which is needed to use it as the owner of database.

Create role to install and manage postgres

We'd like a role to install postgres and manage it's configs.

Ubuntu 18.04.02 - PostgreSQL Not Enabled on Start

I am attempting to run this Ansible role against a Ubuntu 18.04.02 LTS server. Here is my playbook.yml file:

- name: Provision Python
  hosts: all
  gather_facts: no
  tasks:
    - name: Boostrap python
      raw: test -e /usr/bin/python || (apt-get -y update && apt-get install -y python-minimal)

- name: Install Ruby
  hosts: all
  roles:
    - role: ansible-role-ruby

- name: Install NodeJS
  hosts: all
  roles:
    - ansible-role-nodejs

- name: Install PostgreSQL
  hosts: all
  roles:
    - role: ansible-role-postgresql
      become: yes

- name: Modify .bashrc
  hosts: all
  tasks:
    - name: Execute items in profile.d
      lineinfile:
        path: /root/.bashrc
        line: |
          if [ -d /etc/profile.d ]; then
            for i in /etc/profile.d/*.sh; do
              if [ -r $i ]; then
                . $i
              fi
            done
            unset i
          fi

When the server is restarted, PostgreSQL does not launch on start. From what I can tell, postgresql_service_enabled is true by default, so I do not understand why it does not start.

Have I done something wrong here or is there a bug?

Support modifying the Client Authentication file (`pg_hba.conf`)

Without this postgres will only accept connections from the local machine.

Ensure role uses PostgreSQL's normal host-based authentication defaults

I'm getting the following error now in Drupal VM after applying PR #4 :

FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "drupal", database "drupal", SSL off

We need to make sure that all the generic hba entries are in the defaults (which can then be overridden by consumers of this playbook).

Create users before databases

When databases are created before roles, one cannot set the database to be owned by a role as it does not yet exist.

How do you install from PostgresSQL YUM repository

Hi,

I'm trying to use this role to install PostgresSQL 9.6 on Centos 7.

It's fine doing 9.2 which is in the base repos but I cannot work out how to configure it to use the PostgresSQL YUM repository in order to get 9.6.

What is the value that needs to go in the postgresql_enablerepo variable?

I can only find the repository rpms e.g. https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

When I set:

postgresql_enablerepo: "https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm"

It results in the error:

"Error setting/accessing repos: Error getting repository data for https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm, repository not found"

Can you see what I am doing wrong?

Thanks