Re: query and pg_dump problem on my postgresql 6.5.3/Redhat 6.2 - Mailing list pgsql-general
From | Wind Wood |
---|---|
Subject | Re: query and pg_dump problem on my postgresql 6.5.3/Redhat 6.2 |
Date | |
Msg-id | 200312090621.hB96Lp5r004260@staff.xmu.edu.cn Whole thread Raw |
In response to | query and pg_dump problem on my postgresql 6.5.3/Redhat 6.2 ("吴德文" <windwood@jingxian.xmu.edu.cn>) |
List | pgsql-general |
Hi! Thanks all for your help. Now I can dump data from old dababase, but new problem came when I restore the data to postgresql 7.4. Error went out during the COPY command, if I do it in such command lines: 1. pg_dump news -f pgsql-database-news.sql #in old system with postgresql 6.5.3 2. su - postgres #in new system with postgresql 7.4 3. createdb -T template0 news 4. psql news < pgsql-database-news.sql ------ ERROR: missing data for column "user_id" CONTEXT: COPY newses, line 1: ------ But if I do it in such command line, it works well : 1. pg_dump -d news -f pgsql-data-news.sql #in old system with postgresql 6.5.3 2. su - postgres #in new system with postgresql 7.4 3. createdb -T template0 news 4. psql news < pgsql-data-news.sql The database looks fine but I'm not sure it is really good. Then I try postgresql 7.1.3, and I found that it is very well from 6.5.3 to 7.1.3 in the two kind of command line. Later I also found that it likes the 6.5.3 when I dump/restore the data from 7.1.3 to 7.4. Could Anybody explain it? Whether there is a version with big change between 7.1.3 and 7.4? ======= 2003-12-06 Wind Wood wrote:======= >hi, > The disk just had problem, I used fsck to fix it, But the problem >of database is still there. > After I read the postgresql document and found this: >------ >COPY stops operation at the first error. This should not lead to problems in the event of a COPY FROM, but the target relationwill, of course, be partially modified in a COPY TO. The VACUUM query should be used to clean up after a failed copy. >------ > Then I Execute the sql "vacuum newses;" in psql, it return this message: >------ >NOTICE: Rel newses: Uninitialized page 16 - fixing >NOTICE: Rel newses: Uninitialized page 17 - fixing >VACUUM >------ > > It seems VACUUM fixed something, then I retry the SQL complained error before, >they all work well now, my php page work well also. > > It's exciting that all problems is gone, but I'm still not clear about what >happened and what the VACUUM had done, anyone can explain it? > > >======= 2003-12-05 您在来信中写道:======= > >>On Thursday 04 December 2003 14:55, 吴德文 wrote: >>> Help! >>> >>> A few days ago, my php page began to complain this: >>> ------ >>> Warning: PostgresSQL query failed: pqReadData() -- backend closed the >>> channel unexpectedly. This probably means the backend terminated abnormally >>> before or while processing the request. >>[snip] >>> NOTE: >>> I'm on Redhat 6.2 with Postgresql 6.5.3, the database named "news", >>> and the table is "newses", looks like this (dumped from "pg_dump -s -t >>> newses news"): >> >>One of the developers will probably be able to help, but bear in mind many are >>in the USA/Canada and so you might have time-zone delays. It will be >>suggested you upgrade to 7.3.5 or 7.4.0 as soon as possible. That might mean >>upgrading from RedHat 6.2 as well. >> >>At present: >>1. Dump all the other tables, if you can >>2. Stop PostgreSQL >>3. make a file backup of /var/data (or wherever your data is stored) >> >>OK - now at least you know things can't get any worse. >> >>In psql you can use \a to set unaligned output and \o <filename> to output >>query results to a file. You can then try SELECT * FROM newses WHERE news_id >>BETWEEN 1 AND 100, then 101-200 etc. This should let you recover a great deal >>of your data if only one disk-block is damaged. >> >>From what you say, you should be able to recover your table's data. Then, I'd >>recreate the database from your dumps. >> >>> But I found that pg_dump sometimes does not work on that very table, and >>> sometimes work with a long long time then error. >> >>This sounds like either a disk or memory error. I'd guess disk. >> >>-- >> Richard Huxton >> Archonet Ltd >> >> > >= = = = = = = = = = = = = = = = = = = = > > > 致 >礼! > > Wind Wood > windwood@jingxian.xmu.edu.cn > 2003-12-05 > >---------------------------(end of broadcast)---------------------------TIP 4: Don't 'kill -9' the postmaster > = = = = = = = = = = = = = = = = = = = = -- Yours,Wind Wood windwood@jingxian.xmu.edu.cn 2003-12-09
pgsql-general by date: