SEO

Dear PostgreSQL Hackers,

We got a PG 8.1 on a Debian 64 bits, which does a full backup (PITR) daily.
Then we installed a Debian 32 bits (actually, it's on VMWare) and wanted to
restore the previous PG cluster on it.
As there are a lot of indexes, specially GiST, "pg_dump" and "pg_restore"
are not viable - will take a lot of time!

Well, the fact is that we've got the message below on "postmaster" start
attempt:

"WARNING: Calculated CRC checksum does not match value stored in file.
Either the file is corrupt, or it has a different layout than this program
is expecting. The results below are untrustworthy."

As the architecture on both Linuxes are different (32 and 64 bits), I think
"PGDATA/global/pg_control" might contains 64 bit data such that the 32 bits
binary won't recognize or even mispell it. Am I right?

What could be done in order to fix it? Is there any kind of application to
translate it or the only solution was to "pg_dumpall" and "pg_restore" the
cluster?


************************************************** ********************************************

postgres@pga1:/tmp/lala/global$ uname -a
Linux pga1 2.6.8-2-686 #1 Tue Aug 16 13:22:48 UTC 2005 i686 GNU/Linux

postgres@pga1:/tmp/lala/global$ pg_controldata /var/lib/postgresql/8.1/main/
WARNING: Calculated CRC checksum does not match value stored in file.
Either the file is corrupt, or it has a different layout than this program
is expecting. The results below are untrustworthy.

pg_control version number: 812
Catalog version number: 200510211
Database system identifier: 4883914971069546458
Database cluster state: in production
pg_control last modified: Wed 31 Dec 1969 09:00:00 PM BRT
Current log file ID: 1142136269
Next log file segment: 0
Latest checkpoint location: 1/30
Prior checkpoint location: 1/2F71B630
Latest checkpoint's REDO location: 1/2F71B5E0
Latest checkpoint's UNDO location: 1/2F71B630
Latest checkpoint's TimeLineID: 0
Latest checkpoint's NextXID: 0
Latest checkpoint's NextOID: 1
Latest checkpoint's NextMultiXactId: 36239847
Latest checkpoint's NextMultiOffset: 1819439
Time of latest checkpoint: Wed 31 Dec 1969 09:00:11 PM BRT
Maximum data alignment: 25
Database block size: 0
Blocks per segment of large relation: 8
Bytes per WAL segment: 0
Maximum length of identifiers: 0
Maximum columns in an index: 1093850759
Date/time type storage: 64-bit integers
Maximum length of locale name: 131072
LC_COLLATE:
LC_CTYPE:

************************************************** ********************************************

pgsql01:~# uname -a
Linux pgsql01 2.6.8-11-em64t-p4-smp #1 SMP Mon Oct 3 00:07:51 CEST 2005
x86_64 GNU/Linux

pgsql01:~# /usr/lib/postgresql/8.1/bin/pg_controldata /pg/data/
pg_control version number: 812
Catalog version number: 200510211
Database system identifier: 4883914971069546458
Database cluster state: in production
pg_control last modified: Mon Mar 13 14:19:42 2006
Current log file ID: 1
Next log file segment: 51
Latest checkpoint location: 1/3289F8E0
Prior checkpoint location: 1/32827710
Latest checkpoint's REDO location: 1/3289F8E0
Latest checkpoint's UNDO location: 0/0
Latest checkpoint's TimeLineID: 1
Latest checkpoint's NextXID: 37253588
Latest checkpoint's NextOID: 1819439
Latest checkpoint's NextMultiXactId: 11
Latest checkpoint's NextMultiOffset: 25
Time of latest checkpoint: Mon Mar 13 14:19:42 2006
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Date/time type storage: 64-bit integers
Maximum length of locale name: 128
LC_COLLATE: pt_BR
LC_CTYPE: pt_BR

************************************************** ********************************************

Regards,

Rodrigo Hjort
GTI - Projeto PostgreSQL
CELEPAR - Cia de Informática do Paraná
http://www.pr.gov.br

On 3/13/06, Rodrigo Hjort <rodrigo.hjort@gmail.com> wrote:
>
> As the architecture on both Linuxes are different (32 and 64 bits), I
> think "PGDATA/global/pg_control" might contains 64 bit data such that the32
> bits binary won't recognize or even mispell it. Am I right?
>

Yes, the platform architecture is key. You won't be able to read the 64-bit
data files on a 32-bit box.

What could be done in order to fix it? Is there any kind of application to
> translate it or the only solution was to "pg_dumpall" and "pg_restore" the
> cluster?
>

Yes, dump and restore is the best way to go.


--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

On Mon, Mar 13, 2006 at 02:56:00PM -0300, Rodrigo Hjort wrote:
> Dear PostgreSQL Hackers,
>
> We got a PG 8.1 on a Debian 64 bits, which does a full backup (PITR) daily.
> Then we installed a Debian 32 bits (actually, it's on VMWare) and wanted to
> restore the previous PG cluster on it.
> As there are a lot of indexes, specially GiST, "pg_dump" and "pg_restore"
> are not viable - will take a lot of time!

Can't be done. The differences in alignments, size, placement, etc will
make it completly. PostgreSQL doesn't even try to maintain a consistant
file format with different configure options...

pg_dump is the only way.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEFbvaIB7bNG8LQkwRAr5RAJ9zCwAhZoBR2T2DZY25IG FEvXu5qwCeIkQt
/vZB2NcdXwhjtGAx5EATi5A=
=shbU
-----END PGP SIGNATURE-----

"Rodrigo Hjort" <rodrigo.hjort@gmail.com> writes:

> What could be done in order to fix it? Is there any kind of application to
> translate it or the only solution was to "pg_dumpall" and "pg_restore" the
> cluster?

Unfortunately pg_dump/pg_restore is going to be your only option here. The
database files are specific to the architecture and 32-bit and 64-bit linux
are different architectures. It's just as hard as moving from Sparc to IA32.
The only mechanism Postgres has it to do a dump and restore.

--
greg


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Mon, Mar 13, 2006 at 01:36:28PM -0500, Jonah H. Harris wrote:
> What could be done in order to fix it? Is there any kind of application to
> > translate it or the only solution was to "pg_dumpall" and "pg_restore" the
> > cluster?
> >
>
> Yes, dump and restore is the best way to go.

Setting up Slony might be another option; you'd essentially be following
the procedure used to speed up a PostgreSQL upgrade that would normally
require a dump/reload.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On 3/14/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
>
> Setting up Slony might be another option; you'd essentially be following
> the procedure used to speed up a PostgreSQL upgrade that would normally
> require a dump/reload.


If you need to do this on a continuing basis, Slony is the best way to go.
If it's a one-time shot, I'd just pipe pg_dump to a psql that's connected to
PostgreSQL on your 32-bit system.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

On Tue, Mar 14, 2006 at 02:12:39PM -0500, Jonah H. Harris wrote:
> On 3/14/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
> >
> > Setting up Slony might be another option; you'd essentially be following
> > the procedure used to speed up a PostgreSQL upgrade that would normally
> > require a dump/reload.
>
>
> If you need to do this on a continuing basis, Slony is the best way to go.
> If it's a one-time shot, I'd just pipe pg_dump to a psql that's connected to
> PostgreSQL on your 32-bit system.

Well, it's not so much a matter of how often you have to do it, but what
kind of downtime you can tolerate. Setting up Slony just to move a
cluster from one machine to another is a good amount of extra work, so
if you don't have the uptime requirement it probably doesn't make sense.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Well, actually we're ain't gonna do this procedure regularly, but just in
case of failure - if it ever happens.
For the moment, I did the dump/restore and it worked, but took almost 1
hour, due to tsearch2 indexes on a table.
Yeah, I thought 64-bit data could be stored on other files than pg_control.
So, there's only one way.

Thanks for helping!


2006/3/14, Jim C. Nasby <jnasby@pervasive.com>:
>
> On Tue, Mar 14, 2006 at 02:12:39PM -0500, Jonah H. Harris wrote:
> > On 3/14/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
> > >
> > > Setting up Slony might be another option; you'd essentially be
> following
> > > the procedure used to speed up a PostgreSQL upgrade that would
> normally
> > > require a dump/reload.
> >
> >
> > If you need to do this on a continuing basis, Slony is the best way to
> go.
> > If it's a one-time shot, I'd just pipe pg_dump to a psql that's
> connected to
> > PostgreSQL on your 32-bit system.
>
> Well, it's not so much a matter of how often you have to do it, but what
> kind of downtime you can tolerate. Setting up Slony just to move a
> cluster from one machine to another is a good amount of extra work, so
> if you don't have the uptime requirement it probably doesn't make sense.
> --
> Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>


--
Regards,

Rodrigo Hjort
GTI - Projeto PostgreSQL
CELEPAR - Cia de Informática do Paraná
http://www.pr.gov.br