Re: [Gnumed-devel] Upgrade from gnumed v1.3.7 to 1.4.

[Sebastian Hilbert]

I am testig quite a few things at the moment with regards to Windows platform.

So far we have established that PG 8.4 and PG 9.2 can be installed side by 
side and run just fine (on different ports).

Using pg_dumpall and psql to restore does not work for me due to various 
problems.

One of the problem seems to be that the default transaction mode seems to be 
read-only. For that reason all sql queries fail.

When running pg_dumpall and psql in one line seperated by a pipe I get all 
kinds to \n relate errors.

One major showstopper seems to be if one uses pg_dumpall from the older 
postgres (PG 8.4). When using pg_dumpall from PG 9.2 the errors seem to go 
away.

To overcome the read-only transaction problem I had to insert 
default_transaction_read_only = off for every database in the dump

 I  have used a sed one-liner as this one:
sed '/connect gnumed_v/a SET default_transaction_read_only = off;' pg842.sql > 
pg842fixed.sql

Restoring the resulting pg842fixed.sql with psql (as normal user) works just 
fine.

The resulting database in PG 9.2 (e.g. v18) seems to have a different hash then 
the one in PG 8.4.

I have used psql commadn from gm-backup-schema to produce a dump of the v18 db 
in PG 8.4 and PG 9.2 . I used pg_dump from PG 9.2 in both cases. The resulting 
dumps differ by roughly 10MB in size.

I have produce a unified diff on the two dumps which is 90MB. Much of this is 
due to different line endings for equivalent lines despite using the identical 
pg_dump. Both original databases are utf-8 but one has locale German_Germany 
and one has locale Englisch.English.

I then used dos2unix on the file with the dos line endings (^M) and diffed 
again. It shows that differences are mostly due to lines like TOC entry xyz 
where xyz is an integer and this integer seems to be different in the PG 8.4 
and 9.5 database for the same line. Apart from that it seems that sql syntax 
is slightly different in the dumps but other then that I did not see an 
apparent differences.

I don't know if this is enough to explain the different database schema hashes 
which make gnumed 1.3.8 refuse to start off a v18 database stored in PG 9.2.

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

I then apptempted to make use of pg_upgrade (the one from PG 9.2). This 
another beast.

To make a long story short (no write access, wrong user ...) this is what I 
had to do but still have a problem with read-only connections

set md5 --> trust in pg_hba.conf for both PG 8.4 and PG 9.2
set default_transaction_read_only = off in postgresql.conf for both PG 8.4 and 
PG 9.2

RUNAS /USER:postgres "CMD.EXE"
SET PATH=%PATH%;C:\Programme\PostgreSQL\9.2\bin;

cd c:\windows\temp
C:/Programme/PostgreSQL/9.2/bin/pg_upgrade.exe --old-datadir 
"C:/Programme/PostgresPlus/8.4SS/data" --new-datadir 
"C:/Programme/PostgreSQL/9.2/data" --old-bindir 
"C:/Programme/PostgresPlus/8.4SS/bin" --new-bindir 
"C:/Programme/PostgreSQL/9.2/bin" -u postgres

While it seems to run half way and set default_transaction_read_only is set to 
off in postgresql.conf I get : 

Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 ok
Checking cluster versions                                   ok
SQL command failed
CREATE TEMPORARY TABLE info_rels (reloid) AS SELECT c.oid FROM 
pg_catalog.pg_cla
ss c JOIN pg_catalog.pg_namespace n        ON c.relnamespace = n.oid WHERE 
relki
nd IN ('r', 'i', 'S') AND   ((n.nspname !~ '^pg_temp_' AND     n.nspname !~ 
'^pg
_toast_temp_' AND     n.nspname NOT IN ('pg_catalog', 'information_schema',
                                        'binary_upgrade', 'pg_toast') AND
  c.oid >= 16384)   OR (n.nspname = 'pg_catalog' AND     relname IN 
('pg_largeob
ject', 'pg_largeobject_loid_pn_index') ));
ERROR:  transaction is read-only

Failure, exiting

--> Sucks big time. Unless I can find a safe way to do the upgrade I cannot 
recommend anyone to attempt the upgrade on Windows.

PS: It might be possible to bootstrap a database v18 on PG 9.2 directly and 
the dump and restore data only.

Sebastian