Thread: [URGENT] Problems recovering database
Hello,
One of our servers has been hacked and the only thing left are the data
files, no dump. Can I recover a database from that? The database
apparently was shutdown correctly before the files were transferred. The
server was runing pgsql 7.2.2 on RedHat ... I have compiled a version of
PostgreSQL 7.2.2 and copied the whole 'data' directory and when starting
pgsql with:
pg_ctl start -D /location/of/data
It complains
DEBUG: open of /usr2/pgsql/data/pg_xlog/0000000000000000 (log file 0,
segment 0) failed: No such file or directory
DEBUG: invalid primary checkpoint record
DEBUG: open of /usr2/pgsql/data/pg_xlog/0000000000000000 (log file 0,
segment 0) failed: No such file or directory
DEBUG: invalid secondary checkpoint record
But these files were not present in the 'data' directory from this server,
only these:
$ ls
00000010000000B7 00000010000000B8 00000010000000B9 00000010000000BA
00000010000000BB 00000010000000BC 00000010000000BD 00000010000000BE
Any ideas on how should I proceed?
Thank you in advance,
/B
--
--- Bruno Mattarollo <bruno@web1.greenpeace.org> ---
SysAdmin & TechLead - Greenpeace International
http://www.greenpeace.org/
----------------------------------------------------
> It complains > > DEBUG: open of /usr2/pgsql/data/pg_xlog/0000000000000000 (log file 0, > segment 0) failed: No such file or directory > DEBUG: invalid primary checkpoint record > DEBUG: open of /usr2/pgsql/data/pg_xlog/0000000000000000 (log file 0, > segment 0) failed: No such file or directory > DEBUG: invalid secondary checkpoint record > > But these files were not present in the 'data' directory from this > server, only these: > > $ ls > 00000010000000B7 00000010000000B8 00000010000000B9 00000010000000BA > 00000010000000BB 00000010000000BC 00000010000000BD 00000010000000BE > > Any ideas on how should I proceed? try pg_resetxlog from contrib. Regards, Bjoern
Hello Bjoern, Thanks for the tip. It worked to start the DB but when I issue: psql -l I can't see my databases :( Sorry for being such a pain but I am no postgresql expert and I really need to recover these databases. I know, "where's the backup" ... I wonder the same ... :(( /B On Wed, 14 May 2003, Björn Metzdorf wrote: > > It complains > > > > DEBUG: open of /usr2/pgsql/data/pg_xlog/0000000000000000 (log file 0, > > segment 0) failed: No such file or directory > > DEBUG: invalid primary checkpoint record > > DEBUG: open of /usr2/pgsql/data/pg_xlog/0000000000000000 (log file 0, > > segment 0) failed: No such file or directory > > DEBUG: invalid secondary checkpoint record > > > > But these files were not present in the 'data' directory from this > > server, only these: > > > > $ ls > > 00000010000000B7 00000010000000B8 00000010000000B9 00000010000000BA > > 00000010000000BB 00000010000000BC 00000010000000BD 00000010000000BE > > > > Any ideas on how should I proceed? > > try pg_resetxlog from contrib. > > Regards, > Bjoern > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- --- Bruno Mattarollo <bruno@web1.greenpeace.org> --- SysAdmin & TechLead - Greenpeace International http://www.greenpeace.org/ ----------------------------------------------------
Bruno Mattarollo <bruno@web1.greenpeace.org> writes:
> Thanks for the tip. It worked to start the DB but when I issue:
> psql -l
> I can't see my databases :(
You need to set the next transaction ID higher (pg_resetxlog -x).
I'd try 2 billion (2000000000) for starters.
You should probably also be setting the WAL start location (-l)
somewhere past the evidently-already-existing segments ("-l 2,0"
should do, I think).
Needless to say, initdb and reload as soon as you have managed to
get a good dump.
regards, tom lane
Hello Tom,
Thank you for this. Setting the transaction ID higher worked to allow me
to connect to the database but when I issue:
cybercentre=# select count(*) from users;
ERROR: XLogFlush: request 10/B7E93FCC is not satisfied --- flushed only
to 0/4000050
I get that error. Any idea? Should I just set the transaction ID even
higher? I have no idea what this error means ...
Thanks in advance,
/B
On Wed, 14 May 2003, Tom Lane wrote:
> Bruno Mattarollo <bruno@web1.greenpeace.org> writes:
> > Thanks for the tip. It worked to start the DB but when I issue:
> > psql -l
> > I can't see my databases :(
>
> You need to set the next transaction ID higher (pg_resetxlog -x).
> I'd try 2 billion (2000000000) for starters.
>
> You should probably also be setting the WAL start location (-l)
> somewhere past the evidently-already-existing segments ("-l 2,0"
> should do, I think).
>
> Needless to say, initdb and reload as soon as you have managed to
> get a good dump.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
--- Bruno Mattarollo <bruno@web1.greenpeace.org> ---
SysAdmin & TechLead - Greenpeace International
http://www.greenpeace.org/
----------------------------------------------------
Bruno Mattarollo <bruno@web1.greenpeace.org> writes:
> Thank you for this. Setting the transaction ID higher worked to allow me
> to connect to the database but when I issue:
> cybercentre=# select count(*) from users;
> ERROR: XLogFlush: request 10/B7E93FCC is not satisfied --- flushed only
> to 0/4000050
> I get that error. Any idea?
Looks like the starting WAL location needs to be higher than I thought.
Try "-l 100,0".
regards, tom lane
Hello Tom,
Thank you very much. This seems to have done the trick. I can now access
the database and did a pg_dump ... Will now load this data into a PGSQL
7.3.2 ...
Thanks again to you and Bjoern (previous email) for the help!!! :)
Cheers
/B
On Wed, 14 May 2003, Tom Lane wrote:
> Bruno Mattarollo <bruno@web1.greenpeace.org> writes:
> > Thank you for this. Setting the transaction ID higher worked to allow me
> > to connect to the database but when I issue:
>
> > cybercentre=# select count(*) from users;
> > ERROR: XLogFlush: request 10/B7E93FCC is not satisfied --- flushed only
> > to 0/4000050
>
> > I get that error. Any idea?
>
> Looks like the starting WAL location needs to be higher than I thought.
> Try "-l 100,0".
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
--
--- Bruno Mattarollo <bruno@web1.greenpeace.org> ---
SysAdmin & TechLead - Greenpeace International
http://www.greenpeace.org/
----------------------------------------------------