POSTGRESQL 9.1.9 Database Recovery.
Errors thrown in postgresql log file.
2013-09-04 14:06:36 IST 14922 [local] ERROR: missing chunk number 0 for toast value 76775 in pg_toast_1255
2013-09-04 14:05:07 IST 14922 [local] ERROR: missing chunk number 0 for toast value 76775 in pg_toast_1255
2013-09-04 14:03:59 IST 17909 [local] ERROR: missing chunk number 0 for toast value 76775 in pg_toast_1255
2013-09-04 13:54:18 IST 17847 59.99.224.120 ERROR: missing chunk number 1 for toast value 100134 in pg_toast_2618
2013-09-04 13:50:36 IST 17847 59.99.224.120 ERROR: missing chunk number 0 for toast value 76775 in pg_toast_1255
2013-09-04 13:48:30 IST 17909 [local] ERROR: missing chunk number 0 for toast value 76775 in pg_toast_1255
2013-09-04 13:41:35 IST 17847 59.99.224.120 ERROR: missing chunk number 0 for toast value 76775 in pg_toast_1255
2013-09-04 13:29:40 IST 14942 59.99.224.120 ERROR: missing chunk number 0 for toast value 87623 in pg_toast_2618
...
2013-09-04 11:31:00 IST 12499 127.0.0.1 ERROR: missing chunk number 0 for toast value 96044 in pg_toast_2619
2013-09-04 11:30:55 IST 12481 127.0.0.1 ERROR: missing chunk number 0 for toast value 96044 in pg_toast_2619
2013-09-04 11:30:55 IST 12479 127.0.0.1 ERROR: missing chunk number 0 for toast value 96044 in pg_toast_2619
2013-09-04 11:30:28 IST 12392 127.0.0.1 ERROR: missing chunk number 0 for toast value 96044 in pg_toast_2619
...
2013-09-04 00:32:57 IST 15985 [local] DETAIL: Could not open file "pg_clog/0005": No such file or directory.
2013-09-04 00:32:08 IST 15961 [local] DETAIL: Could not open file "pg_clog/0004": No such file or directory.
2013-09-04 00:21:59 IST 15833 [local] DETAIL: Could not open file "pg_clog/0000": No such file or directory.
2013-09-04 00:17:25 IST 15698 [local] DETAIL: Could not open file "pg_clog/0000": No such file or directory.
2013-09-04 00:11:59 IST 15673 [local] DETAIL: Could not open file "pg_clog/0031": No such file or directory.
2013-09-04 00:11:20 IST 15664 [local] DETAIL: Could not open file "pg_clog/0027": No such file or directory.
2013-09-04 00:10:47 IST 15650 [local] DETAIL: Could not open file "pg_clog/0026": No such file or directory.
2013-09-04 00:10:03 IST 15631 [local] DETAIL: Could not open file "pg_clog/002B": No such file or directory.
2013-09-04 00:09:32 IST 15624 [local] DETAIL: Could not open file "pg_clog/0025": No such file or directory.
2013-09-04 00:08:55 IST 15592 [local] DETAIL: Could not open file "pg_clog/0029": No such file or directory.
2013-09-04 00:08:19 IST 15586 [local] DETAIL: Could not open file "pg_clog/0035": No such file or directory.
2013-09-04 00:07:44 IST 15579 [local] DETAIL: Could not open file "pg_clog/002C": No such file or directory.
2013-09-04 00:05:42 IST 15243 [local] DETAIL: Could not open file "pg_clog/002A": No such file or directory.
2013-09-04 00:03:21 IST 15129 [local] DETAIL: Could not open file "pg_clog/002A": No such file or directory.
2013-09-04 00:01:28 IST 15005 [local] DETAIL: Could not open file "pg_clog/0022": No such file or directory.
...
2013-09-03 23:17:06 IST 13266 117.213.89.49 ERROR: missing chunk number 0 for toast value 105498 in pg_toast_2619
2013-09-03 23:17:06 IST 13266 117.213.89.49 ERROR: missing chunk number 0 for toast value 105498 in pg_toast_2619
2013-09-03 23:17:06 IST 13266 117.213.89.49 ERROR: missing chunk number 0 for toast value 105498 in pg_toast_2619
2013-09-03 23:17:06 IST 13266 117.213.89.49 ERROR: missing chunk number 0 for toast value 105498 in pg_toast_2619
2013-09-03 23:17:06 IST 13266 117.213.89.49 ERROR: missing chunk number 0 for toast value 105498 in pg_toast_2619
2013-09-03 23:17:05 IST 13266 117.213.89.49 ERROR: missing chunk number 0 for toast value 105498 in pg_toast_2619
2013-09-03 23:17:05 IST 13266 117.213.89.49 ERROR: missing chunk number 0 for toast value 105498 in pg_toast_2619
2013-09-03 23:17:05 IST 13266 117.213.89.49 ERROR: missing chunk number 0 for toast value 105498 in pg_toast_2619
...
2013-09-03 17:44:53 IST 2456 192.168.1.112 DETAIL: Could not open file "pg_clog/0039": No such file or directory.
2013-09-03 17:44:53 IST 2456 192.168.1.112 DETAIL: Could not open file "pg_clog/0039": No such file or directory.
The errors were very complicated and the data in the database(~10GB) were very critical.
Following steps cleared the errors and the DB was restored.
1) TO FIX missing pg_clog/XXXX files.
Used the following commands to create empty logfiles in pg_clog directory.
#dd if=/dev/zero of=<filename> bs=256k count=1
#chown postgres.postgres <filename>
#chmod 600 <filename>
Around 20 files were created.
2) TO FIX "missing chunk number 0 for toast value XXXXX in pg_toast_2619"
Used the following command to find table name which is corrupted.
courier=#select relname from pg_class where reltoastrelid='pg_toast.pg_toast_2619'::regclass;
It was 'pg_statistic' table which got corrupted.
AS pg_statistic is only a statistic file, deleted all rows in the table.
courier=#DELETE FROM pg_statistic;
*** Note: DB has to create fresh statistic records. Perfromance of initial queries may be slow.
3) TO FIX "missing chunk number 0 for toast value 87623 in pg_toast_2618"
Used the following command to find table name which got corrupted.
courier=#select relname from pg_class where reltoastrelid='pg_toast.pg_toast_2618'::regclass;
It was 'pg_rewrite' table which got corrupted.
pg_rewrite has rules for views and tables.
To find out the corrupted rows, used the following command
courier=#SELECT * FROM pg_rewrite limit 1 offset 27;
Found rules missing for views.
- weight_mismatch_view.
- sync_log_baseview.
- sync_log_view.
- fen_deliveries.
- fen_delivery.
These views have column 'relhasrules' in pg_class set to TRUE.
Changed the relhasrules value to FALSE,
Deleted the views and
Recreated views from schema definitions in backup.
courier=#UPDATE pg_class SET relhasrules=false where oid=69526;
courier=#DROP VIEW sync_log_view;
courier=#CREATE VIEW ................;
3) TO FIX "missing chunk number 0 for toast value 76775 in pg_toast_1255"
Used the following command to find table name which got corrupted.
courier=#select relname from pg_class where reltoastrelid='pg_toast.pg_toast_1255'::regclass;
It was 'pg_proc' table which stores the procedures and functions.
To find out the corrupted row, used the following command.
courier=#select proname,prosrc FROM pg_proc order by proname limit 1 offset 562;
Function delivery_update was corrupted. Especially column 'prosrc' procedure source node got corrupted.
Set the prosrc to NULL,
Droped the function and
Recreated the function from schema backup.
courier=#UPDATE pg_proc SET prosrc=NULL where proname='delivery_update';
courier=#DROP FUNCTION delivery_update(date);
courier=#CREATE OR REPLACE FUNCTION delivery_update(date) ............................;
4) Restarted DB. Everything went smooth.
