Re: Running pg_upgrade under Debian

On Fri, 2015-04-17 at 16:16 -0700, Adrian Klaver wrote:
> On 04/17/2015 03:09 PM, rob stone wrote:
> > Hello,
> >
> > I'm trying to upgrade from 9.3 to 9.4 on my laptop and encountering this
> > error:-
> >
> >
> > postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ ./pg_upgrade
> > -b /usr/lib/postgresql/9.3/bin -B /usr/lib/postgresql/9.4/bin
> > -d /home/postgres/data93/userqueries
> > -D /home/postgres/data94/userqueries -U pguserqueries
> >
> > check for "/home/postgres/data93/userqueries/base" failed: No such file
> > or directory
> >
> > Failure, exiting
> > postgres@roblaptop:/usr/lib/postgresql/9.4/bin$
> >
> > postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ ./pg_upgrade --version
> > pg_upgrade (PostgreSQL) 9.4.1
> >
> >
> > I have two tablespaces defined for this data -- one to hold the tables
> > and another for the indices.
> >
> > There is no "base" file or directory.
>
> Guessing /home/postgres/data*/userqueries/ is the location of your
> tablespace, not the cluster directory.
>

Correct.

> The -d and -D need to point at the cluster directory, which will be
> something like :
>
> /var/lib/postgresql/9.3/main
>
> >
> > Do I just create a dummy directory named "base"?
>
> No, you need to point to the correct directory.
>
> See here for more information:
>
> http://www.postgresql.org/docs/9.4/interactive/pgupgrade.html
>

Actually, the wiki pages on upgrading gave me some more information,
particularly about specifying where to find postgresql.conf. I adopted
the symlink suggestion.

However, running the following still gives errors:-

postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ ./pg_upgrade
-b /usr/lib/postgresql/9.3/bin -B /usr/lib/postgresql/9.4/bin
-d /var/lib/postgresql/9.3/main -D /var/lib/postgresql/9.4/main -p 5432
-P 5433 -o ' -c listen_addresses=localhost ' -O ' -c
listen_addresses=localhost ' --check --verbose
Running in verbose mode
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
pg_control values:

First log segment after reset:        000000010000000000000002
pg_control version number:            937
Catalog version number:               201306121
Database system identifier:           6026352641161422830
Latest checkpoint's TimeLineID:       1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/684
Latest checkpoint's NextOID:          12036
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        674
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0
Current pg_control values:

pg_control version number:            942
Catalog version number:               201409291
Database system identifier:           6108663141646003887
Latest checkpoint's TimeLineID:       1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/719
Latest checkpoint's NextOID:          12142
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        675
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0


Values to be changed:

First log segment after reset:        000000010000000000000002
"/usr/lib/postgresql/9.3/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D
"/var/lib/postgresql/9.3/main" -o "-p 5432 -b  -c
listen_addresses=localhost  -c listen_addresses='' -c
unix_socket_permissions=0700 -c
unix_socket_directories='/usr/lib/postgresql/9.4/bin'" start >>
"pg_upgrade_server.log" 2>&1

*failure*
There were problems executing ""/usr/lib/postgresql/9.3/bin/pg_ctl" -w
-l "pg_upgrade_server.log" -D "/var/lib/postgresql/9.3/main" -o "-p 5432
-b  -c listen_addresses=localhost  -c listen_addresses='' -c
unix_socket_permissions=0700 -c
unix_socket_directories='/usr/lib/postgresql/9.4/bin'" start >>
"pg_upgrade_server.log" 2>&1"
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

pg_ctl failed to start the old server, or connection failed
Failure, exiting
postgres@roblaptop:/usr/lib/postgresql/9.4/bin$

----

The last few lines of pg_upgrade_server.log show:-


-----------------------------------------------------------------
  pg_upgrade run on Mon Apr 20 01:36:44 2015
-----------------------------------------------------------------

command: "/usr/lib/postgresql/9.3/bin/pg_ctl" -w -l
"pg_upgrade_server.log" -D "/var/lib/postgresql/9.3/main" -o "-p 5432 -b
-c listen_addresses=localhost  -c listen_addresses='' -c
unix_socket_permissions=0700 -c
unix_socket_directories='/usr/lib/postgresql/9.4/bin'" start >>
"pg_upgrade_server.log" 2>&1
waiting for server to start....2596 5533cb8c.a24 2015-04-20 01:36:44
AESTLOG:  redirecting log output to logging collector process
2596 5533cb8c.a24 2015-04-20 01:36:44 AESTHINT:  Future log output will
appear in directory "pg_log".
.... stopped waiting
pg_ctl: could not start server
Examine the log output.

------

So, on the command line I'm putting "listen_addresses=localhost" which
pg_upgrade picks up but the following option sets this to ''.
It is starting pg_ctl in the 9.3/bin directory but makes the socket
directory in 9.4/bin??

If pg_upgrade is actually reading the conf files it should not be
necessary to supply any of these options, as port numbers, PID file
locations, etc. are specified there.


I'm really confused. I don't believe that my set-up is so weird that
pg_upgrade won't work out of the box. Obviously I'm doing something that
is wrong.

Cheers,
Robert