Thread: Upgrading using pg_dumpall
Postgres-9.3.4 was built directly from the web site download source. It's installed in /usr/lib/postgresql/9.3.4/. Postgresql-9.4.5 was built from the SlackBuilds.org script and installed in /usr/lib/postgresql/9.4/. The bin/ subdirectory of the 9.4 version has all the excutable files, but that of the 9.3.4 version seens to contain only a few postgis files. When I run psql with a database name I see this: $ psql crm psql (9.4.5, server 9.3.4) Type "help" for help. crm=# so it appears to me that 9.4.5 is running while using the 9.3.4 data directory. I want to move all data from 9.3.4 to 9.4.5. Data are in /var/lib/pgsql/9.3.4/data/ (405 M) and I want to move them to the empty (only 4.0 K) /var/lib/pgsql/9.4/data/. Reading the pg_upgrade man page tells me I need the old bin/ and data/ directories as well as the new ones. I've no idea where the 9.3.4 bin directory now is; /usr/bin/postgres is a soft link to /usr/lib/postgresql/9.4/bin/postgres. This suggests that I need to use pg_dumpall, then read in the file using psql with the 9.4.5 version. I've dumped all databases to a .sql file and am unsure how to proceed. Clue stick needed, Rich
On 09/03/2016 02:19 PM, Rich Shepard wrote: > Postgres-9.3.4 was built directly from the web site download source. It's > installed in /usr/lib/postgresql/9.3.4/. Postgresql-9.4.5 was built from > the > SlackBuilds.org script and installed in /usr/lib/postgresql/9.4/. The bin/ > subdirectory of the 9.4 version has all the excutable files, but that of > the > 9.3.4 version seens to contain only a few postgis files. > > When I run psql with a database name I see this: > $ psql crm > psql (9.4.5, server 9.3.4) > Type "help" for help. > > crm=# > > so it appears to me that 9.4.5 is running while using the 9.3.4 data > directory. No it says you are using the 9.4.5 version of psql to connect to a 9.3.4 server. psql is available independent of the server running. You will need to see if both servers are running by doing something like: ps ax | grep postgres > > I want to move all data from 9.3.4 to 9.4.5. Data are in > /var/lib/pgsql/9.3.4/data/ (405 M) and I want to move them to > the empty (only 4.0 K) /var/lib/pgsql/9.4/data/. > > Reading the pg_upgrade man page tells me I need the old bin/ and data/ > directories as well as the new ones. I've no idea where the 9.3.4 bin > directory now is; /usr/bin/postgres is a soft link to > /usr/lib/postgresql/9.4/bin/postgres. This suggests that I need to use > pg_dumpall, then read in the file using psql with the 9.4.5 version. I've > dumped all databases to a .sql file and am unsure how to proceed. Before you do any of that you need to verify that the servers you want are running. Remember they can not share a port, so you need to look at what the port settings are in the respective postgresql.conf files. > > Clue stick needed, > > Rich > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, 3 Sep 2016, Adrian Klaver wrote: > No it says you are using the 9.4.5 version of psql to connect to a 9.3.4 > server. psql is available independent of the server running. You will need to > see if both servers are running by doing something like: Adrian, Yeah, that's what it said. :-( > ps ax | grep postgres Only the earlier version: # ps ax | grep postgres 1822 ? S 0:33 postgres -D /var/lib/pgsql/9.3.4/data/ 1824 ? Ss 0:01 postgres: checkpointer process 1825 ? Ss 0:21 postgres: writer process 1826 ? Ss 0:21 postgres: wal writer process 1827 ? Ss 0:45 postgres: autovacuum launcher process 1828 ? Ss 1:10 postgres: stats collector process I have not run init on 9.4.5 yet. Thought I should learn how best to proceed first. > Before you do any of that you need to verify that the servers you want are > running. Remember they can not share a port, so you need to look at what the > port settings are in the respective postgresql.conf files. In /var/lib/pgsql/9.3.4/data/postgresql.conf the port assignment (5432) is commented out, yet that's the port I've always seen assigned. What do you suggest as the procedure for me to follow to clean this all up? TIA, Rich
On 09/03/2016 02:44 PM, Rich Shepard wrote: > On Sat, 3 Sep 2016, Adrian Klaver wrote: > >> No it says you are using the 9.4.5 version of psql to connect to a >> 9.3.4 server. psql is available independent of the server running. You >> will need to see if both servers are running by doing something like: > > Adrian, > > Yeah, that's what it said. :-( > >> ps ax | grep postgres > > Only the earlier version: > > # ps ax | grep postgres > 1822 ? S 0:33 postgres -D /var/lib/pgsql/9.3.4/data/ > 1824 ? Ss 0:01 postgres: checkpointer process > 1825 ? Ss 0:21 postgres: writer process > 1826 ? Ss 0:21 postgres: wal writer process > 1827 ? Ss 0:45 postgres: autovacuum launcher process > 1828 ? Ss 1:10 postgres: stats collector process > > I have not run init on 9.4.5 yet. Thought I should learn how best to > proceed first. A question are you really using 9.4.5 or 9.5.4(the latest version of 9.5)? And if you want to use 9.4 I would say use the latest(9.4.9). If the above is a yes, any particular reason you do not want to move to 9.5? > >> Before you do any of that you need to verify that the servers you want >> are running. Remember they can not share a port, so you need to look >> at what the port settings are in the respective postgresql.conf files. > > In /var/lib/pgsql/9.3.4/data/postgresql.conf the port assignment > (5432) is > commented out, yet that's the port I've always seen assigned. That is the default port which is why it is shown and is commented out. If you want to run on a different port you can uncomment it and enter a different port number say 5442. > > What do you suggest as the procedure for me to follow to clean this all > up? Get your 9.4(5) database instance up and running. I generally keep the existing database on the default port(5432) and use a greater port number for Postgres versions greater then my current version, so in your case say 5442 for the new instance. Once you verify that the new instance is running and you can connect to it then: psql -U some_user -d postgres -p 5442 -f your_dumpall.sql In the above some_user has to have sufficient database privileges to deal with all the objects in the your_dumpall.sql. For instance if you are using plpythonu then it needs superuser privileges to install. > > TIA, > > Rich > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, 3 Sep 2016, Adrian Klaver wrote: > A question are you really using 9.4.5 or 9.5.4(the latest version of 9.5)? > And if you want to use 9.4 I would say use the latest(9.4.9). Adrian, It is 9.4.5, but I have the source tarball for 9.5.4 in the build directory. > If the above is a yes, any particular reason you do not want to move to 9.5? No. I'll delete the 9.4.5 package and build the 9.5.4 package. Will run init on port 5442 ... except that I do not find an option for init to specify a different port. So I assume that the initdb doesn't care about ports. > Once you verify that the new instance is running and you can connect to it Not sure how I do this. Don't I need to kill the postgres-9.3.4 process then start the -9.5.4 process? Rich
On 09/03/2016 03:43 PM, Rich Shepard wrote: > On Sat, 3 Sep 2016, Adrian Klaver wrote: > >> A question are you really using 9.4.5 or 9.5.4(the latest version of >> 9.5)? >> And if you want to use 9.4 I would say use the latest(9.4.9). > > Adrian, > > It is 9.4.5, but I have the source tarball for 9.5.4 in the build > directory. >> If the above is a yes, any particular reason you do not want to move >> to 9.5? > > No. > > I'll delete the 9.4.5 package and build the 9.5.4 package. Will run init > on port 5442 ... except that I do not find an option for init to specify a > different port. So I assume that the initdb doesn't care about ports. initdb does not care about ports. > >> Once you verify that the new instance is running and you can connect >> to it > > Not sure how I do this. Don't I need to kill the postgres-9.3.4 process > then start the -9.5.4 process? You can if you already have an up to date dump of the 9.3 instance, but is not necessary. As long as you assign each instance a unique port number in postgesql.conf you can have multiple instances of Postgres running at the same time. > > Rich > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, 3 Sep 2016, Adrian Klaver wrote: > Once you verify that the new instance is running and you can connect to it > then: Shut down 9.3.4 using pg_ctl stop as user postgres. Started 9.5.4 as user postgres using 'pg_ctl start /var/lib/pgsql/9.5/data &'. > psql -U some_user -d postgres -p 5442 -f your_dumpall.sql In /tmp there's only .s.PGSQL.5432= .s.PGSQL.5432.lock and since only 9.5.4 is now running that port should be available. The above command failed when run as both me (as user owning/granted all privileges on all databases) and as superuser postgres. Rich
On 09/03/2016 04:01 PM, Rich Shepard wrote: > On Sat, 3 Sep 2016, Adrian Klaver wrote: > >> Once you verify that the new instance is running and you can connect >> to it then: > > Shut down 9.3.4 using pg_ctl stop as user postgres. Started 9.5.4 as user > postgres using 'pg_ctl start /var/lib/pgsql/9.5/data &'. > >> psql -U some_user -d postgres -p 5442 -f your_dumpall.sql > > In /tmp there's only > .s.PGSQL.5432= .s.PGSQL.5432.lock > and since only 9.5.4 is now running that port should be available. Did you change the port number in the 9.5 postgresql.conf and restart the server? Or if you are fine running the 9.5 instance at port 5432, what happens if you do?: psql -d postgres -U some_user -p 5432 > > The above command failed when run as both me (as user owning/granted all > privileges on all databases) and as superuser postgres. What was the error message? > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, 3 Sep 2016, Adrian Klaver wrote: > Or if you are fine running the 9.5 instance at port 5432, what happens if you > do?: > > psql -d postgres -U some_user -p 5432 $ psql -d postgres -U rshepard -p 5432 Password for user rshepard: psql: FATAL: password authentication failed for user "rshepard" > What was the error message? As above. Thanks, Rich
On 09/03/2016 04:16 PM, Rich Shepard wrote: > On Sat, 3 Sep 2016, Adrian Klaver wrote: > >> Or if you are fine running the 9.5 instance at port 5432, what happens >> if you do?: >> >> psql -d postgres -U some_user -p 5432 > > $ psql -d postgres -U rshepard -p 5432 > Password for user rshepard: psql: FATAL: password authentication failed > for user "rshepard" I am guessing this from before you managed to get the dump file to load and populate the appropriate tables with user information from the old Postgres instance. > >> What was the error message? > > As above. > > Thanks, > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, 3 Sep 2016, Adrian Klaver wrote: > I am guessing this from before you managed to get the dump file to load and > populate the appropriate tables with user information from the old Postgres > instance. Could well be the case. Tomorrow will try removing all user-generated databases and re-reading that file. Rich
Hello On 09/04/2016 01:16 AM, Rich Shepard wrote: > On Sat, 3 Sep 2016, Adrian Klaver wrote: > >> Or if you are fine running the 9.5 instance at port 5432, what happens >> if you do?: >> >> psql -d postgres -U some_user -p 5432 > > $ psql -d postgres -U rshepard -p 5432 > Password for user rshepard: psql: FATAL: password authentication failed > for user "rshepard" Does the user rshepard exist in the new 9.5 instance? > >> What was the error message? > > As above. > > Thanks, > > Rich > > -- Swiss PostgreSQL Users Group c/o Charles Clavadetscher Treasurer Motorenstrasse 18 CH – 8005 Zürich http://www.swisspug.org +-----------------------+ | ____ ______ ___ | | / )/ \/ \ | | ( / __ _\ ) | | \ (/ o) ( o) ) | | \_ (_ ) \ ) _/ | | \ /\_/ \)/ | | \/ <//| |\\> | | _| | | | \|_/ | | | | PostgreSQL 1996-2016 | | 20 Years of Success | | | +-----------------------+
On Sun, 4 Sep 2016, Charles Clavadetscher wrote: > Does the user rshepard exist in the new 9.5 instance? I assume so but do not know how to check this. Rich
On 09/04/2016 06:26 AM, Rich Shepard wrote: Ccing list > On Sat, 3 Sep 2016, Adrian Klaver wrote: > >> Well the pg_dumpall *.sql file has the global >> information(users/passwords), so it is a chicken and egg problem. >> >> FYI, you can do: >> >> pg_dumpall -g >> https://www.postgresql.org/docs/9.5/static/app-pg-dumpall.html >> >> to get only those globals and then use that to prime the new instance >> with that information. > > Adrian, > > The postmaster is not running and when I try to connect to a database I'm > asked for my password which is rejected. The above is not possible. If the postmaster was not running there would be no rejection error. > > I'm thinking what I should do is remove the postgresql-9.5.4 package > (which deletes everything) and re-install it. Since 9.3.4 is not running I > assume that after running initdb I can read the saved pg_dumpall .sql file > and be running 9.5.4 without password requests and rejections. Don't do that, you will end up right back at this point again. Find the pg_hba.conf files for your 9.5 cluster and your 9.3 cluster and post them here. > > Your thoughts, please, > > Rich -- Adrian Klaver adrian.klaver@aklaver.com
On Sun, 4 Sep 2016, Adrian Klaver wrote: > The above is not possible. If the postmaster was not running there would be > no rejection error. Adrian, Yes. I conflated when the postmaster was running -- using the 9.3.4 server -- and now when it's not running. Now I'm wondering if I mistakenly used the -W option to initdb which requires password use for access. > Don't do that, you will end up right back at this point again. Find the > pg_hba.conf files for your 9.5 cluster and your 9.3 cluster and post them > here. OK. But when I used 'diff -y' I saw only a paragraph of comments as different. I'll try attaching them; they're ~100 lines each. If the attachments are stripped off my the mlm I'll list them within the message body. Rich
Attachment
On Sun, 4 Sep 2016, Charles Clavadetscher wrote: > Are you able to connect to the new instance with any user at all, e.g. > with psql? If so you can use the command \du to list all users. Charles, No. The postmaster is not running; trying to start it requires a password which is also rejected. Since I have the pg_dumpall data in a large .sql file I think the best solution is for me to remove the 9.5.4 package completely, re-install from the package tarball, run initdb, then start the postmaster and restore all the databases in the cluster. I'm thinking that without paying attention when I initially ran initdb I used a commandline found on a web site (rather than just reading the man page) and used the -W option which requires a password for everything. So starting over from scratch _should_ remove all issues and successfully end this thread. Thanks, Rich
On 09/04/2016 05:40 AM, Rich Shepard wrote: > On Sun, 4 Sep 2016, Charles Clavadetscher wrote: > >> Does the user rshepard exist in the new 9.5 instance? > > I assume so but do not know how to check this. Actually you already have. From an email that I just realized was offlist: "As superuser postgres connected to 9.5.4 'psql -l' shows all databases loaded: xxx | rshepard | UTF8 | C | en_US.UTF-8 | xxx | rshepard | UTF8 | C | en_US.UTF-8 | rshepard=CT " > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sun, 4 Sep 2016, Adrian Klaver wrote: > Actually you already have. From an email that I just realized was offlist: > > "As superuser postgres connected to 9.5.4 'psql -l' shows all databases > loaded: > > xxx | rshepard | UTF8 | C | en_US.UTF-8 | > xxx | rshepard | UTF8 | C | en_US.UTF-8 | rshepard=CT I did not recognize this as the answer to Charles' question. Thanks, Rich
On 09/04/2016 07:08 AM, Rich Shepard wrote: > On Sun, 4 Sep 2016, Charles Clavadetscher wrote: > >> Are you able to connect to the new instance with any user at all, e.g. >> with psql? If so you can use the command \du to list all users. > > Charles, > > No. The postmaster is not running; trying to start it requires a password > which is also rejected. But the message you sent me offlist showed the 9.5 instance running. How are you starting the instance? 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? > > Since I have the pg_dumpall data in a large .sql file I think the best > solution is for me to remove the 9.5.4 package completely, re-install from > the package tarball, run initdb, then start the postmaster and restore all > the databases in the cluster. > > I'm thinking that without paying attention when I initially ran initdb I > used a commandline found on a web site (rather than just reading the man > page) and used the -W option which requires a password for everything. So > starting over from scratch _should_ remove all issues and successfully end > this thread. 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. Do you remember what password you specified? > > Thanks, > > Rich > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 09/04/2016 07:07 AM, Rich Shepard wrote: > On Sun, 4 Sep 2016, Adrian Klaver wrote: > >> The above is not possible. If the postmaster was not running there >> would be no rejection error. > > Adrian, > > Yes. I conflated when the postmaster was running -- using the 9.3.4 > server > -- and now when it's not running. Now I'm wondering if I mistakenly used > the > -W option to initdb which requires password use for access. > >> Don't do that, you will end up right back at this point again. Find >> the pg_hba.conf files for your 9.5 cluster and your 9.3 cluster and >> post them here. > > OK. But when I used 'diff -y' I saw only a paragraph of comments as > different. I'll try attaching them; they're ~100 lines each. If the > attachments are stripped off my the mlm I'll list them within the message > body. Actually there is an important difference. In your 9.3 file you have set METHOD set to trust and in the 9.5 file it is set to md5, which is password. Set the METHOD to trust in your 9.5 file and restart the database. Now for the non-socket access methods this is a security risk, so you will want to change it back at some point once you get the users/passwords figured out > > Rich > > > > > -- Adrian Klaver adrian.klaver@aklaver.com
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.
> 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.
Rich
On Sat, 3 Sep 2016, Adrian Klaver wrote:
> Or if you are fine running the 9.5 instance at port 5432, what happens if you
> do?:
>
> psql -d postgres -U some_user -p 5432
$ psql -d postgres -U rshepard -p 5432
Password for user rshepard:
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"
but,
$ psql -d postgres -U postgres -p 5432
Password for user postgres:
psql (9.5.4)
Type "help" for help.
postgres=#
So, it appears to be a password issue. Please advise me how to get the
running postmaster process to accept my accessing databases without a
password being required.
Thanks again,
Rich
Hello On 09/04/2016 05:11 PM, 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" Well, there you have it. As Adrian suggested you may set temporarily the authentication method to trust, set yourself a password and change it back to md5. That should do. Bye Charles > > 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. > >> 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. > > Rich > > > > -- Swiss PostgreSQL Users Group c/o Charles Clavadetscher Treasurer Motorenstrasse 18 CH – 8005 Zürich http://www.swisspug.org +-----------------------+ | ____ ______ ___ | | / )/ \/ \ | | ( / __ _\ ) | | \ (/ o) ( o) ) | | \_ (_ ) \ ) _/ | | \ /\_/ \)/ | | \/ <//| |\\> | | _| | | | \|_/ | | | | PostgreSQL 1996-2016 | | 20 Years of Success | | | +-----------------------+
Hi On 09/04/2016 05:14 PM, Rich Shepard wrote: > On Sat, 3 Sep 2016, Adrian Klaver wrote: > >> Or if you are fine running the 9.5 instance at port 5432, what happens >> if you do?: >> >> psql -d postgres -U some_user -p 5432 > > $ psql -d postgres -U rshepard -p 5432 > Password for user rshepard: 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" > > but, > > $ psql -d postgres -U postgres -p 5432 > Password for user postgres: psql (9.5.4) > Type "help" for help. > > postgres=# > > So, it appears to be a password issue. Please advise me how to get the > running postmaster process to accept my accessing databases without a > password being required. Adrian already answered that. You can set the connection authentication method to trust. This can have some risks, however. Bye Charles > > Thanks again, > > Rich > > -- Swiss PostgreSQL Users Group c/o Charles Clavadetscher Treasurer Motorenstrasse 18 CH – 8005 Zürich http://www.swisspug.org +-----------------------+ | ____ ______ ___ | | / )/ \/ \ | | ( / __ _\ ) | | \ (/ o) ( o) ) | | \_ (_ ) \ ) _/ | | \ /\_/ \)/ | | \/ <//| |\\> | | _| | | | \|_/ | | | | PostgreSQL 1996-2016 | | 20 Years of Success | | | +-----------------------+
Hi On 09/04/2016 05:16 PM, Charles Clavadetscher wrote: > Hi > > On 09/04/2016 05:14 PM, Rich Shepard wrote: >> On Sat, 3 Sep 2016, Adrian Klaver wrote: >> >>> Or if you are fine running the 9.5 instance at port 5432, what happens >>> if you do?: >>> >>> psql -d postgres -U some_user -p 5432 >> >> $ psql -d postgres -U rshepard -p 5432 >> Password for user rshepard: 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" >> >> but, >> >> $ psql -d postgres -U postgres -p 5432 >> Password for user postgres: psql (9.5.4) >> Type "help" for help. >> >> postgres=# >> >> So, it appears to be a password issue. Please advise me how to get the >> running postmaster process to accept my accessing databases without a >> password being required. > > Adrian already answered that. You can set the connection authentication > method to trust. This can have some risks, however. You may also consider using a .pgpass file: https://www.postgresql.org/docs/9.5/static/libpq-pgpass.html Bye Charles > > Bye > Charles > >> >> Thanks again, >> >> Rich >> >> > -- Swiss PostgreSQL Users Group c/o Charles Clavadetscher Treasurer Motorenstrasse 18 CH – 8005 Zürich http://www.swisspug.org +-----------------------+ | ____ ______ ___ | | / )/ \/ \ | | ( / __ _\ ) | | \ (/ o) ( o) ) | | \_ (_ ) \ ) _/ | | \ /\_/ \)/ | | \/ <//| |\\> | | _| | | | \|_/ | | | | PostgreSQL 1996-2016 | | 20 Years of Success | | | +-----------------------+
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
On 09/04/2016 08:14 AM, Rich Shepard wrote: > On Sat, 3 Sep 2016, Adrian Klaver wrote: > >> Or if you are fine running the 9.5 instance at port 5432, what happens >> if you do?: >> >> psql -d postgres -U some_user -p 5432 > > $ psql -d postgres -U rshepard -p 5432 > Password for user rshepard: 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" > > but, > > $ psql -d postgres -U postgres -p 5432 > Password for user postgres: psql (9.5.4) > Type "help" for help. > > postgres=# > > So, it appears to be a password issue. Please advise me how to get the > running postmaster process to accept my accessing databases without a > password being required. Asked and answered. If you want the complete information: https://www.postgresql.org/docs/9.5/static/client-authentication.html > > Thanks again, > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sun, 4 Sep 2016, Adrian Klaver wrote: > Actually there is an important difference. In your 9.3 file you have set > METHOD set to trust and in the 9.5 file it is set to md5, which is > password. Set the METHOD to trust in your 9.5 file and restart the > database. Now for the non-socket access methods this is a security risk, > so you will want to change it back at some point once you get the > users/passwords figured out Adrian, I saw that but missed the meaning. I'm the only user on this system so there is no internal security risk. After learning that the Slackware script's restart does not work[1] I stopped the process then restarted it. I can now access my databases and the financial software via the browser UI. Now I can remove the 9.3.4 directory and be comfortable that the next upgrade will be smooth. Thanks very much for your patient help! Much appreciated, Rich [1] I've seen this same behavior in other rc.<program> scripts. Stopping and starting works in all cases.
On Sun, 4 Sep 2016, Charles Clavadetscher wrote: > Well, there you have it. As Adrian suggested you may set temporarily the > authentication method to trust, set yourself a password and change it back > to md5. Charles, I've had a password on this LAN for almost 2 decades. I've not before set a password specifically for postgresql. Since I'm the only user and the trust authentification has worked for the same period, I'll leve it that way. Thanks for your help, Rich
On Sun, 4 Sep 2016, Adrian Klaver wrote: > -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 Adrian, I did not know this. Thanks for the information. Rich
On 09/04/2016 09:01 AM, Rich Shepard wrote: > On Sun, 4 Sep 2016, Adrian Klaver wrote: > >> Actually there is an important difference. In your 9.3 file you have set >> METHOD set to trust and in the 9.5 file it is set to md5, which is >> password. Set the METHOD to trust in your 9.5 file and restart the >> database. Now for the non-socket access methods this is a security risk, >> so you will want to change it back at some point once you get the >> users/passwords figured out > > Adrian, > > I saw that but missed the meaning. I'm the only user on this system so > there is no internal security risk. Just be aware that you now have a password for the postgres user and that if you ever do enable md5 you will need it. I would take Charles's suggestion and set up a .pgpass file just to be safe. > > After learning that the Slackware script's restart does not work[1] I > stopped the process then restarted it. I can now access my databases and > the > financial software via the browser UI. > > Now I can remove the 9.3.4 directory and be comfortable that the next > upgrade will be smooth. > > Thanks very much for your patient help! > > Much appreciated, > > Rich > > [1] I've seen this same behavior in other rc.<program> scripts. Stopping > and starting works in all cases. > > -- Adrian Klaver adrian.klaver@aklaver.com
On 09/04/2016 09:01 AM, Rich Shepard wrote: > On Sun, 4 Sep 2016, Adrian Klaver wrote: > >> Actually there is an important difference. In your 9.3 file you have set >> METHOD set to trust and in the 9.5 file it is set to md5, which is >> password. Set the METHOD to trust in your 9.5 file and restart the >> database. Now for the non-socket access methods this is a security risk, >> so you will want to change it back at some point once you get the >> users/passwords figured out > > Adrian, > > I saw that but missed the meaning. I'm the only user on this system so > there is no internal security risk. Forgot an option in my previous reply, removing the password: https://www.postgresql.org/docs/9.5/static/sql-alterrole.html ALTER ROLE postgres WITH PASSWORD NULL; > > After learning that the Slackware script's restart does not work[1] I > stopped the process then restarted it. I can now access my databases and > the > financial software via the browser UI. > > Now I can remove the 9.3.4 directory and be comfortable that the next > upgrade will be smooth. > > Thanks very much for your patient help! > > Much appreciated, > > Rich > > [1] I've seen this same behavior in other rc.<program> scripts. Stopping > and starting works in all cases. > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sun, 4 Sep 2016, Adrian Klaver wrote: > Just be aware that you now have a password for the postgres user and that > if you ever do enable md5 you will need it. I would take Charles's > suggestion and set up a .pgpass file just to be safe. Adrian, OK. I'll also read the page at the URL you provided and add a password for me, too. Rich
On Sun, 4 Sep 2016, Adrian Klaver wrote: > -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 I see the pg_user in Section 49.79 but don't know how to correctly configure it. It seems that pg_shadow is unnecessary since I don't need to provide compatibility with versions < 8.1. Rich
On Sun, 4 Sep 2016, Adrian Klaver wrote: > I would take Charles's suggestion and set up a .pgpass file just to be > safe. The file ~/.pgpass already exists, but without an explicit password. I added my password (plain text). The file already had perms 0600. Perhaps my password was rejected with the authorization method set to md5 was because the password field was missing. Rich
On 09/04/2016 09:43 AM, Rich Shepard wrote: > On Sun, 4 Sep 2016, Adrian Klaver wrote: > >> -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 > > I see the pg_user in Section 49.79 but don't know how to correctly > configure it. It seems that pg_shadow is unnecessary since I don't need to > provide compatibility with versions < 8.1. You don't it directly. That information is supplied by Postgres when you do CREATE or ALTER ROLE. The -W switch just does that for the superuser(postgres in your case) when you initdb a new cluster. Sorry, old habits. pg_user is a version of the pg_shadow view that blanks out the actual password. pg_shadow is a view over the table pg_authid, where the actual information is stored now. In any case, again they are not tables/views you directly modify. > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sun, 4 Sep 2016, Adrian Klaver wrote: > You don't it directly. That information is supplied by Postgres when you > do CREATE or ALTER ROLE. The -W switch just does that for the > superuser(postgres in your case) when you initdb a new cluster. Adrian, OK. That makes sense. > Sorry, old habits. pg_user is a version of the pg_shadow view that blanks > out the actual password. pg_shadow is a view over the table pg_authid, > where the actual information is stored now. In any case, again they are > not tables/views you directly modify. Good. Then I won't spend time with them. So, given my single-user situation do you think that I should ALTER ROLE to add my password? Adding it to ~/.pgpass did nothing positive when I changed auth method to md5; my attempt to open a database failed because that password was rejected. Strange ... to me. Rich
On 09/04/2016 12:10 PM, Rich Shepard wrote: > On Sun, 4 Sep 2016, Adrian Klaver wrote: > >> You don't it directly. That information is supplied by Postgres when you >> do CREATE or ALTER ROLE. The -W switch just does that for the >> superuser(postgres in your case) when you initdb a new cluster. > > Adrian, > > OK. That makes sense. > >> Sorry, old habits. pg_user is a version of the pg_shadow view that blanks >> out the actual password. pg_shadow is a view over the table pg_authid, >> where the actual information is stored now. In any case, again they are >> not tables/views you directly modify. > > Good. Then I won't spend time with them. > > So, given my single-user situation do you think that I should ALTER ROLE > to add my password? Adding it to ~/.pgpass did nothing positive when I > changed auth method to md5; my attempt to open a database failed because > that password was rejected. Strange ... to me. Well first, if you are going to use trust as your auth method then specifying a password is moot exercise. Second, not sure where you are in the process, but any time you change the pg_hba.conf file you will need to give Postgres a reload signal to get it to recognize the changes. Again not sure how you are signalling Postgres but if you are using pg_ctl https://www.postgresql.org/docs/9.5/static/app-pg-ctl.html then something like: pg_ctl reload -D path_to_your_datadir as OS user postgres. Third, .pgpass should hold information that already exists in the database system tables. It is not a mechanism for entering that information into the database. So yes, you will need to use ALTER ROLE to create the password inside Postgres. > > Rich > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sun, 4 Sep 2016, Adrian Klaver wrote: > Well first, if you are going to use trust as your auth method then specifying > a password is moot exercise. I tried adding an explicit password to ~/.pgpass with md5 as the auth method, but that didn't work so I went back to trust. That's served well for 19 years. :-) > Second, not sure where you are in the process, but any time you change the > pg_hba.conf file you will need to give Postgres a reload signal to get it > to recognize the changes. Again not sure how you are signalling Postgres > but if you are using pg_ctl > https://www.postgresql.org/docs/9.5/static/app-pg-ctl.html > then something like: > > pg_ctl reload -D path_to_your_datadir > > as OS user postgres. Good to know. I use pg_ctl stop and start with the path on both command lines. > Third, .pgpass should hold information that already exists in the database > system tables. It is not a mechanism for entering that information into > the database. So yes, you will need to use ALTER ROLE to create the > password inside Postgres. OK. I'll try that for the learning experience. So much to learn and so little time ... Thanks, Rich
On 09/04/2016 12:10 PM, Rich Shepard wrote: > On Sun, 4 Sep 2016, Adrian Klaver wrote: > >> You don't it directly. That information is supplied by Postgres when you >> do CREATE or ALTER ROLE. The -W switch just does that for the >> superuser(postgres in your case) when you initdb a new cluster. > > Adrian, > > OK. That makes sense. > >> Sorry, old habits. pg_user is a version of the pg_shadow view that blanks >> out the actual password. pg_shadow is a view over the table pg_authid, >> where the actual information is stored now. In any case, again they are >> not tables/views you directly modify. > > Good. Then I won't spend time with them. > > So, given my single-user situation do you think that I should ALTER ROLE > to add my password? Adding it to ~/.pgpass did nothing positive when I > changed auth method to md5; my attempt to open a database failed because > that password was rejected. Strange ... to me. Another thing that came to mind is compatibility with existing applications/clients. You say you have been running using trust and I am betting your client connection parameters reflect that. Now for connections methods that can 'see' the .pgpass file and use libpq as the their underlying Postgres library then things should work. Otherwise your applications may not be able to connect until you supply the correct password in some manner. > > Rich > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 09/04/2016 12:42 PM, Rich Shepard wrote: > On Sun, 4 Sep 2016, Adrian Klaver wrote: > >> Well first, if you are going to use trust as your auth method then >> specifying a password is moot exercise. > > I tried adding an explicit password to ~/.pgpass with md5 as the auth > method, but that didn't work so I went back to trust. That's served well > for > 19 years. :-) auth is not something you enter in .pgpass. The format for the file is spelled out here: https://www.postgresql.org/docs/9.5/static/libpq-pgpass.html > >> Second, not sure where you are in the process, but any time you change >> the >> pg_hba.conf file you will need to give Postgres a reload signal to get it >> to recognize the changes. Again not sure how you are signalling Postgres >> but if you are using pg_ctl >> https://www.postgresql.org/docs/9.5/static/app-pg-ctl.html >> then something like: >> >> pg_ctl reload -D path_to_your_datadir >> >> as OS user postgres. > > Good to know. I use pg_ctl stop and start with the path on both command > lines. > >> Third, .pgpass should hold information that already exists in the >> database >> system tables. It is not a mechanism for entering that information into >> the database. So yes, you will need to use ALTER ROLE to create the >> password inside Postgres. > > OK. I'll try that for the learning experience. > > So much to learn and so little time ... > > Thanks, > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sun, 4 Sep 2016, Adrian Klaver wrote: > Another thing that came to mind is compatibility with existing > applications/clients. You say you have been running using trust and I am > betting your client connection parameters reflect that. Now for > connections methods that can 'see' the .pgpass file and use libpq as the > their underlying Postgres library then things should work. Otherwise your > applications may not be able to connect until you supply the correct > password in some manner. That's a concern. My business financial software uses postgres as the backend and a browser UI and I enter my username and password on the login page. It works with auth method trust. I've no idea exactly how it connects to the database. Since it ain't broke I won't futz with it and possibly break it. I've other things with higher priorities. Rich
On 09/04/2016 12:55 PM, Rich Shepard wrote: > On Sun, 4 Sep 2016, Adrian Klaver wrote: > >> Another thing that came to mind is compatibility with existing >> applications/clients. You say you have been running using trust and I am >> betting your client connection parameters reflect that. Now for >> connections methods that can 'see' the .pgpass file and use libpq as the >> their underlying Postgres library then things should work. Otherwise your >> applications may not be able to connect until you supply the correct >> password in some manner. > > That's a concern. My business financial software uses postgres as the > backend and a browser UI and I enter my username and password on the login > page. It works with auth method trust. I've no idea exactly how it connects > to the database. Since it ain't broke I won't futz with it and possibly It is using its own authentication method and tables, independent of Postgres, that restrict access to its own data, not the cluster as whole. > break it. I've other things with higher priorities. > > Rich > > > -- Adrian Klaver adrian.klaver@aklaver.com