Re: [GENERAL] 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@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@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@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.klaver@aklaver.com