1.I'm not really sure if they have one but its not my business.. I'm just trying to help those guys with an application that is based on postgres...
2.Yeah I realized that it is an option, but do you really think that it should be the first solution ? I tried to search for bugs that seems identical to my case but I didnt find any.
3.I set the vacuum_freeze_table_age to 0 and vacuumed all the duplicated databases but it seems that it didn't solve their problem.
4.I tried to delete the records according to the value in xmin/xmax and the result was that after the delete postgresql didnt recognize that I have those databases. I queried the ctid and the xmin/xman :
postgres=# select xmin,xmax,datname,ctid from pg_Database;
xmin | xmax | datname | ctid
-------+----------+----------------+--------
1802 | 0 | template1 | (0,1)
1803 | 0 | template0 | (0,2)
1806 | 0 | postgres | (0,3)
1809 | 21093518 | db1 | (0,4)
1811 | 21077312 | db2 | (0,5)
2351 | 0 | db1 | (0,21)
3590 | 0 | db3 | (0,22)
3592 | 0 | db2 | (0,23)
12594 | 0 | db4 | (0,24)
I tried to delete db1 with xmax 0 and db2 with xmax 2 (the opposite of what you suggested with the ctid). Now, I thought that if the xmax is set to 0 it means that I "didnt have" any update / delete operations and therefore I should delete those databases. Does it matters which one to delete ? I read about the xmin/xmax/ctid columns but I thought that what I did was legit.
Moreover I realized that I have duplicated rows in more system tables so i don`t really like this solution.
My next attempt will be to upgrade to 9.2.24.
Thanks Tom ! ,
Regards Mariel.
בתאריך יום א׳, 26 באוג׳ 2018 ב-18:51 מאת Tom Lane <tgl@xxxxxxxxxxxxx>:
Mariel Cherkassky <mariel.cherkassky@xxxxxxxxx> writes:
> I'm trying to investigate a database of one of our clients. The database
> version is 9.2.5.
1. Fire their DBA for dereliction of duty.
2. Update to the last available release of 9.2.x (9.2.24, looks like).
3. Vacuum everything and see if it gets better.
Vacuuming may or may not fix the observed data corruption problems, but
it's silly to ignore the fact that they're missing four years worth
of bug fixes in that branch. In particular I'm noticing the first
entry in the change notes for 9.2.6, which recommends "vacuuming all
tables in all databases while having vacuum_freeze_table_age set to zero":
https://www.postgresql.org/docs/9.2/static/release-9-2-6.html
That problem as-described seems to involve rows disappearing, rather than
getting duplicated, but I wouldn't rule out that it could also have
symptoms like this.
If that doesn't fix things, you could then try:
4. Manually eliminate duplicate rows by identifying which one of each pair
seems older and deleting it with DELETE ... WHERE ctid = '(x,y)'. Then
reindex to confirm no duplicates remain.
But you still need step 2, to reduce the odds the problem will recur.
regards, tom lane
