Обсуждение: Need to fix one more glitch in upgrade to -10.2
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/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
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
On Mon, 19 Feb 2018, Tim Cross wrote: > It is possible for the target of a symbolic link to be changed, deleted > etc (a dangling sym link). Tim, Broken symlinks display in a different color, black on a red background if I remember correctly, rather than the light cyan of a working symlink. I've seen enough of the former to recognize the difference. :-) Thanks, Rich
Rich Shepard <rshepard@appl-ecosys.com> writes: > On Mon, 19 Feb 2018, Tim Cross wrote: > >> It is possible for the target of a symbolic link to be changed, deleted >> etc (a dangling sym link). > > Tim, > > Broken symlinks display in a different color, black on a red background if > I remember correctly, rather than the light cyan of a working symlink. I've > seen enough of the former to recognize the difference. :-) > > Thanks, > > Rich Hi Rich, yes, for dangling links, if your ls is configured to show colours and your shell honours those colours, dangling links will show up with a different colour. However, this will not tell you if, for example, the mode of the file being pointed to does not have the executable bit set for the user/group running/calling the sym link. If the file doe snot have the right executable bits set, you would get the error message about not being able to find the executable file. From memory, your cut and paste looked like it has a '*' at the end, which is what ls will show (assuming the alias you have adds that option), so the target probably has at least the owner exec bit set, but when tracking down problems like this, I have learnt it is often best to just verify the modes and owner/group memberships of the target are correct. regards, Tim -- Tim Cross