Thread: [URGENT] Problems recovering database

[URGENT] Problems recovering database

From
Bruno Mattarollo
Date:
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/
----------------------------------------------------



Re: [URGENT] Problems recovering database

From
Björn Metzdorf
Date:
> 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


Re: [URGENT] Problems recovering database

From
Bruno Mattarollo
Date:
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/
----------------------------------------------------



Re: [URGENT] Problems recovering database

From
Tom Lane
Date:
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

Re: [URGENT] Problems recovering database

From
Bruno Mattarollo
Date:
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/
----------------------------------------------------



Re: [URGENT] Problems recovering database

From
Tom Lane
Date:
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

Re: [URGENT] Problems recovering database

From
Bruno Mattarollo
Date:
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/
----------------------------------------------------