Обсуждение: Problems upgrading from 7.1.3
Hi all, I have a database cluster running on PostgreSQL 7.1.3 compiled from source, on Debian Linux. I want to upgrade the cluster to PostgreSQL 7.3.x. In order to get the data over, I ran: PGUSER=postgres /usr/local/pgsql/bin/pg_dumpall > survey1.sql on the production machine running 7.1.3. I then took that file over to test machine running PostgreSQL 7.3.1, and ran: PGUSER=postgres /usr/local/pgsql-7.3.1/bin/psql -d template1 -f survey1.sql I got lots of errors about "Invalid command \N" in the COPY xxx FROM statements, and most of the tables were completely empty. I then tried: PGUSER=postgres /usr/local/pgsql-7.3.1/bin/psql -d template1 < survey1.sql and got the same results. Next, I tried upgrading to 7.2.4 as an intermediate step, but 7.2.4 gave the same "Invalid command \N" errors. So then I then tried the pg_dump from 7.3.1 and 7.2.4 on the 7.1.3 database, and then restoring into the respective server. This still gave the same errors. I also tried editing the dump file, adding explicit "WITH NULL AS '\N'" clauses to each COPY statement. Same result still. Does anyone have ideas on what's going on here? Am I doing something wrong? Is there a step I'm missing? A google search revealed somebody with a similar problem upgrading from 6.5.x a year ago, but there didn't seem to be any resolution to it. TIA, --- Geoffrey Wossum Software Engineer Long Range Systems - http://www.pager.net
> I got lots of errors about "Invalid command \N" in the COPY xxx FROM > statements, and most of the tables were completely empty. Try dumping with -d or -D: -d, --inserts dump data as INSERT, rather than COPY, commands -D, --column-inserts dump data as INSERT commands with column names Regards, Bjoern
Geoffrey Wossum <geoffrey@pager.net> writes:
> I then took that file over to test machine running PostgreSQL 7.3.1,
Er, how did you copy the file over exactly?
This smells to me like it could be a newline-formatting problem (COPY is
pretty picky about its newlines). If you passed the file through
anything that might choose to convert Unix newlines to DOS newlines,
you would have that problem.
BTW, the very best way to do this sort of upgrade is to use the newer
version's pg_dump (ie, 7.3.1's pg_dump) to extract data from the older
server. The newer pg_dump will know how to apply any SQL compatibility
adjustments that might be appropriate. I do not think that will make
any difference to a COPY-data-format problem, but it could help for
other things.
regards, tom lane
On Wednesday 05 February 2003 01:12 pm, Tom Lane wrote: > Er, how did you copy the file over exactly? scp > This smells to me like it could be a newline-formatting problem (COPY is > pretty picky about its newlines). If you passed the file through > anything that might choose to convert Unix newlines to DOS newlines, > you would have that problem. No, looking at the file on the machine I dumped it on, there really are '\N's in it. And adding explicit "WITH NULL AS '\N'" clauses to the COPY's didn't help any either. Bjoern suggested I add "-d" or "-D" to the pg_dump/pg_dumpall command line, which worked flawlessly, although recreating the database was a bit slow. > BTW, the very best way to do this sort of upgrade is to use the newer > version's pg_dump (ie, 7.3.1's pg_dump) to extract data from the older > server. The newer pg_dump will know how to apply any SQL compatibility > adjustments that might be appropriate. I do not think that will make > any difference to a COPY-data-format problem, but it could help for > other things. I originally used 7.3.1's pg_dumpall to dump the 7.1.3's database as recommended in the admin manual, but had the same result. Maybe I forgot to mention that in my original email. There were a lot of combinations I had to mention ^_^ Thanks, --- Geoffrey Wossum Software Engineer Long Range Systems - http://www.pager.net
Geoffrey Wossum <geoffrey@pager.net> writes:
> On Wednesday 05 February 2003 01:12 pm, Tom Lane wrote:
>> This smells to me like it could be a newline-formatting problem (COPY is
>> pretty picky about its newlines).
> [ Nope ]
Drat, another perfectly good theory down the drain.
> Bjoern suggested I add "-d" or "-D" to the pg_dump/pg_dumpall command line,
> which worked flawlessly, although recreating the database was a bit slow.
Hm. The -d business used to be a standard workaround for some known
limitations of COPY, but I was of the opinion that we'd fixed the last
of them as of 7.3. If you found -d necessary even with 7.3.x pg_dump
then I'd like to see the details. The table definition (as dumped by
pg_dump) and the first few lines of COPY data from the dump would be
useful info to start with...
regards, tom lane
Hmmm I face the same problem and it got solved. In my case the message "Invalid command \N" was coming during COPY command execution when some other SQL command had failed prior to COPY execution. ie the COPY command and data in that part were perfectly fine. how i came to know abt it was by redirecting STDERR to a seperate file while restoring and carefully going thru it after the errors started to come. you can do something like. PGUSER=postgres /usr/local/pgsql-7.3.1/bin/psql -d template1 < survey1.sql 2 > errors regds mallah. On Thursday 06 February 2003 12:42 am, Tom Lane wrote: > Geoffrey Wossum <geoffrey@pager.net> writes: > > I then took that file over to test machine running PostgreSQL 7.3.1, > > Er, how did you copy the file over exactly? > > This smells to me like it could be a newline-formatting problem (COPY is > pretty picky about its newlines). If you passed the file through > anything that might choose to convert Unix newlines to DOS newlines, > you would have that problem. > > BTW, the very best way to do this sort of upgrade is to use the newer > version's pg_dump (ie, 7.3.1's pg_dump) to extract data from the older > server. The newer pg_dump will know how to apply any SQL compatibility > adjustments that might be appropriate. I do not think that will make > any difference to a COPY-data-format problem, but it could help for > other things. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Regds Mallah ---------------------------------------- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
On Thursday 06 February 2003 06:31 am, Rajesh Kumar Mallah wrote:
> In my case the message "Invalid command \N" was coming
> during COPY command execution when some other SQL command had
> failed prior to COPY execution. ie the COPY command and data
> in that part were perfectly fine.
Ah, found my problem. The following table was created as:
CREATE TABLE bases (
serialno integer NOT NULL,
ip character varying(15) DEFAULT 'FF.FF.FF.FF.FF',
mac character varying(17) DEFAULT 'FF:FF:FF:FF:FF:FF:FF',
modem_init character varying,
enc_key character varying,
status integer,
"location" integer DEFAULT '-1'
);
You'll notice that the mac column is a VARCHAR(17), but the default value is
20 characters long! I got the following errors:
ERROR: value too long for type character varying(17)
ERROR: Relation "bases" does not exist
So that caused the bases table to not be created. When psql got the "COPY"
command for bases, the table wasn't there, so it didn't really execute the
COPY. Then psql thought all the data for the COPY was really commands, hence
the "Invalid command \N" message. Apparently this really got it confused.
If I hand editted the dump to change the 17 to a 20, everything was happy.
Apparently PostgreSQL 7.1.3 didn't really enforce the length on VARCHAR(#)'s,
which is why this never caused a problem with it.
Now, why did this all work fine if I added "-D" to pg_dump/pg_dumpall?
Shouldn't that have failed for the same reason?
Thanks,
---
Geoffrey Wossum
Software Engineer
Long Range Systems - http://www.pager.net
Geoffrey Wossum <geoffrey@pager.net> writes:
> Apparently PostgreSQL 7.1.3 didn't really enforce the length on VARCHAR(#)'s,
> which is why this never caused a problem with it.
Well, it did, but it did silent truncation instead of complaining.
This was determined to be not spec compliant ...
> Now, why did this all work fine if I added "-D" to pg_dump/pg_dumpall?
> Shouldn't that have failed for the same reason?
Yeah, it sure should have. Can you compare the two dumps to see what's
different besides the data proper?
regards, tom lane
>>>>> "Geoffrey" == Geoffrey Wossum <geoffrey@pager.net> writes:
Geoffrey> Hi all, I have a database cluster running on PostgreSQL
Geoffrey> 7.1.3 compiled from source, on Debian Linux. I want to
Geoffrey> upgrade the cluster to PostgreSQL 7.3.x.
Geoffrey> In order to get the data over, I ran: PGUSER=postgres
Geoffrey> /usr/local/pgsql/bin/pg_dumpall > survey1.sql on the
Geoffrey> production machine running 7.1.3.
Geoffrey> I then took that file over to test machine running
Geoffrey> PostgreSQL 7.3.1, and ran: PGUSER=postgres
Geoffrey> /usr/local/pgsql-7.3.1/bin/psql -d template1 -f survey1.sql
Geoffrey> I got lots of errors about "Invalid command \N" in the COPY
Geoffrey> xxx FROM statements, and most of the tables were completely
Geoffrey> empty.
I find that the whole database dumps do not have 'create user' and
'create group' commands. Since I only have one group (admin) and many
users, I:
for i in `cat survey1.sql | egrep ^.connect\|^GRANT\|^REVOKE | rev |
cut -d\ -f1 | tr -d \;\" | rev | sort -u | grep -v PUBLIC`; do
psql -c "create user $i" template1
done
... add grep -v's to taste. Now... this doesn't recreate users with
'createuser' or 'createdatabase' permissions. You may want to adjust
this.
Dave.
--
============================================================================
|David Gilbert, Velocet Communications. | Two things can only be |
|Mail: dgilbert@velocet.net | equal if and only if they |
|http://daveg.ca | are precisely opposite. |
=========================================================GLO================
David Gilbert <dgilbert@velocet.ca> writes:
> I find that the whole database dumps do not have 'create user' and
> 'create group' commands.
Those are dumped by pg_dumpall, but not by pg_dump. Since users and
groups span all databases in an installation, it wouldn't be very useful
for pg_dump to include them in its (per-database) output.
regards, tom lane
thanks Geoffrey for posting the observation. I hope you will soon find the answer to your question on investigation. regds mallah. On Thursday 06 February 2003 09:27 pm, Geoffrey Wossum wrote: > On Thursday 06 February 2003 06:31 am, Rajesh Kumar Mallah wrote: > > In my case the message "Invalid command \N" was coming > > during COPY command execution when some other SQL command had > > failed prior to COPY execution. ie the COPY command and data > > in that part were perfectly fine. > > Ah, found my problem. The following table was created as: > > CREATE TABLE bases ( > serialno integer NOT NULL, > ip character varying(15) DEFAULT 'FF.FF.FF.FF.FF', > mac character varying(17) DEFAULT 'FF:FF:FF:FF:FF:FF:FF', > modem_init character varying, > enc_key character varying, > status integer, > "location" integer DEFAULT '-1' > ); > > You'll notice that the mac column is a VARCHAR(17), but the default value > is 20 characters long! I got the following errors: > > ERROR: value too long for type character varying(17) > ERROR: Relation "bases" does not exist > > So that caused the bases table to not be created. When psql got the "COPY" > command for bases, the table wasn't there, so it didn't really execute the > COPY. Then psql thought all the data for the COPY was really commands, > hence the "Invalid command \N" message. Apparently this really got it > confused. > > If I hand editted the dump to change the 17 to a 20, everything was happy. > > Apparently PostgreSQL 7.1.3 didn't really enforce the length on > VARCHAR(#)'s, which is why this never caused a problem with it. > > Now, why did this all work fine if I added "-D" to pg_dump/pg_dumpall? > Shouldn't that have failed for the same reason? > > Thanks, > --- > Geoffrey Wossum > Software Engineer > Long Range Systems - http://www.pager.net > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Regds Mallah ---------------------------------------- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.