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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 processI 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 itNot 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
+-----------------------+
| ____ ______ ___ |
| / )/ \/ \ |
| ( / __ _\ ) |
| \ (/ o) ( o) ) |
| \_ (_ ) \ ) _/ |
| \ /\_/ \)/ |
| \/ <//| |\\> |
| _| | |
| \|_/ |
| |
| PostgreSQL 1996-2016 |
| 20 Years of Success |
| |
+-----------------------+
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Import Notes
Reply to msg id not found: 61091b76-ca32-c603-4ed1-96e077d7d7bd@swisspug.orgReference msg id not found: 61091b76-ca32-c603-4ed1-96e077d7d7bd@swisspug.org | Resolved by subject fallback
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.htmlto 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Import Notes
Reply to msg id not found: alpine.LNX.2.11.1609040622570.5431@localhost
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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Import Notes
Reply to msg id not found: D474D02F-9C39-4BD7-BDE2-53BD686EC05B@swisspug.org
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general