Re: after using pg_resetxlog, db lost - Mailing list pgsql-performance
| From | Shea,Dan [CIS] |
|---|---|
| Subject | Re: after using pg_resetxlog, db lost |
| Date | |
| Msg-id | 644D07D3D59D8F408CD01AC2F833D8C62B9210@cisxa.cmc.int.ec.gc.ca Whole thread Raw |
| In response to | after using pg_resetxlog, db lost ("Shea,Dan [CIS]" <Dan.Shea@ec.gc.ca>) |
| Responses |
Re: after using pg_resetxlog, db lost
|
| List | pgsql-performance |
Tom I see you from past emails that you reference using -i -f with pg_filedump. I have tried this, but do not know whatI am looking at. What would be the the transaction id? What parameter am I supposed to pass to find it? ******************************************************************* * PostgreSQL File/Block Formatted Dump Utility - Version 3.0 * * File: /npmu_base/data/base/17347/1259 * Options used: -i -f * * Dump created on: Thu Jun 24 02:44:59 2004 ******************************************************************* Block 0 ******************************************************** <Header> ----- Block Offset: 0x00000000 Offsets: Lower 232 (0x00e8) Block: Size 8192 Version 1 Upper 268 (0x010c) LSN: logid 0 recoff 0x00632c08 Special 8192 (0x2000) Items: 53 Free Space: 36 Length (including item array): 236 0000: 00000000 082c6300 0b000000 e8000c01 .....,c......... 0010: 00200120 c4908801 00908801 3c8f8801 . . ........<... 0020: 788e8801 b48d8801 f08c8801 2c8c8801 x...........,... 0030: 689f3001 688b8801 a48a8801 e0898801 h.0.h........... 0040: 1c898801 58888801 94878801 d0868801 ....X........... 0050: 3c862801 a8852801 e4848801 50842801 <.(...(.....P.(. 0060: bc832801 f8828801 64822801 d0812801 ..(.....d.(...(. 0070: 0c818801 6c110000 d8100000 44100000 ....l.......D... 0080: b00f0000 1c0f0000 d49e2801 409e2801 ..........(.@.(. 0090: ac9d2801 189d2801 849c2801 f09b2801 ..(...(...(...(. 00a0: 5c9b2801 c89a2801 349a2801 a0992801 \.(...(.4.(...(. 00b0: 0c992801 78982801 e4972801 50972801 ..(.x.(...(.P.(. 00c0: bc962801 28962801 94952801 00952801 ..(.(.(...(...(. 00d0: 6c942801 d8932801 44932801 b0922801 l.(...(.D.(...(. 00e0: 1c922801 88912801 00000000 ..(...(..... <Data> ------ Item 1 -- Length: 196 Offset: 4292 (0x10c4) Flags: USED XID: min (2) CMIN|XMAX: 211 CMAX|XVAC: 469 Block Id: 0 linp Index: 1 Attributes: 24 Size: 28 infomask: 0x2912 (HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) 10c4: 02000000 d3000000 d5010000 00000000 ................ 10d4: 01001800 12291c00 cc420000 7461626c .....)...B..tabl 10e4: 655f636f 6e737472 61696e74 73000000 e_constraints... 10f4: 00000000 00000000 00000000 00000000 ................ 1104: 00000000 00000000 00000000 00000000 ................ 1114: 00000000 00000000 00000000 51420000 ............QB.. 1124: cd420000 01000000 00000000 cc420000 .B...........B.. 1134: 00000000 00000000 00000000 00000000 ................ 1144: 00007600 09000000 00000000 00000000 ..v............. 1154: 00000100 30000000 01000000 00000000 ....0........... 1164: 09040000 02000000 00000000 01000000 ................ 1174: 01000000 7f803f40 00000000 01000000 ......?@........ 1184: 02000000 .... Item 2 -- Length: 196 Offset: 4096 (0x1000) Flags: USED XID: min (2) CMIN|XMAX: 215 CMAX|XVAC: 469 Block Id: 0 linp Index: 2 Attributes: 24 Size: 28 infomask: 0x2912 (HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) 1000: 02000000 d7000000 d5010000 00000000 ................ 1010: 02001800 12291c00 d0420000 7461626c .....)...B..tabl 1020: 655f7072 6976696c 65676573 00000000 e_privileges.... 1030: 00000000 00000000 00000000 00000000 ................ 1040: 00000000 00000000 00000000 00000000 ................ 1050: 00000000 00000000 00000000 51420000 ............QB.. 1060: d1420000 01000000 00000000 d0420000 .B...........B.. 1070: 00000000 00000000 00000000 00000000 ................ 1080: 00007600 08000000 00000000 00000000 ..v............. 1090: 00000100 30000000 01000000 00000000 ....0........... 10a0: 09040000 02000000 00000000 01000000 ................ 10b0: 01000000 7f803f40 00000000 01000000 ......?@........ 10c0: 02000000 .... Item 3 -- Length: 196 Offset: 3900 (0x0f3c) Flags: USED XID: min (2) CMIN|XMAX: 219 CMAX|XVAC: 469 Block Id: 0 linp Index: 3 Attributes: 24 Size: 28 Dan. -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Tuesday, June 22, 2004 3:36 PM To: Shea,Dan [CIS] Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] after using pg_resetxlog, db lost "Shea,Dan [CIS]" <Dan.Shea@ec.gc.ca> writes: > The pg_resetxlog was run as root. It caused ownership problems of > pg_control and xlog files. > Now we have no access to the data now through psql. The data is still > there under /var/lib/pgsql/data/base/17347 (PWFPM_DEV DB name). But > there is no reference to 36 of our tables in pg_class. Also the 18 > other tables that are reported in this database have no data in them. > Is there anyway to have the database resync or make it aware of the data > under /var/lib/pgsql/data/base/17347? > How can this problem be resolved? What this sounds like is that you reset the transaction counter along with the xlog, so that those tables appear to have been created by transactions "in the future". This could be repaired by doing pg_resetxlog with a more appropriate initial transaction ID, but figuring out what that value should be is not easy :-( What I'd suggest is grabbing pg_filedump from http://sources.redhat.com/rhdb/ and using it to look through pg_class (which will be file $PGDATA/base/yourdbnumber/1259) to see the highest transaction ID mentioned in any row of pg_class. Then pg_resetxlog with a value a bit larger than that. Now you should be able to see all the rows in pg_class ... but this doesn't get you out of the woods yet, unless there are very-recently-created tables shown in pg_class. I'd suggest next looking through whichever tables you know to be recently modified to find the highest transaction ID mentioned in them, and finally doing another pg_resetxlog with a value a few million greater than that. Then you should be okay. The reason you need to do this in two steps is that you'll need to look at pg_class.relfilenode to get the file names of your recently-modified tables. Do NOT modify the database in any way while you are running with the intermediate transaction ID setting. > Jun 22 13:38:23 murphy postgres[1460]: [2-1] ERROR: xlog flush request > 210/E757F150 is not satisfied --- flushed only to 0/2074CA0 Looks like you also need a larger initial WAL offset in your pg_resetxlog command. Unlike the case with transaction IDs, there's no need to try to be somewhat accurate in the setting --- I'd just use a number WAY beyond what you had, maybe like 10000/0. Finally, the fact that all this happened suggests that you lost the contents of pg_control (else pg_resetxlog would have picked up the right values from it). Be very sure that you run pg_resetxlog under the same locale settings (LC_COLLATE,LC_CTYPE) that you initially initdb'd with. Otherwise you're likely to have nasty index-corruption problems later. Good luck. Next time, don't let amateurs fool with pg_resetxlog (and anyone who'd run it as root definitely doesn't know what they're doing). It is a wizard's tool. Get knowledgeable advice from the PG lists before you use it rather than after. regards, tom lane
pgsql-performance by date: