Thread: backend corruption

Hello Everybody,

I've been whirling along with my new postgresql database (converted from
mysql before Christmas) and have been very pleased.  Until a few days ago .
I was sittin there minding my own business WHEN ... (dot dot dot)

I was goofing around attempting to install new UDF's and in the process,
didn't notice that the backend had died.  I'm not exactly sure when it died,
and since I had been sending output etc to /dev/null
(postmaster -i -o -F -S), not sure why it died, either.  I was/am using the
snapshot as my working environment.  Anyway, I started reading the list
archives and found lots of mentions of bad backend situations.  Apparently
my pg_control was corrupted (?), there were no databases listed in my
pg_databases and some index tree was corrupted.  (There was a thread a bit
ago about lack of documentation about certain postgresql features -- how
about *anything* regarding system tables ...)

So, somewhat blindly, I did a:

rm pg_control
initdb

Apparently, one other time someone had done this, then later it was
suggested s/he should attempt to re-construct pg_shadow and pg_databases.
But then, someone said, no way, can't do that, cause you can't restore a
database from those unnamed directories/files (which I *thought* were the
databases), you had to have the x_logs intact too.

So basically, I've wiped all my databases, it looks like, and no, there is
no directory/structure which atomically can be called "your database", other
than the ENTIRE data/base directory.

I have to just say this aspect of the mysql --> postgresql learning curve is
the most difficult and painful to grasp.  pg_dump is *not* an optional thing
like mysqldump is (for mysql) ...  you can't recover/backup a database just
by what's found in those numbered subdirectories.

If I've left a stone unturned, please let me know.
Thanks,
George Johnson

On Sat, 6 Jan 2001, George Johnson wrote:

> So basically, I've wiped all my databases, it looks like, and no, there is
> no directory/structure which atomically can be called "your database", other
> than the ENTIRE data/base directory.

Sure there is -- under my $PGHOME/data/base, each separate database on my
system is a sub-directory, which contain the system dictionaries, tables,
indexes, etc.

-- Brett
                                     http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
"Just think of a computer as hardware you can program."
-- Nigel de la Tierre

"Brett W. McCoy" <bmccoy@chapelperilous.net> writes:
> On Sat, 6 Jan 2001, George Johnson wrote:
>> So basically, I've wiped all my databases, it looks like, and no, there is
>> no directory/structure which atomically can be called "your database", other
>> than the ENTIRE data/base directory.

> Sure there is -- under my $PGHOME/data/base, each separate database on my
> system is a sub-directory, which contain the system dictionaries, tables,
> indexes, etc.

Yeah, but those files are only half the truth.  The other half lives in
pg_log and the installation-wide tables (pg_database, etc).  George is
correct: you cannot recover using only the contents of $PGDATA/base/foo.
You really need all of $PGDATA.

I think that under 7.1, pg_log is not so critical anymore, but I'm not
sure.  Vadim, any comment?

            regards, tom lane

On Sat, 6 Jan 2001, Tom Lane wrote:

> Yeah, but those files are only half the truth.  The other half lives in
> pg_log and the installation-wide tables (pg_database, etc).  George is
> correct: you cannot recover using only the contents of $PGDATA/base/foo.
> You really need all of $PGDATA.

Really?  I stand corrected.

How does MySQL do things?

-- Brett
                                     http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
There is not much to choose between a woman who deceives us for another,
and a woman who deceives another for ourselves.
        -- Augier

> I think that under 7.1, pg_log is not so critical anymore, but I'm not
> sure.  Vadim, any comment?

Still critical till we implement UNDO and true changes rollback on
transaction abort.

Vadim

>

What is wrong with following queries?

Query1:
delete from zuser where userid = 1 and user_setid = 1;

ERROR:   ERROR:  Unable to identify an operator '&&' for types 'int4' and
'int4'
        You will have to retype this query using an explicit cast

Query2:
delete from zuser where userid = '1'::int4 and user_setid = '1'::int4;

ERROR:   ERROR:  Unable to identify an operator '&&' for types 'int4' and
'int4'
        You will have to retype this query using an explicit cast

Query3:
delete from zuser where userid = '1'::integer and user_setid =
'1'::integer;

ERROR:   ERROR:  Unable to identify an operator '&&' for types 'int4' and
'int4'
        You will have to retype this query using an explicit cast


thanks,
sandeep

Sorry, my mistake.


Sandeep


> >
>
> What is wrong with following queries?
>
> Query1:
> delete from zuser where userid = 1 and user_setid = 1;
>
> ERROR:   ERROR:  Unable to identify an operator '&&' for types 'int4' and
> 'int4'
>         You will have to retype this query using an explicit cast
>
> Query2:
> delete from zuser where userid = '1'::int4 and user_setid = '1'::int4;
>
> ERROR:   ERROR:  Unable to identify an operator '&&' for types 'int4' and
> 'int4'
>         You will have to retype this query using an explicit cast
>
> Query3:
> delete from zuser where userid = '1'::integer and user_setid =
> '1'::integer;
>
> ERROR:   ERROR:  Unable to identify an operator '&&' for types 'int4' and
> 'int4'
>         You will have to retype this query using an explicit cast
>
> thanks,
> sandeep