adrian.klaver@xxxxxxxxxxx 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".) The doc explains how to edit (at least) these: the "config_file", the "hba_file", and the "ident_file". You edit them to achieve certain supported outcomes. I mentioned that, during my "for real" installation (from a re-established freshly created Ubuntu VM) I noted how the config files were set up: -rw-r--r-- 1 postgres postgres 143 Nov 1 15:48 /etc/postgresql/11/main/pg_ctl.conf -rw-r----- 1 postgres postgres 4686 Nov 1 15:48 /etc/postgresql/11/main/pg_hba.conf -rw-r----- 1 postgres postgres 1636 Nov 1 15:48 /etc/postgresql/11/main/pg_ident.conf -rw-r--r-- 1 postgres postgres 24321 Nov 1 15:48 /etc/postgresql/11/main/postgresql.conf -rw-r--r-- 1 postgres postgres 317 Nov 1 15:48 /etc/postgresql/11/main/start.conf So only "postgres" can edit the files that must be so edited. 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: 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 recommends. 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? 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. Rather, I want, now, simply to declare victory with the script that I showed and return to ordinary productive work. |
