Creating a postgis database for ArcGIS

The Esri docs for PostgreSQL data types supported in ArcGIS v10.6 says in part [emphasis added]:

If you create your geodatabase using the Create Enterprise Geodatabase geoprocessing tool, your database is created using a template other than the PostGIS template. Therefore, if you want to use the PostGIS geometry or geography type, you must create a database manually using the PostGIS template. Then you can specify your existing database when you run the Create Enterprise Geodatabase geoprocessing tool, and the geodatabase will be created in this PostGIS-enabled database.

How exactly does one "create a db using the postgis template"?

We intend to use the postgres GEOGRAPHY type for GEOMETRY_STORAGE instead of the Esri default ST_GEOMETRY (ref).

I've installed Postgres 9.6 using the Esri supplied installer* and used Stack Builder to install PostGIS extension. I'm able to connect to Postgres with pgAdmin from a remote computer.

* PostgreSQL_DBMS_for_Windows_963_158796

update: fixed installed postgres version typos. Added ArcGIS version.

Answer

First start PGAdmin III and right click on Databases:

Then in your 'new database' dialog select your PostGIS template:

Be sure to either change your sde_dbtune GEOMETRY_STORAGE to PG_GEOMETRY or use the PG_GEOMETRY keyword when creating feature classes.

Despite being able to connect to PostgreSQL, that only means the port is open, you might have trouble retrieving a feature class/dataset if you haven't installed the PG Client on the workstation side. Read more https://community.esri.com/thread/114415 - the version is not the ArcGIS version it's the PostgreSQL version.

Another trap for beginners is that each connected user should have their own schema to store large selections, if you don't create a schema for each login user they will receive an error (with an unhelpful error message) when they select more than a handful of records (I don't know the exact number).

I hope some of this information brings you less facepalm moments.

This is what I have in PostGIS template:

Perhaps you can make one by creating extensions in an empty database; I'd like to hear from anyone who has done so successfully.