postgresql 灾难恢复

 

PostgreSQL读到坏块，会报块不可读的错误，这种情况下通过设置zero_damaged_pages=on可以跳过损坏的数据块。
如果连元数据都损坏了，又或者坏了一些磁盘，只有某些表空间被幸免于难，这些情况下你的数据库都已经无法启动时,这时只有使用pg_filedump类似的工具直接从二进制文件中直接抽取数据.
git clone git://git.postgresql.org/git/pg_filedump.git
[postgres@redis01 ~]$ cat .bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
	. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

export PGHOME=/u01/pgsql  
export PGDATA=/u01/pgsql/data        
export PATH=$PATH:$HOME/bin:$PGHOME/bin
[postgres@redis01 ~]$ ls
pg_filedump
[postgres@redis01 ~]$ cd pg_filedump/
[postgres@redis01 pg_filedump]$ make ; make install
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -I. -I./ -I/u01/pgsql/include/server -I/u01/pgsql/include/internal  -D_GNU_SOURCE   -c -o pg_filedump.o pg_filedump.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -I. -I./ -I/u01/pgsql/include/server -I/u01/pgsql/include/internal  -D_GNU_SOURCE   -c -o decode.o decode.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -I. -I./ -I/u01/pgsql/include/server -I/u01/pgsql/include/internal  -D_GNU_SOURCE   -c -o stringinfo.o stringinfo.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -I. -I./ -I/u01/pgsql/include/server -I/u01/pgsql/include/internal  -D_GNU_SOURCE   -c -o pg_lzcompress.o pg_lzcompress.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 pg_filedump.o decode.o stringinfo.o pg_lzcompress.o  -L/u01/pgsql/lib   -Wl,--as-needed -Wl,-rpath,'/u01/pgsql/lib',--enable-new-dtags   -L/u01/pgsql/lib -lpgcommon -lpgport -L/u01/pgsql/lib -lpq -o pg_filedump
/bin/mkdir -p '/u01/pgsql/bin'
/usr/bin/install -c  pg_filedump '/u01/pgsql/bin'

 查看文件

postgres=# select pg_relation_filepath('t'); 
 pg_relation_filepath 
----------------------
 base/13237/16404

 

查看数据

pg_filedump读取数据文件
pg_filedump -i -f  /u01/pgsql/data/base/13237/16404

[postgres@redis01 data]$ pg_filedump -D int,charN  /u01/pgsql/data/base/13237/16404|grep "COPY"|more
COPY: 1	http
COPY: 2	qdds
COPY: 3	海天
COPY: 4	海天
COPY: 5	海天
COPY: 6	海天
COPY: 7	海天
COPY: 8	海天
COPY: 9	海天

 导出数据

pg_filedump -D int,charN  /u01/pgsql/data/base/13237/16404|grep COPY > ~/t.log
[postgres@redis01 ~]$ head -10 t.log
COPY: 1	http
COPY: 2	qdds
COPY: 3	海天
COPY: 4	海天
COPY: 5	海天
COPY: 6	海天
COPY: 7	海天
COPY: 8	海天
COPY: 9	海天
COPY: 10	海天

sed 's/COPY: //g' t.log > t.out;mv t.out t.log 

[postgres@redis01 ~]$ head -10 t.log
1	http
2	qdds
3	海天
4	海天
5	海天
6	海天
7	海天
8	海天
9	海天
10	海天

postgres=# select relname,n_live_tup,n_dead_tup from pg_stat_user_tables where relname='t';
 relname | n_live_tup | n_dead_tup 
---------+------------+------------
 t       |          0 |          0
(1 row)

postgres=# truncate table t;
TRUNCATE TABLE
postgres=# copy t from '/home/postgres/t.log';
COPY 10000

postgres=# select count(1) from t limit 10;
 count 
-------
 10000
(1 row)

postgres=# select * from t limit 10;
 id | name 
----+------
  1 | http
  2 | qdds
  3 | 海天
  4 | 海天
  5 | 海天
  6 | 海天
  7 | 海天
  8 | 海天
  9 | 海天
 10 | 海天
(10 rows)