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.