pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Corrupted DB. Help.
3+ messages / 3 participants
[nested] [flat]

* Corrupted DB. Help.
@ 2001-12-12 07:38 Anto Prijosoesilo <antop64@yahoo.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Anto Prijosoesilo @ 2001-12-12 07:38 UTC (permalink / raw)
  To: pgsql-admin

Hi,

One of my databases was running on PostgreSQL 6.5.1
and I accidentally corrupted the pg_class file. After
I restored the file from a backup, postgres would come
up but I can't see my tables. Only the system tables
are visible and trying to SELECT * FROM mytable
returned 0 rows.

SELECT * FROM pg_class only shows the system tables,
but VACUUM would go through all the tables.

pg_class' entry in pg_class table shows that it has 95
tuples but only 47 of them are listed when I do SELECT
* FROM pg_class.

Can someone help me recover my tables?

Thanks.

__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Corrupted DB - Help
@ 2006-07-28 01:55 Mark Liberman <mliberman@mixedsignals.com>
  2006-07-28 03:39 ` Re: Corrupted DB - Help Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Mark Liberman @ 2006-07-28 01:55 UTC (permalink / raw)
  To: pgsql-admin

Running postgres 8.1.2 on gentoo linux 2.6.14

Server crashed (not sure if due to pg operation).  Upon rebooting server and trying to start postgres, get the following log entries:

<4983:2006-07-27 18:04:24 PDT>LOG:  database system was interrupted at 2006-07-27 17:58:51 PDT
<4983:2006-07-27 18:04:24 PDT>LOG:  checkpoint record is at 2A4/2A23F328
<4983:2006-07-27 18:04:24 PDT>LOG:  redo record is at 2A4/2A23F328; undo record is at 0/0; shutdown FALSE
<4983:2006-07-27 18:04:24 PDT>LOG:  next transaction ID: 70649528; next OID: 345957
<4983:2006-07-27 18:04:24 PDT>LOG:  next MultiXactId: 1742; next MultiXactOffset: 3498
<4983:2006-07-27 18:04:24 PDT>LOG:  database system was not properly shut down; automatic recovery in progress
<4983:2006-07-27 18:04:24 PDT>LOG:  redo starts at 2A4/2A23F36C
<4983:2006-07-27 18:04:24 PDT>WARNING:  could not truncate relation 1663/128873/129263 to 398 blocks: Success
<4984:2006-07-27 18:04:24 PDT>LOG:  connection received: host=[local]
<4984:2006-07-27 18:04:24 PDT>FATAL:  the database system is starting up
<4981:2006-07-27 18:04:24 PDT>DEBUG:  forked new backend, pid=4984 socket=7
<4981:2006-07-27 18:04:24 PDT>DEBUG:  server process (PID 4984) exited with exit code 0
<4985:2006-07-27 18:04:25 PDT>LOG:  connection received: host=[local]
<4985:2006-07-27 18:04:25 PDT>FATAL:  the database system is starting up
<4981:2006-07-27 18:04:25 PDT>DEBUG:  forked new backend, pid=4985 socket=7
<4981:2006-07-27 18:04:25 PDT>DEBUG:  server process (PID 4985) exited with exit code 0
<4983:2006-07-27 18:04:26 PDT>PANIC:  could not open relation 1663/128873/129201: No such file or directory
<4981:2006-07-27 18:04:26 PDT>LOG:  startup process (PID 4983) was terminated by signal 6
<4981:2006-07-27 18:04:26 PDT>LOG:  aborting startup due to startup process failure
<4982:2006-07-27 18:04:26 PDT>LOG:  logger shutting down


The last commmand that I did right before the crash was a vacuum full on a very small table.  After it completed, I exited psql, tried a non-pg command and it hung up.  Here are the last log entries before the crash:

<1054:2006-07-27 18:00:18 PDT>LOG:  statement: vacuum full verbose port_etr_state_offline;
<1054:2006-07-27 18:00:18 PDT>INFO:  vacuuming "public.port_etr_state_offline"
<1054:2006-07-27 18:00:18 PDT>INFO:  "port_etr_state_offline": found 0 removable, 213 nonremovable row versions in 16 pages
<1054:2006-07-27 18:00:18 PDT>DETAIL:  0 dead row versions cannot be removed yet.
        Nonremovable row versions range from 45 to 45 bytes long.
        There were 2445 unused item pointers.
        Total free space (including removable row versions) is 109896 bytes.
        12 pages are or will become empty, including 0 at the end of the table.
        16 pages containing 109896 free bytes are potential move destinations.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
<1054:2006-07-27 18:00:18 PDT>INFO:  "port_etr_state_offline": moved 213 row versions, truncated 16 to 2 pages
<1054:2006-07-27 18:00:18 PDT>DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
<1054:2006-07-27 18:00:18 PDT>LOG:  duration: 16.078 ms

Any way to salvage this db?

Thanks,

Mark

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Corrupted DB - Help
  2006-07-28 01:55 Corrupted DB - Help Mark Liberman <mliberman@mixedsignals.com>
@ 2006-07-28 03:39 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2006-07-28 03:39 UTC (permalink / raw)
  To: Mark Liberman <mliberman@mixedsignals.com>; +Cc: pgsql-admin

"Mark Liberman" <mliberman@mixedsignals.com> writes:
> <4983:2006-07-27 18:04:26 PDT>PANIC:  could not open relation =
> 1663/128873/129201: No such file or directory
> <4981:2006-07-27 18:04:26 PDT>LOG:  startup process (PID 4983) was =
> terminated by signal 6

That's really strange ... it's not supposed to be possible to get that
error during xlog replay, because XLogOpenRelation is always willing to
create a file that doesn't exist.  Can you get a stack trace from the
crash dump this presumably left behind?

If you're just in a panic to get the DB up again, I'd try creating the
mentioned file ("touch $PGDATA/base/128873/129201") and see if it'll
start.  But if you have some time to investigate, it'd be good to try
to determine exactly what went wrong here.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2006-07-28 03:39 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-12 07:38 Corrupted DB. Help. Anto Prijosoesilo <antop64@yahoo.com>
2006-07-28 01:55 Corrupted DB - Help Mark Liberman <mliberman@mixedsignals.com>
2006-07-28 03:39 ` Re: Corrupted DB - Help Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox