Re: Testing 9.0beta3 and pg_upgrade

Hi,

I'm trying pg_upgrade on my Windows installation and I have two suggestions for the manual regarding pg_upgrade:

When specifying directories, pg_upgrade *requires* a forward slash as the path separator. This is (still) uncommon in the Windows world (although Windows does support it) and even though the example in the manual does show forward slashes, I think it would be a good idea to specifically mention the fact that it will *not* work with a backslash.

Actually the error message when you do so is a bit misleading as well ("You must identify the directory where the old cluster binaries reside") even though the paramter is there.

After I sorted that out I ran pg_upgrade and it failed somewhere in the middle:

------ snip

C:\etc\pg90-beta3>C:\etc\pg90-beta3\pgsql\bin\pg_upgrade.exe --user=postgres --old-datadir "c:/Daten/db/pgdata84/" -
-old-bindir "c:/Programme/PostgreSQL/8.4/bin/" --new-datadir "c:/etc/pg90-beta3/datadir/" --new-port=5434 --new-bind
ir "C:\etc\pg90-beta3\pgsql\bin"

Performing Consistency Checks
-----------------------------
Checking old data directory (c:/Daten/db/pgdata84) ok
Checking new data directory (c:/etc/pg90-beta3/datadir) ok
Checking for /contrib/isn with bigint-passing mismatch ok
Checking for large objects ok
Creating catalog dump ok
Checking for presence of required libraries ok

| If pg_upgrade fails after this point, you must
| re-initdb the new cluster before continuing.
| You will also need to remove the ".old" suffix
| from c:/Daten/db/pgdata84/global/pg_control.old.

Performing Migration
--------------------
Adding ".old" suffix to old global/pg_control ok
Analyzing all rows in the new cluster ok
Freezing all rows on the new cluster ok
Deleting new commit clogs ok
Copying old commit clogs to new server 1 Datei(en) kopiert
ok
Setting next transaction id for new cluster ok
Resetting WAL archives ok
Setting frozenxid counters in new cluster ok
Creating databases in the new cluster psql:C:/etc/pg90-beta3/pg_upgrade_dump_globals.sql:29: ERROR: CREATE DATABASE c
annot run inside a transaction block

There were problems executing ""C:\etc\pg90-beta3\pgsql\bin/psql" --port 5434 --username "postgres" --set ON_ERROR_STOP=on -f "C:\etc\pg90-b
eta3/pg_upgrade_dump_globals.sql" --dbname template1 >> "nul""

------ end of console output --------

The "cannot run inside a transaction block" rang a bell, and once I removed "\set AUTOCOMMIT off" from my psqlrc.conf, pg_upgrade went through without problems.

I would suggest to either manually change the autocommit mode from within pg_upgrade or to add a note in the manual to disable/remove this setting from psqlrc.conf before running pg_upgrade. Personally I think the first option would be the better one.

Regards
Thomas

On 13/07/10 05:29, Thomas Kellerer wrote:

> I would suggest to either manually change the autocommit mode from
> within pg_upgrade or to add a note in the manual to disable/remove this
> setting from psqlrc.conf before running pg_upgrade. Personally I think
> the first option would be the better one.

Should pg_upgrade be reading psqlrc at all? There are bound to be all
sorts of exciting issues that psqlrc settings can create.

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/

Craig Ringer, 13.07.2010 05:11:
> On 13/07/10 05:29, Thomas Kellerer wrote:
>
>> I would suggest to either manually change the autocommit mode from
>> within pg_upgrade or to add a note in the manual to disable/remove this
>> setting from psqlrc.conf before running pg_upgrade. Personally I think
>> the first option would be the better one.
>
> Should pg_upgrade be reading psqlrc at all? There are bound to be all
> sorts of exciting issues that psqlrc settings can create.
>
I interpret the error message such that pg_upgrade _calls_ (i.e spawns) psql to run the CREATE DATABASE command. If that is true, probably the easiest solution would be to run psql using the -X switch.

Regards
Thomas

Thomas Kellerer, 12.07.2010 23:29:
> Hi,
>
> I'm trying pg_upgrade on my Windows installation and I have two
> suggestions for the manual regarding pg_upgrade:
>
> When specifying directories, pg_upgrade *requires* a forward slash as
> the path separator. This is (still) uncommon in the Windows world
> (although Windows does support it) and even though the example in the
> manual does show forward slashes, I think it would be a good idea to
> specifically mention the fact that it will *not* work with a backslash.

There is another misleading error message.

When the old *bin*dir is not specified correctly, pg_upgrade claims the old *data*dir does not exist

Something like:

pg_upgrade --check --old-bindir="c:/Program Files/PostgreSQL/8.4" ....

(note the missing /bin part)

will cause the following output:

'c:/Program' is not recognized as an internal or external command,
operable program or batch file.
Performing Consistency Checks
-----------------------------
Checking old data directory (c:/Daten/db/pgdata84)
check for postgres failed - No such file or directory

It took me a while to find out that the bindir was wrong, not the datadir. The "c:/Program' is not recognized as an.." made it even more confusing.

Regards
Thomas

Thomas Kellerer, 12.07.2010 23:29:
> Hi,
>
> I'm trying pg_upgrade on my Windows installation and I have two
> suggestions for the manual regarding pg_upgrade:
>

I found another problem and I'm not sure if this is a bug or a user error :)

My batch file to start pg_upgrade looks like this:

%~dp0server\bin\pg_upgrade ^
--check ^
--old-bindir="c:/Program Files/PostgreSQL/8.4/bin" ^
--new-bindir="c:/etc/Postgres9.0-beta3/server/bin" ^
--old-datadir="c:/Daten/db/pgdata84" ^
--new-datadir="c:/etc/Postgres9.0-beta3/datadir" ^
--user=postgres ^
--logfile=migrate.log ^
--verbose

with these settings pg_upgrade fails to start the old server.

On the console I can see:

Checking new data directory (c:/etc/Postgres9.0-beta3/datadir)ok
""c:/Program Files/PostgreSQL/8.4/bin/pg_ctl" -l "migrate.log" -D "c:/Daten/db/pgdata84" -o "-p 5432 -c autovacuum=off -c autovacuum_freeze_max_age=20
00000000" start >> "nul" 2>&1"
Trying to start old server ...........
Unable to start old postmaster with the command: ""c:/Program Files/PostgreSQL/8.4/bin/pg_ctl" -l "migrate.log" -D "c:/Daten/db/pgdata84" -o "-p 5432
-c autovacuum=off -c autovacuum_freeze_max_age=2000000000" start >> "nul" 2>&1"
Perhaps pg_hba.conf was not set to "trust".

Now when I manually copy and paste the logged command, the server starts up fine and I can verify with ProcessExplorer that pg_upgrade indeed does not spawn a postgres.exe (or pg_ctl.exe)

As I have done one successful migration already, and the only difference was the logfile, I removed the --logfile switch from the batch file and then pg_upgrade ran without problems.

In the commandline starting the old server -l "migrate.log" is replaced with -l "nul"

I _think_ the reason for this is that pg_upgrade locks migrate.log and then postgres.exe cannot write to the file and thus fails to start.
When I manually run the command, pg_upgrade is not locking migrate.log and therefor the server starts (and thus the confusion that the same command works from the commandline but not when pg_upgrade runs it)

Regards
Thomas

P.S.: pg_upgrade --help says: "Report bugs to <pg-migrator-general(at)lists(dot)pgfoundry(dot)org>"
Shouldn't that be changed as well?

Thomas Kellerer wrote:
> Hi,
>
> I'm trying pg_upgrade on my Windows installation and I have two
> suggestions for the manual regarding pg_upgrade:
>
> When specifying directories, pg_upgrade *requires* a forward slash as
> the path separator. This is (still) uncommon in the Windows world
> (although Windows does support it) and even though the example in the
> manual does show forward slashes, I think it would be a good idea to
> specifically mention the fact that it will *not* work with a backslash.

Are you sure this is not the behavior of all Postgres commands? For
example, if you use 'psql -f filename dbname', does 'filename' have the
same behavior as the pg_upgrade directory specifications?

> The "cannot run inside a transaction block" rang a bell, and once I
> removed "\set AUTOCOMMIT off" from my psqlrc.conf, pg_upgrade went
> through without problems.
>
> I would suggest to either manually change the autocommit mode from
> within pg_upgrade or to add a note in the manual to disable/remove this
> setting from psqlrc.conf before running pg_upgrade. Personally I think
> the first option would be the better one.

I have applied the attached patch so .psqlrc is no longer read by psql.
That should fix this problem. I can't imagine anything pg_upgrade needs
in .pgsqlrc.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +

Thomas Kellerer wrote:
> Craig Ringer, 13.07.2010 05:11:
> > On 13/07/10 05:29, Thomas Kellerer wrote:
> >
> >> I would suggest to either manually change the autocommit mode from
> >> within pg_upgrade or to add a note in the manual to disable/remove this
> >> setting from psqlrc.conf before running pg_upgrade. Personally I think
> >> the first option would be the better one.
> >
> > Should pg_upgrade be reading psqlrc at all? There are bound to be all
> > sorts of exciting issues that psqlrc settings can create.
> >
> I interpret the error message such that pg_upgrade _calls_ (i.e spawns)
> psql to run the CREATE DATABASE command. If that is true, probably the
> easiest solution would be to run psql using the -X switch.

Yes, this is the solution I used.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +

Thomas Kellerer wrote:
> Thomas Kellerer, 12.07.2010 23:29:
> > Hi,
> >
> > I'm trying pg_upgrade on my Windows installation and I have two
> > suggestions for the manual regarding pg_upgrade:
> >
> > When specifying directories, pg_upgrade *requires* a forward slash as
> > the path separator. This is (still) uncommon in the Windows world
> > (although Windows does support it) and even though the example in the
> > manual does show forward slashes, I think it would be a good idea to
> > specifically mention the fact that it will *not* work with a backslash.
>
> There is another misleading error message.
>
> When the old *bin*dir is not specified correctly, pg_upgrade claims the old *data*dir does not exist
>
> Something like:
>
> pg_upgrade --check --old-bindir="c:/Program Files/PostgreSQL/8.4" ....
>
> (note the missing /bin part)
>
> will cause the following output:
>
> 'c:/Program' is not recognized as an internal or external command,
> operable program or batch file.
> Performing Consistency Checks
> -----------------------------
> Checking old data directory (c:/Daten/db/pgdata84)
> check for postgres failed - No such file or directory
>
> It took me a while to find out that the bindir was wrong, not the datadir. The "c:/Program' is not recognized as an.." made it even more confusing.

OK, I have modified pg_upgrade with the attachd patch that reports /bin
and /data directory tests separately. Thanks for the report.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +

Thomas Kellerer wrote:
> Checking new data directory (c:/etc/Postgres9.0-beta3/datadir)ok
> ""c:/Program Files/PostgreSQL/8.4/bin/pg_ctl" -l "migrate.log" -D
> "c:/Daten/db/pgdata84" -o "-p 5432 -c autovacuum=off -c
> autovacuum_freeze_max_age=20 00000000" start >> "nul" 2>&1" Trying to
> start old server ...........
> Unable to start old postmaster with the command: ""c:/Program
> Files/PostgreSQL/8.4/bin/pg_ctl" -l "migrate.log" -D
> "c:/Daten/db/pgdata84" -o "-p 5432 -c autovacuum=off -c
> autovacuum_freeze_max_age=2000000000" start >> "nul" 2>&1" Perhaps
> pg_hba.conf was not set to "trust".
>
> Now when I manually copy and paste the logged command, the server starts
> up fine and I can verify with ProcessExplorer that pg_upgrade indeed
> does not spawn a postgres.exe (or pg_ctl.exe)
>
> As I have done one successful migration already, and the only difference
> was the logfile, I removed the --logfile switch from the batch file
> and then pg_upgrade ran without problems.
>
> In the commandline starting the old server -l "migrate.log" is replaced
> with -l "nul"

> I _think_ the reason for this is that pg_upgrade locks migrate.log and
> then postgres.exe cannot write to the file and thus fails to start.
> When I manually run the command, pg_upgrade is not locking migrate.log
> and therefor the server starts (and thus the confusion that the same
> command works from the commandline but not when pg_upgrade runs it)

Your diagnosis is correct. I thought we had fixed this by having pg_ctl
output go to NUL, but it turns out even server logs can't go to nul
either if we are already logging to that file. Attached patch removes
logging of pg_ctl and server output on win32.

> P.S.: pg_upgrade --help says: "Report bugs to
> <pg-migrator-general(at)lists(dot)pgfoundry(dot)org>" Shouldn't that be changed
> as well?

Oops, fixed as well. Thanks.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +