Fwd: pg_upgrade

[Ian Harding <harding.ian@xxxxxxxxx>]

Ack!  Sorry.  Bad list etiquette in so many ways...

---------- Forwarded message ----------
From: Ian Harding <harding.ian@xxxxxxxxx>
Date: Sun, Mar 3, 2013 at 8:26 PM
Subject: Re: pg_upgrade
To: Bruce Momjian <bruce@xxxxxxxxxx>

It doesn't seem to though.  Here's what I get when I leave it alone (no changes to postgresql.conf, no environment variables set)

postgres@server:~$ echo $PGPORT

postgres@server:~$ echo $PGOPTIONS

postgres@server:~$ /opt/postgres/9.2/bin/pg_upgrade -d /var/lib/postgresql/8.4/main/ -D /srv/data/ -b /usr/lib/postgresql/8.4/bin/ -B /opt/postgres/9.2/bin/
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 ok
Checking cluster versions                                   ok

connection to database failed: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.50432"?


could not connect to old postmaster started with the command:

"/usr/lib/postgresql/8.4/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/8.4/main/" -o "-p 50432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000 -c listen_addresses='' -c unix_socket_permissions=0700" start

Failure, exiting

When I try to start postgresql with those options it doesn't appear to start because -w makes it wait, where wait means try "psql -l" which doesn't work because of the changed port I assume.

postgres@server:~$ psql -l
psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

postgres@server:~$ psql -p 50432 -l
                               List of databases
    Name    |  Owner   | Encoding  | Collation | Ctype |   Access privileges  
------------+----------+-----------+-----------+-------+-----------------------
 fordparts  | iharding | SQL_ASCII | C         | C     |
 gmparts    | iharding | SQL_ASCII | C         | C     |
 leaguemgmt | iharding | SQL_ASCII | C         | C     |
 postgres   | postgres | SQL_ASCII | C         | C     |
 template0  | postgres | SQL_ASCII | C         | C     | =c/postgres
                                                       : postgres=CTc/postgres
 template1  | postgres | SQL_ASCII | C         | C     | =c/postgres
                                                       : postgres=CTc/postgres
(6 rows)

Although it actually did start, as in the case above.  However, the pg_upgrade message is looking for /tmp/.s.PGSQL.50432 which isn't going to work.

I'm puzzled.  I've tried PGPORT and different combinations of PGOPTIONS to no beneficial effect.

On Sun, Mar 3, 2013 at 8:16 PM, Bruce Momjian <bruce@xxxxxxxxxx> wrote:

On Sun, Mar  3, 2013 at 07:58:03PM -0800, Ian Harding wrote:
> Hi!  Sorry for the delay.  Just getting back to it.  Looks like postgres was
> compiled with a funky socket_directory so even if I change it in the
> postgresql.conf things break. I've tried tricking it with PGOPTIONS=
> "socket_directory = '/var/run/postgresql'" but that didn't help.

You need the -o and -O options which were added to pg_upgrade in 9.2.
Actually, I think we handle that in 9.2 automatically by checking the
server settings, but I am not sure.

Actually, if the client and server libraries were compiled with the same
default, and you are using the default, it should work fine.

---------------------------------------------------------------------------

>
>
>
> On Fri, Feb 15, 2013 at 5:54 PM, Bruce Momjian <bruce@xxxxxxxxxx> wrote:
>
>     On Fri, Feb 15, 2013 at 10:36:25AM -0800, Ian Harding wrote:
>     > Maybe this is it.  8.4 pg_ctl docs say it uses "psql -l" to see if it's
>     > finished when you use -w.  It also says
>     >  
>     > PGPORT
>     >
>     >     Default port for psql (used by the -w option).
>     >
>     >     And since pg_upgrade uses a funky port, it might miss unless the
>     PGPORT
>     >     environment variable is set to match.
>     >      
>     >     I'll try that tonight.
>
>     Yes, you are getting close to the answer.  ;-)  The problem is that
>     Postgres doesn'isn't checking the right port number or socket location
>     or something else.  This was all improved in Postgres 9.1:
>
>             The wait mode is now significantly more robust.  It will not get
>             confused by non-default postmaster port numbers, non-default
>             Unix-domain socket locations, permission problems, or stale
>             postmaster lock files.
>
>     I am guessing there is something non-standard about your old cluster,
>     and 8.4's pg_ctl -w can't handle it.  Tell me what is non-standard and I
>     can help further.  Another idea is to make the old cluster use defaults
>     for everything and do the upgrade.
>
>     --
>       Bruce Momjian  <bruce@xxxxxxxxxx>        http://momjian.us
>       EnterpriseDB                             http://enterprisedb.com
>
>       + It's impossible for everything to be true. +
>
>

--
  Bruce Momjian  <bruce@xxxxxxxxxx>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +