Thanks, Adrian! That worked great for what I needed. I greatly appreciate your help. Do you know if there is a way to also display system columns like xmin, xmax with this extension. I can see the need for that in some future investigation. Thanks, Boyan
On Sat, Mar 26, 2016 at 1:45 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > 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.kla...@aklaver.com >
