city-of-bloomington / ansible-role-postgresql Goto Github PK

Ansible can now be run using Python3. However, our Postgres role is coded expecting to be only used in python 2. The only difference is installing the correct python postgres library.

Python2: python-psycopg2
Python3: python3-psycopg2

We should probably write Ansible to check which version of python is being used and install the appropriate psycopg2 library

The old way of doing apt with-items is deprecated. The new way is to declare a variable for all the package names.

New style for apt-get install

- name: "Install Dependencies"
  apt:
    name: "{{ packages }}"
    state: present
  vars:
    packages:
      - "postgis"

Old, deprecated style

- name: "Install Dependencies"
  apt:
    name: "{{ item }}"
    state: present
  with_items:
    - "postgis"

All the postgres commands during playbooks should be done using the postgres user. Playbooks and roles should have no need of an account for the user running the role.

This is in line with what we do in the MySQL role as well. If the admin running wants their own postgres account, they can create it themselves, manually.

The postgres role should be responsible for setting up a nightly database dump as a backup. It should probably do a pg_dumpall and gzip the sql file.

We have hard-coded version numbers in our ansible script. Since Ubuntu uses the version number in the configuration path, we should check which version of postgres is installed.

The regular expressions we wrote doesn't match the version string for version 10. We need to allow more digits.

We had been attempting to use ansible to maintain all the special user connection details for every server. It turns out pushing this information into an Ansible inventory is more trouble than it's worth.

The pg_hba customizations must be updated as we do user management in the database. Since the database user management happens outside of Ansible control, then it becomes a major hassle to attempt to keep postgresql_access_control host_vars up to date.

Our postgres servers are always going to be on the network, so we don't need a special postgresql_listen_addresses var. It can always be set to listen on '*'.

Ansible doesn't have a great way to write config files on a per-host basis.

Different hosts are going to have different entries in pg_hba.conf. But they're all going to start from the default provided by the distro. The order of the entries is important in pg_hba.conf. The first entry that applies is used with no fallthrough.

I think we should store all the entries as a hashmap. Then, hosts can provide their own custom version, if the group_var version is not good enough. We'll need to template out the pg_hba.conf file, writing values from the hashmap.

In order to restore an ArcGIS geodb safely, you must specify individual schemas to be restored, one at a time. This is not possible using the plain text dump format, which is the default for pg_dump. We need to update our backup CRON to use the binary dump format, so we can get clean restores.

We need to write the appropriate vars yml for 20.04

I think Ubuntu 20.04 is using postgresql 12

Since we started using ssh public key authentication, we have not been able to execute tasks as the postgres user. Ansible's privilege escalation cannot set the correct permissions on the task file that gets copied across to the host.

It looks like, since we aren't using password auth, Ansible attempts to use the "acl" package in Ubuntu to do this.