date:20181031


On Wed, 31 Oct 2018, Adrian Klaver wrote:

Even with assurances I would back up that directory(assuming space available) 
before proceeding with a rebuild. Or do you have a recent dump of the 
cluster?


Adrian,

  That's my thinking, too. No, an explicit pg_dumpall is too old to be
useful. I have daily uncremental backups made by dirvish but I'll copy the
451M data directory to a USB thumb drive for safe keeping.

Regards,

Rich

Re: Broken postgres links need to find callers


On Wed, 31 Oct 2018, Adrian Klaver wrote:


Hmm in the build script the difference is:

VERSION=${VERSION:-10.3}
PG_VERSION=${PG_VERSION:-10.3}

--docdir=/usr/doc/$PRGNAM-$VERSION \
--datadir=/usr/share/$PRGNAM-$PG_VERSION \

Wonder where the script is finding PG_VERSION?
Do you have env variable set for that?


Adrian,

  The first two lines set the variables, along with the earlier PRGNAM
variable:

PRGNAM=postgresql
VERSION=${VERSION:-10.3}
BUILD=${BUILD:-1}
TAG=${TAG:-_SBo}

PG_VERSION=${PG_VERSION:-10.3}
PG_PORT=${PG_PORT:-5432}

  When I check the local repository of installed packages that's how it
displays:

$ ls /var/log/packages/ | grep postgresql
postgresql-10.3-i586-1_SBo

  So, /usr/doc/ should have postgresql-10.3, and it is:
$ ls /usr/doc/postgresql*
/usr/doc/postgresql-10.3

and

$ ls /usr/share/postgresql-10.3/

  So why 'pg_config --configure' has them at 10.2 doesn't follow from the
build configuration.

Regards,

Rich

Re: Broken postgres links need to find callers


On 10/31/18 3:19 PM, Rich Shepard wrote:

On Wed, 31 Oct 2018, Adrian Klaver wrote:


Well there is something strange going. From a previous post:


Andrew,

   Yet it ran without a whimper from the upgrade last March 1st to this
morning when I modified postgresql.conf.

   It's the middle of the night in central Europe so I expect to hear from
the package maintainer tomorrow about a re-buid/re-installation not 
touching

/var/lib/pgsql/data/.


The thing is from here:

http://slackbuilds.org/result/?search=postgres=14.2

I only see Postgres 10.2.



Thanks,

Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Broken postgres links need to find callers


On 10/31/18 3:19 PM, Rich Shepard wrote:

On Wed, 31 Oct 2018, Adrian Klaver wrote:


Well there is something strange going. From a previous post:


Andrew,

   Yet it ran without a whimper from the upgrade last March 1st to this
morning when I modified postgresql.conf.

   It's the middle of the night in central Europe so I expect to hear from
the package maintainer tomorrow about a re-buid/re-installation not 
touching

/var/lib/pgsql/data/.


Even with assurances I would back up that directory(assuming space 
available) before proceeding with a rebuild. Or do you have a recent 
dump of the cluster?




Thanks,

Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Broken postgres links need to find callers


On Wed, 31 Oct 2018, Adrian Klaver wrote:


Well there is something strange going. From a previous post:


Andrew,

  Yet it ran without a whimper from the upgrade last March 1st to this
morning when I modified postgresql.conf.

  It's the middle of the night in central Europe so I expect to hear from
the package maintainer tomorrow about a re-buid/re-installation not touching
/var/lib/pgsql/data/.

Thanks,

Rich

Re: Broken postgres links need to find callers


On 10/31/18 3:08 PM, Adrian Klaver wrote:

On 10/31/18 3:03 PM, Rich Shepard wrote:

On Wed, 31 Oct 2018, Andrew Gierth wrote:


What this says is that you somehow have a pg 10.3 binary which has been
compiled with ./configure --datadir=/usr/share/postgresql-10.2

which seems, to say the least, somewhat odd.


Andrew,

   Quite odd rather than somewhat odd because the configure options in 
the

build script point to version 10.3, not 10.2.


Well there is something strange going. From a previous post:

"
When I run pg_config --configure this is the result:
# ./pg_config --configure
'--prefix=/usr/lib/postgresql/10.2' '--sysconfdir=/etc/postgresql/10.2'
'--includedir=/usr/include' '--datarootdir=/usr/share' '--mandir=/usr/man'
'--docdir=/usr/doc/postgresql-10.3' '--datadir=/usr/share/postgresql-10.2'
'--with-openssl' '--with-tcl' '--with-perl' '--with-python' '--with-libxml'
'--with-libxslt' '--enable-thread-safety'
'--with-system-tzdata=/usr/share/zoneinfo' '--disable-nls'
'--build=i586-slackware-linux' 'build_alias=i586-slackware-linux'
'CFLAGS=-O2 -march=i586 -mtune=i686' 'PKG_CONFIG_PATH=/usr/lib/pkgconfig

"

Note '--docdir=/usr/doc/postgresql-10.3' where everything else is 
pointing at 10.2/


Hmm in the build script the difference is:

VERSION=${VERSION:-10.3}
PG_VERSION=${PG_VERSION:-10.3}

--docdir=/usr/doc/$PRGNAM-$VERSION \
--datadir=/usr/share/$PRGNAM-$PG_VERSION \

Wonder where the script is finding PG_VERSION?

Do you have env variable set for that?





pg_config isn't used by the postgres binary to find paths, so 
"fixing" it

wouldn't help. The same paths that were compiled into pg_config are
compiled into the postgres binary, and pg_config and postgres contain 
the

same relocation logic.


   Okay.

   I'll check with the SlackBuilds.org postgresql package maintainer and
confirm that re-building and re-installing 10.3 will not adversely affect
the data/ directory. Then I'll re-build and re-install it.

   Why it worked flawlessly until today when I modified the 
postgresql.conf

file to add access by another host name and then broke is also quite odd.

Thanks,

Rich








--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Broken postgres links need to find callers


On 10/31/18 3:03 PM, Rich Shepard wrote:

On Wed, 31 Oct 2018, Andrew Gierth wrote:


What this says is that you somehow have a pg 10.3 binary which has been
compiled with ./configure --datadir=/usr/share/postgresql-10.2

which seems, to say the least, somewhat odd.


Andrew,

   Quite odd rather than somewhat odd because the configure options in the
build script point to version 10.3, not 10.2.


Well there is something strange going. From a previous post:

"
When I run pg_config --configure this is the result:
# ./pg_config --configure
'--prefix=/usr/lib/postgresql/10.2' '--sysconfdir=/etc/postgresql/10.2'
'--includedir=/usr/include' '--datarootdir=/usr/share' '--mandir=/usr/man'
'--docdir=/usr/doc/postgresql-10.3' '--datadir=/usr/share/postgresql-10.2'
'--with-openssl' '--with-tcl' '--with-perl' '--with-python' '--with-libxml'
'--with-libxslt' '--enable-thread-safety'
'--with-system-tzdata=/usr/share/zoneinfo' '--disable-nls'
'--build=i586-slackware-linux' 'build_alias=i586-slackware-linux'
'CFLAGS=-O2 -march=i586 -mtune=i686' 'PKG_CONFIG_PATH=/usr/lib/pkgconfig

"

Note '--docdir=/usr/doc/postgresql-10.3' where everything else is 
pointing at 10.2/





pg_config isn't used by the postgres binary to find paths, so "fixing" it
wouldn't help. The same paths that were compiled into pg_config are
compiled into the postgres binary, and pg_config and postgres contain the
same relocation logic.


   Okay.

   I'll check with the SlackBuilds.org postgresql package maintainer and
confirm that re-building and re-installing 10.3 will not adversely affect
the data/ directory. Then I'll re-build and re-install it.

   Why it worked flawlessly until today when I modified the postgresql.conf
file to add access by another host name and then broke is also quite odd.

Thanks,

Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Broken postgres links need to find callers


On 10/31/18 2:40 PM, Rich Shepard wrote:

On Wed, 31 Oct 2018, Rich Shepard wrote:


Still bad links remaining.


   Every pg_* not in /usr/lib/postgresql/10.3/bin/ now points to its 
namesake

there.


Hmm. Grasping at straws. In a previous post you mentioned:

"If it matters, there's no /etc/postgresql/ and none in the backups 
since the beginning of August. "


What is in the backups at the beginning of August.

As Andrew pointed out the binary you are using thinks its files are in 
10.2/.


The question is what was running Postgres 'correctly' before this most 
recent change?


Is there information in the system logs from your last successful start 
that you would help with this?






   Question: if pg_dump, pg_dumpall, pg_restore, pg_ctl, and pg_controldata
have symlinks in /usr/bin/ do they also need symlinks in /bin/? The ones
found there dated back to 2010 and I don't know that they're needed now.

   pg_ctl start still cannot find the proper timezonesets/ directory. If 
you
have suggestions what pg_ctl is calling that looks in the non-existent 
10.2/

directory instead of the existing 10.3/ directory please let me know.

Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Broken postgres links need to find callers


On Wed, 31 Oct 2018, Andrew Gierth wrote:


What this says is that you somehow have a pg 10.3 binary which has been
compiled with ./configure --datadir=/usr/share/postgresql-10.2

which seems, to say the least, somewhat odd.


Andrew,

  Quite odd rather than somewhat odd because the configure options in the
build script point to version 10.3, not 10.2.


pg_config isn't used by the postgres binary to find paths, so "fixing" it
wouldn't help. The same paths that were compiled into pg_config are
compiled into the postgres binary, and pg_config and postgres contain the
same relocation logic.


  Okay.

  I'll check with the SlackBuilds.org postgresql package maintainer and
confirm that re-building and re-installing 10.3 will not adversely affect
the data/ directory. Then I'll re-build and re-install it.

  Why it worked flawlessly until today when I modified the postgresql.conf
file to add access by another host name and then broke is also quite odd.

Thanks,

Rich

Re: Broken postgres links need to find callers


On Wed, 31 Oct 2018, Adrian Klaver wrote:

So no, as I presume you rebooted on the kernel upgrade which caused the 
Postgres server to stop/start.


  True. It stopped for the time it took the server to reboot.

Rich

Re: Broken postgres links need to find callers


On 10/31/18 2:01 PM, Rich Shepard wrote:


Did the server been running continuously from the upgrade to the time you
made the listen_addresses change?


   Yes, other than a few kernel upgrades.


So no, as I presume you rebooted on the kernel upgrade which caused the 
Postgres server to stop/start.




[1] This prompted me to look for more pg_config files, and I found a 
symlink

in /usr/bin/ that pointed to /usr/lib/postgresql/10.2/bin/pg_config which
does not exist. I changed that symlink to point to the 10.3/ pg_config
version but there's still a broken link somewhere because pg_ctl start
cannot find the correct directory for timezonesets.

Regards,

Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Broken postgres links need to find callers

2018-10-31 Thread Andrew Gierth

> "Rich" == Rich Shepard  writes:

 Rich>   Yes, pg_config is present but pointing to the wrong directory:
 Rich> # /usr/lib/postgresql/10.3/bin/pg_config --sharedir
 Rich> /usr/share/postgresql-10.2

What this says is that you somehow have a pg 10.3 binary which has been
compiled with ./configure --datadir=/usr/share/postgresql-10.2

which seems, to say the least, somewhat odd.

pg_config isn't used by the postgres binary to find paths, so "fixing"
it wouldn't help. The same paths that were compiled into pg_config are
compiled into the postgres binary, and pg_config and postgres contain
the same relocation logic.

-- 
Andrew (irc:RhodiumToad)

Re: Broken postgres links need to find callers


On Wed, 31 Oct 2018, Rich Shepard wrote:


Still bad links remaining.


  Every pg_* not in /usr/lib/postgresql/10.3/bin/ now points to its namesake
there.

  Question: if pg_dump, pg_dumpall, pg_restore, pg_ctl, and pg_controldata
have symlinks in /usr/bin/ do they also need symlinks in /bin/? The ones
found there dated back to 2010 and I don't know that they're needed now.

  pg_ctl start still cannot find the proper timezonesets/ directory. If you
have suggestions what pg_ctl is calling that looks in the non-existent 10.2/
directory instead of the existing 10.3/ directory please let me know.

Rich

Re: Broken postgres links need to find callers


On Wed, 31 Oct 2018, Rich Shepard wrote:


I'll fix those links and report the results of running pg_ctl start.


  Still bad links remaining. Some of those symlinks in /usr/bin/ dated back
to versons 9.4 and 9.6. Why they were not removed during upgrades remains a
mystery.

Rich

Re: Broken postgres links need to find callers


On Wed, 31 Oct 2018, Rich Shepard wrote:


[1] This prompted me to look for more pg_config files, and I found a symlink
in /usr/bin/ that pointed to /usr/lib/postgresql/10.2/bin/pg_config which
does not exist. I changed that symlink to point to the 10.3/ pg_config
version but there's still a broken link somewhere because pg_ctl start
cannot find the correct directory for timezonesets.


  Thanks for pointing me to the links in /usr/bin/, Adrian. All of them are
pointing to the mythical 10.2/ directory. I'll fix those links and report
the results of running pg_ctl start.

Regards,

Rich

Re: Broken postgres links need to find callers


On Wed, 31 Oct 2018, Adrian Klaver wrote:

Are there actually 10.2/ directories or is that just what you are seeing in 
the error messages and the pg_config output?


Adrian,

  No 10.2/ directories, only what is shown in the error messages and
pg_config output.


Previously you used:
/usr/lib/postgresql/10.3/bin/pg_config
is that the same as below?:

# ./pg_config --configure


  Yes. I was in that directory when I ran pg_config.[1]


In other words what does:
./pg_config --version
show?


  This shows 10.3


Did the server been running continuously from the upgrade to the time you
made the listen_addresses change?


  Yes, other than a few kernel upgrades.

[1] This prompted me to look for more pg_config files, and I found a symlink
in /usr/bin/ that pointed to /usr/lib/postgresql/10.2/bin/pg_config which
does not exist. I changed that symlink to point to the 10.3/ pg_config
version but there's still a broken link somewhere because pg_ctl start
cannot find the correct directory for timezonesets.

Regards,

Rich

Re: Broken postgres links need to find callers


On 10/31/18 1:09 PM, Rich Shepard wrote:

On Wed, 31 Oct 2018, Adrian Klaver wrote:


What does:
pg_ctl --version
show?


# pg_ctl --version
pg_ctl (PostgreSQL) 10.3


So when you added the new application did you make any other changes?


   I did not add another application; grass has been installed here for
decades. Because I could not connect to the postgres database for a spatial
project it was suggested that I expand the listen_addresses to include the
server name, too.

At this point you need to get back to two discreet Postgres installs 
10.2 and 10.3.


    I did not have two distinct installations of postgres, only the 10.3
version. It was some of the directories labeled 10.2/ that seem to be the
issue.


Are there actually 10.2/ directories or is that just what you are seeing 
in the error messages and the pg_config output?




    When I run pg_config --configure this is the result:


Previously you used:

/usr/lib/postgresql/10.3/bin/pg_config

is that the same as below?:


# ./pg_config --configure


In other words what does:

./pg_config --version

show?



me. And why psql worked without issue from the upgrade date of March 1 to
today with this inconsistency also puzzles me.


Did the server been running continuously from the upgrade to the time 
you made the listen_addresses change?




   If it matters, there's no /etc/postgresql/ and none in the backups since
the beginning of August.

Regards,

Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Broken postgres links need to find callers


On Wed, 31 Oct 2018, Adrian Klaver wrote:


What does:
pg_ctl --version
show?


# pg_ctl --version
pg_ctl (PostgreSQL) 10.3


So when you added the new application did you make any other changes?


  I did not add another application; grass has been installed here for
decades. Because I could not connect to the postgres database for a spatial
project it was suggested that I expand the listen_addresses to include the
server name, too.

At this point you need to get back to two discreet Postgres installs 10.2 and 
10.3.


   I did not have two distinct installations of postgres, only the 10.3
version. It was some of the directories labeled 10.2/ that seem to be the
issue.

   When I run pg_config --configure this is the result:
# ./pg_config --configure
'--prefix=/usr/lib/postgresql/10.2' '--sysconfdir=/etc/postgresql/10.2'
'--includedir=/usr/include' '--datarootdir=/usr/share' '--mandir=/usr/man'
'--docdir=/usr/doc/postgresql-10.3' '--datadir=/usr/share/postgresql-10.2'
'--with-openssl' '--with-tcl' '--with-perl' '--with-python' '--with-libxml'
'--with-libxslt' '--enable-thread-safety'
'--with-system-tzdata=/usr/share/zoneinfo' '--disable-nls'
'--build=i586-slackware-linux' 'build_alias=i586-slackware-linux'
'CFLAGS=-O2 -march=i586 -mtune=i686' 'PKG_CONFIG_PATH=/usr/lib/pkgconfig

  Notice that the prefix, sysconfdir, and datadir specify 10.2. But, the
build script shows the version as 10.3 and uses that to configure the build
(entire script will be provided on request):

PRGNAM=postgresql
VERSION=${VERSION:-10.3}
BUILD=${BUILD:-1}
TAG=${TAG:-_SBo}

PG_VERSION=${PG_VERSION:-10.3}
PG_PORT=${PG_PORT:-5432}
PG_UID=${PG_UID:-209}
PG_GID=${PG_GID:-209}
...
./configure \
  --prefix=/usr/lib${LIBDIRSUFFIX}/$PRGNAM/$PG_VERSION \
  --sysconfdir=/etc/$PRGNAM/$PG_VERSION \
  --includedir=/usr/include \
  --datarootdir=/usr/share \
  --mandir=/usr/man \
  --docdir=/usr/doc/$PRGNAM-$VERSION \
  --datadir=/usr/share/$PRGNAM-$PG_VERSION \

  The build script sets the prefix, sysconfdir, and datadir to $PG_VERSION
which is defined as 10.3 so why 10.2 shows up in pg_config makes no sense to
me. And why psql worked without issue from the upgrade date of March 1 to
today with this inconsistency also puzzles me.

  If it matters, there's no /etc/postgresql/ and none in the backups since
the beginning of August.

Regards,

Rich

Re: Broken postgres links need to find callers


On 10/31/18 12:14 PM, Rich Shepard wrote:

On Wed, 31 Oct 2018, Adrian Klaver wrote:


listen_addresses = ''


Adrian,

#listen_addresses = ''

$ pg_ctl start -D /var/lib/pgsql/10.3/data/
waiting for server to start2018-10-31 12:12:39.530 PDT [4398] 
FATAL:  could not open directory 
"/usr/share/postgresql-10.2/timezonesets": No such file or directory
2018-10-31 12:12:39.530 PDT [4398] HINT:  This may indicate an 
incomplete PostgreSQL installation, or that the file 
"/usr/lib/postgresql/10.3/bin/postgres" has been moved away from its 
proper location.

  stopped waiting
pg_ctl: could not start server


What does:

pg_ctl --version

show?



listen_addresses = ''

$ pg_ctl start -D /var/lib/pgsql/10.3/data/
waiting for server to start2018-10-31 12:13:28.141 PDT [4413] 
FATAL:  could not open directory 
"/usr/share/postgresql-10.2/timezonesets": No such file or directory
2018-10-31 12:13:28.141 PDT [4413] HINT:  This may indicate an 
incomplete PostgreSQL installation, or that the file 
"/usr/lib/postgresql/10.3/bin/postgres" has been moved away from its 
proper location.

  stopped waiting
pg_ctl: could not start server



So it seems listen_addresses is not the issue at this point.

So when you added the new application did you make any other changes?

At this point you need to get back to two discreet Postgres installs 
10.2 and 10.3.


1) Remove all the symlinks you made.

2) In the 10.3/ verify that the programs in bin/ are actually the 10.3 
ones using prgm_name --version.


3) Try pg_ctl start.





Thanks,

Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Broken postgres links need to find callers


On Wed, 31 Oct 2018, Adrian Klaver wrote:


listen_addresses = ''


Adrian,

#listen_addresses = ''

$ pg_ctl start -D /var/lib/pgsql/10.3/data/
waiting for server to start2018-10-31 12:12:39.530 PDT [4398] FATAL:  could not open 
directory "/usr/share/postgresql-10.2/timezonesets": No such file or directory
2018-10-31 12:12:39.530 PDT [4398] HINT:  This may indicate an incomplete PostgreSQL 
installation, or that the file "/usr/lib/postgresql/10.3/bin/postgres" has been 
moved away from its proper location.
 stopped waiting
pg_ctl: could not start server

listen_addresses = ''

$ pg_ctl start -D /var/lib/pgsql/10.3/data/
waiting for server to start2018-10-31 12:13:28.141 PDT [4413] FATAL:  could not open 
directory "/usr/share/postgresql-10.2/timezonesets": No such file or directory
2018-10-31 12:13:28.141 PDT [4413] HINT:  This may indicate an incomplete PostgreSQL 
installation, or that the file "/usr/lib/postgresql/10.3/bin/postgres" has been 
moved away from its proper location.
 stopped waiting
pg_ctl: could not start server

Thanks,

Rich

Re: Broken postgres links need to find callers


On 10/31/18 11:48 AM, Rich Shepard wrote:

On Wed, 31 Oct 2018, Andrew Gierth wrote:


Is there a pg_config binary in /usr/lib/postgresql/10.3/bin/ and if so,
what is the output of /usr/lib/postgresql/10.3/bin/pg_config --sharedir


Andrew,

   Yes, pg_config is present but pointing to the wrong directory:
# /usr/lib/postgresql/10.3/bin/pg_config --sharedir
/usr/share/postgresql-10.2


What does:

/usr/lib/postgresql/10.3/bin/pg_config --version

show?

What does:

ps ax | grep post

show?




   However, the file dates are that of the upgrade from 10.2 to 10.3:

-rwxr-xr-x 1 root root 7096448 Mar  1  2018 postgres*
lrwxrwxrwx 1 root root   8 Mar  1  2018 postmaster -> postgres*
-rwxr-xr-x 1 root root  514732 Mar  1  2018 psql*

and the postgres version is 10.3:


Also what is the output of  /usr/lib/postgresql/10.3/bin/postgres -V


# /usr/lib/postgresql/10.3/bin/postgres -V
postgres (PostgreSQL) 10.3


The most plausible explanation I can see for what you're seeing there is
that what you have as /usr/lib/postgresql/10.3/bin/postgres is not
actually the 10.3 binary but rather the 10.2 one. There should be no
symlinks involved there - the path that is reported in the error message
is the one that the postgres binary actually did try to open.


   Can pg_config be corrected independent of anything else. That seems 
to be

where the blockage is found.

Thanks,

Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Broken postgres links need to find callers


On 10/31/18 11:33 AM, Rich Shepard wrote:

On Wed, 31 Oct 2018, Adrian Klaver wrote:

If you refuse to implement the suggestions I asked for then I cannot 
help you, as you are now off on a different tangent. One that on the 
face of it is dangerous.


   In var/lib/pgsql/10.3/data/postgresql.conf:

# - Connection Settings -

_addresses = 'localhost'

   No listener_addresses present.


As the below shows that is not a valid setting.

Try:

listen_addresses = ''



$ pg_ctl start -D /var/lib/pgsql/10.3/data/
waiting for server to start2018-10-31 18:33:03.323 GMT [3352] LOG:  
unrecognized configuration parameter "_addresses" in file 
"/var/lib/pgsql/10.3/data/postgresql.conf" line 59
2018-10-31 18:33:03.323 GMT [3352] FATAL:  configuration file 
"/var/lib/pgsql/10.3/data/postgresql.conf" contains errors

  stopped waiting
pg_ctl: could not start server

Rich






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Broken postgres links need to find callers


On Wed, 31 Oct 2018, Andrew Gierth wrote:


Is there a pg_config binary in /usr/lib/postgresql/10.3/bin/ and if so,
what is the output of /usr/lib/postgresql/10.3/bin/pg_config --sharedir


Andrew,

  Yes, pg_config is present but pointing to the wrong directory:
# /usr/lib/postgresql/10.3/bin/pg_config --sharedir
/usr/share/postgresql-10.2

  However, the file dates are that of the upgrade from 10.2 to 10.3:

-rwxr-xr-x 1 root root 7096448 Mar  1  2018 postgres*
lrwxrwxrwx 1 root root   8 Mar  1  2018 postmaster -> postgres*
-rwxr-xr-x 1 root root  514732 Mar  1  2018 psql*

and the postgres version is 10.3:


Also what is the output of  /usr/lib/postgresql/10.3/bin/postgres -V


# /usr/lib/postgresql/10.3/bin/postgres -V
postgres (PostgreSQL) 10.3


The most plausible explanation I can see for what you're seeing there is
that what you have as /usr/lib/postgresql/10.3/bin/postgres is not
actually the 10.3 binary but rather the 10.2 one. There should be no
symlinks involved there - the path that is reported in the error message
is the one that the postgres binary actually did try to open.


  Can pg_config be corrected independent of anything else. That seems to be
where the blockage is found.

Thanks,

Rich

Re: Broken postgres links need to find callers

2018-10-31 Thread Andrew Gierth

> "Rich" == Rich Shepard  writes:

 Rich>   I managed to mess up postgresql-10.3 on this Slackware-14.2
 Rich> desktop server/workstation. It worked OK until I tried adding
 Rich> access to an another application.

 Rich> waiting for server to start2018-10-31 10:02:01.312 PDT [1285] FATAL:
 Rich> could not open directory "/usr/share/postgresql-10.2/timezonesets": No 
such
 Rich> file or directory 2018-10-31 10:02:01.312 PDT [1285] HINT: This may 
indicate
 Rich> an incomplete PostgreSQL installation, or that the file
 Rich> "/usr/lib/postgresql/10.3/bin/postgres" has been moved away from its 
proper
 Rich> location.

Is there a pg_config binary in /usr/lib/postgresql/10.3/bin/ and if so,
what is the output of  /usr/lib/postgresql/10.3/bin/pg_config --sharedir

Also what is the output of  /usr/lib/postgresql/10.3/bin/postgres -V

The most plausible explanation I can see for what you're seeing there is
that what you have as /usr/lib/postgresql/10.3/bin/postgres is not
actually the 10.3 binary but rather the 10.2 one. There should be no
symlinks involved there - the path that is reported in the error message
is the one that the postgres binary actually did try to open.

-- 
Andrew (irc:RhodiumToad)

Re: Broken postgres links need to find callers


On Wed, 31 Oct 2018, Adrian Klaver wrote:

If you refuse to implement the suggestions I asked for then I cannot help 
you, as you are now off on a different tangent. One that on the face of it is 
dangerous.


  In var/lib/pgsql/10.3/data/postgresql.conf:

# - Connection Settings -

_addresses = 'localhost'

  No listener_addresses present.

$ pg_ctl start -D /var/lib/pgsql/10.3/data/
waiting for server to start2018-10-31 18:33:03.323 GMT [3352] LOG:  unrecognized configuration 
parameter "_addresses" in file "/var/lib/pgsql/10.3/data/postgresql.conf" line 
59
2018-10-31 18:33:03.323 GMT [3352] FATAL:  configuration file 
"/var/lib/pgsql/10.3/data/postgresql.conf" contains errors
 stopped waiting
pg_ctl: could not start server

Rich

Re: Broken postgres links need to find callers


On 10/31/18 11:15 AM, Rich Shepard wrote:

On Wed, 31 Oct 2018, Adrian Klaver wrote:

You said it made a difference when you added it, just trying to figure 
out if removing it also makes a difference. If not then we need to 
look elsewhere for an explanation.


Adrian,

   Each time I hit a broken symlink pg_ctl told me which link was 
broken. For

example, the libpgtypes.so* files were sought in
/usr/lib/postgresql/10.2/lib/ when they're actually now in
/usr/lib/postgresql/10.3/lib/. Removing broken links and creating new,
valid ones fixes them.

   So, I'm seeking the library or executable file that's finding 
timezonesets

in the no-longer existing ../10.2/ directory.



If you refuse to implement the suggestions I asked for then I cannot 
help you, as you are now off on a different tangent. One that on the 
face of it is dangerous.



Regards,

Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Broken postgres links need to find callers


On Wed, 31 Oct 2018, Adrian Klaver wrote:

You said it made a difference when you added it, just trying to figure out if 
removing it also makes a difference. If not then we need to look elsewhere 
for an explanation.


Adrian,

  Each time I hit a broken symlink pg_ctl told me which link was broken. For
example, the libpgtypes.so* files were sought in
/usr/lib/postgresql/10.2/lib/ when they're actually now in
/usr/lib/postgresql/10.3/lib/. Removing broken links and creating new,
valid ones fixes them.

  So, I'm seeking the library or executable file that's finding timezonesets
in the no-longer existing ../10.2/ directory.

Regards,

Rich

Re: Broken postgres links need to find callers


On 10/31/18 10:55 AM, Rich Shepard wrote:

On Wed, 31 Oct 2018, Adrian Klaver wrote:


What was the listening address you added?


Adrian,

   I added the host name.


What happens if you remove the listening address?


   I don't think this makes a difference. pg_ctl is calling a program that
looks for timezonesets in the wrong directory


You said it made a difference when you added it, just trying to figure 
out if removing it also makes a difference. If not then we need to look 
elsewhere for an explanation.





Did you recently upgrade from 10.2 --> 10.3?


   On March 1st of this year.

Regards,

Rich




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Broken postgres links need to find callers


On Wed, 31 Oct 2018, Adrian Klaver wrote:


What was the listening address you added?


Adrian,

  I added the host name.


What happens if you remove the listening address?


  I don't think this makes a difference. pg_ctl is calling a program that
looks for timezonesets in the wrong directory


Did you recently upgrade from 10.2 --> 10.3?


  On March 1st of this year.

Regards,

Rich

Re: Broken postgres links need to find callers


On 10/31/18 10:18 AM, Rich Shepard wrote:

   I managed to mess up postgresql-10.3 on this Slackware-14.2 desktop
server/workstation. It worked OK until I tried adding access to an another
application.

   For a reason I don't know, adding that listening address revealed that
many sym links are looking for 10.2 directories. I've found and fixed many
of these and need help finding the rest (perhaps only one more).


What was the listening address you added?

What happens if you remove the listening address?

Did you recently upgrade from 10.2 --> 10.3?



   Running pg_ctl start fails:
$ pg_ctl start -D /var/lib/pgsql/10.3/data/
waiting for server to start2018-10-31 10:02:01.312 PDT [1285] FATAL:
could not open directory "/usr/share/postgresql-10.2/timezonesets": No such
file or directory 2018-10-31 10:02:01.312 PDT [1285] HINT: This may 
indicate

an incomplete PostgreSQL installation, or that the file
"/usr/lib/postgresql/10.3/bin/postgres" has been moved away from its proper
location.
  stopped waiting

   What file is looking for the timezonesets directory? It's using a 
symlink

but the error message is not telling me where it is so I can remove it and
replace it with a symlink to the proper postgresql-10.3/timezonesets
directory.

TIA,

Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Broken postgres links need to find callers


  I managed to mess up postgresql-10.3 on this Slackware-14.2 desktop
server/workstation. It worked OK until I tried adding access to an another
application.

  For a reason I don't know, adding that listening address revealed that
many sym links are looking for 10.2 directories. I've found and fixed many
of these and need help finding the rest (perhaps only one more).

  Running pg_ctl start fails:
$ pg_ctl start -D /var/lib/pgsql/10.3/data/
waiting for server to start2018-10-31 10:02:01.312 PDT [1285] FATAL:
could not open directory "/usr/share/postgresql-10.2/timezonesets": No such
file or directory 2018-10-31 10:02:01.312 PDT [1285] HINT: This may indicate
an incomplete PostgreSQL installation, or that the file
"/usr/lib/postgresql/10.3/bin/postgres" has been moved away from its proper
location.
 stopped waiting

  What file is looking for the timezonesets directory? It's using a symlink
but the error message is not telling me where it is so I can remove it and
replace it with a symlink to the proper postgresql-10.3/timezonesets
directory.

TIA,

Rich

Re: Is there a way to speed up WAL replay?

2018-10-31 Thread Nicolas Grilly

This tool may be useful:

https://github.com/joyent/pg_prefaulter
Faults pages into PostgreSQL shared_buffers or filesystem caches in advance
of WAL apply

Nicolas

On Wed, Oct 31, 2018 at 6:38 AM Torsten Förtsch 
wrote:

> Hi,
>
> I am working on restoring a database from a base backup + WAL. With the
> default settings the database replays about 3-4 WAL files per second. The
> startup process takes about 65% of a CPU and writes data with something
> between 50 and 100 MB/sec.
>
> Is there a way to speed that up? The disk can easily sustain 400-500
> MB/sec.
>
> Thanks,
> Torsten
>

Re: Is there a way to speed up WAL replay?

2018-10-31 Thread Stephen Frost

Greetings,

* Jeff Janes (jeff.ja...@gmail.com) wrote:
> One way I found to speed up restore_command is to have another program run
> a few WAL files ahead of it, copying the WAL from the real archive into a
> scratch space which is on the same filesystem as pg_xlog/pg_wal.  Then have
> restore_command simply move (not copy) the requested files from the scratch
> space onto %p.  The intra-filesystem move completes much faster than a copy.

Right, that can definitely be a big help and is more-or-less what
pgbackrest does too.

> If it spends time waiting for blocks that need to be recovered into to get
> read from disk, and you have enough RAM, you could speed it up by
> pre-warming the file system cache.  Something like:
> 
> tar -cf - $PGDATA | wc -c

Yeah, that's also a good idea.  We've discussed having something in
pgbackrest to basically go pre-load things off disk in advance of WAL
replay, and I seem to recall someone had also written an external tool
to do that.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: Is there a way to speed up WAL replay?

2018-10-31 Thread Stephen Frost

Greetings,

* Torsten Förtsch (tfoertsch...@gmail.com) wrote:
> I am working on restoring a database from a base backup + WAL. With the
> default settings the database replays about 3-4 WAL files per second. The
> startup process takes about 65% of a CPU and writes data with something
> between 50 and 100 MB/sec.

What are you using for a restore_command..?  You can typically get some
improvement by using a restore_command that's faster or pre-stages files
locally to minimize the time required to run, or dumping all the WAL
into the xlog, but that's not something I typically recommend.

> Is there a way to speed that up? The disk can easily sustain 400-500 MB/sec.

Kind of depends what you're ultimately going for..  If you've made
everything else faster and you're still only seeing 65% CPU utilization
on the one CPU, then maybe there's just enough latency to the disks to
be an issue, in which case you could possibly restore on to a ramdisk if
you've got enough memory/space and don't mind that.

There's other things that can be done too, like adjusting the amount of
shared buffers; depending on what you're doing that can also make a
difference in replay speed (we've been working to improve that though).

I tend to find that it's better to just reduce the amount of WAL that
needs to be replayed by taking incremental backups more frequently and
using things like pgbackrest's delta restore ability for doing repeated
restores to the same location.  Of course, that's not likely to help you
out much here.

Thanks!

Stephen

signature.asc
Description: PGP signature

Re: Is there a way to speed up WAL replay?

2018-10-31 Thread Jeff Janes

On Wed, Oct 31, 2018 at 1:38 AM Torsten Förtsch 
wrote:

> Hi,
>
> I am working on restoring a database from a base backup + WAL. With the
> default settings the database replays about 3-4 WAL files per second. The
> startup process takes about 65% of a CPU and writes data with something
> between 50 and 100 MB/sec.
>
> Is there a way to speed that up? The disk can easily sustain 400-500
> MB/sec.
>

WAL replay is single-threaded, so the most you would be able to speed it up
is 50%, to where it would be taking 100% CPU.

Is the time spent not on the CPU being spent waiting for WAL files to
arrive from the restore_command, or waiting for the blocks it needs to
replay into to get read from disk, or waiting for dirty pages to get
written to disk so they can be evicted to make way for new ones?

One way I found to speed up restore_command is to have another program run
a few WAL files ahead of it, copying the WAL from the real archive into a
scratch space which is on the same filesystem as pg_xlog/pg_wal.  Then have
restore_command simply move (not copy) the requested files from the scratch
space onto %p.  The intra-filesystem move completes much faster than a copy.

If it spends time waiting for blocks that need to be recovered into to get
read from disk, and you have enough RAM, you could speed it up by
pre-warming the file system cache.  Something like:

tar -cf - $PGDATA | wc -c

Cheers,

Jeff

Re: editable spreadsheet style interface

2018-10-31 Thread Basques, Bob (CI-StPaul)

I can second this as an option.  We’ve done some editing with Libre office as 
well in Postgres.

bobb



On Oct 31, 2018, at 5:01 AM, Tony Shelver 
mailto:tshel...@gmail.com>> wrote:

For a quick and dirty data editor, LibreOffice Base seems to work fine.

On Tue, 30 Oct 2018 at 23:05, Tim Clarke 
mailto:tim.cla...@minerva-analytics.info>> 
wrote:
On 30/10/2018 20:32, Martin Mueller wrote:
>
> I have used Aqua Data Studio for several years. Jetbrains recently
> released a similar product. Academic licensing is affordable (~ $200 a
> year) and very cheap if considered in terms of the time it saves you.
>
> *From: *David Gauthier 
> mailto:davegauthie...@gmail.com>>
> *Date: *Tuesday, October 30, 2018 at 2:06 PM
> *To: *"pgsql-gene...@postgresql.org" 
> mailto:pgsql-gene...@postgresql.org>>
> *Subject: *editable spreadsheet style interface
>
> I think I know the answer to this one but I'll ask anyway...
>
> Is there a spreadsheet style interface to a PG DB where users can...
>
> - lock records
>
> - edit records
>
> - submit changes (transaction)
>
> Is there any after-market tool for PG that does something like this ?
>

Have a look here https://www.postgresql.org/download/product-categories/
under "Administration/Development". I'm using pgAdmin right now.


Tim Clarke





"He who hesitates is a damned fool."
- Mae West

Re: bug in autovacuum_analyze_scale_factor meta data

2018-10-31 Thread Szymon Lipiński

Yep, I messed a couple of options, I'm sorry.

regards,
Szymon Lipiński

On Wed, 31 Oct 2018 at 15:07, Adrian Klaver 
wrote:

> On 10/31/18 6:58 AM, Szymon Lipiński wrote:
> > This is what I have in postgres 10 pg_settings table: and the
> > documentation has different description. What's more the default value
> > is outside the range of the min/max value.
>
> Not seeing it:
>
> 0 < 0.1 < 100
>
>
> reltuples is a measure of the sixe of the table:
>
> https://www.postgresql.org/docs/11/static/catalog-pg-class.html
>
> "reltuples  float4  Number of live rows in the table. This is
> only an
> estimate used by the planner. It is updated by VACUUM, ANALYZE, and a
> few DDL commands such as CREATE INDEX.
> "
>
>
> >
> > -[ RECORD 2
> >
> ]---+--
> > name| autovacuum_analyze_scale_factor
> > setting | 0.1
> > unit| [NULL]
> > category| Autovacuum
> > short_desc  | Number of tuple inserts, updates, or deletes prior to
> > analyze as a fraction of reltuples.
> > extra_desc  | [NULL]
> > context | sighup
> > vartype | real
> > source  | default
> > min_val | 0
> > max_val | 100
> > enumvals| [NULL]
> > boot_val| 0.1
> > reset_val   | 0.1
> > sourcefile  | [NULL]
> > sourceline  | [NULL]
> > pending_restart | f
> >
> > regards,
> > Szymon Lipiński
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: bug in autovacuum_analyze_scale_factor meta data


On 10/31/18 6:58 AM, Szymon Lipiński wrote:
This is what I have in postgres 10 pg_settings table: and the 
documentation has different description. What's more the default value 
is outside the range of the min/max value.


Not seeing it:

0 < 0.1 < 100


reltuples is a measure of the sixe of the table:

https://www.postgresql.org/docs/11/static/catalog-pg-class.html

"reltuples 	float4 	  	Number of live rows in the table. This is only an 
estimate used by the planner. It is updated by VACUUM, ANALYZE, and a 
few DDL commands such as CREATE INDEX.

"




-[ RECORD 2 
]---+--

name    | autovacuum_analyze_scale_factor
setting | 0.1
unit    | [NULL]
category    | Autovacuum
short_desc  | Number of tuple inserts, updates, or deletes prior to 
analyze as a fraction of reltuples.

extra_desc  | [NULL]
context | sighup
vartype | real
source  | default
min_val | 0
max_val | 100
enumvals    | [NULL]
boot_val    | 0.1
reset_val   | 0.1
sourcefile  | [NULL]
sourceline  | [NULL]
pending_restart | f

regards,
Szymon Lipiński



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: rw_redis_fdw: SQL Errors when statement is within a function

On 10/31/18 2:03 AM, GPT wrote:

Very good morning,

Thanks very much for your direct, clear and enlightening response!

As regards Q2, and any other dynamic behaviour/feature or whatever PG
includes or will include in the future and has to do with 3rd entities
(modules, or whatever) of which the behaviour is out of the PG
control, safe precautions would be take easily, in favour of the
passive protection of the end-user, and the good reputation of PG
(consider the last as marketing cookie addressed to the commercial
community ;) ).

For example: in the .control file more fileds would be added to
clarify dynamic manners/behaviours/communications.
For example: subexpr_type = T_Param, T_RelabelType

So, when a module (which makes use of internal parts of PG) is
created, those parameters are recorded in the DB. When the 3rd party
initiates an activity/communication with PG, PG checks this parameters
and behaves/responds to a compatible manner that 3rd party always
understands. A warning about an old-fashion parameter value would be
triggered by PG in every communication instance (or not) to inform the
user/developer that something has changed/improved! When such a
message/warning is seen by them, then they can easily add the new
feature, such as T_FuncExpr, after, of course, the code has been
updated properly, to declare the support.

So, PG continues being developed under the hood, retains backward
compatibility without any real cost and retains the operability of the
3rd entities improving, at the same time, the control on them (and the
eco-system, in general), and end-users are protected, too!

The short version:

The above is not going to happen.

The long version:

1) You are asking Postgres to do what previously you said you did not
want it to do:

https://www.postgresql.org/message-id/CADep2PMJVpVu-ne42yYpqjzGHQ1cunvX92Oo6_hNLfgrj%2BMa_Q%40mail.gmail.com

" You are looking for Postgres to
> follow its responses all the way through the software stack and tell you
> if the response is being misused. That is not going to happen.
For God sake! No, I am not! As soon as the correct data left the
PG-space in the format that the statement requested, and the KEY was
not NULL, of course, I do not blame PG."

2) Trying to track the state of every third party code that hits a
database and it's internal diff from the current internal state of the
Postgres database code would be intensive and intrusive, for little or
no benefit in all but a few cases. Those few cases are better dealt with
by the existing process of issue reporting.

3) Having said 1) and 2) Postgres does do a limited version of what you
want:

https://www.postgresql.org/docs/11/static/protocol.html

"This document describes version 3.0 of the protocol, implemented in
PostgreSQL 7.4 and later. For descriptions of the earlier protocol
versions, see previous releases of the PostgreSQL documentation. A
single server can support multiple protocol versions. The initial
startup-request message tells the server which protocol version the
client is attempting to use. If the major version requested by the
client is not supported by the server, the connection will be rejected
(for example, this would occur if the client requested protocol version
4.0, which does not exist as of this writing). If the minor version
requested by the client is not supported by the server (e.g. the client
requests version 3.1, but the server supports only 3.0), the server may
either reject the connection or may respond with a
NegotiateProtocolVersion message containing the highest minor protocol
version which it supports. The client may then choose either to continue
with the connection using the specified protocol version or to abort the
connection."

Though it should be noted the above is for the public API, not the
private parts your extension had a problem with. They are
private(internal) for a reason. If code needs to touch them then the
developer becomes responsible for keeping up to date with their changes.

4) 3) also addresses the backwards comparability issue as the current
protocol extends back to 7.4, which went EOL of life 8 years ago.

Tia

--
Adrian Klaver
adrian.kla...@aklaver.com

bug in autovacuum_analyze_scale_factor meta data

2018-10-31 Thread Szymon Lipiński

This is what I have in postgres 10 pg_settings table: and the documentation
has different description. What's more the default value is outside the
range of the min/max value.

-[ RECORD 2
]---+--
name| autovacuum_analyze_scale_factor
setting | 0.1
unit| [NULL]
category| Autovacuum
short_desc  | Number of tuple inserts, updates, or deletes prior to
analyze as a fraction of reltuples.
extra_desc  | [NULL]
context | sighup
vartype | real
source  | default
min_val | 0
max_val | 100
enumvals| [NULL]
boot_val| 0.1
reset_val   | 0.1
sourcefile  | [NULL]
sourceline  | [NULL]
pending_restart | f

regards,
Szymon Lipiński

Re: editable spreadsheet style interface

2018-10-31 Thread legrand legrand

Tim Clarke-2 wrote
> On 30/10/2018 20:32, Martin Mueller wrote:
>>
>> I have used Aqua Data Studio for several years. Jetbrains recently 
>> released a similar product. Academic licensing is affordable (~ $200 a 
>> year) and very cheap if considered in terms of the time it saves you.
>>
>> *From: *David Gauthier 

> davegauthierpg@

> 
>> *Date: *Tuesday, October 30, 2018 at 2:06 PM
>> *To: *"

> pgsql-general@

> " 

> pgsql-general@

> 
>> *Subject: *editable spreadsheet style interface
>>
>> I think I know the answer to this one but I'll ask anyway...
>>
>> Is there a spreadsheet style interface to a PG DB where users can...
>>
>> - lock records
>>
>> - edit records
>>
>> - submit changes (transaction)
>>
>> Is there any after-market tool for PG that does something like this ?
>>
> 
> Have a look here https://www.postgresql.org/download/product-categories/ 
> under "Administration/Development". I'm using pgAdmin right now.
> 
> 
> Tim Clarke

SQLeo http://sqleo.sourceforge.net/index.html is in that list (free), and
can
- insert, update, delete records,
- choose the update, delete key (if no pk),
- view the changes in sql format,
- apply to db,
- commit or rollback (if not in auto-commit mode)

(without locking)

see
http://sqleo.sourceforge.net/guide/English_SQLeo_AdvancedHelp.htm#_Toc467095120

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Fwd: Log file


On 10/30/18 9:20 AM, Igor Korot wrote:

Now is there a command to flush the log - delete the content of it?


The only thing I know of is:

https://www.postgresql.org/docs/10/static/functions-admin.html

pg_rotate_logfile() boolean Rotate server's log file




All I'm looking for in the log are DDL commands - CREATE/ALTER/DELETE ones.


On Wed, Oct 31, 2018 at 12:32 AM Igor Korot  wrote:


Hi, Tom,

On Mon, Oct 29, 2018 at 5:08 PM Tom Lane  wrote:


Igor Korot  writes:

On Mon, Oct 29, 2018 at 1:56 PM Tom Lane  wrote:

You can set up the log files as readable by the OS group of the server
(see log_file_mode), and then grant membership in that group to whichever
OS accounts you trust.  You may also need to move the log directory
out from under $PGDATA to make that work, since PG doesn't like
world-readable data directories.



I'm trying to make the log file of PG readable of the user who logs in
to the current
OS session. I don't need a write permission, just read.
Because my program will not be started from the "postgres" account.


Well, any such setup is a serious security hole in itself, because
there is likely to be sensitive data in the postmaster log, eg
passwords.  (Remember that the log file is global to the whole cluster,
it will not contain just data relevant to the current session.)
You should only grant access to people who you trust at more or less
the level of trust you'd put in the installation DBA.

It may be that these concerns are all irrelevant to you because it's
a single-user installation anyway, but they're not irrelevant to
people running multi-user installations.  So that's why you can't
get Postgres to do it.  In a single-user installation, maybe you
should just launch the postmaster as that user.

 regards, tom lane


OK, I understand.

Thank you.






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Full-text Search - Thesaurus relationships

2018-10-31 Thread Nicolas Paris

On Wed, Oct 31, 2018 at 10:49:04AM +0100, Laurenz Albe wrote:
> Nicolas Paris wrote:
> > > > The documentation[1] says thesaurus can include informations of terms
> > > > relationships such broader terms, preferred terms ...
> > > > I haven't been able to find out how to exploit those relationship in
> > > > postgres. Is there any keyword to and associated syntax to make use of
> > > > them ?
> > 
> > If "broader than" or "narrower than" have the same behavior than "is
> > equivalent to" I cannot figure out what's the purpose of them.
> Can you come up with a clear question?

Actually no because I finally understood the behavior. 

Thanks,

-- 
nicolas

Re: Is the centos repository for postgresql 10 is broken now?

2018-10-31 Thread Олег Самойлов

Looked like fixed now.

Re: can I use privileged user operate pg, and how?

枫 wrote:
> The routine of using pg is first adding unprivileged user group and user, 
> starting pg via the new user.
> 
> For the safety software and the safety rule of my company.It is not easy to 
> add a unprivileged user to operate pg.
> 
> Can I have a way to change the restriction of the unprivileged user of pg, I 
> want to start pg with root user.if does, how?
> 
> Additionally, I want to know Y pg should be operate with unprivileged user, 
> and the risk when  privileged user operate pg?

Are you saying that the security policy of your company forces you to
run software as root rather than as an ordinary user?

If yes, you should get rid of the people who came up with
that policy.

PostgreSQL must be run as a normal user for security reasons, namely
to limit the negative effects of software bugs and user attacks.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

can I use privileged user operate pg, and how?

2018-10-31 Thread ??

hi all,


The routine of using pg is first adding unprivileged user group and user, 
starting pg via the new user.


For the safety software and the safety rule of my company.It is not easy to add 
a unprivileged user to operate pg.


Can I have a way to change the restriction of the unprivileged user of pg, I 
want to start pg with root user.if does, how? 


Additionally, I want to know Y pg should be operate with unprivileged user, and 
the risk when  privileged user operate pg?


Thank very much!

Re: Question about servicescript for stopping and starting postgresql instance

2018-10-31 Thread Marian Forums




Hi,
Postgres was installed through a repo with yum install.
I will try to run it with another type.
Regards
Marian

> Op 30 okt. 2018 om 21:56 heeft Adrian Klaver  het 
> volgende geschreven:
> 
>> On 10/30/18 9:27 AM, Marian Forums wrote:
>> Hi Adrain,
>> Thanks for your reply.
> 
> 
> How was Postgres installed on this machine?
> 
> Have you tried the script from the docs?
> 
> 
>> My script looks like this:
> 
> I am no systemd expert, still the Type=forking does not seem right to me.
> 
>> # cat /usr/lib/systemd/system/postgresql-9.6.pg-tstxxx.service
>> [Unit]
>> Description=postgresql_pg-tstxxx
>> After=syslog.target
>> After=network.target
>> [Service]
>> Type=forking
>> User=postgresql
>> Group=postgresqlg
>> Environment=PGDATA=/data/pg-tstxxx/data-01/
>> # Run ExecStartPre with root-permissions
>> PermissionsStartOnly=true
>> ExecStartPre=/bin/chown postgresql:postgresqlgroup /var/run/postgresql
>> ExecStart=/usr/pgsql-9.6/bin/pg_ctl start -D ${PGDATA}
>> ExecStop=/usr/pgsql-9.6/bin/pg_ctl stop -D ${PGDATA} -s -m fast
>> ExecReload=/usr/pgsql-9.6/bin/pg_ctl reload -D ${PGDATA} -s
>> Restart=on-failure
>> KillMode=control-group
>> ExecStop=/bin/kill -SIGTERM $MAINPID
>> RestartSec=10s
>> [Install]
>> WantedBy=multi-user.target
>> thanks in advance.
>> regards,
>> Marian
>> Op di 30 okt. 2018 om 14:30 schreef Adrian Klaver > >:
>>On 10/30/18 5:09 AM, Marian Forums wrote:
>> > Hi,
>> >
>> > I have a question about creating a service script to
>>start/stop/status
>> > postgresql instance ( version 9.6.8) on Red Hat 7.
>>How did you install Postgres on the machine?
>> >
>> > I have read the explanation of how to create such a service
>>script on
>> > https://www.postgresql.org/docs/9.6/static/server-start.html .
>>Did you use the example at the above link or did you write your own?
>>If you wrote your own can you show it?
>> >
>> > Stopping and Starting works.
>> > However when I stop the service with:
>> > systemctl stop postgresq.service and than ask for the status with
>> > systemctl status postgresql.service, the service gets started again.
>> >
>> > Is this normal functionality on Red Hat 7?
>> > I have searched on the Internet ( including the Red Hat site) for a
>> > parameter to replace this behavior, but did not find anything.
>> >
>> > Your help is much appreciated.
>> > Thanks in advance.
>> > Regards,
>> > Marian
>>-- Adrian Klaver
>>adrian.kla...@aklaver.com 
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: editable spreadsheet style interface

2018-10-31 Thread Tony Shelver

For a quick and dirty data editor, LibreOffice Base seems to work fine.

On Tue, 30 Oct 2018 at 23:05, Tim Clarke 
wrote:

> On 30/10/2018 20:32, Martin Mueller wrote:
> >
> > I have used Aqua Data Studio for several years. Jetbrains recently
> > released a similar product. Academic licensing is affordable (~ $200 a
> > year) and very cheap if considered in terms of the time it saves you.
> >
> > *From: *David Gauthier 
> > *Date: *Tuesday, October 30, 2018 at 2:06 PM
> > *To: *"pgsql-gene...@postgresql.org" 
> > *Subject: *editable spreadsheet style interface
> >
> > I think I know the answer to this one but I'll ask anyway...
> >
> > Is there a spreadsheet style interface to a PG DB where users can...
> >
> > - lock records
> >
> > - edit records
> >
> > - submit changes (transaction)
> >
> > Is there any after-market tool for PG that does something like this ?
> >
>
> Have a look here https://www.postgresql.org/download/product-categories/
> under "Administration/Development". I'm using pgAdmin right now.
>
>
> Tim Clarke
>
>
>

Re: Full-text Search - Thesaurus relationships

Nicolas Paris wrote:
> > > The documentation[1] says thesaurus can include informations of terms
> > > relationships such broader terms, preferred terms ...
> > > I haven't been able to find out how to exploit those relationship in
> > > postgres. Is there any keyword to and associated syntax to make use of
> > > them ?
> 
> If "broader than" or "narrower than" have the same behavior than "is
> equivalent to" I cannot figure out what's the purpose of them.

Can you come up with a clear question?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: rw_redis_fdw: SQL Errors when statement is within a function

2018-10-31 Thread GPT

Very good morning,

Thanks very much for your direct, clear and enlightening response!

As regards Q2, and any other dynamic behaviour/feature or whatever PG
includes or will include in the future and has to do with 3rd entities
(modules, or whatever) of which the behaviour is out of the PG
control, safe precautions would be take easily, in favour of the
passive protection of the end-user, and the good reputation of PG
(consider the last as marketing cookie addressed to the commercial
community ;) ).

For example: in the .control file more fileds would be added to
clarify dynamic manners/behaviours/communications.
For example: subexpr_type = T_Param, T_RelabelType

So, when a module (which makes use of internal parts of PG) is
created, those parameters are recorded in the DB. When the 3rd party
initiates an activity/communication with PG, PG checks this parameters
and behaves/responds to a compatible manner that 3rd party always
understands. A warning about an old-fashion parameter value would be
triggered by PG in every communication instance (or not) to inform the
user/developer that something has changed/improved! When such a
message/warning is seen by them, then they can easily add the new
feature, such as T_FuncExpr, after, of course, the code has been
updated properly, to declare the support.

So, PG continues being developed under the hood, retains backward
compatibility without any real cost and retains the operability of the
3rd entities improving, at the same time, the control on them (and the
eco-system, in general), and end-users are protected, too!

Tia

Re: Full-text Search - Thesaurus relationships

2018-10-31 Thread Nicolas Paris

On Wed, Oct 31, 2018 at 07:56:28AM +0100, Laurenz Albe wrote:
> > The documentation[1] says thesaurus can include informations of terms
> > relationships such broader terms, preferred terms ...
> > I haven't been able to find out how to exploit those relationship in
> > postgres. Is there any keyword to and associated syntax to make use of
> > them ?
> No, it should happen automatically.

If "broader than" or "narrower than" have the same behavior than "is
equivalent to" I cannot figure out what's the purpose of them.

-- 
nicolas

Re: Fwd: Log file

Igor Korot wrote:
> Now is there a command to flush the log - delete the content of it?

No, managing the logs is outside of PostgreSQL's responsibility.

But it shouldn't be a problem to do this outside the database.
Of course you could write a funtion in PostgreSQL that uses one
of the "untrusted" procedural languages to do it for you.

> All I'm looking for in the log are DDL commands - CREATE/ALTER/DELETE ones.

You mean DROP, right?

You can set "log_statement = 'ddl'" for that.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Full-text Search - Thesaurus relationships