Re: Upgrading using pg_dumpall

On 09/04/2016 08:11 AM, Rich Shepard wrote:
> On Sun, 4 Sep 2016, Adrian Klaver wrote:
>
>> But the message you sent me offlist showed the 9.5 instance running.
>
>   But now it's not running.
>
>> How are you starting the instance?
>
>   As superuser poostgres: pg_ctl start -D /var/lib/pgsql/data &
>
>   After removing an orphaned postmaster.pid the above seemed to have
> started
> postgres, but there's no postmaster process running.
>
>> Are you sure that the password being asked for is not for the OS user
>> you are using to run whatever start script you are using?
>
>   Thinking postmaster is running I tried this:
>
> $ psql crm
> Password: FATAL:  password authentication failed for user "rshepard"
> DETAIL:  User "rshepard" has no password assigned.
>     Connection matched pg_hba.conf line 80: "local   all             all
> md5"
> psql: FATAL:  password authentication failed for user "rshepard"
>
>   The crm database is owned by me.
>
>> I have never used it, but I am pretty sure that is not what -W means. It
>> looks to me that it asks you to create a password at init for the
>> database
>> superuser(in this case postgres) and only that user when that user tries
>> to use log into a database after the cluster is started.
>
>   From man initdb:
>
> -W, --pwprompt
>            Makes initdb prompt for a password to give the database
> superuser.
>            If you don't plan on using password authentication, this is not
>            important. Otherwise you won't be able to use password
>            authentication until you have a password set up.
>
>   The superuser already exists in /etc/passwd.

-W is not referring to the OS user but the database superuser. Now in
your case they have the same name, postgres. The settings in /etc/passwd
are not relevant to what -W is doing. -W is referring to user
information being stored in the cluster in the system tables pg_user and
pg_shadow:

https://www.postgresql.org/docs/9.5/static/catalogs.html

>
>> Do you remember what password you specified?
>
>   Yes. It's the same password I use for logging in as a user. If it's the
> superuser password being requested, then that's the same as my user
> password.
>
>   The Slackware rc.postgresql file for 9.5 has changed from 9.3 and
> earlier.
> It's asking for passwords:
>
>  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
>
>   Note the '-W' at the end. But, I ran initdb from the command line as user
> postgres.

The -W is not the issue the auth MEHOD set in your pg_hba.conf is. Set
it to trust for now. I'm guessing that since you ran using trust in your
9.3 instance you do not actually have any passwords set up for the
users. This means there are also none in the new 9.5 cluster created
from the dump file, with one exception. That is since you used -W to
initdb you now have a password for the postgres user.

>
> Rich
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com