BUG #12670: Can't create postgis extension in single-user mode

warrior2031@mail.ru writes:

[ 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.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

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

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

The context, for what it's worth, is setting up docker containers, so
there's a reasonable desire to be able to do some basic database setup
before starting any listener.

(Not my project; my only involvement in this is in answering questions
on irc, as usual)

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

Ok, I'll bite: what are they?

(I did a quick review of uses of IsUnderPostmaster. Essentially none of
them are visible at SQL level except this one (the only other one that
comes close is the disabling of event triggers). Almost everything else
is about process management; the only exceptions I found are one for
accepting tsearch dictionary options and another for running as
superuser in spite of missing role entries.)

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

Which personally I think is misguided. There never was any good reason
other than rowcount of pg_type not to make array types for catalog
tables, and it's inconsistent not to be able to use arrays of system
views or i_s views and so on.

--
Andrew (irc:RhodiumToad)

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Andrew Gierth <andrew@tao11.riddles.org.uk> writes:

"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
Tom> Single-user mode is a barely-documented disaster recovery aid.
Tom> It's not meant for routine activity. There are a *whole lot*
Tom> of behaviors you want that are turned off in single-user mode.

Ok, I'll bite: what are they?

By far the worst problem is the lack of any background checkpointing
behavior. But you probably don't want to be without autovacuum either
(which requires the stats collector). The other background processes
are more about performance than minimum expected functionality.

Now, I'll agree that if you just intend to install a couple of simple
extensions, you might be able to get away with doing that much without
any background support processes. But it's not a mode meant for
routine activity. And I rather wonder why you need to install
extensions in that way anyway.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 01/28/2015 03:49 AM, Tom Lane wrote:

Now, I'll agree that if you just intend to install a couple of simple
extensions, you might be able to get away with doing that much without
any background support processes. But it's not a mode meant for
routine activity. And I rather wonder why you need to install
extensions in that way anyway.

Using the single-user mode for installing extensions etc. is a sensible
idea; or was if it worked. It'd be nice to run initdb, install
extensions, even load data etc. all in single-user mode, before opening
up the cluster for connections. Sure, you can hack pg_hba.conf /
postgresql.conf to keep the real users away until you're done with the
setup, but it's more hassle than just using the single-user mode.

We had discussions some time ago about teaching the backend to speak the
regular FE/BE protocol in single-user mode, and having libpq "connect"
to that. That would still be nice...

- Heikki

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Heikki Linnakangas <hlinnakangas@vmware.com> writes:

On 01/28/2015 03:49 AM, Tom Lane wrote:

Now, I'll agree that if you just intend to install a couple of simple
extensions, you might be able to get away with doing that much without
any background support processes. But it's not a mode meant for
routine activity. And I rather wonder why you need to install
extensions in that way anyway.

Using the single-user mode for installing extensions etc. is a sensible
idea; or was if it worked. It'd be nice to run initdb, install
extensions, even load data etc. all in single-user mode,

... and right there is where the argument falls to the ground. Loading
any significant amount of data without background checkpointing, bgwriter,
wal writer, etc will be exceedingly slow, *and* have the risk of having
to replay a whole lot of WAL if you have any problems. It's not apparent
to me how this is superior to loading all the same definitions and data
after the system is up for real.

We had discussions some time ago about teaching the backend to speak the
regular FE/BE protocol in single-user mode, and having libpq "connect"
to that. That would still be nice...

Yeah, I was one of the ones pushing it. But the idea stalled after it
was pointed out how unpleasant an operating environment it would be.

At this point I think a more practical solution to the lock-out-users-
for-maintenance problem would be to invent some postmaster command line
switch for overriding pg_hba.conf. Not sure exactly what it would look
like.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 2015-01-28 01:05:20 +0000, Andrew Gierth wrote:

"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

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

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

The context, for what it's worth, is setting up docker containers, so
there's a reasonable desire to be able to do some basic database setup
before starting any listener.

Well, that's about as easily done using a nonstandard/private socket
directory. Has the advantage of being able to use a sane client, useful
error handling, performance ...

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Wed, Jan 28, 2015 at 10:23:25AM -0500, Tom Lane wrote:

At this point I think a more practical solution to the lock-out-users-
for-maintenance problem would be to invent some postmaster command line
switch for overriding pg_hba.conf. Not sure exactly what it would look
like.

See pg_upgrade for the most popular current lockout method.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Wed, Jan 28, 2015 at 04:26:03PM +0100, Andres Freund wrote:

On 2015-01-28 01:05:20 +0000, Andrew Gierth wrote:

"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

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

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

The context, for what it's worth, is setting up docker containers, so
there's a reasonable desire to be able to do some basic database setup
before starting any listener.

Well, that's about as easily done using a nonstandard/private socket
directory. Has the advantage of being able to use a sane client, useful
error handling, performance ...

Yes, that's what pg_upgrade does. Windows doesn't have sockets, which
limits that option for general use.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs