Re: Any hope for more specific error message for "value too long..."?

[Ken Tanzer]

>
> I dug in the archives and came across a crude POC hack here:
>
> https://www.postgresql.org/message-id/21693.1478376...@sss.pgh.pa.us
>
> At the time I didn't want to pursue it further because of Andres'
> pending work on redoing expression execution, but that's landed now.
>
> regards, tom lane
>


For that matter, it's not totally
> clear what would constitute an improvement --- what do you wish it would
> show you, exactly?


It looks like that patch is about showing which value or where in the
statement the error is being caused.  At least for my case, it would be
helpful to know which field is causing the error.  And just guessing, but
maybe simpler?  I'd be happy to see:

The error text was: ERROR *for field [field_name]*:  value too long for
type character varying(10)

Cheers,
Ken


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Join query

[hmidi slim]

Hi,
I have two tables: establishment which contains these columns: id, name,
longitude, latitude, geom (Geometric column)
Product contains: id, name, establishment_id
First of all I want to select the establishment within a radius.
I run this query:
select e.name, e1.name
from establishment as e, establishment as e1
where e.id <> e1.id
and e1.id = 1
and ST_DWithin(geom, ST_MakePoint(e1.longitude, e1.latitude)::geography,
1000)

The result of this query returns all the establishment within a radius 1KM
from from a given establishment which has an id = 1.

After that I want to get the product's name of each establishment from the
query's result.

Is there an other optimized solution to make a query such this:
select * from (
select e.name, e1.name, e.id
from establishment as e, establishment as e1
where e.id <> e1.id
and e1.id = 1
and ST_DWithin(geom, ST_MakePoint(e1.longitude, e1.latitude)::geography,
1000)) as tmp inner join product as p on p.establishment_id = tmp.id

Re: postgres connection with port option in shell script

[Abhra Kar]

On Wed, Feb 14, 2018 at 9:03 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Feb 14, 2018 at 8:21 AM, Abhra Kar  wrote:
>
>> Hi,
>>
>>   I want to get postgres connection in script file. I am executing
>> below command and successfully getting connected ---
>>
>>
>> psql postgresql://$USER:$PASSWORD@$HOST/$DATABASE <>
>>
>>
> If all you are going to do is substitute environment variables into a URI
> why not just identify the environment variables that psql uses directly,
> set those, and call "psql" directly.​
>
> select * from abc;
>>
>>
>> This is executing with default 5432 port.If postgres is installed to any
>> other port then port option need to be include here[Don't want to take port
>> from .pgpass file].
>>
>
> ​You couldn't even if you wanted to.​  Did you maybe mean the
> .pg_service.conf file?
>
> You should strongly consider using .pgpass instead of "$PASSWORD" -
> especially depending upon where perform the export.
>
>
>> How I can modify this command with PORT option.
>>
>
> ​https://www.postgresql.org/docs/10/static/libpq-connect.
> html#LIBPQ-CONNSTRING
>
> ​David J.
>




Hi David,
 I meant .pgpass file with in user home directory.And I
followed the link you provided.It worked.Thanks a lot.

Regards,
Abhra

Re: Any hope for more specific error message for "value too long..."?

[Tom Lane]

Ken Tanzer  writes:
>>> I dug in the archives and came across a crude POC hack here:
>>> https://www.postgresql.org/message-id/21693.1478376...@sss.pgh.pa.us
>> For that matter, it's not totally
>> clear what would constitute an improvement --- what do you wish it would
>> show you, exactly?

> It looks like that patch is about showing which value or where in the
> statement the error is being caused.  At least for my case, it would be
> helpful to know which field is causing the error.  And just guessing, but
> maybe simpler?

No; read the rest of that thread.  It would actually be nigh impossible to
do it that way in the current system, except for a small subset of cases.
Furthermore, if we did do it like that, what about similar errors in
non-INSERT commands?  The error cursor approach at least has the advantage
of being pretty generically applicable.  In principle we could make it
work for any error arising during expression evaluation.

regards, tom lane

Re: Rationale for PUBLIC having CREATE and USAGE privileges on the schema "public" by default

[Olegs Jeremejevs]

Thanks for the reply.

> I'm not sure whether you are really being limited/forced here or if you
are thinking that having CREATE and USAGE on a schema is more powerful than
it is...

As far as I know, having these permissions has a DoS potential, though,
admittedly, negligible, if the rest of the database is secured properly.
Just wanted to play safe and revoke them.

> ... PostgreSQL isn't going to be changing its default install and almost
certainly break tons of scripts that rely upon it
>
> It is a convenience feature by definition ... that nearly all users have
come to rely upon

That's understandable, but, if deemed necessary, can't PostgreSQL break
something like this on a major release? Or is the benefit of doing so being
weighed against the added friction to upgrading?

> ... any solution at this point would require intervention on the part of
Compose
>
> Given the above, you should probably ask Compose

Have already done that. This was a bit over a year ago, actually, just
never got around to reporting it here, until now. Seems like they still
haven't addressed this.

> ... I'm not seeing where "essentially forced to use a non-public schema"
is a hardship

Now that I have thought about it some more, there's no point in using an
application-specific schema just because the "public" schema has these
permissions, because the DoS angle is still there, as long as the
permissions aren't revoked or the schema isn't deleted, so I take back the
"essentially forced to" part.

Regards,
Olegs

On Sat, Feb 17, 2018 at 2:57 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Fri, Feb 16, 2018 at 4:50 PM, Olegs Jeremejevs 
> wrote:
>
>> Hi,
>>
>> I'm aware that these default privileges are documented:
>>
>> https://www.postgresql.org/docs/10/static/ddl-schemas.html#
>> DDL-SCHEMAS-PRIV
>>
>> However, I'm unable to find any reasoning behind their existence.
>> Normally, one can just revoke them and move on, but they have caused me
>> some trouble in a managed deployment (Compose, where you can't login as the
>> owner of the schema or as a superuser, so it's impossible to do the
>> revocation, so you're essentially forced to use a non-public schema), and I
>> would like to understand the need for them.
>>
>> I assume they make it a bit easier for new users to try out the database,
>> and I see that they go at least as far as 7.3, but has there been any
>> discussion of removing them? And if so, why was it decided to keep them?
>>
>>
> ​No recent discussions that I recall.
>
> I'm not sure whether you are really being limited/forced here or if you
> are thinking that having CREATE and USAGE on a schema is more powerful than
> it is...
>
> In any case any solution at this point would require intervention on the
> part of Compose since PostgreSQL isn't going to be changing its default
> install and almost certainly break tons of scripts that rely upon it.  No
> matter whether the original rationale still holds as strongly, whatever it
> may have been.  It is a convenience feature by definition, and one that
> especially benefits new users but that nearly all users have come to rely
> upon (some may choose to immediately revoke public defaults on their
> systems but I'd assume many do not).
>
> Given the above, you should probably ask Compose to add an option to their
> system where you can request disabling of certain default privileges (or
> removal of the public schema altogether) and their system will do so, as
> superuser, on your behalf.
>
> I haven't had any issues with creating and using application-specific
> schemas and as such I'm not seeing where "essentially forced to use a
> non-public schema" is a hardship.
>
> David J.
>
>

Re: Rationale for PUBLIC having CREATE and USAGE privileges on the schema "public" by default

[David G. Johnston]

On Saturday, February 17, 2018, Olegs Jeremejevs 
wrote:

> Thanks for the reply.
>
> > I'm not sure whether you are really being limited/forced here or if you
> are thinking that having CREATE and USAGE on a schema is more powerful than
> it is...
>
> As far as I know, having these permissions has a DoS potential, though,
> admittedly, negligible, if the rest of the database is secured properly.
> Just wanted to play safe and revoke them.
>

To an extent it is possible to DoS so long as you have a session and access
to pg_catalog.  Having create and usage on public doesn't meaningfully (if
at all) expand the risk surface area.  Default also provides for creating
temporary tables.

David J.

Re: Rationale for PUBLIC having CREATE and USAGE privileges on the schema "public" by default

[Olegs Jeremejevs]

Okay, in other words, there's no way to completely defend oneself from DoS
attacks which require having a session? If so, is there a scenario where
some bad actor can create a new user for themselves (to connect to the
database with), and not be able to do anything more damaging than that? For
example, if I can do an SQL injection, then I can do something more clever
than running a CREATE ROLE. And if not, then there's no point in worrying
about privileges in a single-tenant database? Beyond human error safeguards.

Olegs

On Sat, Feb 17, 2018 at 10:08 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Saturday, February 17, 2018, Olegs Jeremejevs 
> wrote:
>
>> Thanks for the reply.
>>
>> > I'm not sure whether you are really being limited/forced here or if
>> you are thinking that having CREATE and USAGE on a schema is more powerful
>> than it is...
>>
>> As far as I know, having these permissions has a DoS potential, though,
>> admittedly, negligible, if the rest of the database is secured properly.
>> Just wanted to play safe and revoke them.
>>
>
> To an extent it is possible to DoS so long as you have a session and
> access to pg_catalog.  Having create and usage on public doesn't
> meaningfully (if at all) expand the risk surface area.  Default also
> provides for creating temporary tables.
>
> David J.
>

Re: Rationale for PUBLIC having CREATE and USAGE privileges on the schema "public" by default

[Tim Clarke]

On 17/02/18 20:48, Olegs Jeremejevs wrote:
> Okay, in other words, there's no way to completely defend oneself from
> DoS attacks which require having a session? If so, is there a scenario
> where some bad actor can create a new user for themselves (to connect
> to the database with), and not be able to do anything more damaging
> than that? For example, if I can do an SQL injection, then I can do
> something more clever than running a CREATE ROLE. And if not, then
> there's no point in worrying about privileges in a single-tenant
> database? Beyond human error safeguards.
>
> Olegs

How about execution limits, Olegs?

Tim Clarke



smime.p7s
Description: S/MIME Cryptographic Signature

Re: Rationale for PUBLIC having CREATE and USAGE privileges on the schema "public" by default

[David G. Johnston]

On Saturday, February 17, 2018, Olegs Jeremejevs 
wrote:

> Okay, in other words, there's no way to completely defend oneself from DoS
> attacks which require having a session? If so, is there a scenario where
> some bad actor can create a new user for themselves (to connect to the
> database with), and not be able to do anything more damaging than that? For
> example, if I can do an SQL injection, then I can do something more clever
> than running a CREATE ROLE. And if not, then there's no point in worrying
> about privileges in a single-tenant database? Beyond human error safeguards.
>

Roles that applications use should not be superuser or given createrole so
your example should not arise.  But any logged user can do something like:

Select * from generate_series1,1) cross join
generate_series(1,1)

Privileges are largely valuable for information privacy and security, and
preventing subtle attacks.

David J.

Need to fix one more glitch in upgrade to -10.2

[Rich Shepard]

Hi folks,

  Today I upgraded from -9.6.6 to -10.2 on my Slackware-14.2 desktop. The
user and group IDs changed from before, but I have that all fixed now.
Starting postgres (as user postgres) succeeded, but the role for me (as a
use and owner of most databases) seems to have become lost during the
transition.

  I try to open a database and see this:

$ psql jerr2018-02-17 13:45:35.852 PST [5839] FATAL:  password authentication failed for 
user "rshepard"
2018-02-17 13:45:35.852 PST [5839] DETAIL:  Role "rshepard" does not exist.
Connection matched pg_hba.conf line 80: "local   all all
md5"
2018-02-17 13:45:35.853 PST [5839] LOG:  could not send data to client: Broken 
pipe

  So I edited pg_hba.conf to change the method to 'trust' as I'm the only
user on this system. Ran /etc/rc.d/rc.postfix reload and see:

# /etc/rc.postgresql reload
Could not find 'postgres' binary. Maybe PostgreSQL is not installed properly?

  $ ps ax | grep postgres
 5826 pts/0S  0:00 postgres -D /var/lib/pgsql/10.2/data
 5828 ?Ss 0:00 postgres: checkpointer process
 5829 ?Ss 0:00 postgres: writer process
 5830 ?Ss 0:00 postgres: wal writer process
 5831 ?Ss 0:00 postgres: autovacuum launcher process
 5832 ?Ss 0:00 postgres: stats collector process
 5833 ?Ss 0:00 postgres: bgworker: logical replication launcher

  I would appreciate a pointer on what to check to determine why I cannot
reload postgres to see the changed pg_hba.conf and let me access my
databases.

Regards,

Rich

Re: Rationale for PUBLIC having CREATE and USAGE privileges on the schema "public" by default

[Olegs Jeremejevs]

Okay, thanks, I'll stop worrying about the defaults then. Have a nice
evening!

Olegs

On Sat, Feb 17, 2018 at 11:49 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Saturday, February 17, 2018, Olegs Jeremejevs 
> wrote:
>
>> Okay, in other words, there's no way to completely defend oneself from
>> DoS attacks which require having a session? If so, is there a scenario
>> where some bad actor can create a new user for themselves (to connect to
>> the database with), and not be able to do anything more damaging than that?
>> For example, if I can do an SQL injection, then I can do something more
>> clever than running a CREATE ROLE. And if not, then there's no point in
>> worrying about privileges in a single-tenant database? Beyond human error
>> safeguards.
>>
>
> Roles that applications use should not be superuser or given createrole so
> your example should not arise.  But any logged user can do something like:
>
> Select * from generate_series1,1) cross join
> generate_series(1,1)
>
> Privileges are largely valuable for information privacy and security, and
> preventing subtle attacks.
>
> David J.
>
>

Re: Need to fix one more glitch in upgrade to -10.2

[Adrian Klaver]

On 02/17/2018 02:00 PM, Rich Shepard wrote:

Hi folks,

   Today I upgraded from -9.6.6 to -10.2 on my Slackware-14.2 desktop. The
user and group IDs changed from before, but I have that all fixed now.
Starting postgres (as user postgres) succeeded, but the role for me (as a
use and owner of most databases) seems to have become lost during the
transition.


How did you upgrade, dump/restore or pg_upgrade?

If dump/restore did you use pg_dumpall or pg_dump individual databases?

If pg_dump only did you use pg_dumpall -g to get the globals(of which 
roles are one) to restore the global values to the new cluster?




   I try to open a database and see this:

$ psql jerr2018-02-17 13:45:35.852 PST [5839] FATAL:  password 
authentication failed for user "rshepard"

2018-02-17 13:45:35.852 PST [5839] DETAIL:  Role "rshepard" does not exist.
 Connection matched pg_hba.conf line 80: "local   all 
all    md5"
2018-02-17 13:45:35.853 PST [5839] LOG:  could not send data to client: 
Broken pipe


   So I edited pg_hba.conf to change the method to 'trust' as I'm the only
user on this system. Ran /etc/rc.d/rc.postfix reload and see:

# /etc/rc.postgresql reload
Could not find 'postgres' binary. Maybe PostgreSQL is not installed 
properly?


What is in rc.postgresql?

Is there maybe another start script in etc/ for the new version?



   $ ps ax | grep postgres
  5826 pts/0    S  0:00 postgres -D /var/lib/pgsql/10.2/data
  5828 ?    Ss 0:00 postgres: checkpointer process
  5829 ?    Ss 0:00 postgres: writer process
  5830 ?    Ss 0:00 postgres: wal writer process
  5831 ?    Ss 0:00 postgres: autovacuum launcher process
  5832 ?    Ss 0:00 postgres: stats collector process
  5833 ?    Ss 0:00 postgres: bgworker: logical replication 
launcher


   I would appreciate a pointer on what to check to determine why I cannot
reload postgres to see the changed pg_hba.conf and let me access my
databases.

Regards,

Rich







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

Re: Need to fix one more glitch in upgrade to -10.2

[Rich Shepard]

On Sat, 17 Feb 2018, Adrian Klaver wrote:


How did you upgrade, dump/restore or pg_upgrade?


Adrian,

  Ran 'pg_dumpall -c -f .sql' prior to doing anything. Then built,
installed the new version, upgraded rc.postgresql (only differences were
version numbers), and ran 'pg_upgrade ...' with the appropriate binary and
data directory paths.


What is in rc.postgresql? Is there maybe another start script in etc/ for
the new version?


  Nope. One script per application.

/etc/rc.d/rc.postgresql:

#!/bin/bash

# PostgreSQL startup script for Slackware Linux
#
# $Revision$
# $Date$
#
# Copyright 2007-2018 Adis Nezirovic 
# All rights reserved.
#
# Redistribution and use of this script, with or without modification, is
# permitted provided that the following conditions are met:
#
# 1. Redistributions of this script must retain the above copyright
#notice, this list of conditions and the following disclaimer.
#
#  THIS SOFTWARE IS PROVIDED BY THE AUTHOR "AS IS" AND ANY EXPRESS OR IMPLIED
#  WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
#  MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.  IN NO
#  EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
#  SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
#  PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS;
#  OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
#  WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
#  OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
#  ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

# Do not source this script (since it contains exit() calls)
#
# Since version 9.3 this startup script can run multiple PostgreSQL
# versions on different ports and with different data dirs.
# # e.g. PG_VERSION=10.2 PG_PORT=6432 /etc/rc.d/rc.@PRGNAM@ start

PG_VERSION=${PG_VERSION:-@PG_VERSION@}
PG_PORT=${PG_PORT:-@PG_PORT@}
LIBDIRSUFFIX="@LIBDIRSUFFIX@"
LOGFILE=/var/log/@PRGNAM@-$PG_VERSION
DATADIR=/var/lib/pgsql/$PG_VERSION/data
POSTGRES=/usr/lib${LIBDIRSUFFIX}/@PRGNAM@/$PG_VERSION/bin/postgres
PG_CTL=/usr/lib${LIBDIRSUFFIX}/@PRGNAM@/$PG_VERSION/bin/pg_ctl
PIDFILE=$DATADIR/postmaster.pid

# oom-killer score
#
# 
http://www.postgresql.org/docs/10.2/static/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT

PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
PG_MASTER_OOM_SCORE_ADJ=-1000
PG_CHILD_OOM_SCORE_ADJ=0
PG_ENV="PG_OOM_ADJUST_FILE=$PG_OOM_ADJUST_FILE 
PG_OOM_ADJUST_VALUE=$PG_CHILD_OOM_SCORE_ADJ"


# Return values (according to LSB):
# 0 - success
# 1 - generic or unspecified error
# 2 - invalid or excess argument(s)
# 3 - unimplemented feature (e.g. "reload")
# 4 - insufficient privilege
# 5 - program is not installed
# 6 - program is not configured
# 7 - program is not running

pg_ctl()
{
CMD="$PG_CTL -o '-p $PG_PORT' $@"
su - postgres -c "$PG_ENV $CMD"
}

if [ ! -f $POSTGRES ]; then
	echo "Could not find 'postgres' binary. Maybe PostgreSQL is not 
installed properly?"

exit 5
fi

case "$1" in

"start")
echo "Starting PostgreSQL"
touch $LOGFILE
chown postgres:wheel $LOGFILE
chmod 0640 $LOGFILE

if [ ! -e $DATADIR/PG_VERSION ]; then
			echo "You should initialize the PostgreSQL database at 
location $DATADIR"
			echo "e.g. su postgres -c \"initdb -D $DATADIR 
--locale=en_US.UTF-8 -A md5 -W\""

exit 6
fi

if [ $(pgrep -f $POSTGRES) ]; then

echo "PostgreSQL daemon already running"
if [ ! -f $PIDFILE ]; then
echo "Warning: Missing pid file $PIDFILE"
fi
exit 1

else
			test -e "$PG_OOM_ADJUST_FILE" && echo 
"$PG_MASTER_OOM_SCORE_ADJ" > "$PG_OOM_ADJUST_FILE"

pg_ctl start -w -l $LOGFILE -D $DATADIR
exit 0
fi
;;

"stop")
echo "Shutting down PostgreSQL..."
pg_ctl stop -l $LOGFILE -D $DATADIR -m smart
;;

"force-stop")
# Take care! This will kill _all_ client connections
# and rollback current transactions.
echo "Shutting down PostgreSQL (fast)..."
pg_ctl stop -l $LOGFILE -D $DATADIR -m fast
;;

"unclean-stop")
# Take care! This will abort server process itself
# resulting with database recovery on next start.
echo "Shutting down PostgreSQL (immediate)..."
pg_ctl stop -l $LOGFILE -D $DATADIR -m immediate
;;

"restart")
echo "Restarting PostgreSQL..."
		test -e "$PG_OOM_ADJUST_FILE" && echo 
"$PG_MASTER_OOM_SCORE_ADJ" > "$PG_OOM_ADJUST_FILE"

pg_ctl restart -l $LOGFILE -D $D

Re: Need to fix one more glitch in upgrade to -10.2

[Adrian Klaver]

On 02/17/2018 02:25 PM, Rich Shepard wrote:

On Sat, 17 Feb 2018, Adrian Klaver wrote:


How did you upgrade, dump/restore or pg_upgrade?


Adrian,

   Ran 'pg_dumpall -c -f .sql' prior to doing anything. Then 
built,

installed the new version, upgraded rc.postgresql (only differences were
version numbers), and ran 'pg_upgrade ...' with the appropriate binary and
data directory paths.


Did pg_upgrade spit out any warnings/errors?




What is in rc.postgresql? Is there maybe another start script in etc/ for
the new version?


In your previous post you showed:
# /etc/rc.postgresql reload

yet below shows:

/etc/rc.d/rc.postgresql

Cut and paste error or not?

So are env variables set correctly?

Your ps ax output showed a Postgres instance running:

postgres -D /var/lib/pgsql/10.2/data

Do you know what port it is using and try to connect to it?




   Nope. One script per application.

/etc/rc.d/rc.postgresql:

#!/bin/bash

# PostgreSQL startup script for Slackware Linux
#
# $Revision$
# $Date$
#
# Copyright 2007-2018 Adis Nezirovic 
# All rights reserved.
#
# Redistribution and use of this script, with or without modification, is
# permitted provided that the following conditions are met:
#
# 1. Redistributions of this script must retain the above copyright
#    notice, this list of conditions and the following disclaimer.
#
#  THIS SOFTWARE IS PROVIDED BY THE AUTHOR "AS IS" AND ANY EXPRESS OR 
IMPLIED

#  WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
#  MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.  
IN NO

#  EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
#  SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT 
LIMITED TO,
#  PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR 
PROFITS;

#  OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
#  WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
#  OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
#  ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

# Do not source this script (since it contains exit() calls)
#
# Since version 9.3 this startup script can run multiple PostgreSQL
# versions on different ports and with different data dirs.
# # e.g. PG_VERSION=10.2 PG_PORT=6432 /etc/rc.d/rc.@PRGNAM@ start

PG_VERSION=${PG_VERSION:-@PG_VERSION@}
PG_PORT=${PG_PORT:-@PG_PORT@}
LIBDIRSUFFIX="@LIBDIRSUFFIX@"
LOGFILE=/var/log/@PRGNAM@-$PG_VERSION
DATADIR=/var/lib/pgsql/$PG_VERSION/data
POSTGRES=/usr/lib${LIBDIRSUFFIX}/@PRGNAM@/$PG_VERSION/bin/postgres
PG_CTL=/usr/lib${LIBDIRSUFFIX}/@PRGNAM@/$PG_VERSION/bin/pg_ctl
PIDFILE=$DATADIR/postmaster.pid

# oom-killer score
#
# 
http://www.postgresql.org/docs/10.2/static/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT 


PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
PG_MASTER_OOM_SCORE_ADJ=-1000
PG_CHILD_OOM_SCORE_ADJ=0
PG_ENV="PG_OOM_ADJUST_FILE=$PG_OOM_ADJUST_FILE 
PG_OOM_ADJUST_VALUE=$PG_CHILD_OOM_SCORE_ADJ"


# Return values (according to LSB):
# 0 - success
# 1 - generic or unspecified error
# 2 - invalid or excess argument(s)
# 3 - unimplemented feature (e.g. "reload")
# 4 - insufficient privilege
# 5 - program is not installed
# 6 - program is not configured
# 7 - program is not running

pg_ctl()
{
 CMD="$PG_CTL -o '-p $PG_PORT' $@"
 su - postgres -c "$PG_ENV $CMD"
}

if [ ! -f $POSTGRES ]; then
 echo "Could not find 'postgres' binary. Maybe PostgreSQL is not 
installed properly?"

 exit 5
fi

case "$1" in

 "start")
     echo "Starting PostgreSQL"
     touch $LOGFILE
     chown postgres:wheel $LOGFILE
     chmod 0640 $LOGFILE

     if [ ! -e $DATADIR/PG_VERSION ]; then
     echo "You should initialize the PostgreSQL database at 
location $DATADIR"
     echo "e.g. su postgres -c \"initdb -D $DATADIR 
--locale=en_US.UTF-8 -A md5 -W\""

     exit 6
     fi

     if [ $(pgrep -f $POSTGRES) ]; then

     echo "PostgreSQL daemon already running"
     if [ ! -f $PIDFILE ]; then
     echo "Warning: Missing pid file $PIDFILE"
     fi
     exit 1

     else
     test -e "$PG_OOM_ADJUST_FILE" && echo 
"$PG_MASTER_OOM_SCORE_ADJ" > "$PG_OOM_ADJUST_FILE"

     pg_ctl start -w -l $LOGFILE -D $DATADIR
     exit 0
     fi
 ;;

 "stop")
     echo "Shutting down PostgreSQL..."
     pg_ctl stop -l $LOGFILE -D $DATADIR -m smart
 ;;

 "force-stop")
     # Take care! This will kill _all_ client connections
     # and rollback current transactions.
     echo "Shutting down PostgreSQL (fast)..."
     pg_ctl stop -l $LOGFILE -D $DATADIR -m fast
 ;;

 "unclean-stop")
     # Take care! This will abort server process itself
     # resulting with database recovery on next start.
     echo "Shutting down PostgreSQL (immediate)..."
     pg_ctl stop -l $LOGFILE -D $DATADIR -m immediate
 

query performance

[hmidi slim]

Hi,
I have two tables: establishment which contains these columns: id, name,
longitude, latitude, geom (Geometric column)
Product contains: id, name, establishment_id
First of all I want to select the establishment within a radius.
I run this query:
select e.name, e1.name
from establishment as e, establishment as e1
where e.id <> e1.id
and e1.id = 1
and ST_DWithin(geom, ST_MakePoint(e1.longitude, e1.latitude)::geography,
1000)

The result of this query returns all the establishment within a radius 1KM
from from a given establishment which has an id = 1.

After that I want to get the product's name of each establishment from the
query's result.

Is there an other optimized solution to make a query such this:
select * from (
select e.name, e1.name, e.id
from establishment as e, establishment as e1
where e.id <> e1.id
and e1.id = 1
and ST_DWithin(geom, ST_MakePoint(e1.longitude, e1.latitude)::geography,
1000)) as tmp inner join product as p on p.establishment_id = tmp.id

Re: Need to fix one more glitch in upgrade to -10.2

[Rich Shepard]

On Sat, 17 Feb 2018, Adrian Klaver wrote:


Did pg_upgrade spit out any warnings/errors?


Adrian,

  Yes. The uid and gid were mis-matched and, because of that, the
data/directory and all its files were owned by group user, not group
postgres.


In your previous post you showed:
# /etc/rc.postgresql reload

yet below shows:

/etc/rc.d/rc.postgresql


  No, the first left off the rc.d/ directory.


Cut and paste error or not?

So are env variables set correctly?

Your ps ax output showed a Postgres instance running:

postgres -D /var/lib/pgsql/10.2/data


  Well, I had 'killall postgres' and the server shut down. Don't know why
that was displayed.


Do you know what port it is using and try to connect to it?


  The default: /tmp/.s.PGSQL.5432. Everything's shut down now. So I try:

[root@salmo /etc/rc.d]# killall postgres
[root@salmo /etc/rc.d]# ./rc.postgresql start
Could not find 'postgres' binary. Maybe PostgreSQL is not installed properly?

  Yet,

# ll /usr/bin/postgres 
lrwxrwxrwx 1 root root 35 Feb 17 09:30 /usr/bin/postgres -> ../lib/postgresql/10.2/bin/postgres*


Hence, my confustion.

Rich

Re: Need to fix one more glitch in upgrade to -10.2

[Adrian Klaver]

On 02/17/2018 03:59 PM, Rich Shepard wrote:

On Sat, 17 Feb 2018, Adrian Klaver wrote:


Did pg_upgrade spit out any warnings/errors?


Adrian,

   Yes. The uid and gid were mis-matched and, because of that, the
data/directory and all its files were owned by group user, not group
postgres.


In your previous post you showed:
# /etc/rc.postgresql reload

yet below shows:

/etc/rc.d/rc.postgresql


   No, the first left off the rc.d/ directory.


Cut and paste error or not?

So are env variables set correctly?

Your ps ax output showed a Postgres instance running:

postgres -D /var/lib/pgsql/10.2/data


   Well, I had 'killall postgres' and the server shut down. Don't know why
that was displayed.


Do you know what port it is using and try to connect to it?


   The default: /tmp/.s.PGSQL.5432. Everything's shut down now. So I try:

[root@salmo /etc/rc.d]# killall postgres
[root@salmo /etc/rc.d]# ./rc.postgresql start
Could not find 'postgres' binary. Maybe PostgreSQL is not installed 
properly?


   Yet,

# ll /usr/bin/postgres lrwxrwxrwx 1 root root 35 Feb 17 09:30 
/usr/bin/postgres -> ../lib/postgresql/10.2/bin/postgres*


From a previous post:

POSTGRES=/usr/lib${LIBDIRSUFFIX}/@PRGNAM@/$PG_VERSION/bin/postgres

From here:

http://slackbuilds.org/slackbuilds/14.1/system/postgresql/postgresql.SlackBuild

if [ "$ARCH" = "i486" ]; then
  SLKCFLAGS="-O2 -march=i486 -mtune=i686 -DLINUX_OOM_SCORE_ADJ=0"
  LIBDIRSUFFIX=""
elif [ "$ARCH" = "i686" ]; then
  SLKCFLAGS="-O2 -march=i686 -mtune=i686 -DLINUX_OOM_SCORE_ADJ=0"
  LIBDIRSUFFIX=""
elif [ "$ARCH" = "x86_64" ]; then
  SLKCFLAGS="-O2 -fPIC -DLINUX_OOM_SCORE_ADJ=0"
  LIBDIRSUFFIX="64"
else
  SLKCFLAGS="-O2 -DLINUX_OOM_SCORE_ADJ=0"
  LIBDIRSUFFIX=""
fi

So are you on 64bit system? Because then?:

/usr/bin/postgres -> ../lib/postgresql/10.2/bin/postgres*

would be:

/usr/bin/postgres -> ../lib64/postgresql/10.2/bin/postgres*


You could also try using pg_ctl to start the server directly:

https://www.postgresql.org/docs/10/static/app-pg-ctl.html

Just to prove that the install is good.



Hence, my confustion.

Rich






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

Re: Need to fix one more glitch in upgrade to -10.2

[Rich Shepard]

On Sat, 17 Feb 2018, Adrian Klaver wrote:


From a previous post:

POSTGRES=/usr/lib${LIBDIRSUFFIX}/@PRGNAM@/$PG_VERSION/bin/postgres

From here:

http://slackbuilds.org/slackbuilds/14.1/system/postgresql/postgresql.SlackBuild


  The desktop runs 32-bit 14.2.


You could also try using pg_ctl to start the server directly:


  postgres@salmo:~$ pg_ctl start -D /var/lib/pgsql/10.2/data/ 
waiting for server to start2018-02-17 16:43:18.344 PST [6761] LOG:  listening on IPv4 address "127.0.0.1", port 5432

2018-02-17 16:43:18.368 PST [6761] LOG:  listening on Unix socket 
"/tmp/.s.PGSQL.5432"
2018-02-17 16:43:18.454 PST [6762] LOG:  database system was shut down at 
2018-02-17 15:53:32 PST
2018-02-17 16:43:18.487 PST [6761] LOG:  database system is ready to accept 
connections
 done
server started
postgres@salmo:~$ 2018-02-17 16:43:29.899 PST [6774] FATAL:  role "rshepard" 
does not exist
2018-02-17 16:43:31.669 PST [6776] FATAL:  role "rshepard" does not exist

  So I guess I need to re-learn how to set roles and set one for me for all
databases I own. That's tomorrow morning. Will report results after doing
that.

Thanks, Adrian,

Rich

Re: Need to fix one more glitch in upgrade to -10.2

[Adrian Klaver]

On 02/17/2018 04:44 PM, Rich Shepard wrote:

On Sat, 17 Feb 2018, Adrian Klaver wrote:


From a previous post:

POSTGRES=/usr/lib${LIBDIRSUFFIX}/@PRGNAM@/$PG_VERSION/bin/postgres

From here:

http://slackbuilds.org/slackbuilds/14.1/system/postgresql/postgresql.SlackBuild 



   The desktop runs 32-bit 14.2.


You could also try using pg_ctl to start the server directly:


   postgres@salmo:~$ pg_ctl start -D /var/lib/pgsql/10.2/data/ waiting 
for server to start2018-02-17 16:43:18.344 PST [6761] LOG:  
listening on IPv4 address "127.0.0.1", port 5432
2018-02-17 16:43:18.368 PST [6761] LOG:  listening on Unix socket 
"/tmp/.s.PGSQL.5432"
2018-02-17 16:43:18.454 PST [6762] LOG:  database system was shut down 
at 2018-02-17 15:53:32 PST
2018-02-17 16:43:18.487 PST [6761] LOG:  database system is ready to 
accept connections

  done
server started
postgres@salmo:~$ 2018-02-17 16:43:29.899 PST [6774] FATAL:  role 
"rshepard" does not exist

2018-02-17 16:43:31.669 PST [6776] FATAL:  role "rshepard" does not exist

   So I guess I need to re-learn how to set roles and set one for me for 
all

databases I own. That's tomorrow morning. Will report results after doing
that.


If it was in the 9.6 cluster it should be in the 10 cluster. I would log 
into the postgres db as postgres via psql and do \du.

If the roles are not there then:

1) If the 9.6 cluster can be started then do pg_dumpall -g to get the 
roles and other globals. Then feed that to the 10 cluster.


2) If the 9.6 cluster is not available then pull the roles out of:
pg_dumpall -c -f .sql'


As to starting, borrowing from the rc.postgresql script:

PG_VERSION=10.2 PG_PORT=5432 /etc/rc.d/rc.postgresql start



Thanks, Adrian,

Rich





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

Re: Need to fix one more glitch in upgrade to -10.2

[Adrian Klaver]

On 02/17/2018 04:44 PM, Rich Shepard wrote:

On Sat, 17 Feb 2018, Adrian Klaver wrote:


From a previous post:

POSTGRES=/usr/lib${LIBDIRSUFFIX}/@PRGNAM@/$PG_VERSION/bin/postgres

From here:

http://slackbuilds.org/slackbuilds/14.1/system/postgresql/postgresql.SlackBuild 



   The desktop runs 32-bit 14.2.


You could also try using pg_ctl to start the server directly:


   postgres@salmo:~$ pg_ctl start -D /var/lib/pgsql/10.2/data/ waiting 
for server to start2018-02-17 16:43:18.344 PST [6761] LOG:  
listening on IPv4 address "127.0.0.1", port 5432
2018-02-17 16:43:18.368 PST [6761] LOG:  listening on Unix socket 
"/tmp/.s.PGSQL.5432"
2018-02-17 16:43:18.454 PST [6762] LOG:  database system was shut down 
at 2018-02-17 15:53:32 PST
2018-02-17 16:43:18.487 PST [6761] LOG:  database system is ready to 
accept connections

  done
server started
postgres@salmo:~$ 2018-02-17 16:43:29.899 PST [6774] FATAL:  role 
"rshepard" does not exist

2018-02-17 16:43:31.669 PST [6776] FATAL:  role "rshepard" does not exist

   So I guess I need to re-learn how to set roles and set one for me for 
all

databases I own. That's tomorrow morning. Will report results after doing
that.


Got to thinking that given the issues with the upgrade I would be leery 
about the state of the new cluster as a whole. Might want to consider 
doing it over again or just use the pg_dumpall output to recreate the 
database(s).





Thanks, Adrian,

Rich





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

Re: Need to fix one more glitch in upgrade to -10.2

[Rich Shepard]

On Sat, 17 Feb 2018, Adrian Klaver wrote:


Got to thinking that given the issues with the upgrade I would be leery
about the state of the new cluster as a whole. Might want to consider
doing it over again or just use the pg_dumpall output to recreate the
database(s).


Adrian,

  That's what I was thinking, too. I can remove the 10.2 package, rebuild
and re-install it. Run initdb, then, as postgres, read in the .sql file.
This is probably the pragmatic thing to do.

Best regards,

Rich

Re: Need to fix one more glitch in upgrade to -10.2

[Tim Cross]

Rich Shepard  writes:

> On Sat, 17 Feb 2018, Adrian Klaver wrote:
>
>
> [root@salmo /etc/rc.d]# killall postgres
> [root@salmo /etc/rc.d]# ./rc.postgresql start
> Could not find 'postgres' binary. Maybe PostgreSQL is not installed properly?
>
>Yet,
>
> # ll /usr/bin/postgres 
> lrwxrwxrwx 1 root root 35 Feb 17 09:30 /usr/bin/postgres -> 
> ../lib/postgresql/10.2/bin/postgres*
>
> Hence, my confustion.
>

Try doing an 'll' on the second part of that output i.e. 

ll /usr//lib/postgresql/10.2/bin/postgres*

to verify the location the symbolic link in  /usr/bin is pointing to is
correct.

Tim

-- 
Tim Cross

Re: Need to fix one more glitch in upgrade to -10.2

[Tim Cross]

Rich Shepard  writes:

> On Sat, 17 Feb 2018, Adrian Klaver wrote:
>
>> Got to thinking that given the issues with the upgrade I would be leery
>> about the state of the new cluster as a whole. Might want to consider
>> doing it over again or just use the pg_dumpall output to recreate the
>> database(s).
>
> Adrian,
>
>That's what I was thinking, too. I can remove the 10.2 package, rebuild
> and re-install it. Run initdb, then, as postgres, read in the .sql file.
> This is probably the pragmatic thing to do.
>

This may not be relevant, but figured I'd mention it anyway. I'm not
familiar with Slackware (last ran it in 1995!), but many Linux distros
have switched from the old init scripts setup to using systemd. If
Slackware has also made that switch, then you may find there is a
systemd service for postgres and instead of using the old /etc/iinit.d
scripts, you now do something like systemctl start postgres-10.2.service

Tim


-- 
Tim Cross

Re: query performance

[David Rowley]

On 18 February 2018 at 12:35, hmidi slim  wrote:
> Is there an other optimized solution to make a query such this:
> select * from (
> select e.name, e1.name, e.id
> from establishment as e, establishment as e1
> where e.id <> e1.id
> and e1.id = 1
> and ST_DWithin(geom, ST_MakePoint(e1.longitude, e1.latitude)::geography,
> 1000)) as tmp inner join product as p on p.establishment_id = tmp.id

You really should state what is wrong with the performance of the
above version and also include the EXPLAIN  (ANALYZE, BUFFERS) of that
query.

The version of PostgreSQL that you're running it on is also a good
thing to share.

Details of the indexes which you've defined on the tables are also
useful information. It would especially be good to know if you've
added an index on product (establishment_id), for example.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services