Adding PostGIS to PostgreSQL template1 so that all new databases get postgis automatically

[MikeTheCanuck]

Summary

Problem: users of the current RDS build need the database pre-created and the postgis extension already installed, or else they need superuser access to the RDS instance.

Impact

Frees up project teams to install as many databases later as they like without superuser (i.e. #team-infra) intervention.

Ensuring there is an SQL method of doing this will make this possible to add to the future "RDS server CloudFormation" automation.

Tasks

• [ ] Find out the manual (i.e. psql) way to do it
• [ ] Find the SQL command sequence to be able to do this the same way for every server

Definition of Done

It's a one-step SQL command to enable this on a new or existing RDS instance for #team-infra.

When RDS non-superusers create a new database, postgis is installed by default.

[MikeTheCanuck]

So Ed made this suggestion, which is a good one, and now I'm trying to track down the actual commands that make this automatable.

https://www.postgresql.org/docs/11/manage-ag-templatedbs.html talks about the existence of template1.

https://stackoverflow.com/a/24984040 talks about doing this against template1.

https://blog.dbi-services.com/what-the-hell-are-these-template0-and-template1-databases-in-postgresql/ and https://stackoverflow.com/questions/35173723/installing-extension-postgresql-9-4-for-all-schemes discuss a couple of methods of using psql for connecting to template1 and then running CREATE EXTENSION extensionx.

What's the SQL equivalent? Will it always be necessary to have a human run psql commands from an attached shell somewhere, for postgis to be available for their RDS instance? Seems ridiculous in a CI/CD day and age.

[MikeTheCanuck]

So it looks like the hard (manual, cloudformation-hostile) way would be something like this:

psql -h <host> -p <port> -U <username> template1
template1=>  create extension postgis;

To confirm which extensions are available in each database (via stackoverflow): psql> \dx

Other handy commands I keep having to look up:

• list databases from psql: \l
• quit psql: \q

[MikeTheCanuck]

And it appears from the GCloud documentation that this is THE method for extensions (as in, there is no SQL way to do this): https://cloud.google.com/sql/docs/postgres/extensions