Thread: changing the endianness of a database

changing the endianness of a database

From
"Chris Saldanha"
Date:
Hi,

We'd like to ship PostgreSQL as part of a product that runs on both PPC and
Intel Macs, but the database files are tied to the build settings and
endianness of the computer that the database was initialized on.

Is there any way to cause the server to modify the database files in-place
for endianness issues?  I know that a backup-then-restore process would fix
the data, but on Macs, many users use Apple's computer migration tools to
copy all their programs/data/users/etc.. to new Macs.

If the user moves from a PPC to an Intel Mac, for instance, the database
would be copied over, but the data would be for the old computer, and the
database won't start.  The backup/restore process is hard for end users,
since they don't understand it -- and they won't contact us until after the
migration is done, and often not until they've discarded the old computer.

It would be nice if there was a way to recover the data from the existing
database files.

I found this old thread on a related topic, and it seems that this cannot be
done...
http://archives.postgresql.org/pgsql-general/2008-01/msg00635.php

Thanks,
Chris

--
Chris Saldanha
Parliant Corporation
http://www.parliant.com/



Re: changing the endianness of a database

From
"Merlin Moncure"
Date:
On Mon, May 12, 2008 at 4:02 PM, Chris Saldanha
<postgres@cfs.parliant.com> wrote:
>  We'd like to ship PostgreSQL as part of a product that runs on both PPC and
>  Intel Macs, but the database files are tied to the build settings and
>  endianness of the computer that the database was initialized on.
>
>  Is there any way to cause the server to modify the database files in-place
>  for endianness issues?  I know that a backup-then-restore process would fix
>  the data, but on Macs, many users use Apple's computer migration tools to
>  copy all their programs/data/users/etc.. to new Macs.
>
>  If the user moves from a PPC to an Intel Mac, for instance, the database
>  would be copied over, but the data would be for the old computer, and the
>  database won't start.  The backup/restore process is hard for end users,
>  since they don't understand it -- and they won't contact us until after the
>  migration is done, and often not until they've discarded the old computer.
>
>  It would be nice if there was a way to recover the data from the existing
>  database files.
>
>  I found this old thread on a related topic, and it seems that this cannot be
>  done...
>  http://archives.postgresql.org/pgsql-general/2008-01/msg00635.php

Surely it's easier just to have your application dump on schedule and
add some front end GUI import feature to your app?  It looks like you
are maybe trying to solve the wrong problem...namely that it is too
difficult for your users to do backup/restore themselves.

merlin

Re: changing the endianness of a database

From
Craig Ringer
Date:
Merlin Moncure wrote:

> Surely it's easier just to have your application dump on schedule and
> add some front end GUI import feature to your app?  It looks like you
> are maybe trying to solve the wrong problem...namely that it is too
> difficult for your users to do backup/restore themselves.

Maybe it's an opportunity to introduce the users to backups.

Honestly, though, PostgreSQL doesn't seem to be designed for application
bundling and embedding, where the user never knows there's a database
engine present. I'm under the impression that there's no consideration
of what happens if you move from 32 to 64 bit hosts, big endian to
little endian, etc; it's expected that you'll dump and reload.

The ability to build a custom standalone backend that could read (and
only read) a "foreign" database structure would be pretty handy in this
sort of situation and other cases of poorly planned disaster recovery or
migration. Of course, it's much better to avoid such situations, but
where end users are involved they're always going to arise.

It's a pity the system cloning/migration tools don't have hooks for
applications to do pre-migration and post-migration tasks, so you could
just dump then initdb and reload.

--
Craig Ringer

Re: changing the endianness of a database

From
"Chris Saldanha"
Date:
> Maybe it's an opportunity to introduce the users to backups.

Yes, we do backups for the user, but the problem with Apple's migration is
that it happens not on a schedule that meshes with the backup schedule.  Our
applications have fairly frequently changing data.

> Honestly, though, PostgreSQL doesn't seem to be designed for application
> bundling and embedding, where the user never knows there's a database
> engine present. I'm under the impression that there's no consideration
> of what happens if you move from 32 to 64 bit hosts, big endian to
> little endian, etc; it's expected that you'll dump and reload.

Agreed that PGSQL isn't designed for embedding, but it's actually very close
to being supportable in that kind of use model.  The binaries and database
files are nicely contained, the server/libraries can be easily built as a
Universal (i.e. multi-architecture) binary for Macs, and the server is
actually quite small (26MB for a complete install as PPC/Intel binaries not
stripped) compared to commercial databases.

If the data files themselves were portable or convertible, then it would be
an almost perfect solution.

> It's a pity the system cloning/migration tools don't have hooks for
> applications to do pre-migration and post-migration tasks, so you could
> just dump then initdb and reload.

Yes, that's exactly the problem.  For the migration, you actually shut down
the old Mac that's the source of the data and boot it in a special FireWire
disk mode, and connect it like a hard disk to the new Mac.  As a result,
there's no code able to run on the source computer during the migration.

For our kind of users (non-technical often), it's almost impossible to have
them plan out stuff or even consider what needs to be done in terms of
advance tasks.

I had hoped that there would be a way to "rescue" the database, even if it
took a lot of processing...

Chris

--
Chris Saldanha
Parliant Corporation
http://www.parliant.com/


Re: changing the endianness of a database

From
"A.M."
Date:
On May 12, 2008, at 4:02 PM, Chris Saldanha wrote:

> Hi,
>
> We'd like to ship PostgreSQL as part of a product that runs on both
> PPC and
> Intel Macs, but the database files are tied to the build settings and
> endianness of the computer that the database was initialized on.
>
> Is there any way to cause the server to modify the database files in-
> place
> for endianness issues?  I know that a backup-then-restore process
> would fix
> the data, but on Macs, many users use Apple's computer migration
> tools to
> copy all their programs/data/users/etc.. to new Macs.
>
> If the user moves from a PPC to an Intel Mac, for instance, the
> database
> would be copied over, but the data would be for the old computer,
> and the
> database won't start.  The backup/restore process is hard for end
> users,
> since they don't understand it -- and they won't contact us until
> after the
> migration is done, and often not until they've discarded the old
> computer.
>
> It would be nice if there was a way to recover the data from the
> existing
> database files.
>
> I found this old thread on a related topic, and it seems that this
> cannot be
> done...
> http://archives.postgresql.org/pgsql-general/2008-01/msg00635.php

You know that you don't have to compile postgresql as "Universal",
right? If you have separate PPC and Intel versions (not lipo'd
together), then, presumably, you should be able to figure out which
one needs to run. The PPC postgresql would then run on the Macintel
under Rosetta and you would then have control to proceed with an
automatic dump/restore. However, this would not work for someone
moving the database from an Intel machine to a PPC machine.

Postgresql is simply not well-suited for such uncontrolled
environments. What happens when you upgrade postgresql? Do you then
ship with 4 version of the db (Intel/PPC * 8.2/83)? Perhaps you should
dump all the non-transient data whenever the application is shut down
(in anticipation of an upgrade)?

Cheers,
M

Re: changing the endianness of a database

From
Shane Ambler
Date:
A.M. wrote:

> You know that you don't have to compile postgresql as "Universal",
> right? If you have separate PPC and Intel versions (not lipo'd
> together), then, presumably, you should be able to figure out which one
> needs to run. The PPC postgresql would then run on the Macintel under
> Rosetta and you would then have control to proceed with an automatic
> dump/restore. However, this would not work for someone moving the
> database from an Intel machine to a PPC machine.

That would be my suggestion - run a ppc version to dump then restore
with an intel version. Maybe a startup script can detect when to do this.

Maybe this is an argument against making universal postgres binaries.

> Postgresql is simply not well-suited for such uncontrolled environments.
> What happens when you upgrade postgresql? Do you then ship with 4
> version of the db (Intel/PPC * 8.2/83)? Perhaps you should dump all the
> non-transient data whenever the application is shut down (in
> anticipation of an upgrade)?

As far as upgrades that could/should be handled in the installer script.
Dump from the installed version then install the new one and restore.
That is - using Apple's installer setup.



--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz