Thread: Truncation on restore
Hi everybody, forgive me if this is covered in an FAQ or something, I just haven't been able to find it. This question might not even belong on this list and I apologize if that's the case. I was recently handed a CD containing a CVS checkout of more than 26 thousand files and the dump of a production DB, and told to duplicate the development environment. I'm always up for a challenge, so of course I didn't hurt anybody. I seem already to be failing at trying to use the dump to create the database, though, and I'm wondering where I should start looking. If I do a simple grep of the dump file, I count 265 table creation statements. However, after restoring (psql test -f dump.sql) there are only 240 tables in the test database, and there were truncations and errors due to them sent to the console during the restore. It seems that many of the index names are being truncated 31 characters and this cascades to other errors. I guess that my question in its most basic form is, "What have I done wrong?". A quick google didn't turn up much other than apparently truncation at 31 chars is common and generally never causes a problem. And yet, my stuff takes a nose dive. Any help out there? The system where the dump was generated and the system where it's trying to be restored are both RH 7.3 boxes, driving 7.2.1 versions of PostgreSQL. Thanks everybody. Andy
Andrew Kelly <akelly@transparency.org> writes:
> It seems that many of the index names are being truncated
> 31 characters and this cascades to other errors.
31 was the standard name length limit in PG 7.2. If you see longer
names in a dump from a 7.2 server, then that server is *not* standard
but was built with a custom value of NAMEDATALEN. You will need to
build yours likewise. I would recommend holding a gun to someone's
head till they tell you what other nonstandard build options were
used, too ;-)
regards, tom lane
Tom Lane wrote: > > Andrew Kelly <akelly@transparency.org> writes: > > It seems that many of the index names are being truncated > > 31 characters and this cascades to other errors. > > 31 was the standard name length limit in PG 7.2. If you see longer > names in a dump from a 7.2 server, then that server is *not* standard > but was built with a custom value of NAMEDATALEN. You will need to > build yours likewise. I would recommend holding a gun to someone's > head till they tell you what other nonstandard build options were > used, too ;-) > > regards, tom lane Oh Tom, if you only knew... The difficulties I'm having getting information out of these chuckle-heads is at a last stage prior to open warfare. In fact, I'm flying to London on Monday for a lousy 30 minute face-to-face. Maybe seeing that some computer weenies like to train with free weights while they mentally debug will put a little "here you go, sir" on the table. I'll have a gun, though, just in case... Can I pass the value for NAMEDATALEN to the configure sript (or use a spec file in an rpm build from src), or do I have to make the change in postgres_ext.h? And thanks for the help, I really appreciate it. Andy
Andrew Kelly <akelly@transparency.org> writes:
> Can I pass the value for NAMEDATALEN to the configure
> sript (or use a spec file in an rpm build from src), or
> do I have to make the change in postgres_ext.h?
You have to hack postgres_ext.h before building. Probably this can be
handled from an RPM spec file, but not being an RPM guy I dunno how.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > Can I pass the value for NAMEDATALEN to the configure > sript (or use a spec file in an rpm build from src), or > do I have to make the change in postgres_ext.h? For what it's worth, a configurable option has been proposed before, but rejected for client reasons. You probably have to compile yourself, I doubt there is a rpm option. http://archives.postgresql.org/pgsql-patches/2001-05/msg00137.php - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200312102048 -----BEGIN PGP SIGNATURE----- iD8DBQE/180gvJuQZxSWSsgRAuXNAKDsyTNv7Cp1SIjcQdz3LE8d0WZK8gCgq23B n1vsfG0zYsvA3R025HOxkYk= =kSIs -----END PGP SIGNATURE-----
greg@turnstep.com wrote: [in response to my writing] > > Can I pass the value for NAMEDATALEN to the configure > > sript (or use a spec file in an rpm build from src), or > > do I have to make the change in postgres_ext.h? > > For what it's worth, a configurable option has been proposed > before, but rejected for client reasons. You probably have > to compile yourself, I doubt there is a rpm option. > > http://archives.postgresql.org/pgsql-patches/2001-05/msg00137.php Yeah, Tom Lane told me the deal a few days ago. Thanks for the link to the patch discussion, it's good to have the background. I changed the value of NAMEDATALEN in a source package for 7.2.1 and compiled a quick test server, and with that little beastie the dump restored cleanly. I wonder what else they've hacked at and kept quiet about. By the way, what's the value of NAMEDATALEN in the current release? Andy
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > By the way, what's the value of NAMEDATALEN in the > current release? The value of NAMEDATALEN switched from 32 to 64 in release 7.3 of PostgreSQL. IIRC, the SQL standard encourages 128, but that probably won't happen as the default for quite a while. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200312141444 -----BEGIN PGP SIGNATURE----- iD8DBQE/3L3fvJuQZxSWSsgRAjtqAKDxcEtMx/I/x8iI0W7i4lomIMdqLgCgxIrG XiaaC/ggGnUgB5Wml4d/S7M= =TREp -----END PGP SIGNATURE-----