Thread: pg_upgrade
When I run pg_upgrade, it tries to start the old cluster with the -w flag, which waits a while and declares failure, even though it starts the server. If I start/stop without -w everything is great. Can I tell pg_upgrade not to use that flag, or is there a reason it is not working that I should look into? version ------------------------------------------------------------------------------------------------------ PostgreSQL 8.4.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit
2013/2/15 Ian Harding <harding.ian@gmail.com> > When I run pg_upgrade, it tries to start the old cluster with the -w flag, > which waits a while and declares failure, even though it starts the > server. If I start/stop without -w everything is great. > > Can I tell pg_upgrade not to use that flag, or is there a reason it is not > working that I should look into? > > > version > > ------------------------------------------------------------------------------------------------------ > PostgreSQL 8.4.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real > (Debian 4.4.5-8) 4.4.5, 64-bit > > Which PostgreSQL version is the old cluster, and which version is the new cluster? What options are you supplying to pg_upgrade, and what output are you getting? Ian Barwick
Old is 8.4, new is 9.2. I am not supplying an but the minimum options and -=
-check succeeds. My pg_ctl fails when run by hand with -w (although the dat=
abase does start) so I know that's the issue.
On Feb 14, 2013, at 9:50 PM, Ian Lawrence Barwick <barwick@gmail.com> wrote:=
> 2013/2/15 Ian Harding <harding.ian@gmail.com>
>> When I run pg_upgrade, it tries to start the old cluster with the -w flag=
, which waits a while and declares failure, even though it starts the server=
. If I start/stop without -w everything is great. =20
>>=20
>> Can I tell pg_upgrade not to use that flag, or is there a reason it is no=
t working that I should look into?
>>=20
>> version =
=20
>> -------------------------------------------------------------------------=
-----------------------------
>> PostgreSQL 8.4.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (D=
ebian 4.4.5-8) 4.4.5, 64-bit
>>=20
>=20
>=20
> Which PostgreSQL version is the old cluster, and which version is the new c=
luster? What options are you supplying to pg_upgrade, and what output are yo=
u getting?=20
>=20
> Ian Barwick
2013/2/15 Ian Harding <harding.ian@gmail.com> > > > On Feb 14, 2013, at 9:50 PM, Ian Lawrence Barwick <barwick@gmail.com> wrote: > > 2013/2/15 Ian Harding <harding.ian@gmail.com> >> >> When I run pg_upgrade, it tries to start the old cluster with the -w flag, which waits a while and declares failure, eventhough it starts the server. If I start/stop without -w everything is great. >> >> Can I tell pg_upgrade not to use that flag, or is there a reason it is not working that I should look into? >> >> version >> ------------------------------------------------------------------------------------------------------ >> PostgreSQL 8.4.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit >> > > Which PostgreSQL version is the old cluster, and which version is the new cluster? What options are you supplying to pg_upgrade,and what output are you getting? > > Old is 8.4, new is 9.2. I am not supplying an but the minimum options and --check succeeds. My > pg_ctl fails when run by hand with -w (although the database does start) so I know that's the issue. Maybe try running pg_upgrade with the --retain option and check pg_upgrade_server.log for clues? Ian Barwick
I don't think it would get any further... It fails and --retain says "Retain sql and log files after success" I can look at that log file and all it indicates is failure to start the server. Maybe I should rephrase the question: pg_ctl returns failure, even though it starts the server, when run with the -w flag. On Thu, Feb 14, 2013 at 10:41 PM, Ian Lawrence Barwick <barwick@gmail.com>wrote: > 2013/2/15 Ian Harding <harding.ian@gmail.com> > > > > > > On Feb 14, 2013, at 9:50 PM, Ian Lawrence Barwick <barwick@gmail.com> > wrote: > > > > 2013/2/15 Ian Harding <harding.ian@gmail.com> > >> > >> When I run pg_upgrade, it tries to start the old cluster with the -w > flag, which waits a while and declares failure, even though it starts the > server. If I start/stop without -w everything is great. > >> > >> Can I tell pg_upgrade not to use that flag, or is there a reason it is > not working that I should look into? > >> > >> version > >> > ------------------------------------------------------------------------------------------------------ > >> PostgreSQL 8.4.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real > (Debian 4.4.5-8) 4.4.5, 64-bit > >> > > > > Which PostgreSQL version is the old cluster, and which version is the > new cluster? What options are you supplying to pg_upgrade, and what output > are you getting? > > > > > Old is 8.4, new is 9.2. I am not supplying an but the minimum options > and --check succeeds. My > > pg_ctl fails when run by hand with -w (although the database does start) > so I know that's the issue. > > Maybe try running pg_upgrade with the --retain option and check > pg_upgrade_server.log for clues? > > > Ian Barwick >
On 02/15/2013 04:54 AM, Ian Harding wrote: > I don't think it would get any further... It fails and --retain says > "Retain sql and log files after success" I can look at that log file > and all it indicates is failure to start the server. What is the exact error message from the log? > > Maybe I should rephrase the question: pg_ctl returns failure, even > though it starts the server, when run with the -w flag. > > -- Adrian Klaver adrian.klaver@gmail.com
On Fri, Feb 15, 2013 at 4:54 AM, Ian Harding <harding.ian@gmail.com> wrote: > I don't think it would get any further... It fails and --retain says > "Retain sql and log files after success" I can look at that log file and > all it indicates is failure to start the server. > > Maybe I should rephrase the question: pg_ctl returns failure, even though > it starts the server, when run with the -w flag. How long does the server take to start? If it takes the server more than 1 minute to start, then what you describe is expected. Cheers, Jeff
----------------------------------------------------------------- pg_upgrade run on Fri Feb 15 05:09:34 2013 ----------------------------------------------------------------- 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 >> "pg_upgrade_server.log" 2>&1 waiting for server to start...............................................................could not start server The funny thing is, the server starts instantly. On Fri, Feb 15, 2013 at 9:19 AM, Jeff Janes <jeff.janes@gmail.com> wrote: > On Fri, Feb 15, 2013 at 4:54 AM, Ian Harding <harding.ian@gmail.com> > wrote: > > I don't think it would get any further... It fails and --retain says > > "Retain sql and log files after success" I can look at that log file and > > all it indicates is failure to start the server. > > > > Maybe I should rephrase the question: pg_ctl returns failure, even > though > > it starts the server, when run with the -w flag. > > How long does the server take to start? > > If it takes the server more than 1 minute to start, then what you > describe is expected. > > Cheers, > > Jeff >
This is interesting, although I'm not sure it's relevant. From pg_ctl source. 00477 write_stderr<http://doxygen.postgresql.org/bin_2psql_2common_8c.html#aeca3eef79cc2b37affc8d760c33e1de8> (_<http://doxygen.postgresql.org/elog_8c.html#af20b8d139279b360b0fdeae71f8f43bc> ("\n%s: -w option is not supported when starting a pre-9.1 server\n"), On Fri, Feb 15, 2013 at 9:45 AM, Ian Harding <harding.ian@gmail.com> wrote: > > ----------------------------------------------------------------- > pg_upgrade run on Fri Feb 15 05:09:34 2013 > ----------------------------------------------------------------- > > 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 >> > "pg_upgrade_server.log" 2>&1 > waiting for server to > start...............................................................could > not start server > > > The funny thing is, the server starts instantly. > > > On Fri, Feb 15, 2013 at 9:19 AM, Jeff Janes <jeff.janes@gmail.com> wrote: > >> On Fri, Feb 15, 2013 at 4:54 AM, Ian Harding <harding.ian@gmail.com> >> wrote: >> > I don't think it would get any further... It fails and --retain says >> > "Retain sql and log files after success" I can look at that log file >> and >> > all it indicates is failure to start the server. >> > >> > Maybe I should rephrase the question: pg_ctl returns failure, even >> though >> > it starts the server, when run with the -w flag. >> >> How long does the server take to start? >> >> If it takes the server more than 1 minute to start, then what you >> describe is expected. >> >> Cheers, >> >> Jeff >> > >
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<http://www.postgresql.org/docs/8.4/static/app-psql.html>(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. On Fri, Feb 15, 2013 at 10:09 AM, Ian Harding <harding.ian@gmail.com> wrote: > This is interesting, although I'm not sure it's relevant. From pg_ctl > source. > > > 00477 write_stderr<http://doxygen.postgresql.org/bin_2psql_2common_8c.html#aeca3eef79cc2b37affc8d760c33e1de8> > (_<http://doxygen.postgresql.org/elog_8c.html#af20b8d139279b360b0fdeae71f8f43bc> > ("\n%s: -w option is not supported when starting a pre-9.1 server\n"), > > > On Fri, Feb 15, 2013 at 9:45 AM, Ian Harding <harding.ian@gmail.com>wrote: > >> >> ----------------------------------------------------------------- >> pg_upgrade run on Fri Feb 15 05:09:34 2013 >> ----------------------------------------------------------------- >> >> 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 >> >> "pg_upgrade_server.log" 2>&1 >> waiting for server to >> start...............................................................could >> not start server >> >> >> The funny thing is, the server starts instantly. >> >> >> On Fri, Feb 15, 2013 at 9:19 AM, Jeff Janes <jeff.janes@gmail.com> wrote: >> >>> On Fri, Feb 15, 2013 at 4:54 AM, Ian Harding <harding.ian@gmail.com> >>> wrote: >>> > I don't think it would get any further... It fails and --retain says >>> > "Retain sql and log files after success" I can look at that log file >>> and >>> > all it indicates is failure to start the server. >>> > >>> > Maybe I should rephrase the question: pg_ctl returns failure, even >>> though >>> > it starts the server, when run with the -w flag. >>> >>> How long does the server take to start? >>> >>> If it takes the server more than 1 minute to start, then what you >>> describe is expected. >>> >>> Cheers, >>> >>> Jeff >>> >> >> >
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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Ack! Sorry. Bad list etiquette in so many ways...
---------- Forwarded message ----------
From: Ian Harding <harding.ian@gmail.com>
Date: Sun, Mar 3, 2013 at 8:26 PM
Subject: Re: [GENERAL] pg_upgrade
To: Bruce Momjian <bruce@momjian.us>
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.
From: Ian Harding <harding.ian@gmail.com>
Date: Sun, Mar 3, 2013 at 8:26 PM
Subject: Re: [GENERAL] pg_upgrade
To: Bruce Momjian <bruce@momjian.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@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:~$ 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
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)
On Sun, Mar 3, 2013 at 8:16 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Sun, Mar 3, 2013 at 07:58:03PM -0800, Ian Harding wrote:You need the -o and -O options which were added to pg_upgrade in 9.2.
> 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.
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@momjian.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@momjian.us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
>
>
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +