postgresql 备份恢复(二)

最新推荐文章于 2024-09-23 12:45:00 发布

日积月累_滴水石穿

最新推荐文章于 2024-09-23 12:45:00 发布

阅读量1.9k

点赞数

分类专栏： Postgres 文章标签： postgres备份 postgres恢复 postgresPITR postgres在线热备

Postgres 专栏收录该内容

16 篇文章 1 订阅

订阅专栏

postgresql同样支持在线备份,该备份方式与oracle中用户热备的方式相同,手动拷贝数据文库文件与归档日志。可以根据测试过程将备份直接写成script,通过定制,完成数据库的自动备份。
postgresql的恢复支持基于时间戳与事务ID,可以通过时间戳或事务ID的方式,完成数据库的不完全恢复或者因错误操作的故障恢复。
该测试目的：postgresql的在线备份;通过在线备份完成恢复。

1,开启归档

  
  
   
   [postgre@daduxiong ~]$ more /usr/local/pgsql/data/postgresql.conf |grep archive_
archive_mode = on               # allows archiving to be done
archive_command = 'cp -i %p /archive/%f >/dev/null'

2,重新启动数据库

  
  
   
   [root@daduxiong ~]# service postgresql stop
Stopping PostgreSQL: server stopped
ok
[root@daduxiong ~]# service postgresql start
Starting PostgreSQL: ok

3,启动备份

  
  
   
   [postgre@daduxiong archive]$ psql postgres -c "select pg_start_backup('hot_backup');" 
 pg_start_backup 
-----------------
 0/7000020
(1 row)

4,使用tar命令备份数据库文件,不包含pg_xlog目录

  
  
   
   [postgre@daduxiong archive]$ tar --exclude $PGDATA/pg_xlog -cvjpf /archive/pgbackup.tar.bz2 $PGDATA

5,完成备份

  
  
   
   [postgre@daduxiong archive]$ psql postgres -c "select pg_stop_backup();"
 pg_stop_backup 
----------------
 0/70133A0
(1 row)

说明: 调用 select pg_start_backup('xxx');开始备份时，数据库释放对数据目录中各文件的锁，使其它进程可以进行操作，此时用户对数据库的操作，都缓存在数据缓冲区中，当调用select pg_stop_backup();时,数据缓冲区中的数据刷写到磁盘，终止备份模式，并执行自动切换到下一个WAL段。

6,在postgres数据库中创建表并插入记录,作为恢复时的判断。

 
代码 
  
    [postgre@daduxiong archive]$ psql postgres
Welcome to psql 8.3.10, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# create table abc(id integer);
CREATE TABLE
postgres=# insert into abc values(1);
INSERT 0 1
postgres=# \q

7,此时假设数据库出现问题,停止数据库,拷贝日志

  
  
   
   [root@daduxiong pgsql]# service postgresql stop
Stopping PostgreSQL: server stopped
ok
[postgre@daduxiong archive]$ cp $PGDATA/pg_xlog/*00* /archive/

8,删除"发生错误"的data目录

  
  
   
   [root@daduxiong pgsql]# rm -rf data

9,解压之前的备份文件压缩包

 
代码 
  
    [postgre@daduxiong pgsql]$ tar -xvf /archive/pgbackup.tar.bz2
....省略
/usr/local/pgsql/data/global/2843
/usr/local/pgsql/data/postmaster.opts
/usr/local/pgsql/data/pg_twophase/
/usr/local/pgsql/data/postmaster.pid
/usr/local/pgsql/data/backup_label
/usr/local/pgsql/data/PG_VERSION

10,恢复data目录,重新创建pg_xlog目录及其子目录archive_status

 
代码 
  
    [root@daduxiong pgsql]# mv /archive/usr/local/pgsql/data /usr/local/pgsql
[root@daduxiong data]# mkdir pg_xlog
[root@daduxiong data]# chmod 0700 pg_xlog/
[root@daduxiong data]# chown postgre:postgre pg_xlog/
[root@daduxiong data]# cd pg_xlog/
[root@daduxiong pg_xlog]# mkdir archive_status
[root@daduxiong pg_xlog]# chmod 0700 archive_status/
[root@daduxiong pg_xlog]# chown postgre:postgre archive_status/
[root@daduxiong pg_xlog]# mv /archive/*00* /usr/local/pgsql/data/pg_xlog
[root@daduxiong pg_xlog]# cd ..
[root@daduxiong data]# ls
backup_label  pg_clog        pg_multixact  pg_twophase  postgresql.conf
base          pg_hba.conf    pg_subtrans   PG_VERSION   postmaster.opts
global        pg_ident.conf  pg_tblspc     pg_xlog      postmaster.pid

11,配置恢复配置文件

代码 
  
    [root@daduxiong data]# touch recovery.conf
[root@daduxiong data]# echo "restore_command='cp -i /archive/%f %p'" >>recovery.conf
[root@daduxiong data]# chown postgre:postgre recovery.conf
[root@daduxiong data]# chmod 0750 recovery.conf

12,启动数据库,观察数据库启动的日志

 
代码 
  
    [root@daduxiong data]# service postgresql start
Starting PostgreSQL: ok

---省略日志部分内容
LOG:  selected new timeline ID: 3
LOG:  restored log file "00000002.history" from archive
LOG:  archive recovery complete
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
cp: overwrite `/archive/000000010000000000000008'? cp: overwrite `/archive/000000010000000000000009'? cp: overwrite `/archive/000000020000000000000009'? cp: overwrite `/archive/00000002000000000000000A'?

13,验证恢复结果。检查之前创建的表与记录。

 
   
 
代码 
  
    
    [postgre@daduxiong archive]$ psql postgres
Welcome to psql 8.3.10, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# select * from abc;
 id
----
  1
(1 row)

postgres=# \q


[root@daduxiong data]# ls -l
total 80
-rw------- 1 postgre postgre   147 Aug 31 10:26 backup_label.old
drwx------ 6 postgre postgre  4096 Aug 27 11:33 base
drwx------ 2 postgre postgre  4096 Aug 31 10:41 global
drwx------ 2 postgre postgre  4096 Aug 10 11:06 pg_clog
-rwx------ 1 postgre postgre  3429 Aug 10 11:10 pg_hba.conf
-rwx------ 1 postgre postgre  1460 Aug 10 11:06 pg_ident.conf
drwx------ 4 postgre postgre  4096 Aug 10 11:06 pg_multixact
drwx------ 2 postgre postgre  4096 Aug 10 11:06 pg_subtrans
drwx------ 2 postgre postgre  4096 Aug 10 11:06 pg_tblspc
drwx------ 2 postgre postgre  4096 Aug 10 11:06 pg_twophase
-rwx------ 1 postgre postgre     4 Aug 10 11:06 PG_VERSION
drwx------ 3 postgre postgre  4096 Aug 31 10:35 pg_xlog
-rwx------ 1 postgre postgre 16727 Aug 31 09:42 postgresql.conf
-rwx------ 1 postgre postgre    59 Aug 31 10:35 postmaster.opts
-rw------- 1 postgre postgre    47 Aug 31 10:35 postmaster.pid
-rwxr-x--- 1 postgre postgre    39 Aug 31 10:34 recovery.done
   
   
 
 
 
   
 