To monitor postgres logs you can navigate to cd "/usr/local/var/postgres" where you will find the server.log file.

If you want to watch the log in action you can pass the following command into the terminal tail -f server.log

Run your application in the background and if there is an issue occurring in postgres the server.log will catch it.

In my case, I ran RSpec in the background as that's where I saw the original "out of shared memory" message occur. The postgres log picked it up when it happen.

Someone previously ask a very similar question on the lists. Anyway, as told in the thread, reclassarg is a composite type.

RhodiumToad: Guest78493: in single-user, doing CREATE TYPE ... AS ...; doesn't create the array type

RhodiumToad: Guest78493: it creates only the non-array one, but I have no idea yet why

RhodiumToad: Guest78493: so for now, any extension with a CREATE TYPE AS won't load correctly in single-user

RhodiumToad: Guest78493: arguably if it doesn't work it should error rather than quietly getting it wrong, so I think you're justified in calling it a postgres bug

Further, from Tom Lane himself!

[ Can't create postgis extension in single-user mode ]

Why in the world would you think that's a good thing to do?

Single-user mode is a barely-documented disaster recovery aid. It's not meant for routine activity. There are a whole lot of behaviors you want that are turned off in single-user mode.

The specific reason why this doesn't work is this bit in heap_create_with_catalog:

/*
 * Decide whether to create an array type over the relation's rowtype. We
 * do not create any array types for system catalogs (ie, those made
 * during initdb). We do not create them where the use of a relation as
 * such is an implementation detail: toast tables, sequences and indexes.
 */
if (IsUnderPostmaster && (relkind == RELKIND_RELATION ||
                          relkind == RELKIND_VIEW ||
                          relkind == RELKIND_MATVIEW ||
                          relkind == RELKIND_FOREIGN_TABLE ||
                          relkind == RELKIND_COMPOSITE_TYPE))
    new_array_oid = AssignTypeArrayOid();

We could possibly develop some other mechanism for detecting whether we're within the initdb sequence, but I can't get very excited about treating this as a bug. Single-user mode hasn't been considered a standard user environment since maybe the early 90s.

If it was you, please update us in the future and self-answer your questions when you get word from the horse's mouth.

UPDATE.

So maybe there is a way to reformulate the PostGIS creation script so it achieves the same thing without requiring those language features, i.e. to make it "more portable".

No, there is not. ST_Reclass requires it. The method you're using is not supported. Initializing a database is currently viewed as a different thing than CREATE EXTENSION on one. I don't think PostgreSQL intends -single to be used in that way, and certainly PostGIS doesn't intent to rewrite working code so it functions in PostgreSQL's unsupported modes, with outdated single-user contexts.