Обсуждение: Re: [GENERAL] A change in the Debian install
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
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
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
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
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
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
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