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
Import Notes
Resolved by subject fallback
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
Show quoted text
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 castQuery2:
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 castQuery3:
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 castthanks,
sandeep