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:

Previous
From: Tom Lane
Date:
Subject: Re: pgsql 7.4 on minimal environment
Next
From: "Wind Wood"
Date:
Subject: A problem during restoring database from PostgreSql 6.5.3/7.1.3 to 7.4