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

[Karsten Hilbert]

On Sun, Nov 17, 2013 at 02:19:29PM +0100, Hilbert, Sebastian wrote:

> 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.

This is absolutely the procedure recommended by the PostgreSQL team.

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

This is quite the same bug/deficiency/limitation/quirk as with
pg_upgrade - except now in pg_dump/pg_restore/psql:

The pg_dump should IMO detect whether the dumped DB is readonly
by default and act accordingly: set it to readwrite at the
beginning of the dump and reset it to readonly at the very end.

Perhaps this should be reported to PostgreSQL as well.

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

If so it typically results from sort orders being different on
different clusters despite taking care not to have any. To help
with debugging such things we've got:

        gm-fingerprint_db <DATABASE> <GM-DBO PASSWORD> --with-dump

> 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.

It is not. We'd need to find out why the hash is different.

Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346