Thread: from 6.5 > 7.1, cannot pg_dumpall.
hi,
i'm ready to upgrade (i know) to 7.1, but i have a big problem in my
[PostgreSQL 6.5.3] :
medias=> select * from contacts_secteurs;
... freeze ...
medias=> select * from contacts_secteurs where contact_id = 534;
contact_id|soussecteur_id|secteur_id
----------+--------------+----------
534| 118| 6
534| 114| 1
(2 rows)
so, i cannot pg_dump this database = cannot upgrade !
the vacuum command freeze also, but wait, i just try it, and here the result
:
medias=> vacuum;
NOTICE: Rel contacts: TID 223/38: OID IS INVALID. TUPGONE 1.
NOTICE: Rel medias_categories: TID 28/81: OID IS INVALID. TUPGONE 0.
NOTICE: Rel contacts_secteurs: Uninitialized page 66 - fixing
NOTICE: Rel contacts_secteurs: Uninitialized page 67 - fixing
...
NOTICE: Rel contacts_secteurs: Uninitialized page 526 - fixing
NOTICE: Rel contacts_secteurs: Uninitialized page 527 - fixing
NOTICE: Index contacts_secteurs_contact_id_ke: pointer to EmptyPage (blk
527 off 169) - fixing
... freeze ...
like i said in past post (no answer), i don't want to loose data in my
upgrade, can someone try to help me to fix this, so i can be like you with a
good and stable version. if possible, this week-end, cause i have a vacation
on monday.
patrick
Patrick, A few questions How big is this database? (how many rows?) Do you have more than one table? I had a similar problem, I wrote a small program that extracted the data to a flat file, then I pushed it back out to a new database (after I upgraded). This only works if the schema is not very complicated. Ted -----Original Message----- From: Patrick Coulombe <pcoulombe@mediacces.com> To: pgsql-novice@postgresql.org Date: Sat, 06 Oct 2001 14:28:39 -0400 Subject: [NOVICE] from 6.5 > 7.1, cannot pg_dumpall. > hi, > > i'm ready to upgrade (i know) to 7.1, but i have a big problem in my > [PostgreSQL 6.5.3] : > > medias=> select * from contacts_secteurs; > .... freeze ... > > medias=> select * from contacts_secteurs where contact_id = 534; > contact_id|soussecteur_id|secteur_id > ----------+--------------+---------- > 534| 118| 6 > 534| 114| 1 > (2 rows) > > so, i cannot pg_dump this database = cannot upgrade ! > > > the vacuum command freeze also, but wait, i just try it, and here the > result > : > > medias=> vacuum; > NOTICE: Rel contacts: TID 223/38: OID IS INVALID. TUPGONE 1. > NOTICE: Rel medias_categories: TID 28/81: OID IS INVALID. TUPGONE 0. > NOTICE: Rel contacts_secteurs: Uninitialized page 66 - fixing > NOTICE: Rel contacts_secteurs: Uninitialized page 67 - fixing > .... > NOTICE: Rel contacts_secteurs: Uninitialized page 526 - fixing > NOTICE: Rel contacts_secteurs: Uninitialized page 527 - fixing > NOTICE: Index contacts_secteurs_contact_id_ke: pointer to EmptyPage > (blk > 527 off 169) - fixing > .... freeze ... > > > like i said in past post (no answer), i don't want to loose data in my > upgrade, can someone try to help me to fix this, so i can be like you > with a > good and stable version. if possible, this week-end, cause i have a > vacation > on monday. > > patrick > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
Patrick Coulombe <pcoulombe@mediacces.com> writes:
> NOTICE: Index contacts_secteurs_contact_id_ke: pointer to EmptyPage (blk
> 527 off 169) - fixing
> ... freeze ...
Try dropping that index.
regards, tom lane
> How big is this database? (how many rows?) around 10000 rows > Do you have more than one table? 2 big tables (10-16 col) and 18 small tables (2-3 col). only 1 table cause me trouble : medias=> \d contacts_secteurs Table = contacts_secteurs +----------------------------------+----------------------------------+----- --+ | Field | Type | Length| +----------------------------------+----------------------------------+----- --+ | contact_id | int4 | 4 | | soussecteur_id | int2 | 2 | | secteur_id | int2 | 2 | +----------------------------------+----------------------------------+----- --+ Index: contacts_secteurs_contact_id_ke and it seem that the contacts_secteurs_contact_id_ke is causing also trouble. can i "reload" it ? patrick
> Try dropping that index. done. but, when i try to recreate it : medias=> create index contacts_secteurs_contact_id_ke on contacts_secteurs (contact_id); ... freeze ... what about if i drop it, and then vacuum or look into this table for null value and other bad data. and after recreate it ? it's safe to vacuum my bd after dropping an index ? patrick
Patrick Coulombe <pcoulombe@mediacces.com> writes:
> what about if i drop it, and then vacuum or look into this table for null
> value and other bad data. and after recreate it ?
Sorry, I guess I wasn't clear: that's what I meant you to do. I'd
actually not bother with recreating the index at all, at least not
till you've gotten your data moved over to 7.1.
> it's safe to vacuum my bd after dropping an index ?
Sure. The data in the table is all you should care about at this point.
However, I think you should resign yourself to the likelihood that
you've lost data: you've got a pretty badly hosed database there,
it would seem. How recent is your last pg_dump backup?
regards, tom lane
> How recent is your last pg_dump backup? 2 weeks... i have more recent versions, but i think those versions are like actual one. the table that cause me trouble is contacts_secteurs. i have take a look at a copy of this table that i did past week. 26 meg of null value! how can i remove null value from this table is this query doesn't work : medias=> delete from contacts_secteurs where contact_id is null; ERROR: heap_delete: (am)invalid tid (the index is drop) patrick