Re: BUG #12617: DETAIL: Could not read from file "pg_subtrans/06F8" at offset 90112: Success. - Mailing list pgsql-bugs

On Wed, Jan 21, 2015 at 1:07 PM, Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

> davividal@gmail.com wrote:
>
> > My scenario: I have a daily physical backup from my production server
> > (9.1).
> > I create a 9.1 cluster from it and upgrade it to 9.4 daily. After that, I
> > deploy a bunch of changes using sqitch (sqitch.org). Again: I do it on a
> > daily basis, but this is the first time I faced this issue:
> >
> > $ sqitch deploy -t sandbox views/sistema.sf_guard_user@HEAD
> > Deploying changes through views/sistema.sf_guard_user to sandbox
> >   + data_migration/rate_plan.payment_policies ...............
> > psql:sql/deploy/data_migration/rate_plan.payment_policies.sql:21: ERROR:
> > could not access status of transaction 116940611
> > DETAIL:  Could not read from file "pg_subtrans/06F8" at offset 90112:
> > Success.
> > CONTEXT:  while updating tuple (6302,20) in relation
> "rate_daily_policies"
> > not ok
>
> Can you please paste the output of pg_controldata on both clusters?
>
>
Sure:

# /usr/pgsql-9.1/bin/pg_controldata /var/lib/pgsql/9.1/data
pg_control version number:            903
Catalog version number:               201105231
Database system identifier:           6084433861875394175
Database cluster state:               in production
pg_control last modified:             Tue Jan 20 03:01:14 2015
Latest checkpoint location:           7E/4C3989F8
Prior checkpoint location:            7E/49B6F770
Latest checkpoint's REDO location:    7E/49C3C350
Latest checkpoint's TimeLineID:       1
Latest checkpoint's NextXID:          0/116927458
Latest checkpoint's NextOID:          2168506
Latest checkpoint's NextMultiXactId:  899337
Latest checkpoint's NextMultiOffset:  2295779
Latest checkpoint's oldestXID:        1875
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  116927458
Time of latest checkpoint:            Tue Jan 20 03:00:01 2015
Minimum recovery ending location:     0/0
Backup start location:                0/0
Current wal_level setting:            hot_standby
Current max_connections setting:      1200
Current max_prepared_xacts setting:   64
Current max_locks_per_xact setting:   64
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value



# /usr/pgsql-9.4/bin/pg_controldata /var/lib/pgsql/9.4/data/
pg_control version number:            942
Catalog version number:               201409291
Database system identifier:           6106609909976182597
Database cluster state:               in production
pg_control last modified:             Thu Jan 22 10:48:57 2015
Latest checkpoint location:           7F/8F000830
Prior checkpoint location:            7F/8F000758
Latest checkpoint's REDO location:    7F/8F0007F8
Latest checkpoint's REDO WAL file:    000000010000007F0000008F
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/116936094
Latest checkpoint's NextOID:          2168930
Latest checkpoint's NextMultiXactId:  899338
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        1875
Latest checkpoint's oldestXID's DB:   16414
Latest checkpoint's oldestActiveXID:  116936094
Latest checkpoint's oldestMultiXid:   899337
Latest checkpoint's oldestMulti's DB: 1
Time of latest checkpoint:            Thu Jan 22 10:48:57 2015
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
Current wal_level setting:            hot_standby
Current wal_log_hints setting:        off
Current max_connections setting:      1200
Current max_worker_processes setting: 8
Current max_prepared_xacts setting:   64
Current max_locks_per_xact setting:   64
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0



As soon as I got that error, I stop'ed the services and aborted my daily
restore routine.


Davi