Need to fix one more glitch in upgrade to -10.2

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.klaver@aklaver.com

On Sat, 17 Feb 2018, Adrian Klaver wrote:

How did you upgrade, dump/restore or pg_upgrade?

Adrian,

Ran 'pg_dumpall -c -f <filename>.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 <adis_at_linux.org.ba>
# 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 $DATADIR -m smart
;;

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

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

"reload")
echo "Reloading configuration for PostgreSQL..."
pg_ctl reload -l $LOGFILE -D $DATADIR -m smart
;;

"status")
if [ $(pgrep -f $POSTGRES) ]; then
echo "PostgreSQL is running"

if [ ! -e $PIDFILE ]; then
echo "Warning: Missing pid file $PIDFILE"
fi

exit 0
else
echo "PostgreSQL is stopped"

if [ -e $PIDFILE ]; then
echo "Detected stale pid file $PIDFILE"
fi

exit 0
fi
;;

*)
# unclean-stop and unclean-restart are not documented on
purpose.
echo "Usage: $0
{start|stop|force-stop|status|restart|force-restart|reload}"
exit 1
;;
esac

Regards,

Rich

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 <filename>.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 <adis_at_linux.org.ba>
# 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 $DATADIR -m smart
    ;;

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

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

    "reload")
        echo "Reloading configuration for PostgreSQL..."
        pg_ctl reload -l $LOGFILE -D $DATADIR -m smart
    ;;

    "status")
        if [ $(pgrep -f $POSTGRES) ]; then
            echo "PostgreSQL is running"

            if [ ! -e $PIDFILE ]; then
                echo "Warning: Missing pid file $PIDFILE"
            fi

            exit 0
        else
            echo "PostgreSQL is stopped"

            if [ -e $PIDFILE ]; then
                echo "Detected stale pid file $PIDFILE"
            fi

            exit 0
        fi
    ;;

    *)
        # unclean-stop and unclean-restart are not documented on purpose.
        echo "Usage: $0
{start|stop|force-stop|status|restart|force-restart|reload}"
        exit 1
    ;;
esac

Regards,

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

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

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.klaver@aklaver.com

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 start....2018-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

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 start....2018-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 <filename>.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.klaver@aklaver.com

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 start....2018-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.klaver@aklaver.com

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

Rich Shepard <rshepard@appl-ecosys.com> 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

Rich Shepard <rshepard@appl-ecosys.com> 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

On Sun, 18 Feb 2018, Tim Cross wrote:

This may not be relevant,

Tim,

Nope. Pat goes for stability, not cutting edge. No systemd in the
forthcoming 15.0, either.

Thanks,

Rich

On Sun, 18 Feb 2018, Tim Cross wrote:

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

Try doing an 'll' on the second part of that output i.e.
ll /usr//lib/postgresql/10.2/bin/postgres*

See my message, repeated above.

Regards,

Rich

On Sat, 17 Feb 2018, Rich Shepard wrote:

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.

Rather than doing this my reading of the 10.2 initdb pages suggest that I
can remove /var/lib/pgsql/10.2/data/*, leaving an empty data directory owned
by postgres.postgres. Then, as user postgres, I can run initdb with -D
/full/path/to/data/ and start over. Is this appropriate?

Looking at the backed up .sql file I see roles for databases that were
removed a long time ago. I'll read the roles section of the 10.2 docs and
edit the .sql file to clean those up.

Since I'm the only user on this host and postgres cluster would 'trust' be
the appropriate method for use in pb_hba.conf; e.g.,
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust

so I can access my databases from the desktop or portables without a
password?

Regards,

Rich

On 02/18/2018 08:05 AM, Rich Shepard wrote:

On Sat, 17 Feb 2018, Rich Shepard wrote:

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.

  Rather than doing this my reading of the 10.2 initdb pages suggest
that I
can remove /var/lib/pgsql/10.2/data/*, leaving an empty data directory
owned
by postgres.postgres. Then, as user postgres, I can run initdb with -D
/full/path/to/data/ and start over. Is this appropriate?

Yes.

  Looking at the backed up .sql file I see roles for databases that were
removed a long time ago. I'll read the roles section of the 10.2 docs and

They could not have been removed as they are in the file. I am guessing
you are saying they are not in use as far as you know. Just a
warning(from experience), memory is a tricky thing and removing what is
thought to be inactive roles is a quick way to find they are not.

edit the .sql file to clean those up.

  Since I'm the only user on this host and postgres cluster would
'trust' be
the appropriate method for use in pb_hba.conf; e.g., # "local" is for
Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust

so I can access my databases from the desktop or portables without a
password?

So can anyone who knows that postgres role is generally always there. If
you want to do this at least restrict the user field.

Regards,

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

On Sun, 18 Feb 2018, Adrian Klaver wrote:

Is this appropriate?

Yes.

Adrian,

Thanks for confirming

They could not have been removed as they are in the file. I am guessing
you are saying they are not in use as far as you know. Just a warning(from
experience), memory is a tricky thing and removing what is thought to be
inactive roles is a quick way to find they are not.

Well, one set of roles is related to my former bookkeeping system and that
database had not been removed. Another set of roles was related to a
replacement bookkeeping system I didn't use and I don't recall seeing that
database when I last ran 'psql -l'.

So can anyone who knows that postgres role is generally always there. If you
want to do this at least restrict the user field.

As I'm the only one here unless someone is sitting here and logging in
under my username they won't see a thing. And no one's going to sit here an
log in as me other than me. :-) One advantage of working from home.

Regards,

Rich

On Sun, 18 Feb 2018, Rich Shepard wrote:

Thanks for confirming

Removed all files in the data/ directory, re-initialized the cluster, and
restored the dumped .sql file (minus three databases and their roles
manually deleted). All works well now.

Thanks, Adrian!

Best regards,

Rich

Rich Shepard <rshepard@appl-ecosys.com> writes:

On Sun, 18 Feb 2018, Tim Cross wrote:

This may not be relevant,

Tim,

Nope. Pat goes for stability, not cutting edge. No systemd in the
forthcoming 15.0, either.

Thanks,

Rich

No worries, though I'm not sure you can call systemd 'cutting edge'
anymore. Even many of the distros which argued against it have switched
(i.e. Debian, Ubuntu).

Tim

--
Tim Cross

Rich Shepard <rshepard@appl-ecosys.com> writes:

On Sun, 18 Feb 2018, Tim Cross wrote:

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

Try doing an 'll' on the second part of that output i.e.
ll /usr//lib/postgresql/10.2/bin/postgres*

See my message, repeated above.

Regards,

Rich

Your ll command is only showing what the symbolic link is pointing to,
not the status of the thing it points to. It is possible for the target
of a symbolic link to be changed, deleted etc (a dangling sym link). My
point was to verify the target and it's permissions, not just the sym
link itself.

Tim

--
Tim Cross