On 11/2/22 15:31, Bryn Llewellyn wrote:
david.g.johnston@xxxxxxxxx <mailto:david.g.johnston@xxxxxxxxx> EARLIER
wrote:
A strange mutual misunderstanding has arisen here. I suppose that it
must be my fault. I have no interest whatsoever in "going down to first
principles". And I most certainly never said that I want to "have the
o/s admin and postgres bootstrap user be anything but postgres". On the
contrary: I want just that. Saying this more abstractly, I want to
install PG (admittedly the old version 11) in a freshly created Ubuntu
20.04 LTS VM. And I want to follow the reigning notions of proper
practice. As far as possible, I'd like to find that I simply get such an
outcome without explicit intervention—or at least by accepting all the
defaults.
I did exactly that. And I selected "Linux" and under that "Ubuntu".
Notice that I did NOT select "Debian", though it was on offer, because
that's not what I have. If Ubuntu and Debian were effectively the same,
then there wouldn't be two distinct choices. My choice took me here:
They use the same basic packaging and wrapper mechanism. They are
separated out because, very simple explanation, Ubuntu is downstream of
Debian and the version releases are not the same.
In summary, then, I followed a link from the PG doc to a site whose URL
makes it sound "official", selected my environment, and followed some
simple steps as specified. That doesn't sound like "opinionated" to me.
Nor does it sound like stubbornly insisting on doing things my own way.
It is official as these are the community released packages.
The opinionated reference was to what the Debian/Ubuntu packaging does,
not you.
Adrian gave me this link:
https://ubuntu.com/server/docs/databases-postgresql
<https://ubuntu.com/server/docs/databases-postgresql>
Of course I'd read that right at the outset. The subtext is loud and
clear. You need to do some things as the "postgres" user and some things
as "root". That's why I enabled "sudo" for "postgres" (just as the code
examples on that page imply).
You don't need to do that. Just use sudo as what ever user you log in
as. For example:
aklaver@arkansas:~$ sudo pg_ctlcluster stop 14/main
[sudo] password for aklaver:
Again very simple explanation, the OS postgres user is just created to
run the server. It does not even have a home directory.
However, I have no a priori requirement to use the root-needing
"systemctl" to stop and start my cluster. But I do want to be able to
use "initdb" so that I can simply trash an extant cluster and start
again from a well-defined, pristine state. (I want to do this to be
completely sure that my own scripts make no assumptions about
pre-existing objects.) This script works perfectly well:
*sudo systemctl stop postgresql
rm -Rf /var/lib/postgresql/11/main
initdb \
-U postgres --encoding UTF8 --locale=C --lc-collate=C
--lc-ctype=en_US.UTF-8 \
-A md5 --pwfile=/etc/ybmt-code/misc/.superuser-passwd-for-initdb \
-D /var/lib/postgresql/11/main
sudo systemctl start postgresql
psql -f <my_script.sql>
*
But it does require that my "postgres" O/S user is enabled for "sudo" —
which you all insist is a terrible crime, even on my personal laptop
that nobody else can access. Moreover, I'm using "initdb". This is
apparently verboten (it isn't exposed via a link on "/usr/bin")—in the
env produced by the installation procedure that the PG doc lead me to.
The same is true for "pg_ctl" and "postgres". A bit of Googling took me
here:
Again, when in Rome:
sudo pg_createcluster 15 test
Creating new PostgreSQL cluster 15/test ...
/usr/lib/postgresql/15/bin/initdb -D /var/lib/postgresql/15/test
--auth-local peer --auth-host scram-sha-256 --no-instructions
The files belonging to this database system will be owned by user
"postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/15/test ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/Los_Angeles
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Ver Cluster Port Status Owner Data directory Log file
15 test 5433 down postgres /var/lib/postgresql/15/test
/var/log/postgresql/postgresql-15-test.log
man pg_createcluster
for more options.
So, apparently, Debian and Ubuntu are the same after all, even though
they have separate install pages from www.postgresql.org/download/linux
<http://www.postgresql.org/download/linux>.
The same as Debian and Ubuntu are.
Anyway, the examples show "sudo pg_ctlcluster ..." and that defeats the
aim of doing everything as the "postgres" user.
But as my example above shows you will be, in that the cluster will be
owned by postgres.
This, I believe, is why Adrian wrote this:
«
If you are going to use the Debian/Ubuntu packaging then you will need
to follow its "rules".
Which you have not been doing.
But it seems that I'm now in a regime where critical PG utilities don't
work like the PG doc says, and where what you need, "pg_ctlcluster",
isn't even mentioned in the PG doc. (I do see that it's present in my
env and is properly wired up from "/usr/bin". (Actually, it's right
there on that directory as an executable file.)
Because they are package specific programs. You will find similar
differences in the RH family packaging.
How can it be that the PG doc itself leads you by the hand to a regime
where you need to use undocumented features?
Because the docs are for the unpackaged version of Postgres, namely what
you would get if you installed from source.
And, b.t.w., the debian.org site that Adrian linked me to is NOT
ordinary user-facing doc. Moreover, "pg_ctlcluster --help" fails with
"Unknown option: help". That's a terrible start. Of course I did "man
pg_ctlcluster". This doesn't give a single example of using the command.
It says that it "essentially wraps the pg_ctl(1) command". But what does
"essentially" mean—is that some flavor of "partially"? And it doesn't
hint at why wrapping is essential and why the bare command cannot be used.
Where is the ordinary, prose, account of what this is all about—and why
the ordinary documented PG executables cannot be used as intended. It's
the same story for "pg_createcluster" as a wrapper for "initdb".
They can be used as intended, though you may end up with Postgres
clusters outside the place where the packaging expects them.
Yet, somehow, "systemctl start postgresql" happily manages to find my
customized config files in the location where I did the customization.
It's hard to imagine a more confusing design. What thinking underlies it?
Don't customize, use the provided tools. The idea behind all this is to
have the provided tools allow you to run multiple clusters of the same
version or different versions of Postgres concurrently and have the
tools take care of setting up the cluster data_dir and config_dir and
assign ports.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
