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