Re: Beta2 Vacuum and pg_dump failures and mangled databases - Mailing list pgsql-hackers

"Mikheev, Vadim" wrote:
> 
> > [ . . . ]
> > > Restarting the server didn't make a difference.
> >
> > I upgraded to beta3 just now and the problem persists. I
> > didn't do an initdb obviously cuz
> > I cannot save the data via pg_dump. Beta3 will read beta2
> > data OK (I guess this means that
> > an initdb is not required when going from beta2 to beta3?!)
> > but I can't vacuum or dump on
> > any database.
> 
> So, server doesn't restart?

Yes, it does restart, that is not the problem (did I explain that properly?).

> Could add
> 
> wal_debug = 1
> 
> to postgresql.conf, start postmaster and send me stderr output?

I did add wal_debug = 1  to postgresql.conf. Starting up is OK, when I then try a vacuum
verbose on a database, it goes:

-------------------------------------- start log --------------------------------------
DEBUG:  database system is shut down
DEBUG:  starting up
DEBUG:  database system was shut down at 2001-01-12 20:11:37
DEBUG:  CheckPoint record at (0, 11629776)
DEBUG:  Redo record at (0, 11629776); Undo record at (0, 0); Shutdown TRUE
DEBUG:  NextTransactionId: 8284; NextOid: 98635
DEBUG:  database system is in production state
NOTICE:  --Relation pg_type--
NOTICE:  Pages 2: Changed 0, reaped 1, Empty 0, New 0; Tup 131: Vac 0, Keep/VTL 0/0, Crash
0, UnUsed 2, MinLen 106, MaxLen 109; Re-using: Free/Avail. Space 1428/0; EndEmpty/Avail.
Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_type_oid_index: Pages 2; Tuples 131: Deleted 0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_type_typname_index: Pages 2; Tuples 131: Deleted 0. CPU 0.00s/0.00u sec.
INSERT @ 0/11629840: prev 0/11629776; xprev 0/0; xid 8291; bkpb 1: Heap - clean: node
95464/1247; blk 1
XLogFlush: rqst 0/11638108; wrt 0/0; flsh 0/0
INSERT @ 0/11638108: prev 0/11629840; xprev 0/11629840; xid 8291: Transaction - commit:
2001-01-12 20:12:51
XLogFlush: rqst 0/11638144; wrt 0/11638108; flsh 0/11638108
NOTICE:  --Relation pg_attribute--
NOTICE:  Pages 9: Changed 0, reaped 1, Empty 0, New 0; Tup 649: Vac 0, Keep/VTL 0/0, Crash
0, UnUsed 18, MinLen 98, MaxLen 98; Re-using: Free/Avail. Space 5500/0; EndEmpty/Avail.
Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_attribute_relid_attnam_index: Pages 10; Tuples 649: Deleted 0. CPU
0.01s/0.00u sec.
NOTICE:  Index pg_attribute_relid_attnum_index: Pages 5; Tuples 649: Deleted 0. CPU
0.00s/0.00u sec.
INSERT @ 0/11638144: prev 0/11638108; xprev 0/0; xid 8292; bkpb 1: Heap - clean: node
95464/1249; blk 8
XLogFlush: rqst 0/11646412; wrt 0/11638144; flsh 0/11638144
INSERT @ 0/11646412: prev 0/11638144; xprev 0/11638144; xid 8292: Transaction - commit:
2001-01-12 20:12:52
XLogFlush: rqst 0/11646448; wrt 0/11646412; flsh 0/11646412
NOTICE:  --Relation pg_class--
NOTICE:  Pages 7: Changed 0, reaped 6, Empty 2, New 0; Tup 155: Vac 29, Keep/VTL 0/0,
Crash 0, UnUsed 81, MinLen 115, MaxLen 160; Re-using: Free/Avail. Space 37060/30444;
EndEmpty/Avail. Pages 0/5. CPU 0.00s/0.00u sec.
INSERT @ 0/11646448: prev 0/11646412; xprev 0/0; xid 8293; bkpb 1: Btree - delete: node
95464/17118; tid 1/81
INSERT @ 0/11654720: prev 0/11646448; xprev 0/11646448; xid 8293: Btree - delete: node
95464/17118; tid 1/81
INSERT @ 0/11654768: prev 0/11654720; xprev 0/11654720; xid 8293: Btree - delete: node
95464/17118; tid 1/81
INSERT @ 0/11654816: prev 0/11654768; xprev 0/11654768; xid 8293: Btree - delete: node
95464/17118; tid 1/82
INSERT @ 0/11654864: prev 0/11654816; xprev 0/11654816; xid 8293: Btree - delete: node
95464/17118; tid 1/83
INSERT @ 0/11654912: prev 0/11654864; xprev 0/11654864; xid 8293: Btree - delete: node
95464/17118; tid 1/84
INSERT @ 0/11654960: prev 0/11654912; xprev 0/11654912; xid 8293: Btree - delete: node
95464/17118; tid 1/85
INSERT @ 0/11655008: prev 0/11654960; xprev 0/11654960; xid 8293: Btree - delete: node
95464/17118; tid 1/86
INSERT @ 0/11655056: prev 0/11655008; xprev 0/11655008; xid 8293: Btree - delete: node
95464/17118; tid 1/87
INSERT @ 0/11655104: prev 0/11655056; xprev 0/11655056; xid 8293: Btree - delete: node
95464/17118; tid 1/88
NOTICE:  Index pg_class_oid_index: Pages 2; Tuples 88: Deleted 10. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_class_oid_index: NUMBER OF INDEX' TUPLES (88) IS NOT THE SAME AS HEAP'
(155).Recreate the index.
INSERT @ 0/11655152: prev 0/11655104; xprev 0/11655104; xid 8293; bkpb 1: Btree - delete:
node 95464/17121; tid 1/1
INSERT @ 0/11663424: prev 0/11655152; xprev 0/11655152; xid 8293: Btree - delete: node
95464/17121; tid 1/1
INSERT @ 0/11663472: prev 0/11663424; xprev 0/11663424; xid 8293: Btree - delete: node
95464/17121; tid 1/1
INSERT @ 0/11663520: prev 0/11663472; xprev 0/11663472; xid 8293: Btree - delete: node
95464/17121; tid 1/71
INSERT @ 0/11663568: prev 0/11663520; xprev 0/11663520; xid 8293: Btree - delete: node
95464/17121; tid 1/72
INSERT @ 0/11663616: prev 0/11663568; xprev 0/11663568; xid 8293: Btree - delete: node
95464/17121; tid 1/73
INSERT @ 0/11663664: prev 0/11663616; xprev 0/11663616; xid 8293: Btree - delete: node
95464/17121; tid 1/74
INSERT @ 0/11663712: prev 0/11663664; xprev 0/11663664; xid 8293: Btree - delete: node
95464/17121; tid 1/86
INSERT @ 0/11663760: prev 0/11663712; xprev 0/11663712; xid 8293: Btree - delete: node
95464/17121; tid 1/87
INSERT @ 0/11663808: prev 0/11663760; xprev 0/11663760; xid 8293: Btree - delete: node
95464/17121; tid 1/88
NOTICE:  Index pg_class_relname_index: Pages 2; Tuples 88: Deleted 10. CPU 0.00s/0.00u
sec.
NOTICE:  Index pg_class_relname_index: NUMBER OF INDEX' TUPLES (88) IS NOT THE SAME AS
HEAP' (155).Recreate the index.
INSERT @ 0/11663856: prev 0/11663808; xprev 0/11663808; xid 8293; bkpb 1: Heap - clean:
node 95464/1259; blk 6
INSERT @ 0/11672124: prev 0/11663856; xprev 0/11663856; xid 8293; bkpb 1: Heap - clean:
node 95464/1259; blk 1
INSERT @ 0/11680392: prev 0/11672124; xprev 0/11672124; xid 8293: Heap - move: node
95464/1259; tid 6/3; new 1/1
INSERT @ 0/11680592: prev 0/11680392; xprev 0/11680392; xid 8293: Btree - insert: node
95464/17118; tid 1/81
INSERT @ 0/11680652: prev 0/11680592; xprev 0/11680592; xid 8293: Btree - insert: node
95464/17121; tid 1/1
INSERT @ 0/11680740: prev 0/11680652; xprev 0/11680652; xid 8293: Heap - move: node
95464/1259; tid 6/5; new 1/2
INSERT @ 0/11680940: prev 0/11680740; xprev 0/11680740; xid 8293: Btree - insert: node
95464/17118; tid 1/81
INSERT @ 0/11681000: prev 0/11680940; xprev 0/11680940; xid 8293: Btree - insert: node
95464/17121; tid 1/1
INSERT @ 0/11681088: prev 0/11681000; xprev 0/11681000; xid 8293: Heap - move: node
95464/1259; tid 6/11; new 1/3
INSERT @ 0/11681288: prev 0/11681088; xprev 0/11681088; xid 8293: Btree - insert: node
95464/17118; tid 1/83
INSERT @ 0/11681348: prev 0/11681288; xprev 0/11681288; xid 8293: Btree - insert: node
95464/17121; tid 1/88
INSERT @ 0/11681436: prev 0/11681348; xprev 0/11681348; xid 8293: Heap - move: node
95464/1259; tid 6/17; new 1/4
INSERT @ 0/11681596: prev 0/11681436; xprev 0/11681436; xid 8293: Btree - insert: node
95464/17118; tid 1/87
INSERT @ 0/11681656: prev 0/11681596; xprev 0/11681596; xid 8293: Btree - insert: node
95464/17121; tid 1/74
INSERT @ 0/11681744: prev 0/11681656; xprev 0/11681656; xid 8293: Heap - move: node
95464/1259; tid 6/18; new 1/5
INSERT @ 0/11681916: prev 0/11681744; xprev 0/11681744; xid 8293: Btree - insert: node
95464/17118; tid 1/86
INSERT @ 0/11681976: prev 0/11681916; xprev 0/11681916; xid 8293: Btree - insert: node
95464/17121; tid 1/73
INSERT @ 0/11682064: prev 0/11681976; xprev 0/11681976; xid 8293: Heap - move: node
95464/1259; tid 6/28; new 1/6
INSERT @ 0/11682224: prev 0/11682064; xprev 0/11682064; xid 8293: Btree - insert: node
95464/17118; tid 1/92
INSERT @ 0/11682284: prev 0/11682224; xprev 0/11682224; xid 8293: Btree - insert: node
95464/17121; tid 1/78
INSERT @ 0/11682372: prev 0/11682284; xprev 0/11682284; xid 8293: Heap - move: node
95464/1259; tid 6/29; new 1/7
INSERT @ 0/11682532: prev 0/11682372; xprev 0/11682372; xid 8293: Btree - insert: node
95464/17118; tid 1/91
INSERT @ 0/11682592: prev 0/11682532; xprev 0/11682532; xid 8293: Btree - insert: node
95464/17121; tid 1/77
INSERT @ 0/11682680: prev 0/11682592; xprev 0/11682592; xid 8293: Heap - move: node
95464/1259; tid 6/33; new 1/8
INSERT @ 0/11682880: prev 0/11682680; xprev 0/11682680; xid 8293: Btree - insert: node
95464/17118; tid 1/90
INSERT @ 0/11682940: prev 0/11682880; xprev 0/11682880; xid 8293: Btree - insert: node
95464/17121; tid 1/3
INSERT @ 0/11683028: prev 0/11682940; xprev 0/11682940; xid 8293: Heap - move: node
95464/1259; tid 6/36; new 1/9
INSERT @ 0/11683188: prev 0/11683028; xprev 0/11683028; xid 8293: Btree - insert: node
95464/17118; tid 1/96
INSERT @ 0/11683248: prev 0/11683188; xprev 0/11683188; xid 8293: Btree - insert: node
95464/17121; tid 1/96
INSERT @ 0/11683336: prev 0/11683248; xprev 0/11683248; xid 8293: Heap - move: node
95464/1259; tid 6/37; new 1/10
INSERT @ 0/11683536: prev 0/11683336; xprev 0/11683336; xid 8293: Btree - insert: node
95464/17118; tid 1/85
INSERT @ 0/11683596: prev 0/11683536; xprev 0/11683536; xid 8293: Btree - insert: node
95464/17121; tid 1/95
INSERT @ 0/11683684: prev 0/11683596; xprev 0/11683596; xid 8293; bkpb 1: Heap - clean:
node 95464/1259; blk 5
INSERT @ 0/11691952: prev 0/11683684; xprev 0/11683684; xid 8293: Heap - move: node
95464/1259; tid 5/1; new 1/11
ERROR:  Cannot insert a duplicate key into unique index pg_class_oid_index
-------------------------------------- start log --------------------------------------

Which makes me pause . . . are OIDs unique per database or per PostgreSQL installation? I
think per database. Therefore if databases are mangled togeher, then things would be
pretty messed up, oid-wise. Maybe I did something really stupid on  importing mpi into
this new installation, but I don't think so. Basically what I did was 

>createdb mpi 

and then 

>psql -e mpi < whatevernameIassignedtothefilewhichIdumpedtheorignaldatabaseinto

I managed to rescue my data via COPY but if this is a 7.1-related error and not
Frank-confusedness, then it looks like an evil issue indeed.

Regards, Frank