Fwd: pg_upgrade

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

---------- Forwarded message ----------
From: Ian Harding <harding(dot)ian(at)gmail(dot)com>
Date: Sun, Mar 3, 2013 at 8:26 PM
Subject: Re: [GENERAL] pg_upgrade
To: Bruce Momjian <bruce(at)momjian(dot)us>

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(at)server:~$ echo $PGPORT

postgres(at)server:~$ echo $PGOPTIONS

postgres(at)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(at)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(at)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(at)momjian(dot)us> 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(at)momjian(dot)us> 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(at)momjian(dot)us> http://momjian.us
> > EnterpriseDB http://enterprisedb.com
> >
> > + It's impossible for everything to be true. +
> >
> >
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
>