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

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

> davividal(at)gmail(dot)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(dot)sf_guard_user(at)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