Re: A change in the Debian install

On 04/07/2017 08:56 PM, rob stone wrote:
> Hello Adrian,
>
>
>
> On Thu, 2017-04-06 at 21:24 -0700, Adrian Klaver wrote:
>> On 04/06/2017 08:01 PM, rob stone wrote:
>>>
>>>
>>>
>> That is the default location and is generally the case in a source
>> install. Package maintainers can and do often put them elsewhere.
>> AFAIK
>> the Debian/Ubuntu way using postgresql-common is to put them in
>> /etc/postgresql/version/cluster_name/. So on one of my Ubuntu 16.04
>> installs with Postgres 9.6.2:
>>
>> aklaver(at)arkansas:~$ l /etc/postgresql/9.6/main/
>> total 56
>> drwxr-xr-x 2 postgres postgres 4096 Feb 11 16:23 ./
>> drwxr-xr-x 3 postgres postgres 4096 Feb 11 07:15 ../
>> -rw-r--r-- 1 postgres postgres 315 Feb 11 07:15 environment
>> -rw-r--r-- 1 postgres postgres 143 Feb 11 07:15 pg_ctl.conf
>> -rw-r----- 1 postgres postgres 4642 Feb 11 16:23 pg_hba.conf
>> -rw-r----- 1 postgres postgres 1636 Feb 11 07:15 pg_ident.conf
>> -rw-r--r-- 1 postgres postgres 22438 Feb 11 16:11 postgresql.conf
>> -rw-r--r-- 1 postgres postgres 317 Feb 11 07:15 start.conf
>>
>>
>> and PGDATA:
>>
>> aklaver(at)arkansas:~$ sudo ls -al /var/lib/postgresql/9.6/main/
>> total 92
>> drwx------ 19 postgres postgres 4096 Apr 6 15:54 .
>> drwxr-xr-x 3 postgres postgres 4096 Feb 11 07:15 ..
>> drwx------ 6 postgres postgres 4096 Feb 11 16:06 base
>> drwx------ 2 postgres postgres 4096 Mar 22 12:22 global
>> drwx------ 2 postgres postgres 4096 Feb 11 07:15 pg_clog
>> drwx------ 2 postgres postgres 4096 Feb 11 07:15 pg_commit_ts
>> drwx------ 2 postgres postgres 4096 Feb 11 07:15 pg_dynshmem
>> drwx------ 4 postgres postgres 4096 Feb 11 07:15 pg_logical
>> drwx------ 4 postgres postgres 4096 Feb 11 07:15 pg_multixact
>> drwx------ 2 postgres postgres 4096 Mar 22 12:21 pg_notify
>> drwx------ 2 postgres postgres 4096 Feb 11 07:15 pg_replslot
>> drwx------ 2 postgres postgres 4096 Feb 11 07:15 pg_serial
>> drwx------ 2 postgres postgres 4096 Feb 11 07:15 pg_snapshots
>> drwx------ 2 postgres postgres 4096 Mar 22 12:21 pg_stat
>> drwx------ 2 postgres postgres 4096 Feb 11 07:15 pg_stat_tmp
>> drwx------ 2 postgres postgres 4096 Feb 11 07:15 pg_subtrans
>> drwx------ 2 postgres postgres 4096 Feb 11 07:15 pg_tblspc
>> drwx------ 2 postgres postgres 4096 Feb 11 07:15 pg_twophase
>> -rw------- 1 postgres postgres 4 Feb 11 07:15 PG_VERSION
>> drwx------ 3 postgres postgres 4096 Feb 11 07:15 pg_xlog
>> -rw------- 1 postgres postgres 88 Feb 11 07:15
>> postgresql.auto.conf
>> -rw------- 1 postgres postgres 133 Mar 22 12:21 postmaster.opts
>> -rw------- 1 postgres postgres 100 Mar 22 12:21 postmaster.pid
>>
>
>> This is why I am trying to figure out if you are using the Debian
>> packaging:
>>
>> A) What is your PGDATA?
>>
>> B) How postgresql.conf got there?
>>
>> The reason it is important is that the next update will probably
>> land
>> you back in the same situation that started this thread, unless we
>> figure out what is going on.
>>
>> Another question:
>>
>> Have you ever installed Postgres on this machine using something
>> other
>> then the Debian packages?
>>
>
>
> Everything is done via a bog standard install from Debian repos.
>
> The installation process creates a path containing these files:-
>
> /etc/postgresql/9.5/main# ls -al
> total 56
> drwxr-xr-x 2 postgres postgres 4096 Jul 7 2016 .
> drwxr-xr-x 3 postgres postgres 4096 Feb 3 2016 ..
> -rw-r--r-- 1 postgres postgres 315 Feb 3 2016 environment
> -rw-r--r-- 1 postgres postgres 143 Feb 3 2016 pg_ctl.conf
> -rw-r----- 1 postgres postgres 4641 Feb 3 2016 pg_hba.conf
> -rw-r----- 1 postgres postgres 1636 Feb 3 2016 pg_ident.conf
> -rw-r--r-- 1 postgres postgres 21869 Feb 3 2016 postgresql.conf
> -rw-r--r-- 1 postgres postgres 378 Feb 3 2016 start.conf
>
> I can't show you the 9.6 ones as I removed them but they are on a
> backup. However, the number of files is exactly as above.
>
> The binaries are installed in /usr/lib/postgresl/VN/bin
>
> You create a new cluster by running initdb:-
>
> initdb -D /path/to/my/new/cluster

See below for correct way to do this using the Debian tools.

>
> That path has to exist and be empty otherwise initdb throws an error
> and exits. It then creates the sub-directories for pg_log, etc. and
> drops in the three conf files as defaults.
> You need to edit those conf files and set the parameters for your site.
> E.g., autovacuum, work memory, etc.
>
> Your PGDATA environment variable has to point to:-
> /path/to/my/new/cluster
> which means you need to set that up accordingly, depending on the
> applications accessing that cluster.
>
> You can create multiple clusters all running from the same set of
> binaries.
>
> I'm quite happy with the way in which the Debian packages are set up as
> it means you can have multiple clusters and each can have its own
> postgresql.conf file.
> For example you might have a production database streaming to a slave,
> a playpen database where users can try something out before running it
> on production and a training database that you restore from a dump file
> ate the beginning of each training session, and so forth.
>
> My original post was about the access sequence to the postgresql.conf
> file as it changed between 9.6.2-1 and 9.6.2-2 in that it looked first
> in the /etc path, found the default and tried to use it. In the past it
> always looked first in $PGDATA.
> By removing the conf files from the 9.6 /etc path it used the one in
> $PGDATA and the cluster started up without any errors and with the
> vacuum daemon running.
> So, if you want to have multiple clusters with each one using its own
> postgresql.conf, then you have to eliminate those files from the /etc
> path, or alter your start-up scripts to use the config_file parameter.
> Note that this also applies to the other two conf files.

No you don't, see Daniel's post for a link to the documentation for
using the Debian Postgresql packaging. As an example to init a new
cluster you would do:

aklaver(at)arkansas:~$ sudo pg_createcluster 9.6 test
[sudo] password for aklaver:
Creating new cluster 9.6/test ...
config /etc/postgresql/9.6/test
data /var/lib/postgresql/9.6/test
locale en_US.UTF-8
socket /var/run/postgresql

port 5433

The way you are doing it now is going to require a manual intervention
every time you upgrade.

>
>
> On two occasions over the years a beta release has been compiled from
> source but into the exact same directory structure as the Debian
> packages installed from the repo. When the "official" release became
> available, it just overwrote those binaries.
>
> I don't know how Postgres is structured on Ubuntu and always assumed it
> was identical to Debian.
>
> HTH.
>
> Cheers,
> Rob
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com