Upgrade development environment to PostgreSQL 9.6

[rbreslow]

Overview

In addition to adding support for PostgreSQL 9.6, upgrade PostGIS to 2.3, which coincides with the version utilized by RDS in staging and production.

Use the ansible_local provisioner to execute Ansible directly on the guest machine to avoid incompatibility with Ansible 2.9+ on hosts.

Lastly, the project specific init script for PostgreSQL was removed because it only existed to emit a postgresql-started event for the pgweb service, which is also being removed in this change set.

Resolves #527

---

Testing

• Destroy any existing development environment virtual machines

$ vagrant destroy -f

• Bring up the new development environment; ensure it provisions cleanly

$ vagrant up --provision

• Execute migrations

$ ./scripts/manage.sh migrate

• Execute the Django test suite and ensure it completes successfully

$ ./scripts/manage.sh test

• Ensure health check is passing

$ http localhost:8000/health-check/
HTTP/1.1 200 OK
Connection: keep-alive
Content-Encoding: gzip
Content-Type: application/json
Date: Tue, 07 Jan 2020 17:23:24 GMT
Server: nginx
Transfer-Encoding: chunked
Vary: Accept-Encoding

{
    "caches": [
        {
            "default": {
                "ok": true
            }
        }
    ],
    "databases": [
        {
            "default": {
                "ok": true
            }
        }
    ]
}

• Lastly, validate the versions of PostgreSQL and PostGIS

$ vagrant ssh services
vagrant@services:~$ sudo su postgres
postgres@services:/home/vagrant$ psql
psql (9.6.13)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 icp       | icp      | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=# \c icp
You are now connected to database "icp" as user "postgres".
icp=# SELECT version();
                                                                version
---------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.13 on x86_64-pc-linux-gnu (Ubuntu 9.6.13-1.pgdg14.04+1), compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.4) 4.8.4, 64-bit
(1 row)

icp=# SELECT postgis_version();
            postgis_version
---------------------------------------
 2.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

[hectcastro]

Started off my trying to provision these changes locally and I ran into a failure specific to the worker virtual machine as it attempts to install NTP:

vagrant@worker:~$ sudo apt install ntp=1:4.2.*
Reading package lists... Done
Building dependency tree
Reading state information... Done
Selected version '1:4.2.6.p5+dfsg-3ubuntu2.14.04.13+esm1' (UbuntuESM:14.04/trusty-infra-security [amd64]) for 'ntp'
The following extra packages will be installed:
  libopts25
Suggested packages:
  ntp-doc
The following NEW packages will be installed:
  libopts25 ntp
0 upgraded, 2 newly installed, 0 to remove and 0 not upgraded.
Need to get 450 kB/505 kB of archives.
After this operation, 1,683 kB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 https://esm.ubuntu.com/ubuntu/ trusty-infra-security/main ntp amd64 1:4.2.6.p5+dfsg-3ubuntu2.14.04.13+esm1 [450 kB]
Err https://esm.ubuntu.com/ubuntu/ trusty-infra-security/main ntp amd64 1:4.2.6.p5+dfsg-3ubuntu2.14.04.13+esm1
  HttpError401
E: Failed to fetch https://esm.ubuntu.com/ubuntu/pool/main/n/ntp/ntp_4.2.6.p5+dfsg-3ubuntu2.14.04.13+esm1_amd64.deb  HttpError401

E: Unable to fetch some archives, maybe run apt-get update or try with --fix-missing?

Seems unrelated to the changes, but posting in the event that it isn't transient or unique to me. Will look into it closer tomorrow.

[rbreslow]

@hectcastro After upgrading my Vagrant box I was able to run into the same issue. Pinning NTP to the last version described here seemed to fix provisioning issues for me:

https://www.ubuntuupdates.org/package/core/trusty/main/security/ntp

[rbreslow]

As a workaround, a virtualenv using the version of dependencies in the deployment/requirements.txt may have sufficed.

In hindsight, I would have first taken this approach.

Thanks for the review.