cengos 7 postgresql 备份和还原 psql备份表

转载

mob6454cc70219b 2024-03-06 11:41:17

文章标签 PostgreSQL 备份 sql hive 数据库 文章分类 云原生云计算

针对于PostgreSQL备份方式有三种：

1.SQL转储

2.热备

3.文件系统冷备份

之后对于这三种备份方式一一演示：

1.SQL转储

这里我们用到的工具是pg_dump和pg_dumpall，它和Oracle中的expdp，Mysql的mysqldump类似，它可以在数据库正在使用的时候进行完整一致的备份，并不阻塞其它用户对数据库的访问。

同时在使用此种备份方法时可以使用数据库层面的命令对数据进行压缩或分割，如：

1）压缩

备份：

pg_dump dbname | gzip > filename.gz

恢复

gunzip -c filename.gz | psql dbname

2）分割

备份：

pg_dump dbname | split -b 1m - filename

恢复

cat filename* | psql dbname

具体实验步骤如下：

pg_dump
首先创建数据库并创建测试表
[pg@edb1 ~]$ createdb pg
[pg@edb1 ~]$ psql pg
psql (9.3.9)
Type "help" for help.
 
pg=# create table backup_test(x int);
CREATE TABLE
pg=# insert into backup_test values(1);
INSERT 0 1
pg=# insert into backup_test values(2);
INSERT 0 1
pg=# select * from backup_test;
 x 
---
 1
 2
(2 rows)
pg=# \q
使用pg_dump工具进行SQL转储：
[pg@edb1 ~]$ pg_dump pg > /home/pg/pg.dmp
删掉原有数据库，使用dmp文件重建：
[pg@edb1 ~]$ dropdb pg
[pg@edb1 ~]$ createdb pg
[pg@edb1 ~]$ psql pg < /home/pg/pg.dmp
....

验证数据是否恢复：

[pg@edb1 ~]$ psql pg
psql (9.3.9)
Type "help" for help.
 
pg=# select * from backup_test;
 x 
---
 1
 2
(2 rows)
 
pg_dumpall

刚才使用pg_dump只能备份单库，使用pg_dumpall可以备份本地所有库

实验过程如下：

创建测试数据库及测试表

[pg@edb1 ~]$ createdb pg1
[pg@edb1 ~]$ createdb pg2
[pg@edb1 ~]$ psql pg1
psql (9.3.9)
Type "help" for help.
 
pg1=# create table btest1(x int);
CREATE TABLE
pg1=# insert into btest1 values(1);
INSERT 0 1
pg1=# \q
[pg@edb1 ~]$ psql pg2
psql (9.3.9)
Type "help" for help.
 
pg2=# create table btest2(x int);
CREATE TABLE
pg2=# insert into btest2 values(1);
INSERT 0 1
pg2=# \q
备份数据库：
[pg@edb1 ~]$ pg_dumpall > /home/pg/pg_all.dmp
删除数据库：
[pg@edb1 ~]$ dropdb pg1
[pg@edb1 ~]$ dropdb pg2
恢复数据库：
[pg@edb1 ~]$ psql -f /home/pg/pg_all.dmp postgres
......
验证数据：
[pg@edb1 ~]$ psql pg1
psql (9.3.9)
Type "help" for help.
 
pg1=# select * from btest1;
 x 
---
 1
(1 row)
 
pg1=# \q
[pg@edb1 ~]$ psql pg2
psql (9.3.9)
Type "help" for help.
 
pg2=# select * from btest2;
 x 
---
 1
(1 row)
 
pg2=# \q

2.在线热备

使用select pg_start_backup和select pg_stop_backup()对数据库进行热备

实验过程如下：

（1）开启归档

[pg@edb1 ~]$ vi /home/pg/pgsql/data/postgresql.conf 
配置以下三个参数：
wal_level = archive
archive_mode = on
archive_command = 'cp %p /home/pg/pgsql/backup/archived_log/%f'
（2）创建归档目录
[pg@edb1 ~]$ mkdir -p /home/pg/pgsql/backup/archived_log
（3）重启数据库，使参数生效
[pg@edb1 ~]$ pg_ctl -D /home/pg/pgsql/data stop
[pg@edb1 ~]$ pg_ctl -D /home/pg/pgsql/data start
（4）创建测试库
[pg@edb1 ~]$ createdb arch
[pg@edb1 ~]$ psql arch
psql (9.3.9)
Type "help" for help.
 
arch=# create table btest(x int);
CREATE TABLE
arch=# insert into btest values(1);
INSERT 0 1
（5）创建备份
arch=# select pg_start_backup('baseline');
 pg_start_backup 
-----------------
 0/5000028
(1 row)
 
arch=# \q
（6）备份数据库目录
[pg@edb1 ~]$ tar -zcvf /home/pg/pg_backup.tar.gz /home/pg/pgsql/data/
......
（7）停止备份
[pg@edb1 ~]$ psql arch
psql (9.3.9)
Type "help" for help.
 
arch=# select pg_stop_backup();
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup 
----------------
 0/50000B8
(1 row)
（8）再插入一些其他数据并切换WAL日志
arch=# insert into btest values(2);
INSERT 0 1
arch=# insert into btest values(3);
INSERT 0 1
arch=# insert into btest values(4);
INSERT 0 1
arch=# select pg_switch_xlog();
 pg_switch_xlog 
----------------
 0/6000208
(1 row)
（9）停止数据库
[pg@edb1 ~]$ pg_ctl -D /home/pg/pgsql/data stop
LOG:  received smart shutdown request
LOG:  autovacuum launcher shutting down
LOG:  shutting down
waiting for server to shut down....LOG:  database system is shut down
 done
server stopped
（10）删除目录
[pg@edb1 ~]$ rm -rf /home/pg/pgsql/data/
（11）使用tar包备份恢复目录
[pg@edb1 ~]$ tar -zxvf pg_backup.tar.gz -C /
......
（12）重置pg_xlog目录
[pg@edb1 ~]$ rm -r /home/pg/pgsql/data/pg_xlog/
[pg@edb1 ~]$ mkdir /home/pg/pgsql/data/pg_xlog/
（13）创建目录存放之前的归档
[pg@edb1 ~]$ mkdir arch
[pg@edb1 ~]$ cp /home/pg/pgsql/backup/archived_log/* arch/
（14）编辑recovery.conf文件
[pg@edb1 ~]$ vi pgsql/data/recovery.conf
restore_command = ‘cp /home/pg/arch/%f  “%p”’
(15)启动数据库
[pg@edb1 ~]$ pg_ctl -D /home/pg/pgsql/data/ start
server starting
[pg@edb1 ~]$ LOG:  database system was interrupted; last known up at 2015-09-21 18:27:41 CST
LOG:  starting archive recovery
LOG:  restored log file "000000010000000000000005" from archive
LOG:  redo starts at 0/5000090
LOG:  consistent recovery state reached at 0/50000B8
LOG:  restored log file "000000010000000000000006" from archive
cp: cannot stat `/home/pg/arch/000000010000000000000007': No such file or directory
LOG:  redo done at 0/60001E8
LOG:  last completed transaction was at log time 2015-09-21 18:30:16.033281+08
LOG:  restored log file "000000010000000000000006" from archive
cp: cannot stat `/home/pg/arch/00000002.history': No such file or directory
LOG:  selected new timeline ID: 2
cp: cannot stat `/home/pg/arch/00000001.history': No such file or directory
LOG:  archive recovery complete
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

可以看到数据库恢复到了最后一个归档所在的时间点

（16）验证数据

[pg@edb1 ~]$ psql arch
psql (9.3.9)
Type "help" for help.
 
arch=# select * from btest;
 x 
---
 1
 2
 3
 4
(4 rows)

3.文件系统冷备份

方法就是对数据库相关文件直接进行备份，由于需要停库，对业务影响较大，所以一般不使用这种方法。

实验过程如下：

创建测试库

[pg@edb1 ~]$ createdb test1
[pg@edb1 ~]$ psql test1
psql (9.3.9)
Type "help" for help.
 
test1=# create table test1(x int);
CREATE TABLE
test1=# insert into test1 values(1);
INSERT 0 1
test1=# \q
关库
[pg@edb1 ~]$ pg_ctl -D /home/pg/pg
pg_all.dmp  pg.dmp      pg.log      pgsql/      
[pg@edb1 ~]$ pg_ctl -D /home/pg/pgsql/data/ stop
waiting for server to shut down.... done
server stopped
进行文件系统打包冷备
[pg@edb1 ~]$ tar -zcvf pgdata.tar.gz  /home/pg/pgsql/data 
......
删除原来的文件
[pg@edb1 ~]$ rm -rf /home/pg/pgsql/data/
使用tar包中内容进行替换
[pg@edb1 ~]$ tar -zxvf pgdata.tar.gz -C /
启动数据库查看
[pg@edb1 ~]$ pg_ctl -D /home/pg/pgsql/data/ start
server starting
[pg@edb1 ~]$ LOG:  database system was shut down at 2015-09-21 18:02:27 CST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
 
[pg@edb1 ~]$ 
[pg@edb1 ~]$ psql test1
psql (9.3.9)
Type "help" for help.
 
test1=# select * from test1;
 x 
---
 1
(1 row)
 
test1=# \q

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。