A change in the Debian install

rob stone <floriparob@gmail.com> writes:

Upgraded to version 9.6.2-2 and these are the log entries on start-up:-

2017-04-05 08:03:29 AESTLOG:  test message did not get through on
socket for statistics collector

This is not something that would be affected by any Postgres setting;
the stats collector always depends on a loopback TCP connection to
"localhost", and has done so since day one. If it was working before,
you need to look at what you changed related to network and firewall
configuration. These symptoms looks like some kernel firewall rule
deciding to block local-loopback TCP traffic, for what that's worth.

(But ... these statements are based on an assumption of out-of-the-
box Postgres behavior. I would not exactly put it past the Debian
packagers to have decided to change this for reasons of their own,
and their track record of telling us about such decisions is many
miles south of abysmal. So you might look at whatever patches
are in the Debian package to see if there's anything touching
pgstat.c's socket-setup logic.)

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 04/05/2017 08:03 PM, rob stone wrote:

Hello,

Postgres is started via pg_ctl and NOT systemd.
Below are the log entries when version 9.6.2-1 is started.

2017-04-04 07:15:27 AESTLOG: database system was shut down at 2017-04-
03 13:08:27 AEST
2017-04-04 07:15:28 AESTLOG: MultiXact member wraparound protections
are now enabled
2017-04-04 07:15:28 AESTLOG: database system is ready to accept
connections
2017-04-04 07:15:28 AESTLOG: autovacuum launcher started

Upgraded to version 9.6.2-2 and these are the log entries on start-up:-

What repos are you using, the Debian or the PGDG one?

I guess the question I should really ask is, are you using a repo or
some other method to upgrade?

2017-04-05 08:03:29 AESTLOG: test message did not get through on
socket for statistics collector
2017-04-05 08:03:29 AESTLOG: disabling statistics collector for lack
of working socket
2017-04-05 08:03:29 AESTWARNING: autovacuum not started because of
misconfiguration
2017-04-05 08:03:29 AESTHINT: Enable the "track_counts" option.
2017-04-05 08:03:29 AESTLOG: database system was shut down at 2017-04-
04 13:05:46 AEST
2017-04-05 08:03:30 AESTLOG: MultiXact member wraparound protections
are now enabled
2017-04-05 08:03:30 AESTLOG: database system is ready to accept
connections

It is ignoring the PGDATA path and obtaining postgresql.conf from
/etc/postgresql/9.6/main.

Removed all the Postgres conf files from the /etc path and it is back
working as intended.

Is this just something the Debian package maintainers have done or do
we have to alter the start-up scripts to specify the config_file
setting?

The doco says "By default, all three configuration files are stored in
the database cluster's data directory." which IMHO means where PGDATA
is pointing.

That is packaging dependent. When using the Debian/Ubuntu
postgresql-common system the postgresql.conf will be in
/etc/postgresql/version/cluster_name/

Has anybody else struck this issue?

Cheers,
Rob

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Apr 06, 2017 at 12:05:51AM -0400, Tom Lane wrote:

rob stone <floriparob@gmail.com> writes:

Upgraded to version 9.6.2-2 and these are the log entries on start-up:-

2017-04-05 08:03:29 AESTLOG:��test message did not get through on
socket for statistics collector

...

(But ... these statements are based on an assumption of out-of-the-
box Postgres behavior. I would not exactly put it past the Debian
packagers to have decided to change this for reasons of their own,
and their track record of telling us about such decisions is many
miles south of abysmal. So you might look at whatever patches
are in the Debian package to see if there's anything touching
pgstat.c's socket-setup logic.)

PG 9.6 works just fine for me on mostly-Stable and Testing++
Debian systems.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

(But ... these statements are based on an assumption of out-of-the-
box Postgres behavior. I would not exactly put it past the Debian
packagers to have decided to change this for reasons of their own,
and their track record of telling us about such decisions is many
miles south of abysmal. So you might look at whatever patches
are in the Debian package to see if there's anything touching
pgstat.c's socket-setup logic.)

I don't believe this is really a fair assessment. Maybe at some point
in the distant past, but not today. Christoph is regularly on this list
contributing to threads regarding packaging, submitting patches of his
own for improvements to PG, and the patches currently included in the
Debian distribution, at least mostly, are for things which really should
be possible to do with configure options, but which we don't provide
today, or things we should just be handling already.

The non-comment/documentation patches include for the Debian PG 9.6
packages are:

50-per-version-dirs.patch
Use version specific installation directories so that several major
versions can be installed in parallel. This includes changing
pkglibdir and includedir_server. Those might be able to be set
through existing configure flags and that's probably something we
could work with Christoph to do. There's also a change to pg_config
which might be a bit more difficult to handle in upstream (related to
how pg_config ends up in /usr/bin, but that isn't the "right" BINDIR).

51-default-sockets-in-var.patch
Use /var/run/postgresql/ for the DEFAULT_PGSOCKET_DIR. We really
should allow this to be changed in configure.

54-debian-alternatives-for-external-tools.patch
Use 'sensible-editor' for DEFAULT_EDITOR, and 'pager' for
DEFAULT_PAGER. These could also be done through configure switches, I
would think.

64-pg_upgrade-sockdir
This is a bit of a curious one, the description is:
Fix for: connection to database failed: Unix-domain socket path
"/build/buildd-postgresql-9.3_9.3~beta1-1-i386-mHjRUH/postgresql-9.3-9.3~beta1/build/contrib/pg_upgrade/.s.PGSQL.50432"
is too long (maximum 107 bytes)

See also: http://lists.debian.org/debian-wb-team/2013/05/msg00015.html

This basically adds a mechanism to fall back to using /tmp if the
socket path is too long. Would probably be good to figure out a
better way.

filter-debug-prefix-map
Description: Remove -fdebug-prefix-map=/p/w/d=. from CFLAGS and
CONFIGURE in pg_config. The embedded path makes the build
non-reproducible, and the flag is useless for building extensions
anyway.
Not sure there's much we can do about this one, but it's also just for
pg_config.

hurd-sem_init
Adds -pthread to LIBS and forces POSIX semaphores on hurd. I'm not
sure if this is all still required, but if so, it'd probably be ideal
if we could figure out a way to handle it automatically instead of
making the Debian packagers have to do it.

In short, there's certainly nothing that touches pgstat.c's socket-setup
logic and almost all of the changes are just changes to #define's to
specify alternative paths or external tools.

I'll start a discussion with Christoph on if we might, already, be able
to remove some of these, and where we might be able to make upstream
changes to remove the need for others.

Thanks!

Stephen

On Thu, Apr 6, 2017 at 3:46 PM, Stephen Frost <sfrost@snowman.net> wrote:

Tom,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

(But ... these statements are based on an assumption of out-of-the-
box Postgres behavior. I would not exactly put it past the Debian
packagers to have decided to change this for reasons of their own,
and their track record of telling us about such decisions is many
miles south of abysmal. So you might look at whatever patches
are in the Debian package to see if there's anything touching
pgstat.c's socket-setup logic.)

I don't believe this is really a fair assessment. Maybe at some point
in the distant past, but not today. Christoph is regularly on this list
contributing to threads regarding packaging, submitting patches of his
own for improvements to PG, and the patches currently included in the
Debian distribution, at least mostly, are for things which really should
be possible to do with configure options, but which we don't provide
today, or things we should just be handling already.

+1. While this may have been true in a *very* distant past, it's certainly
not anymore. So let's try to avoid spreading disinformation about that.

And FWIW, the RPM distributions have about the same number of patches...

51-default-sockets-in-var.patch
Use /var/run/postgresql/ for the DEFAULT_PGSOCKET_DIR. We really
should allow this to be changed in configure.

This looks exactly like something the RPMs want as well, so we should
definitely look at providing that upstream.

I'll start a discussion with Christoph on if we might, already, be able

to remove some of these, and where we might be able to make upstream
changes to remove the need for others.

That'd be useful. I think you should also include Devrim to figure out what
things would actually make *both* sides happier.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On Wed, 2017-04-05 at 21:14 -0700, Adrian Klaver wrote:

What repos are you using, the Debian or the PGDG one?

I guess the question I should really ask is, are you using a repo or 
some other method to upgrade?

Upgraded from standard Debian repos.

Nothing else was changed other than the binaries.
It appears that the prior version obtained postgresql.conf from the
PGDATA path whilst the 9.6.2-2 looked in the Postgres /etc paths where
it tried to use the conf file, and as a consequence, the autovacuum
daemon was not started.
I removed the Postgres /etc conf file and as it didn't find one there,
fell back to the PGDATA path and all is well.

That is packaging dependent. When using the Debian/Ubuntu 
postgresql-common system the postgresql.conf will be in 
/etc/postgresql/version/cluster_name/

I'm quite happy with the standard /etc/postgresql/major_version/main
method of installation. It means you can run different major versions
on the same box.

Cheers,
Rob

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 04/06/2017 03:16 PM, rob stone wrote:

On Wed, 2017-04-05 at 21:14 -0700, Adrian Klaver wrote:

What repos are you using, the Debian or the PGDG one?

I guess the question I should really ask is, are you using a repo or
some other method to upgrade?

Upgraded from standard Debian repos.

Nothing else was changed other than the binaries.
It appears that the prior version obtained postgresql.conf from the
PGDATA path whilst the 9.6.2-2 looked in the Postgres /etc paths where
it tried to use the conf file, and as a consequence, the autovacuum
daemon was not started.
I removed the Postgres /etc conf file and as it didn't find one there,
fell back to the PGDATA path and all is well.

That is packaging dependent. When using the Debian/Ubuntu
postgresql-common system the postgresql.conf will be in
/etc/postgresql/version/cluster_name/

I'm quite happy with the standard /etc/postgresql/major_version/main
method of installation. It means you can run different major versions
on the same box.

Which is what has me confused. If you are using the postgresql-common
system then the *.conf files should be in
/etc/postgresql/version/cluster_name/.

Where exactly is PGDATA and why is the *.conf file there?

Cheers,
Rob

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, 2017-04-06 at 15:58 -0700, Adrian Klaver wrote:

On 04/06/2017 03:16 PM, rob stone wrote:

Which is what has me confused. If you are using the postgresql-
common 
system then the *.conf files should be in 
/etc/postgresql/version/cluster_name/.

Where exactly is PGDATA and why is the *.conf file there?

It's always been in the PGDATA path and it has always worked.
The doco implies that PGDATA is the place for the *.conf files unless
overridden by the config_file parameter.

Cheers,
Rob

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 04/06/2017 08:01 PM, rob stone wrote:

On Thu, 2017-04-06 at 15:58 -0700, Adrian Klaver wrote:

On 04/06/2017 03:16 PM, rob stone wrote:

Which is what has me confused. If you are using the postgresql-
common
system then the *.conf files should be in
/etc/postgresql/version/cluster_name/.

Where exactly is PGDATA and why is the *.conf file there?

It's always been in the PGDATA path and it has always worked.
The doco implies that PGDATA is the place for the *.conf files unless
overridden by the config_file parameter.

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?

Cheers,
Rob

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Magnus Hagander 2017-04-06 <CABUevEzkTLFSFZvCVsk07oEVgSZmWo9kPaGiJtrONYjQrH5PLw@mail.gmail.com>

On Thu, Apr 6, 2017 at 3:46 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

(But ... these statements are based on an assumption of out-of-the-
box Postgres behavior. I would not exactly put it past the Debian
packagers to have decided to change this for reasons of their own,
and their track record of telling us about such decisions is many
miles south of abysmal. So you might look at whatever patches
are in the Debian package to see if there's anything touching
pgstat.c's socket-setup logic.)

I don't believe this is really a fair assessment. Maybe at some point
in the distant past, but not today. Christoph is regularly on this list
contributing to threads regarding packaging, submitting patches of his
own for improvements to PG, and the patches currently included in the
Debian distribution, at least mostly, are for things which really should
be possible to do with configure options, but which we don't provide
today, or things we should just be handling already.

+1. While this may have been true in a *very* distant past, it's certainly
not anymore. So let's try to avoid spreading disinformation about that.

And FWIW, the RPM distributions have about the same number of patches...

Thanks Stephen and Magnus. I don't think the way Martin and I have
been handling the Debian packages over the last year deserves being
bashed that loudly. At least I would expect a Cc on such matters, Tom
should know very well whom to address here.

51-default-sockets-in-var.patch
Use /var/run/postgresql/ for the DEFAULT_PGSOCKET_DIR. We really
should allow this to be changed in configure.

This looks exactly like something the RPMs want as well, so we should
definitely look at providing that upstream.

That one is touching src/include/pg_config_manual.h only, i.e.
something that is actually meant to be altered.

https://anonscm.debian.org/cgit/pkg-postgresql/postgresql.git/tree/debian/patches/51-default-sockets-in-var.patch?h=10

I'll start a discussion with Christoph on if we might, already, be able

to remove some of these, and where we might be able to make upstream
changes to remove the need for others.

That'd be useful. I think you should also include Devrim to figure out what
things would actually make *both* sides happier.

Aye.

Re: Stephen Frost 2017-04-06 <20170406134629.GT9812@tamriel.snowman.net>

The non-comment/documentation patches include for the Debian PG 9.6
packages are:

50-per-version-dirs.patch
Use version specific installation directories so that several major
versions can be installed in parallel. This includes changing
pkglibdir and includedir_server. Those might be able to be set
through existing configure flags and that's probably something we
could work with Christoph to do.

Nod. If someone figures how to translate that to configure (or
possibly make) arguments, I'd be happy to move to using that.

There's also a change to pg_config
which might be a bit more difficult to handle in upstream (related to
how pg_config ends up in /usr/bin, but that isn't the "right" BINDIR).

pg_config is special there, because we ship it twice, once in
/usr/bin/ libpq-dev, and then again in
/usr/lib/postgresql/$version/bin/ from postgresql-server-dev-version.
Not sure if there's a saner approach that still allows co-installation
of multiple versions, while still supplying a pg_config from libpq-dev
that allows using --includedir and other version-independent queries.

https://anonscm.debian.org/cgit/pkg-postgresql/postgresql.git/tree/debian/patches/50-per-version-dirs.patch?h=10

54-debian-alternatives-for-external-tools.patch
Use 'sensible-editor' for DEFAULT_EDITOR, and 'pager' for
DEFAULT_PAGER. These could also be done through configure switches, I
would think.

https://anonscm.debian.org/cgit/pkg-postgresql/postgresql.git/tree/debian/patches/54-debian-alternatives-for-external-tools.patch?h=10

64-pg_upgrade-sockdir
This is a bit of a curious one, the description is:
Fix for: connection to database failed: Unix-domain socket path
"/build/buildd-postgresql-9.3_9.3~beta1-1-i386-mHjRUH/postgresql-9.3-9.3~beta1/build/contrib/pg_upgrade/.s.PGSQL.50432"
is too long (maximum 107 bytes)

See also: http://lists.debian.org/debian-wb-team/2013/05/msg00015.html

This basically adds a mechanism to fall back to using /tmp if the
socket path is too long. Would probably be good to figure out a
better way.

That one is actually on my TODO list:
/messages/by-id/20140711094009.GB3115@msg.df7cb.de
I'll need to restart working on it.

https://anonscm.debian.org/cgit/pkg-postgresql/postgresql.git/tree/debian/patches/64-pg_upgrade-sockdir?h=10

filter-debug-prefix-map
Description: Remove -fdebug-prefix-map=/p/w/d=. from CFLAGS and
CONFIGURE in pg_config. The embedded path makes the build
non-reproducible, and the flag is useless for building extensions
anyway.
Not sure there's much we can do about this one, but it's also just for
pg_config.

I hope to be able to drop this one again. There is a patch for gcc in
preparation that will allow using a constant -fdebug-prefix-map=
compile flag that doesn't embed the current build directory:

https://tests.reproducible-builds.org/debian/issues/unstable/captures_build_path_issue.html

https://anonscm.debian.org/cgit/pkg-postgresql/postgresql.git/tree/debian/patches/filter-debug-prefix-map?h=10

hurd-sem_init
Adds -pthread to LIBS and forces POSIX semaphores on hurd. I'm not
sure if this is all still required, but if so, it'd probably be ideal
if we could figure out a way to handle it automatically instead of
making the Debian packagers have to do it.

This one is already gone again for the 10 packaging. The 9.6 version
is:

https://anonscm.debian.org/cgit/pkg-postgresql/postgresql.git/tree/debian/patches/hurd-sem_init

In short, there's certainly nothing that touches pgstat.c's socket-setup
logic and almost all of the changes are just changes to #define's to
specify alternative paths or external tools.

I don't have an answer the OP's question. My bet would be on
"localhost" resolving to something weird.

Christoph

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 04/05/2017 09:05 PM, Tom Lane wrote:

(But ... these statements are based on an assumption of out-of-the-
box Postgres behavior. I would not exactly put it past the Debian
packagers to have decided to change this for reasons of their own,
and their track record of telling us about such decisions is many
miles south of abysmal. So you might look at whatever patches
are in the Debian package to see if there's anything touching
pgstat.c's socket-setup logic.)

Wow Tom, FUD much?

Let's remember that the .Deb folks are .Org members are try very hard to
create quality packages that are of production quality.

JD

regards, tom lane

--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

rob stone wrote:

You create a new cluster by running initdb:-

initdb -D /path/to/my/new/cluster

No, if you want to use the infrastructure brought by the
debian packages for your cluster, it should be created with:
pg_createcluster 9.5 nameofcluster

See https://wiki.debian.org/PostgreSql
or the manpages of pg_createcluster and pg_wrapper.

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.

When using the abovementioned commands you don't need or want
to do that, because /etc/postgresql/<version>/<cluster> is the
only place they're going to be in.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general