Re: Could not open file "pg_subtrans/01EB"

So As Tom Lane suggested I upgraded the database to the 9.2.24 version. I
vacuumed (vacuum verbose) all the databases and for one of the databases
(that has a duplicated record in pg_database) I got the next meesages for
two of the obejcts :
2018-08-27 16:57:59 +08 db4 22026 WARNING: relation "a" page 1560 is
uninitialized --- fixing
2018-08-27 16:58:00 +08 db4 22026 WARNING: relation "a" page 1561 is
uninitialized --- fixing
2018-08-27 16:58:00 +08 db4 22026 WARNING: relation "a" page 1562 is
uninitialized --- fixing
2018-08-27 16:58:00 +08 db4 22026 WARNING: relation "a" page 1563 is
uninitialized --- fixing
2018-08-27 16:58:00 +08 db4 22026 WARNING: relation "a" page 1564 is
uninitialized --- fixing
2018-08-27 16:58:00 +08 db4 22026 WARNING: relation "a" page 1565 is
uninitialized --- fixing
2018-08-27 16:58:00 +08 db4 22026 WARNING: relation "a" page 1566 is
uninitialized --- fixing
2018-08-27 16:58:00 +08 db4 22026 WARNING: relation "a" page 1567 is
uninitialized --- fixing
2018-08-27 16:58:00 +08 db4 22026 WARNING: relation "a" page 1568 is
uninitialized --- fixing
2018-08-27 16:58:00 +08 db4 22026 WARNING: relation "a" page 1569 is
uninitialized --- fixing
2018-08-27 16:58:23 +08 db4 22026 WARNING: relation "b" page 1031 is
uninitialized --- fixing
2018-08-27 16:58:23 +08 db4 22026 WARNING: relation "b" page 1032 is
uninitialized --- fixing
2018-08-27 16:58:23 +08 db4 22026 WARNING: relation "b" page 1033 is
uninitialized --- fixing
2018-08-27 16:58:23 +08 db4 22026 WARNING: relation "b" page 1034 is
uninitialized --- fixing
2018-08-27 16:58:23 +08 db4 22026 WARNING: relation "b" page 1035 is
uninitialized --- fixing
2018-08-27 16:58:23 +08 db4 22026 WARNING: relation "b" page 1036 is
uninitialized --- fixing
2018-08-27 16:58:23 +08 db4 22026 WARNING: relation "b" page 1037 is
uninitialized --- fixing

I tried re indexing the problematic databases :
db1>reindex system db1
2018-08-27 17:09:44 +08 db1 23218 ERROR: could not access status of
transaction 32212695
2018-08-27 17:09:44 +08 db1 23218 DETAIL: Could not open file
"pg_subtrans/01EB": No such file or directory.

I tried to delete the duplicated rows in pg_database but the delete doesnt
delete anything :

select ctid,xmin,xmax,datname from pg_database order by datname;
ctid | xmin | xmax | datname
--------+-------+----------+----------------
(0,21) | 2351 | 0 | db1
(0,4) | 1809 | 21093518 | db1
(0,3) | 1806 | 0 | postgres
(0,24) | 12594 | 0 | db2
(0,2) | 1803 | 0 | template0
(0,1) | 1802 | 0 | template1
(0,22) | 3590 | 0 | db3
(0,23) | 3592 | 0 | db4
(0,5) | 1811 | 21077312 | db4
(9 rows)

As you can see db1 and db4 have duplicated records. I tried to delete them
:
delete from pg_database where ctid='(0,4)';
DELETE 0

but the record does exist :
select ctid,datname from pg_database where ctid='(0,4)';
ctid | datname
-------+---------
(0,4) | db1
(1 row)

I set the zero_damaged_pages to on but it didnt help either.

How can I continue ?

‫בתאריך יום א׳, 26 באוג׳ 2018 ב-19:42 מאת ‪Mariel Cherkassky‬‏ <‪
mariel(dot)cherkassky(at)gmail(dot)com‬‏>:‬

> 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(at)sss(dot)pgh(dot)pa(dot)us
> ‬‏>:‬
>
>> Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> 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
>>
>