Re: View deleted records in a table

On 03/26/2016 10:32 AM, Boyan Botev wrote:
>
> I want to view deleted records in table from a week ago in order to
> troubleshoot a data issue. The table has not been vacuumed yet.
> I was
>
>
> Are you sure?
> In other words do you have autovacuum turned off?
>
> Autovacuum is on. The table is fairly static and the last vacuum analyze
> was a month ago as part of an upgrade. pg_stat_user_tables does not show
> any autovac counts or autovac timestamps since then. Based on that info
> I assume any records deleted/updated last week should still be there.
>
>
>
> trying to use the pageinspect v1.4 extension but can't seem to
> convert
> the t_data to a readable record. Is there an easy way for me to
> get a
>
>
> What is t_data?
>
> t_data is a column with the record data returned by function
> heap_page_items from the extension pageinspect, assuming I have
> understood the documentation correctly. I was told the extension may
> allow me to view invisible/deleted records. It's my first time using it
> and I am having trouble getting what I need. I was hoping someone else
> has had a similar issue and figured out a solution.
>
>
> What version of Postgres?
>
>
> I am running version 9.5.0.
>
>
>
> SQL to produce all system columns like xmin, xmax along with the
> visible
> and invisible records of the table in a readable form.
>
> I was hoping to find a setting similar to what Netezza has "set
> show_deleted_records=1;" to easily turn this visibility on or
> off, but
> it seems that feature was shot down several years ago. As a DBA
> I want
> to say that a feature like this is indispensible when
> troubleshooting
> data problems in large tables and environments where restores
> may not
> always be an option.
>
>
> That means keeping deleted records around for some indefinite period
> of time, which means table bloat.
>
>
> Not necessarily. I am only interested in records since the last vacuum
> run. Nothing extra in terms of storage is needed.

Aah, that is a different problem. On a heavily used table your time
frame may be very short.

I have not tried, but:

http://www.depesz.com/2012/04/04/lets-talk-dirty/

https://github.com/omniti-labs/pgtreats/tree/master/contrib/pg_dirtyread

>
> Thanks,
> Boyan
>

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