Recover with main/base files

From: Sylvain Avril <avrils(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Recover with main/base files
Date: 2009-01-26 17:16:46
Message-ID: 26ce3de40901260916g1fccacb2p14db55064cd9feab@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello,

I use postgresql 8.1 on a debian server with a /var JFS filesystem.
After a crash of this FS (due to hardware instability: PSU), the
/var/lib/postgresql/8.1/main folder was missing and its files were
found under /var/lost+found.
We have a full daily backup of this DB but I try to recover data from
the morning it crashed.

This is what I was able to recover :
- main/base apparently without missing files !
- main/pg_xlog : I don't have these files names (but I can guess it
from PG start errors) and I don't know in which order I've have to
name them
- main/pg_clog files 0000 and 0001 but not 0003 which I replaced with
a zero padded file
- main/pg_multixact
- main/global/pg_control

But the global folder content was not found. I probably have piece of
it but I wasn't able to identify them except for pg_control.

I tried to restore all the files over a base debian installation and
then inserting a record in pg_database table like that :
insert into pg_database (datname, datdba, encoding, datistemplate,
datallowconn, datconnlimit, datlastsysoid, datvacuumxid, datfrozenxid,
dattablespace) VALUES ( 'ex1', 10, 8, 'f', 't', -1, 10792, 499, 499,
1663);

PG started without error and I can connect to the DB created but \dt
show me nothing.

Do you have alternatives to restore a db from main/base files ?
If you have a method to reorder pg_xlog files, it interest me too.

Thank you

Browse pgsql-admin by date

  From Date Subject
Next Message Jaume Sabater 2009-01-26 18:50:36 Re: postgresql and xfs filesystrem
Previous Message Ezra Taylor 2009-01-26 16:01:20 Re: postgresql and xfs filesystrem