Corrupt DB
Corrupt DB
От:
"Simpson" <asimpson@i-55.com>
Дата:
Greetings, I am running Postgresql 8.0 on "Linux xxx 2.4.18-14 #1 Wed Sep 4 12:13:11 EDT 2002 i686 athlon i386 GNU/Linux" and while loading data my program received: DBD::Pg::db do failed: server closed the connection unexpectedly ... Now I can't access any data in the DB, not even the \d command: mydb=# \d ERROR: could not identify an ordering operator for type name HINT: Use an explicit ordering operator or modify the query. I tried vacuum: mydb=# vacuum; ERROR: could not open relation 1663/12649295/16396: No such file or directory What would be the best way to fix this? How would I determine what caused the corruption and prevent it from happening again? Thanks for any assistance, Drew.
Re: Corrupt DB
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
"Simpson" writes: > mydb=# vacuum; > ERROR: could not open relation 1663/12649295/16396: No such file or > directory > What would be the best way to fix this? [ checks catalogs... ] In 8.0.*, 16396 is pg_am which is basically constant, so you could replace that file by copying it out of another database, eg cp $PGDATA/base/1663/1/16396 $PGDATA/base/1663/12649295/16396 After that I would try a REINDEX DATABASE in a standalone backend, to repair any damage to the system catalog indexes. Then start the postmaster and see if you can pg_dump and reload the database. VACUUM is *not* a good idea if you have any doubts about the consistency of the database. It's quite likely though that there is more damage and that this procedure will not get you out of trouble :-(. You apparently have got either a flaky disk drive or severe kernel bugs, because files don't just disappear without cause. The kernel version you mentioned looks a bit old, so updating to something more recent would be a good idea. regards, tom lane
pg_enconding
От:
Dextra - Gustavo Bartz Guedes <gustavo-bartz@dextra.com.br>
Дата:
The version 8.0 has not the pg_encoding tool, so how can I find a encoding by its number on pg_database.enconding? Thanks.
RES: pg_enconding
От:
Gustavo Franklin Nóbrega - Planae<gfnobrega@planae.com.br>
Дата:
Hi Gustavo,
pg_encoding has been removed, because is not need anymore. Try this
query to see what encoding a database have.
SELECT pg_database.datname as "Database",
pg_user.usename as "Owner",
pg_encoding_to_char(pg_database.encoding) as "Encoding"FROM
pg_database, pg_user
WHERE pg_database.datdba = pg_user.usesysid
UNION
SELECT pg_database.datname as "Database",
NULL as "Owner",
pg_encoding_to_char(pg_database.encoding) as "Encoding"FROM
pg_database
WHERE pg_database.datdba NOT IN (SELECT usesysid FROM pg_user)
ORDER BY "Database";
This query was copied from
http://archives.postgresql.org/pgsql-php/2001-10/msg00071.php. Thanks to
Alessandro Ferrarin.
Gustavo Franklin Nóbrega
gfnobrega@planae.com.br
Infraestrutura e Banco de Dados
Planae Tecnologia da Informação
(+55) 14 2106-3514
http://www.planae.com.br
-----Mensagem original-----
De: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] Em nome de Dextra - Gustavo Bartz
Guedes
Enviada em: segunda-feira, 18 de abril de 2005 13:35
Para: pgsql-admin@postgresql.org
Assunto: [ADMIN] pg_enconding
The version 8.0 has not the pg_encoding tool, so how can I find a
encoding by its number on pg_database.enconding?
Thanks.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: pg_enconding
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Dextra - Gustavo Bartz Guedes writes: > The version 8.0 has not the pg_encoding tool, so how can I find a > encoding by its number on pg_database.enconding? See pg_char_to_encoding() and pg_encoding_to_char(). For instance select datname,pg_encoding_to_char(encoding) from pg_database; regards, tom lane