Re: View deleted records in a table

>
>
> 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.

Thanks,
Boyan