Re: query and pg_dump problem on my postgresql 6.5.3/Redhat 6.2

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 relation will, 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(at)jingxian(dot)xmu(dot)edu(dot)cn
> 2003-12-05
>
>---------------------------(end of broadcast)---------------------------TIP 4: Don't 'kill -9' the postmaster
>

= = = = = = = = = = = = = = = = = = = =

--

Yours,Wind Wood
windwood(at)jingxian(dot)xmu(dot)edu(dot)cn
2003-12-09