Thread: possible data loss.

I did an vacuum full on a particular table of
pgsql 7.2.3 database as a  non superuser.
and i feel i have lost considerable data in this
text feild in this table. I think it was a known bug with
this version.

btw there was a # sign in the prompt does it
not mean the connected user was a super user?

THE IMPORTANT QUESTION IS IS THERE ANYTHING
I CAN DO BEFORE RESORTING TO THE LAST
BACKUP ?

(sorry for the caps though )





rt2=# VACUUM VERBOSE  attachments ;
NOTICE:  --Relation attachments--
NOTICE:  Pages 9895: Changed 0, Empty 0; Tup 91902: Vac 0, Keep 0, UnUsed 0.
        Total CPU 0.42s/0.04u sec elapsed 0.47 sec.
NOTICE:  --Relation pg_toast_180723--
NOTICE:  Pages 27218: Changed 5637, Empty 0; Tup 112728: Vac 0, Keep 0, UnUsed 0.
        Total CPU 1.40s/0.15u sec elapsed 2.43 sec.
VACUUM
rt2=# VACUUM FULL VERBOSE  attachments ;
NOTICE:  --Relation attachments--

NOTICE:  Pages 9895: Changed 0, reaped 0, Empty 0, New 0; Tup 91902: Vac 0, Keep/VTL 0/0, UnUsed
0, MinLen 96, MaxLen 2032; Re-using: Free/Avail. Space 5710720/5628156; EndEmpty/Avail. Pages
0/7940.        CPU 0.49s/0.07u sec elapsed 0.57 sec.
NOTICE:  Index attachments_pkey: Pages 315; Tuples 91902.
        CPU 0.02s/0.01u sec elapsed 13.03 sec.
NOTICE:  Index attachments1: Pages 243; Tuples 91902.
        CPU 0.01s/0.00u sec elapsed 1.24 sec.
NOTICE:  Index attachments2: Pages 243; Tuples 91902.
        CPU 0.01s/0.00u sec elapsed 0.81 sec.
NOTICE:  Index attachments3: Pages 333; Tuples 91902.
        CPU 0.02s/0.00u sec elapsed 1.21 sec.
NOTICE:  Rel attachments: Pages: 9895 --> 9635; Tuple(s) moved: 2202.
        CPU 0.13s/0.40u sec elapsed 1.71 sec.
NOTICE:  Index attachments_pkey: Pages 323; Tuples 91902: Deleted 2202.
        CPU 0.03s/0.03u sec elapsed 0.09 sec.
NOTICE:  Index attachments1: Pages 249; Tuples 91902: Deleted 2202.
        CPU 0.02s/0.03u sec elapsed 0.20 sec.

NOTICE:  Index attachments2: Pages 250; Tuples 91902: Deleted 2202.
        CPU 0.03s/0.02u sec elapsed 0.32 sec.
NOTICE:  Index attachments3: Pages 339; Tuples 91902: Deleted 2202.
        CPU 0.03s/0.03u sec elapsed 0.14 sec.
NOTICE:  --Relation pg_toast_180723--
NOTICE:  Pages 27218: Changed 0, reaped 0, Empty 0, New 0; Tup 112728: Vac 0, Keep/VTL 0/0, UnUsed
0, MinLen 45, MaxLen 2034; Re-using: Free/Avail. Space 6256152/6005660; EndEmpty/Avail. Pages
0/6484.        CPU 1.11s/0.11u sec elapsed 1.22 sec.
NOTICE:  Index pg_toast_180723_idx: Pages 433; Tuples 112728.
        CPU 0.02s/0.00u sec elapsed 0.02 sec.
NOTICE:  Rel pg_toast_180723: Pages: 27218 --> 27213; Tuple(s) moved: 24.
        CPU 0.00s/0.00u sec elapsed 0.66 sec.
NOTICE:  Index pg_toast_180723_idx: Pages 433; Tuples 112728: Deleted 24.
        CPU 0.00s/0.02u sec elapsed 0.02 sec.
VACUUM









VACUUM FULL AGAIN AS SUPER USER.



rt2=# VACUUM FULL VERBOSE  attachments;
NOTICE:  --Relation attachments--
NOTICE:  Pages 20302: Changed 0, reaped 11356, Empty 0, New 0; Tup 91906: Vac 82457, Keep/VTL 0/0,
UnUsed 0, MinLen 96, MaxLen 2032; Re-using: Free/Avail. Space 82558712/82517284; EndEmpty/Avail.
Pages 0/19392.        CPU 0.90s/0.16u sec elapsed 1.06 sec.
NOTICE:  Index attachments_pkey: Pages 565; Tuples 91906: Deleted 82457.
        CPU 0.07s/0.66u sec elapsed 1.14 sec.
NOTICE:  Index attachments1: Pages 506; Tuples 91906: Deleted 82457.
        CPU 0.06s/0.68u sec elapsed 0.76 sec.
NOTICE:  Index attachments2: Pages 537; Tuples 91906: Deleted 82457.
        CPU 0.09s/0.70u sec elapsed 4.63 sec.
NOTICE:  Index attachments3: Pages 658; Tuples 91906: Deleted 82457.
        CPU 0.11s/0.66u sec elapsed 1.26 sec.

NOTICE:  Index content_idx_gist: Pages 3164; Tuples 53411: Deleted 0.
        CPU 0.17s/0.10u sec elapsed 5.73 sec.


NOTICE:  Rel attachments: Pages: 20302 --> 10363; Tuple(s) moved: 63483.
        CPU 7.50s/69.25u sec elapsed 195.07 sec.
NOTICE:  Index attachments_pkey: Pages 565; Tuples 91906: Deleted 63483.
        CPU 0.12s/0.51u sec elapsed 1.01 sec.
NOTICE:  Index attachments1: Pages 584; Tuples 91906: Deleted 63483.
        CPU 0.08s/0.48u sec elapsed 17.82 sec.
NOTICE:  Index attachments2: Pages 537; Tuples 91906: Deleted 63483.
        CPU 0.10s/0.54u sec elapsed 8.62 sec.
NOTICE:  Index attachments3: Pages 658; Tuples 91906: Deleted 63483.
        CPU 0.10s/0.52u sec elapsed 1.40 sec.
NOTICE:  Index content_idx_gist: Pages 6264; Tuples 53411: Deleted 51628.
        CPU 0.49s/0.36u sec elapsed 6.22 sec.
NOTICE:  --Relation pg_toast_180723--
NOTICE:  Pages 37107: Changed 9896, reaped 980, Empty 0, New 0; Tup 154940: Vac 5192, Keep/VTL
0/0, UnUsed 0, MinLen 45, MaxLen 2034; Re-using: Free/Avail. Space 18494596/18201812;
EndEmpty/Avail. Pages 0/13028.        CPU 2.07s/0.21u sec elapsed 2.34 sec.
NOTICE:  Index pg_toast_180723_idx: Pages 613; Tuples 154940: Deleted 5192.
        CPU 0.05s/0.06u sec elapsed 0.11 sec.
NOTICE:  Rel pg_toast_180723: Pages: 37107 --> 35872; Tuple(s) moved: 5962.
        CPU 0.71s/1.16u sec elapsed 39.68 sec.
NOTICE:  Index pg_toast_180723_idx: Pages 636; Tuples 154940: Deleted 5962.
        CPU 0.06s/0.06u sec elapsed 0.12 sec.














-----------------------------------------
Get your free web based email at trade-india.com.
   "India's Leading B2B eMarketplace.!"
http://www.trade-india.com/

<mallah@trade-india.com> writes:
> I did an vacuum full on a particular table of
> pgsql 7.2.3 database as a  non superuser.
> and i feel i have lost considerable data in this
> text feild in this table. I think it was a known bug with
> this version.

The vacuum output shows 91902 live tuples during the first vacuum,
and 91906 during the second.  What is the basis of your assertion
that you've lost data?

It does appear that some other operation updated most of the rows in
the table in between, since the second vacuum found 82457 dead tuples.
I'm suspecting that your real problem is an ill-considered update.

            regards, tom lane

~~~~~~~~~~~~~~~~~~~~~~~
There was no data loss.
~~~~~~~~~~~~~~~~~~~~~~~

This message was posted during the disturbance
period in our mailing lists. hence could not
update on this .

In fact i was under the impression that it would
have never reached the lists.


My false assertion was based on disappearance of the
records from the user interface of a certain application
which did not bother to display errors to browser from
the database. It was only when i peeked into postgres
log that i realized whats going on.


Actaully i had added a txtidx feild in certain table from
which distinct * was being performed since txtidx type did
not have < or > operators it was rasing errors.
later i removed that column (by some means) and the problem
got corrected.

I got the shock of my life as i really did not have
backups but now i started. my be lord was kind enough
to make me realize timely on the lack of back ups.

Regds
mallah.


On Sunday 08 Jun 2003 9:04 pm, Tom Lane wrote:
> <mallah@trade-india.com> writes:
> > I did an vacuum full on a particular table of
> > pgsql 7.2.3 database as a  non superuser.
> > and i feel i have lost considerable data in this
> > text feild in this table. I think it was a known bug with
> > this version.
>
> The vacuum output shows 91902 live tuples during the first vacuum,
> and 91906 during the second.  What is the basis of your assertion
> that you've lost data?
>
> It does appear that some other operation updated most of the rows in
> the table in between, since the second vacuum found 82457 dead tuples.
> I'm suspecting that your real problem is an ill-considered update.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.