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

Hmm, you do realise that 6.5 is *really* *really* old. Who knows how many
bugs there are. There have been *5* major releases since and many more minor
ones.

I remember back then, 6.5 had many quirks that required a vacuum to fix (or
couldn't be fixed at all). I remember when creating a temporary table inside
a transaction that aborted left an orphand file on disk.

You're going to have a hard time getting someone to help you with a version
that old.

Good luck,

On Sat, Dec 06, 2003 at 03:15:05PM +0800, 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

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato