Thread: Problems importing my 6.3 database into 6.5.1
I am having some trouble; probably due to my poor database design. We have an old server running PostgreSQL 6.3. I've built 6.5.1 on a new machine and am trying to bring up the database on it. I used both pg_dumpall from the 6.3 dist and from 6.5.1 - both appear to produce the same output. I used: pg_dumpall > database.dump to create the file I am trying to read. The command I am using to attempt to read in the database is: psql -e template1 < /path/to/database.dump 2>&1 | less so that I can read the output. Otherwise, it just starts scrolling the help screen from psql many times and finally dumps core. In the first coupla screens of output is what appears to be the problem: QUERY: CREATE TABLE jobs (id int4, folder text, open bool, synopsis text, user text, severity int2, status text, problem text, submitted int4, due int4, lastmod int4, lastperson text, keywords text, inform text); ERROR: parser: parse error at or near "user" followed by several similar lines. Sure enough, "user" is a key field among many of my tables - is it also a restricted word? The old database is still up, and can remain up for a bit. But I do need to migrate to the new server as quickly as I reasonably can. How can I get around the problem? In case it matters, the OS is linux on both machines - redhat 5.2/2.0.35 on the old one, and redhat 6.0/2.2.10 on the new one. Both installations are compiled from source, not just installed from the rpms. If there is additional information I can give, please let me know what it is. Thanks in advance for any help. I can provide the actual error output if it helps. -- Randy Dees -o) SCA: Talorgen nei Wrguist /\\ Unix Systems Administrator Comptek Amherst Systems, Inc. _\_v
At 10:47 +0300 on 28/07/1999, Randy Dees wrote: > QUERY: CREATE TABLE jobs (id int4, folder text, open bool, synopsis text, > user text, severity int2, status text, problem text, submitted int4, due >int4, > lastmod int4, lastperson text, keywords text, inform text); > ERROR: parser: parse error at or near "user" > > followed by several similar lines. Sure enough, "user" is a key field among > many of my tables - is it also a restricted word? > > The old database is still up, and can remain up for a bit. But I do need to > migrate to the new server as quickly as I reasonably can. How can I get > around the problem? No way to get around it. You have to change the name of the field in the create statement (and all the queries that use it), or put it in double quotes (and do the same in all the queries). While you are at it, do a thorough search for all the SQL92 reserved words, and make sure you don't have any other such problems. This will save you going through this process again (the main problem is going through all your applications, and possibly through your view definitions) in the future, when more and more SQL92 words will be put into use in PostgreSQL. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
On Wed, Jul 28, 1999, Herouth Maoz boldly stated
> No way to get around it. You have to change the name of the field in the
> create statement (and all the queries that use it), or put it in double
> quotes (and do the same in all the queries).
>
> While you are at it, do a thorough search for all the SQL92 reserved words,
> and make sure you don't have any other such problems. This will save you
> going through this process again (the main problem is going through all
> your applications, and possibly through your view definitions) in the
> future, when more and more SQL92 words will be put into use in PostgreSQL.
Thanks for the info, and for the quick reply. For others who find themselves
in a similar situation, here is a quick and dirty perl script that might help
them clean up. It's not generic, or well written, or suited for the job
without some looking at the errors, but it cleans up my output file by doing
these things:
For every column name that does not already have an _ in it, the script
prefixes tablename_ to the column name. That should guarantee no keywords are
there. It skips the pga stuff, and skips columns with _ in their name just
because we had started using a cleaner namespace, and table_name_tn_id is
lousy when tn_id does what we wanted it to do in the first place.
We had char2, char4, char16 datatypes peppered through our tables. These
appeared to be fine in 6.3, but trash 6.5.1. the script replaces them with
char(x) instead.
To find out what the problems were, I did the import and tee'd stdout & stderr
to a file which I could then search for ERROR. This could be done better,
too, but this command got the job done under bash:
psql -e template1 < db.out 2>&1 | tee error.out
You got a good database when grep ERROR error.out gives you no output.
Without further ado, here is the script. I hope this isn't needed by anyone
out there, and hope it helps anyone who does need it. It doesn't fix the
apps, but at least now I have a database to test the apps against as I fix
them.
--
Randy Dees -o)
SCA: Talorgen nei Wrguist /\\
Unix Systems Administrator Comptek Amherst Systems, Inc. _\_v
#! /usr/bin/perl
$database = shift;
open DATABASE,$database;
while ($line=<DATABASE>) {
unless ($line =~ /CREATE TABLE/) {
print $line;
next;
}
my ($create,$tble,$table_name,$table) = split / /, $line, 4;
if ($table_name =~ /pga_/) {
print $line;
next;
}
$table =~ s/[\(\)\;]//g;
chomp $table;
@table = split /,/, $table;
@newtable = undef;
foreach $pair (@table) {
$pair =~ s/^ //;
$pair =~ s/char([1-9]+)/char($1)/g;
if ($pair =~ /_/) {
push @newtable,$pair;
}
else {
$pair = "${table_name}_${pair}";
push @newtable,$pair;
}
}
$newtable = undef;
foreach $pair (@newtable) {
if ($newtable) {
$newtable = $newtable .", $pair";
}
else {$newtable = $pair;}
}
print "$create $tble $table_name ($newtable);\n";
}