On 11/3/22 14:49, Bryn Llewellyn wrote:
I should make it clear that my VM has four "ordinary" users. Two are
present on first use when VM creation finishes: the system administrator
(called "parallels") and, of course, "root". The "parallels" user is
enabled for "sudo". Installing PG brings "postgres". (I created mine
before hand, and it was unperturbed by the PG installation. This is my
private laptop. And it suits me to give it a home directory and to
customize its ".bashrc". But that's so that I can look around with the
minimum of fuss.) Finally, there's my "clstr_mgr" O/S user that acts as
the authorization vehicle for my "clstr$mgr" cluster-role. Each of
"postgres" and "clstr_mgr" is in its own singleton group—and in no other
groups. And neither is set up for "sudo".)
So only "postgres" can edit the files that must be so edited.
That is not true:
aklaver@arkansas:~$ whoami
aklaver
aklaver@arkansas:~$ sudo vi /etc/postgresql/14/main/pg_hba.conf
[sudo] password for aklaver:
which opens pg_hba.conf for editing.
Apparently, an unwritten rule says that one must never end up so that
"whoami" shows "postgres". I see that I can, then, always do, for
example, this (from "parallels"):
*sudo -u postgres vi pg_hba.conf*
And, given that one major theme in our recent mutual, extended,
exchanges is that I want to use "local", "peer" authentication for the
cluster-role "postgres" via the O/S user with the same name, I see that
I can always run all the SQL scripts that I want, using this
authentication, like this:
You want to use local peer with OS user postgres, that is not a
requirement. You could set up Postgres to log in the db user postgres by
any of the other auth means and do the below without sudo(ing) to OS
user postgres.
*sudo -u postgres psql -f my_script.sql*
With this in mind, I re-wrote my "clean start" script thus:
*#!/bin/bash
*
*# do this as ANY user that can do "sudo" (and this includes "root" itself)
sudo pg_ctlcluster stop 11/main
sudo rm -Rf /var/lib/postgresql/11/main
sudo -u postgres initdb \
-U postgres --encoding UTF8 --locale=C --lc-collate=C
--lc-ctype=en_US.UTF-8 \
-A md5 --pwfile=my_password_file \
-D /var/lib/postgresql/11/main
sudo pg_ctlcluster start 11/main
sudo -u postgres **psql -f my_script.sql**
*
It certainly works. And when it's finished, this:
*sudo -u postgres psql -c " select name, setting from pg_settings where
category = 'File Locations'; "
*
shows that my files are where they ought to be. Then, further tests show
that "local", "peer" authorization works as intended for my "clstr$mgr"
role and that I can connect from client machines. So all is good.
Why, though, is "pg_ctlcluster stop/start... " preferred over "systemctl
stop/start postgresql?". Both are quick enough. And I can't see any
difference in effect. Moreover, the latter is what
https://ubuntu.com/server/docs/databases-postgresql
<https://ubuntu.com/server/docs/databases-postgresql> recommends.
It isn't you where using pg_ctl and in the Debian/Ubuntu packaging the
better option for that is pg_ctlcluster. I generally use the systemd
scripts to start/stop Postgres instances, though when I do pg_lsclusters
I tend to fall into using pg_ctlcluster as the cluster info is right there.
Notice that I'm still using the off-limits "initdb" here. (I wired it up
with a link on "/usr/bin".) Is there any reason to change that and to
try to work out how to use what David (in an email soon after Adrian's)
suggested?
Yes and since that is basically coloring outside the lines, then that
leads to the below blowing up.
*sudo pg_dropcluster --stop 11 main
sudo pg_createcluster 11 main
sudo pg_ctlcluster start 11/main-
*
I tried it. But it "did not work". I don't have the energy to describe
the errors that were reported and the nasty outcomes that I got when I
tried to use the new cluster. There's no useful doc for that approach
and I've already established that Internet search gets me nowhere. So
I'm inclined not to use it.
Per the saying, "In a ham and eggs breakfast the chicken is involved but
the pig is committed", right now you are involved in the Debian/Ubuntu
process not committed. Until you commit you will not get the results you
want.
Rather, I want, now, simply to declare victory with the script that I
showed and return to ordinary productive work.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
