Re: Putting the O/S user for "local" "peer" authentication in the "postgres" group vs chmod'ing the "pg*.conf" files to be readable by "all" - Mailing list pgsql-general

On 11/3/22 18:50, Bryn Llewellyn wrote:
>> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> wrote:


> 
>> 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.
> 
> Can't parse this. Sorry.

Short version, use what works for you.


> Fair enough. I started again from the state where my "config_file", my 
> "hba_file", and my "ident_file" are all customized as I want them to be 
> but where I hadn't yet tried to trash my cluster and re-create it. Then 
> I tried with "pg_dropcluster --stop" and "pg_createcluster". I 
> discovered immediately that this approach (in contrast to the "initdb" 

initdb will not work on an directory with existing files, so this:

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

Will only work if /var/lib/postgresql/11/main is empty, so none of your 
customized files will be there. You will have to copy them in just as 
you do below.

As example:

postgres@maura:/usr/local/pgsql15> bin/initdb -D data/
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.

initdb: error: directory "data" exists but is not empty
initdb: hint: If you want to create a new database system, either remove 
or empty the directory "data" or run initdb with an argument other than 
"data".


FYI, you might want to take a look at the the includes mechanism:

https://www.postgresql.org/docs/15/config-setting.html#CONFIG-SETTING-CONFIGURATION-FILE


> approach) blows away all the *.conf files and recreates them in 
> canonical form—just as the "man" page says. This is a bit of a nuisance. 
> But it's negotiable. I installed my files as I want them in a safe 
> place, outside of the entire PG world, and then used this script:
> sudo pg_dropcluster --stop 11 main

sudo pg_createcluster 11 main \
   -e UTF8 --locale=C --lc-collate=C --lc-ctype=en_US.UTF-8 \
   -d /var/lib/postgresql/11/main \
   > /dev/null

sudo cp /etc/ybmt-code/pg-and-yb-config-files/*.conf \
                     /etc/postgresql/11/main

sudo chown postgres /etc/postgresql/11/main/*.conf
sudo chgrp postgres /etc/postgresql/11/main/*.conf
sudo chmod 644      /etc/postgresql/11/main/*.conf
sudo chmod 640      /etc/postgresql/11/main/pg_hba.conf
sudo chmod 640      /etc/postgresql/11/main/pg_ident.conf

sudo pg_ctlcluster start 11/main

sudo -u postgres psql -c " select name, setting from pg_settings where 
category = 'File Locations'; "

sudo -u postgres psql -f 
/etc/ybmt-code/cluster-mgmt/01-initialize-brand-new-YB-or-PG-clstr/00.sql

> It worked without error and had the intended effect. My old approach 
> with the uncommitted chicken used to take ~3 sec. This new approach 
> takes ~5 sec. The difference is completely unimportant.
> 
> For various reasons, I need the non-standard "--lc-collate=C" choice. I 
> could doubtless leave all the other options unspecified. But that's the 
> style discussion we had the other day—and I prefer, here, to self-doc my 
> choices.
> 
> I discovered that using, say, the "initdb" approach, then the "kosher" 
> approach, and then the "initdb" approach brought all sorts of errors. 
> That's the beauty of using a VM and file backups (or snapshots). I 
> suppose this is to be expected.

Hence commitment instead of involvement.

> 
> *Can I declare victory, now, with the approach that I showed above?*

You are setting the goals not us, that is your decision.

> p.s. Is my pessimism justified—that there simply exists no plain English 
> user guide for this whole Debian/Ubuntu apparatus—correct. Or is it, 
> rather, that my search skills are just too feeble?

Not that I know of.

-- 
Adrian Klaver
adrian.klaver@aklaver.com