Postgresql在线备份和恢复

时间:2022-09-03 11:58:39

1.实验环境

OS:  RedHat Linux Enterprisedb 6.3
DB: postgresql 9.3
PGHOME: /opt/PostgreSQL/9.3
PGDATA: /opt/PostgreSQL/9.3/data
归档目录:/opt/pg_archive
基础备份目录:/opt/base_archive

--生产环境中归档和数据等目录应该放在不同的分区,或者存储上。

2.修改归档配置,添加测试数据

  • 修改pg配置文件postgresql.conf中以下条目
wal_level = archive
archive_mode = on
archive_command = 'cp -i %p /opt/pg_archive/%f </dev/null'
  • 建立归档目录,并重启数据库,使归档生效
[root@rhela /]# mkdir -p /opt/pg_archive/
[root@rhela /]# chown -R postgres:postgres /opt/pg_archive/
[root@rhela /]# chmod -R 700 /opt/pg_archive/
[root@rhela /]# mkdir -p /opt/base_archive
[root@rhela /]# chown -R postgres:postgres /opt/base_archive
[root@rhela /]# chmod -R 700 /opt/base_archive
[root@rhela opt]# su - postgres
-bash-4.1$ pg_ctl restart
  • 创建一个测试表
-bash-4.1$ psql -d test
psql.bin (9.3.6)
Type "help" for help.
test=# create table time (ctime time);
CREATE TABLE
test=# insert into time values(current_time);
INSERT 0 1
test=# select * from time;
ctime
-----------------
11:40:26.229787
(1 row)
test=# \q

3.执行基础备份,并添加测试数据

  • 备份data目录,并压缩
-bash-4.1$ psql postgres -c "select pg_start_backup('hot_backup');"
pg_start_backup
-----------------
0/A000028
(1 row) -bash-4.1$ tar --exclude $PGDATA/pg_xlog -cvjpf /opt/base_archive/pgbackup.tar.bz2 $PGDATA
tar: Removing leading `/' from member names
/opt/PostgreSQL/9.3/data/
/opt/PostgreSQL/9.3/data/pg_clo
......
-bash-4.1$ psql postgres -c "select pg_stop_backup();"
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
pg_stop_backup
----------------
0/A0000B8
(1 row)

--pg_start_backup会执行一个checkpoint的检查点,可能需要消耗一段时间

--pg_stop_backup会终止备份模式,并自动切换到下一个WAL文件,直到最后的WAL段文件被归档后pg_stop_backup命令才会返回。

  • 继续添加测试数据
-bash-4.1$ psql -d test
psql.bin (9.3.6)
Type "help" for help. test=# insert into time values (current_time);
INSERT 0 1
test=# insert into time values (current_time);
INSERT 0 1
test=# select * from time;
ctime
-----------------
11:40:26.229787
11:49:56.836129
11:52:04.235879
(3 rows) test=# \q
  • 执行一次手动归档
-bash-4.1$ psql
psql.bin (9.3.6)
Type "help" for help. postgres=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/B000368
(1 row)
postgres=# \q
-bash-4.1$ ls -alt /opt/pg_archive/
total 196628
-rw-------. 1 postgres postgres 16777216 May 5 11:54 00000002000000000000000B
drwx------. 2 postgres postgres 4096 May 5 11:54 .
-rw-------. 1 postgres postgres 294 May 5 11:48 00000002000000000000000A.00000028.backup
-rw-------. 1 postgres postgres 16777216 May 5 11:48 00000002000000000000000A
-rw-------. 1 postgres postgres 16777216 May 5 11:45 000000020000000000000009
-rw-------. 1 postgres postgres 16777216 May 5 10:30 000000020000000000000008
drwxr-xr-x. 7 root root 4096 May 4 12:01 ..

4.模拟数据库崩溃

[root@rhela pg_archive]# ps -ef |grep postgres
postgres 8112 1 0 10:30 pts/4 00:00:00 /opt/PostgreSQL/9.3/bin/postgres -D /opt/PostgreSQL/9.3/data
postgres 8113 8112 0 10:30 ? 00:00:00 postgres: logger process
postgres 8115 8112 0 10:30 ? 00:00:00 postgres: checkpointer process
postgres 8116 8112 0 10:30 ? 00:00:00 postgres: writer process
postgres 8117 8112 0 10:30 ? 00:00:00 postgres: wal writer process
postgres 8118 8112 0 10:30 ? 00:00:00 postgres: autovacuum launcher process
postgres 8119 8112 0 10:30 ? 00:00:00 postgres: archiver process last was 00000002000000000000000B
postgres 8120 8112 0 10:30 ? 00:00:00 postgres: stats collector process
root 9161 9034 0 14:02 pts/3 00:00:00 grep postgres
[root@rhela pg_archive]# kill -9 8112
[root@rhela pg_archive]# ps -ef |grep postgres
root 9174 9034 0 14:06 pts/3 00:00:00 grep postgres

5.数据库恢复到指定时间 11:50:00

  • 移除数据目录
[root@rhela pg_archive]# cd /opt/PostgreSQL/9.3/
[root@rhela 9.3]# mv data data_bak

--一般为了安全考虑,存储空间足够时,会把事故发生时的文件做一个备份,而不是直接删除掉。具体到这里,整个data目录和归档目录最好不要删除。

  • 复制基础备份到指定的位置,并检查属主和权限
[root@rhela 9.3]# cd /opt/base_archive/
[root@rhela base_archive]# ls
pgbackup.tar.bz2
[root@rhela base_archive]# tar -xvf pgbackup.tar.bz2
......
[root@rhela base_archive]# cd opt/PostgreSQL/9.3/
[root@rhela 9.3]# ls
data
[root@rhela 9.3]# cp -r data/ /opt/PostgreSQL/9.3/
[root@rhela 9.3]# cd /opt/PostgreSQL/9.3/
[root@rhela 9.3]# ls -al |grep data
drwx------. 15 root root 4096 May 5 14:15 data
drwx------. 16 postgres postgres 4096 May 5 11:48 data_bak
[root@rhela 9.3]# chown -R postgres:postgres data
[root@rhela 9.3]# chmod -R 700 data

--如果复制的数据中有软连接(如建立了表空间等情况),要确认软连接是否备份完整,恢复的时候也要注意处理。

--备份的时候,postgresql.conf、 pg_hba.conf 和 pg_ident.conf文件不会自动备份,需要手工备份。

  • 在data目录中创建pg_xlog目录
[root@rhela data]# mkdir -p /opt/PostgreSQL/9.3/data/pg_xlog
[root@rhela data]# chown -R postgres:postgres /opt/PostgreSQL/9.3/data/pg_xlog
[root@rhela data]# chmod -R 700 /opt/PostgreSQL/9.3/data/pg_xlog
  • 在pg_xlog目录中创建子目录
[root@rhela data]# mkdir -p /opt/PostgreSQL/9.3/data/pg_xlog/archive_status
[root@rhela data]# chown -R postgres:postgres /opt/PostgreSQL/9.3/data/pg_xlog/archive_status/
[root@rhela data]# chmod -R 700 /opt/PostgreSQL/9.3/data/pg_xlog/archive_status/
  • 在$PGDATA目录中,创建恢复配置文件recovery.conf
[root@rhela data]# cd /opt/PostgreSQL/9.3/data
[root@rhela data]# touch recovery.conf
[root@rhela data]# echo "restore_command='cp -i /opt/pg_archive/%f %p'" >>recovery.conf
[root@rhela data]# echo "recovery_target_time = '2015-05-05 11:50:00'" >>recovery.conf
[root@rhela data]# cat recovery.conf
restore_command='cp -i /opt/pg_archive/%f %p'
recovery_target_time = '2015-05-05 11:50:00'
[root@rhela data]# chown postgres:postgres recovery.conf
[root@rhela data]# chmod 700 recovery.conf

--恢复完成后,recovery.conf会被自动重命名为recovery.done

--如果不指定恢复时间点的话,默认恢复到接收到的最后一个归档文件。

6.检查数据恢复情况

  • 启动数据库服务
[root@rhela data]# service postgresql-9.3 start
Starting PostgreSQL 9.3:
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start.... done
server started
PostgreSQL 9.3 started successfully
  • 连接进入数据库查看是否恢复到对应的时间点
[root@rhela archive_status]# su - postgres
-bash-4.1$ psql -d test
psql.bin (9.3.6)
Type "help" for help. test=# select * from time;
ctime
-----------------
11:40:26.229787
11:49:56.836129
(2 rows) test=# \q
-bash-4.1$

  可以看到该表已经成功恢复到指定的时间点。

参考文档:https://wiki.postgresql.org/wiki/9.1%E7%AC%AC%E4%BA%8C%E5%8D%81%E5%9B%9B%E7%AB%A0

Postgresql在线备份和恢复的更多相关文章

  1. PostgreSQL的备份和恢复

    关于PostgreSQL的备份和恢复详细信息请参阅<PostgreSQL中文文档>. 备份: #pg_dump --username=postgres v70_demo > v70_ ...

  2. postgresql数据库备份和恢复

    PostgreSQL自带一个客户端pgAdmin,里面有个备份,恢复选项,也能对数据库进行备份 恢复(还原),但最近发现数据库慢慢庞大的时候,经常出错,备份的文件过程中出错的几率那是相当大,手动调节灰 ...

  3. MySQL Cluster在线备份和恢复

    备份方式 一般MySQL数据库都是用mysqldump命令进行数据备份,其生成的文件实际上是创建对象和导入对象的sql语句. 在MySQL Cluster集群上,可以在管理节点上使用start bac ...

  4. postgresql数据库备份和恢复(超快)

    PostgreSQL自带一个客户端pgAdmin,里面有个备份,恢复选项,也能对数据库进行备份 恢复(还原),但最近发现数据库慢慢庞大的时候,经常出错,备份的文件过程中出错的几率那是相当大,手动调节灰 ...

  5. innobackupex在线备份及恢复(全量和增量)

    Xtrabackup是由percona开发的一个开源软件,它是innodb热备工具ibbackup(收费的商业软件)的一个开源替代品.Xtrabackup由个部分组成:xtrabackup和innob ...

  6. &lbrack;MySQL&rsqb; innobackupex在线备份及恢复(全量和增量)

    安装percona-xtrabackup 方法1: percona-xtrabackup-2.1.9-744-Linux-x86_64.tar.gz(D:\share\src\linux-mysql) ...

  7. postgresql批量备份和恢复数据表

    备份数据库:pg_dump -h localhost -U root demo02 > /home/arno/dumps/demo02.bak 恢复数据库:psql -h localhost - ...

  8. 12 postgresql数据库备份和恢复

    数据表结构备份与恢复 备份 1.找到postgres 安装目录,在找到bin文件夹,会看到一堆exe后缀的文件,用win+r 打开cmd,将pg_dump.exe 拖进cmd黑窗口中 2.基本语法:- ...

  9. &lbrack;完整版&rsqb;Postgresql 数据库 备份以及恢复的过程

    0. 准备工作 linux机器上面 必须安装上pg数据库 然后 需要将 pg的主程序目录 放到环境变量里面去 便于执行命令. 1. 先备份 . 备份目标数据库: pg_dump -h -F c -f ...

随机推荐

  1. &lbrack;异常解决&rsqb; ubuntu上安装JLink驱动遇到的坑及给后来者的建议

    一.前言 最近将整个电脑格式化,改成了linux操作系统 希望这样能让自己在一个新的世界探索技术.提升自己吧- win上的工具用多了,就不想变化了- 继上一篇<ubuntu上安装虚拟机遇到的问题 ...

  2. CF380C&period; Sereja and Brackets&lbrack;线段树 区间合并&rsqb;

    C. Sereja and Brackets time limit per test 1 second memory limit per test 256 megabytes input standa ...

  3. Jmeter使用入门

    修改时间 修改内容 修改人 2016.3.12 创建 刘永志 2016.6.18 完成 刘永志 Jmeter简介 Jmeter的基本概念 百度百科: Apache JMeter是Apache组织开发的 ...

  4. ubuntu16&period;04下sublime text3安装和配置

    ubuntu16.04下sublime text3安装和配置 2018年04月20日 10:31:08 zhengqijun_ 阅读数:1482 1.安装方法 1)使用ppa安装 sudo add-a ...

  5. SCP和Rsync远程拷贝的几个技巧

    scp是secure copy的简写,用于在Linux下进行远程拷贝文件的命令,和它类似的命令有cp,不过cp只是在本机进行拷贝不能跨服务器,而且scp传输是加密的.可能会稍微影响一下速度.当你服务器 ...

  6. idea自动生成文档注释

    这方面主要分为两块内容,一是利用idea本身具有的生成模板工具进行生成:二是利用第三方插件生成,比如jindent 后期会进行整理更新,待续 下面的网址目前只是关于这方面的介绍,先留存一份而已 htt ...

  7. LIN、CAN、FlexRay、MOST,三分钟搞明白四大汽车总线

    LIN.CAN.FlexRay.MOST,三分钟搞明白四大汽车总线 2016-09-21 13:09 汽车中的电子部件越来越多,光是ECU就有几十个,这么多的电子单元都要进行信息交互.传统的点对点通信 ...

  8. Java 8新特性之接口改善(八恶人-1)

    Daisy Donergue 多莫歌·黛西 “By woman, you mean her?” 她也能叫女人? Java 8在13年9月发布,写这篇博文的时间已经是17年12月份了.来的有点晚,但是有 ...

  9. AsyncContext简介

    为了支持异步处理,在Servlet 3.0中,在ServletRequest上提供了startAsync()方法: AsyncContext startAsync() throws Java.lang ...

  10. discuz回贴通知插件实现-配置邮件服务器

    添加smtp服务器,填写相应的smtp服务器,发信人地址,用户名和密码.   填写发件人地址和收件人地址来测试邮件是否发送成功.