Re: [GENERAL] View deleted records in a table
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 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 >
Re: [GENERAL] View deleted records in a table
That works only if I know I will need the deleted/updated records for a table ahead of time. I need this feature/ability as a way to perform "data forensics" on a random table that someone has done something to. Next time it may be another table. Basically I need a way to remove the visibility map and show all records along with xmin and xmax alongside so I can tell what records were there and what exactly happened. > >I want to view deleted records in table from a week ago i > The accepted method for handling deleted (or updated) records is to create > a TRIGGER (or RULE) on the table(s) that writes the record to a history > file after it has been deleted (or updated). > > >
Re: [GENERAL] 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
[GENERAL] 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 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 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. Thanks in advance for any help, Boyan
[GENERAL] Charlotte Postgres User Group
If you live near or around Charlotte, please join us for the inaugural meeting of the Charlotte PUG on March 1, followed by a second meeting on April 11 featuring Bruce Momjian. More information about the two events can be found here: http://www.meetup.com/Charlotte-PostgreSQL-User-Group Also if you are travelling through Charlotte, NC or you just like to visit the "Queen City" you are welcome to stop by an give a talk to the Charlotte PUG. Just contact me when you would be in town and I can pencil you in for a talk or a presentation. Thanks, Boyan Botev
