Re: postgres 9.5 DB corruption

On 7/24/19 7:38 AM, Thomas Tignor wrote:
> Hello postgres community,
>
> Writing again to see if there are insights on this issue. We have had
> infrequent but recurring corruption since upgrading from postgres 9.1 to
> postgres 9.5. We are presently on 9.5.16. Our DB-facing app continually
> performs a mixture of DML, primarily inserts and updates on two specific
> tables, with no single op being suspect. In the past, corruption events
> have produced encoding errors on COPY operations (invalid byte sequence
> for encoding "UTF8"). More recently, they have caused segmentation
> faults. We were able to take a cold backup after a recent event.
> SELECTing the corrupted data on our cold backup yields the following
> stack. Any info on a solution or how to proceed towards a solution would
> be much appreciated.

More information would be useful:

1) Schema of the tables.

2) Source of the data.

>
> Thanks in advance.
>
>
> (gdb) where
> #0  pglz_decompress (source=source(at)entry=0xa617904 "0", slen=8139, dest=dest(at)entry=0x4268e028 "", rawsize=808452096) at pg_lzcompress.c:745
> #1  0x080f3079 in toast_decompress_datum (attr=0xa6178fc) at tuptoaster.c:2210
> #2  0x080f3716 in heap_tuple_untoast_attr (attr=0xa6178fc) at tuptoaster.c:183
> #3  0x08440955 in pg_detoast_datum_packed (datum=<optimized out>) at fmgr.c:2270
> #4  0x084145bf in text_to_cstring (t=0x7592fd2a) at varlena.c:176
> #5  0x0843e874 in FunctionCall1Coll (flinfo=flinfo(at)entry=0xa614738, collation=collation(at)entry=0, arg1=arg1(at)entry=1972567338) at fmgr.c:1297
> #6  0x0843fef8 in OutputFunctionCall (flinfo=0xa614738, val=1972567338) at fmgr.c:1950
> #7  0x080bf84b in printtup (slot=0xa613bf4, self=0xa60d714) at printtup.c:359
> #8  0x08220f9a in ExecutePlan (dest=0xa60d714, direction=<optimized out>, numberTuples=0, sendTuples=<optimized out>, operation=CMD_SELECT, planstate=0xa613974, estate=0xa6138ec) at execMain.c:1574
> #9  standard_ExecutorRun (queryDesc=0xa6134e4, direction=ForwardScanDirection, count=0) at execMain.c:337
> #10 0x08332c1b in PortalRunSelect (portal=portal(at)entry=0xa6114dc, forward=forward(at)entry=1 '\001', count=0, count(at)entry=2147483647, dest=dest(at)entry=0xa60d714) at pquery.c:942
> #11 0x08333fa7 in PortalRun (portal=portal(at)entry=0xa6114dc, count=count(at)entry=2147483647, isTopLevel=isTopLevel(at)entry=1 '\001', dest=dest(at)entry=0xa60d714, altdest=altdest(at)entry=0xa60d714, completionTag=completionTag(at)entry=0xffd5d71c "")
>     at pquery.c:786
> #12 0x08330ba8 in exec_simple_query (query_string=0xa5f1754 "select * from ams.alert_attribute_bak;") at postgres.c:1096
> #13 PostgresMain (argc=1, argv=0xa53dbbc, dbname=0xa53daec "ams", username=0xa53dadc "akamai") at postgres.c:4049
> #14 0x080b53af in BackendRun (port=0xa584b78) at postmaster.c:4312
> #15 BackendStartup (port=0xa584b78) at postmaster.c:3986
> #16 ServerLoop () at postmaster.c:1705
> #17 0x082d0dd7 in PostmasterMain (argc=argc(at)entry=3, argv=argv(at)entry=0xa53d2a8) at postmaster.c:1313
> #18 0x080b68eb in main (argc=3, argv=0xa53d2a8) at main.c:228
> (gdb)
>
>
>
> Tom    :-)

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com