Re: emergency outage requiring database restart

On 10/21/16 2:02 PM, Alvaro Herrera wrote:
> Merlin Moncure wrote:
>
>> OK, I have some good (very- in the specific case of yours truly) news
>> to report. Doing a filesystem level copy to a test server I was able
>> to relfilenode swap one of the critical tables over the place of the
>> refilenode of the stored backup. Not being able know the file to copy
>> from, I figured out the source node by judging the size and using
>> 'strings' utility. Data recovery for that table at least appears to
>> be 100%.
>
> FWIW you can use pg_filedump and match based on the number of columns.
> I suppose you could also use the pageinspect extension, by 'dd'ing a
> page from the file into the database and feeding into heap_page_items as
> bytea.

It occurs to me that it might be worth embedding the relation name in
the free space of the first block. Most people would never notice the
missing 64 bytes, but it would be incredibly helpful in cases like this...
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461