Dump data from PostgreSQL's table which have few bad

今天在群里有位兄弟遇到PostgreSQL坏块的表无法读取的问题，版本是8.2的。当涉及到全表扫描是会遇到错误块的提示。 以前我在 GreenPlum中也遇到过类似问题。当时是使用COPY的方法把可以导出的数据导出。遇到坏块之后的数据都无法导出。 今天再来看这个问题，可 能还有更好的解决办法：不过已经没有场景可以验证了，以后遇到的时候一定要试一试。 参考方法1 ： COPY bad_table to csv 参考方法2 : 删除坏块的所有记录,可行性不高，因为删除记录是需要改动TUPLE的HEAD的xmax的，而扫描到PAGE 都有问题，这样肯定还是不行。 参考方法3 : 采用指针,跳过出错的块，这样的好处是可以导出更多的正常数据。 参考方法4 : 来 自互连网,Below is the entry quoted from the online manual for 7.3: ZERO_DAMAGED_PAGES (boolean) DetectionofadamagedpageheadernormallycausesPostgreSQLto reportanerror,abortingthecurrenttransaction.Setting zero_damaged_pagestotruecausesthesystemtoinsteadreporta warning,zerooutthedamagedpage,andcontinueprocessing. Thisbehaviorwilldestroydata,namelyalltherowsonthe damagedpage.Butitallowsyoutogetpasttheerrorand retrieverowsfromanyundamagedpagesthatmaybepresentin thetable.Soitisusefulforrecoveringdataifcorruptionhas occurredduetohardwareorsoftwareerror.Youshouldgenerally notsetthistrueuntilyouhavegivenuphopeofrecovering datafromthedamagedpage(s)ofatable.Thedefaultsettingis off,anditcanonlybechangedbyasuperuser. 修 复过程 : Please note - Note: vacuum is a time-consuming procedure; it may take up to several hours to complete! This procedure requires a lot of system resources and creates a high server load. Be careful: if the server gets down during this process,some data may be lost! We recommend you to backup HS database before you follow the steps below. So,to fix the issue,you should do the following : 1. Stop H-Sphere and postgres : Login into CP box under root user and run : Linux : # /etc/rc.d/init.d/httpcp stop # /etc/rc.d/init.d/postgresql stop FreeBSD : # /usr/local/etc/rc.d/apachecp.sh stop # /usr/local/etc/rc.d/010.pgsql.sh stop 2. Please edit the postgresql.conf ( Linux - ~postgres/data/postgresql.conf,FreeBSD - ~pgsql/data/postgresql.conf ) file and add the following line : zero_damaged_pages=on 3. Start postgresql service : Linux : # /etc/rc.d/init.d/postgresql start FreeBSD : # /usr/local/etc/rc.d/010.pgsql.sh start 4. Log into H-Sphere database and run the "vacuum analyze" command. E.g. : [root@cp ~]# su -l cpanel -bash-3.00$ psql hsphere Welcome to psql 7.4.13,the PostgreSQL interactive terminal. Type: /copyright for distribution terms /h for help with SQL commands /? for help on internal slash commands /g or terminate with semicolon to execute query /q to quit hsphere=# vacuum analyze hsphere-# ; WARNING: relation "bill_entry" page 31516 is uninitialized --- fixing VACUUM hsphere=# 5. Comment the "zero_damaged_pages=on" line in the postgresql.conf file and restart postgres/H-Sphere.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!