Re: [GENERAL] A change in the Debian install

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

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.


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