Postgresql 备份与恢复示例 Itpub 网友:caleble 一、 简介 Postgresql 的备份分为三种: l sql 转储 l 文件系统级别备份(冷备份) l 在线热备份(归档) 以下通过实例来讲解 Postgresql 的三种备份。 二、 sql 转储 (一)pg_dump 1,创建数据库 createdb pg 2,连入数据库 pg psql pg 3,创建测试表,插入数据 pg=# create table pg_test(a int); pg=# insert into pg_test(a) values(1); pg=# insert into pg_test(a) values(2); 4,查看数据 pg=# select * from tb; a --- 1 2 (2 rows) 5,备份 pg_dump pg > /usr/local/pgsql/backup/pg.dmp 6,删除数据库 pg dropdb pg 7,创建新数据库(恢复之前需创建数据库) createdb pg 开源时代 2009 年 10 月刊 - 69 - 投稿邮箱:rmzhou@staff.chinaunix.net ChinaUnix.net -----全球最大的 Linux/Unix 应用与开发者中文社区 8,恢复数据 psql pb < /usr/local/pgsql/backup/pg.dmp 9,查看数据是否回复 pg=# select * from tb; a --- 1 2 (2 rows) 至此,数据已成功恢复! 注:pg_dump 可以对针对单表或者多表进行备份 如:pg_dump databasename –t tablename1 –t tablename2 >filename (二)pg_dumpall pg_dump 只能备份单个数据库,而且恢复的时候需要创建空数据库。pg_dumpall 可以备份所 有数据库,并且备份角色、表空间。 1,创建数据库 createdb pg1 createdb pg2 2,pg1 中创建表并插入数据 psql pg1 pg=# create table tb1(a int) pg=# insert into tb1(a) values(1); 3,pg2 中创建表并插入数据 psql pg2 pg=# create table tb2(a int) pg=# insert into tb2(a) values(2); 4,备份数据库 pg_dumpall > /usr/local/pgsql/backup/pg_all.dmp 5,删除数据库 dropdb pg1 dropdb pg2 6,恢复数据库 psql –f /usr/local/pgsql/backup/pg_all.dmp postgres 可以指定任何数据库名,如果恢复到一个空的集群中,通常使用 postgres 数据库 7,查看数据库 pg1 是否恢复 psql pg1 pg=# select * from tb1; a --- 1 (1 rows) 8,查看数据库 pg2 是否恢复 psql pg2 pg=# select * from tb2; a --- 2 (1 rows) 至此,数据已成功恢复! (三)备份压缩与分割 压缩与分割的原理都是利用 Linux 的管线(PIPE)命令,不再进行试验。 1)压缩 l 备份: pg_dump dbname | gzip > filename.gz l 恢复 gunzip -c filename.gz | psql dbname 2)分割 l 备份: pg_dump dbname | split -b1m- filename l 恢复 cat filename* | psql dbname 三、 文件系统级别备份(冷备份) 文件系统级别的备份是冷备份,需要停止数据库。 1,停止数据库 pg_ctl –D /usr/local/pgsql/data stop 2,备份数据库 tar –jcv –f /usr/local/pgsql/backup/filesystem.tar.bz2 /usr/local/pgsql/data/ 3,删除/usr/local/pgsql/data/目录 rm –r /usr/local/pgsql/data/ 4,解压备份文件到原目录 tar –jxv –f /usr/local/pgsql/backup/filesystem.tar.bz2 –C / 5,启动数据库 pg_ctl –D /usr/local/pgsql/data start 6,查看数据库 pg1 是否恢复 psql pg1 pg=# select * from tb1; a --- 1 (1 rows) 7,数据已成功恢复! 四、 在线热备份(归档) (一)备份 1,配置归档模式 配置归档需要编辑 postgresql.conf 文件,默认为与/usr/local/pgsql/data/目录下 开源时代 2009 年 10 月刊 - 72 - 投稿邮箱:rmzhou@staff.chinaunix.net ChinaUnix.net -----全球最大的 Linux/Unix 应用与开发者中文社区 vim /usr/local/pgsql/data/postgesql.conf archive_mode = on archive_command = ‘cp %p /usr/local/pgsql/backup/archived_log/%f’ 注:%p 要被归档的日志文件的路径,%f 是要被归档的日志文件的文件名 2,启动数据库 pg_ctl –D /usr/local/pgsql/data start 3,创建数据库 arch createdb arch 4,创建表并插入记录 psql arch arch=# create table tb(a int); arch=# insert into tb(a) values(1); 5,创建备份 arch=# select pg_start_backup(‘baseline’); 6,备份整个 data 目录 tar –jcv –f /usr/local/pgsql/backup/baseline.tar.bz2 /usr/local/pgsql/data/ 7,停止备份 psql arch arch=# select pg_stop_backup(); 8,插入新记录,然后切换日志,重复 3 次 arch=# insert into tb(a) values(2); arch=# select pg_switch_xlog(); arch=# insert into tb(a) values(3); arch=# select pg_switch_xlog(); arch=# insert into tb(a) values(4); arch=# select pg_switch_xlog(); 9,把/data/pg_xlog/下的 WAL 日志文件复制到预设的归档目录下,保证产生的 WAL 日志都已归 档。 (二)恢复 1,停止数据库 pg_ctl –D /usr/local/pgsql/data/ stop 2,删除/data/ rm –r /usr/local/pgsql/data/ 3,恢复备份 tar –jxv –f /usr/local/pgsql/backup/baseline.tar.bz2 –C / 4,清空/data/pg_xlog/目录下所有文件 rm –r /usr/local/pgsql/data/pg_xlog/ 5,创建/pg_xlog/及其下面的 archive_status 目录 mkdir /usr/local/pgsql/data/pg_xlog/ mkdir /usr/local/pgsql/data/pg_xlog/archive_status 6,在/data/目录下创建 recovery.conf vim /usr/local/pgsql/data/recovery.conf restore_command = ‘cp /usr/local/pgsql/backup/archived_log/%f “%p”’ 7,启动数据库 pg_ctl –D /usr/local/pgsql/data/ start 一切正常的话数据库就会自动应用 WAL 日志进行恢复 8,查看数据库 arch 是否恢复 psql arch arch=# select * from tb; a --- 1 2 3 4 (4 rows) 至此,数据已经成功恢复!