如何将阿里云RDS PgSQL恢复到本地主机中

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 阿里云的RDS for PostgreSQL目前提供的备份为物理备份,备份粒度可以自己设置,最频繁的基础备份可以做到一天一次。有了这个备份和归档日志,我们可以做到基于任意时间点(实际上是事务提交或回滚点的粒度)的恢复。在RDS的控制台可以看到: 接下来我将演示一下如何实现以及如何设计一个好的恢复方

阿里云的RDS for PostgreSQL目前提供的备份为物理备份,备份粒度可以自己设置,最频繁的基础备份可以做到一天一次。
有了这个备份和归档日志,我们可以做到基于任意时间点(实际上是事务提交或回滚点的粒度)的恢复。
在RDS的控制台可以看到:
2438417723262679885
6630193653955627404
2879207536791326529

接下来我将演示一下如何实现以及如何设计一个好的恢复方案。
甚至我们在了解其中的原理后,如果阿里云将来提供基于时间点的恢复服务,我们应该如何更好的与之结合来使用。

要恢复到任意时间点,我们就必须告诉恢复进程一个点,这个点可以是时间,字符串,或者XID。

  1. 时间很好理解,其实就是触及这个时间后的XLOG中的第一个事务结束位置作为停止点。
  2. 字符串,这个是需要通过pg_create_restore_point函数来创建的一个还原点,需要超级用户调用这个函数。
  3. XID也很好理解,就是恢复到指定事务的结束位置。
    既然我们已经知道了数据库可以恢复到指定的这几个位置,我们怎么来结合呢?

例如我们在做一笔比较重要的操作前,可以创建一个还原点(但是需要超级用户),不适合阿里云RDS。

postgres=# select pg_create_restore_point('digoal');  
 pg_create_restore_point   
-------------------------  
 1D6/FB17EC08  
(1 row)  

阿里云为了防止一些用户的误操作,只开放了普通用户给用户使用,所以有一些东西都无法操作,例如创建检查点,切换XLOG,创建还原点都无法操作。

postgres=> checkpoint;  
ERROR:  must be superuser to do CHECKPOINT  
postgres=> select pg_switch_xlog();  
ERROR:  must be superuser to switch transaction log files  
postgres=> select pg_create_restore_point('ab');  
ERROR:  must be superuser to create a restore point  

时间其实是一个比较模糊的概念,所以也不建议使用,除非是我们没有其他信息,才使用时间。
XID是一个很不错的信息,我们在阿里云就用它了。
首先要创建一个记录还原点XID的表。记录XID,时间,以及描述信息。(来代替pg_create_restore_point系统函数的功能)

postgres=> create table restore_point(id serial primary key, xid int8, crt_time timestamp default now(), point text);  
CREATE TABLE  

创建一个函数,代替pg_create_restore_point的功能,插入还原点。

postgres=> create or replace function create_restore_point(i_point text) returns void as 
$$
  
declare   
begin  
  insert into restore_point(xid,point) values (txid_current(),i_point);  
end;  

$$
 language plpgsql strict;  
CREATE FUNCTION  

插入一个还原点

postgres=> select create_restore_point('digoal');  
 create_restore_point   
----------------------  
   
(1 row)  

查询这个表的信息:

postgres=> select * from restore_point;  
 id |  xid   |          crt_time          | point    
----+--------+----------------------------+--------  
  1 | 561426 | 2015-06-19 09:18:57.525475 | digoal  
(1 row)  
postgres=> select * from restore_point where point='digoal';  
 id |  xid   |          crt_time          | point    
----+--------+----------------------------+--------  
  1 | 561426 | 2015-06-19 09:18:57.525475 | digoal  
(1 row)  

接下来要模拟一下还原:

postgres=> create table test(id int,info text);  
CREATE TABLE  
postgres=> insert into test select generate_series(1,1000),md5(random()::text);  
INSERT 0 1000  

记录当前哈希值。用于恢复后的比对。

postgres=> select sum(hashtext(t.*::text)) from test t;  
     sum        
--------------  
 -69739904784  
(1 row)  

接下来我要做一笔删除操作,在删除前,我先创建一条还原点信息。

postgres=> select create_restore_point('before delete test');  
 create_restore_point   
----------------------  
   
(1 row)  
postgres=> delete from test;  
DELETE 1000  
postgres=> select * from restore_point where point='before delete test';  
 id |  xid   |          crt_time          |       point          
----+--------+----------------------------+--------------------  
  2 | 561574 | 2015-06-19 09:45:28.030295 | before delete test  
(1 row)  

我只需要恢复到561574 即可。接下来就是模拟恢复了。
但是这个文件可能还没有归档,而pg_switch_xlog()函数又不能用,我们只能主动产生一些XLOG,让RDS触发归档。

postgres=> select pg_xlogfile_name(pg_current_xlog_location());  
     pg_xlogfile_name       
--------------------------  
 000000010000000200000041  
(1 row)  
postgres=> insert into test select generate_series(1,100000);  
INSERT 0 100000  
postgres=> insert into test select generate_series(1,100000);  
INSERT 0 100000  
postgres=> select pg_xlogfile_name(pg_current_xlog_location());  
     pg_xlogfile_name       
--------------------------  
 000000010000000200000042  
(1 row)  

已经切换。接下来我们需要下载阿里云RDS的备份和归档到本地。
并且在本地需要安装一个postgresql, 并且与阿里云RDS的编译配置参数一致(例如数据块的大小),最好使用的模块也一致,但是这里没有用到其他模块,所以无所谓。
给阿里云RDS一个建议,最好提供用户一个软件打包,方便用户恢复,降低恢复门槛。
编译项可以使用pg_config命令查看,但是RDS我们没有办法这么查看。通过pg_settings来看吧。

postgres=> select name,setting,unit from pg_settings where category='Preset Options';  
         name          | setting | unit   
-----------------------+---------+------  
 block_size            | 8192    |    
 data_checksums        | on      |   
 integer_datetimes     | on      |   
 max_function_args     | 100     |   
 max_identifier_length | 63      |   
 max_index_keys        | 32      |   
 segment_size          | 131072  | 8kB  
 server_version        | 9.4.1   |   
 server_version_num    | 90401   |   
 wal_block_size        | 8192    |   
 wal_segment_size      | 2048    | 8kB  
(11 rows)  
postgres=> select version();  
                                                   version                                                      
--------------------------------------------------------------------------------------------------------------  
 PostgreSQL 9.4.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3), 64-bit  
(1 row)  

本地编译安装PostgreSQL 9.4.1,编译参数与RDS一致。阿里云RDS这几个参数都是默认的。

  --with-blocksize=BLOCKSIZE  
                          set table block size in kB [8]  
  --with-segsize=SEGSIZE  set table segment size in GB [1]  
  --with-wal-blocksize=BLOCKSIZE  
                          set WAL block size in kB [8]  
  --with-wal-segsize=SEGSIZE  
                          set WAL segment size in MB [16]  

安装

# useradd digoal  
# su - digoal  
$ vi .bash_profile  
# add by digoal  
export PS1="$USER@`/bin/hostname -s`-> "  
export PGPORT=1931  
export PGDATA=/home/digoal/pg_root  
export LANG=en_US.utf8  
export PGHOME=/home/digoal/pgsql9.4.1  
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH  
export DATE=`date +"%Y%m%d%H%M"`  
export PATH=$PGHOME/bin:$PATH:.  
export MANPATH=$PGHOME/share/man:$MANPATH  
export PGHOST=$PGDATA  
export PGDATABASE=postgres  
alias rm='rm -i'  
alias ll='ls -lh'  
unalias vi  
  
# wget https://ftp.postgresql.org/pub/source/v9.4.1/postgresql-9.4.1.tar.bz2  
# tar -jxvf postgresql-9.4.1.tar.bz2  
# cd postgresql-9.4.1  
# ./configure --prefix=/home/digoal/pgsql9.4.1 --with-pgport=1931 --with-perl --with-python --with-tcl --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safety --enable-debug  
# gmake world && gmake install-world  

下载备份和归档文件,解压:
基础备份选择需要恢复的时间点之前的一个备份,归档则选择在此之后的所有归档文件。

total 453M  
-rw-r--r-- 1 root   root    17M Jun 19 10:23 000000010000000200000040.tar.gz  
-rw-r--r-- 1 root   root    17M Jun 19 10:23 000000010000000200000041.tar.gz  
-rw-r--r-- 1 root   root   404M Jun 19 10:23 hins668881_xtra_20150618232331.tar.gz  
  
# mkdir /home/digoal/pg_root  
# mv hins668881_xtra_20150618232331.tar.gz /home/digoal/pg_root  
# tar -zxvf hins668881_xtra_20150618232331.tar.gz  
[root@db-172-16-3-150 ~]# tar -xvf 000000010000000200000040.tar.gz   
000000010000000200000040  
[root@db-172-16-3-150 ~]# tar -xvf 000000010000000200000041.tar.gz   
000000010000000200000041  
[root@db-172-16-3-150 ~]# mv 000000010000000200000040 /home/digoal/  
[root@db-172-16-3-150 ~]# mv 000000010000000200000041 /home/digoal/  
[root@db-172-16-3-150 ~]# chown -R digoal:digoal /home/digoal  
[root@db-172-16-3-150 ~]# chmod -R 700 /home/digoal/pg_root  

修改配置文件

$ vi postgresql.conf  
port=1931  
注释RDS自定义的GUC参数  
#rds_enable_proxy=on  
#rds_available_extensions='plpgsql,pg_stat_statements,btree_gin,btree_gist,chkpass,citext,cube,dblink,dict_int,earthdistance,hstore,intagg,intarray,isn,ltree,pgcrypto,pgrowlocks,pg_prewarm,pg_trgm,postgres_fdw,sslinfo,tablefunc,tsearch2,unaccent,postgis,postgis_topology,fuzzystrmatch,postgis_tiger_geocoder,plperl,pltcl,plv8,plls,plcoffee,"uuid-ossp"'  
#rds_enable_admin_user_as_super=on  

配置recovery.conf
打开hot_standby,恢复到目标点后暂停,如果确认已经到达,使用resume激活。

$ vi recovery.conf  
standby_mode = 'on'  
restore_command = 'cp /home/digoal/%f %p'             
recovery_target_xid='561574'  # 使用我们自建的恢复点的XID  
recovery_target_inclusive=true  
pause_at_recovery_target=true  

启动数据库

$ pg_ctl start  

检查是否恢复到指定XID

digoal@db-172-16-3-150-> psql -h 127.0.0.1 -p 1931  
psql (9.4.1)  
Type "help" for help.  
  
postgres=> \dt  
             List of relations  
 Schema |      Name       | Type  | Owner    
--------+-----------------+-------+--------  
 public | ha_health_check | table | aurora  
 public | login_log       | table | digoal  
 public | restore_point   | table | digoal  
 public | session         | table | digoal  
 public | tbl_small       | table | digoal  
 public | test            | table | digoal  
 public | userinfo        | table | digoal  
(7 rows)  

检查,已经恢复到DELETE test表的数据之前了,HASH sum值一致。

postgres=> select count(*) from test;  
 count   
-------  
  1000  
(1 row)  
postgres=> select sum(hashtext(t.*::text)) from test t;  
     sum        
--------------  
 -69739904784  
(1 row)  

接下来就交给你了,导出逻辑备份,还原,或者其他。。。。。。

[其他]
.1. RDS增加了一些GUC控制参数,例如,

rds_enable_proxy=on  
rds_available_extensions='plpgsql,pg_stat_statements,btree_gin,btree_gist,chkpass,citext,cube,dblink,dict_int,earthdistance,hstore,intagg,intarray,isn,ltree,pgcrypto,pgrowlocks,pg_prewarm,pg_trgm,postgres_fdw,sslinfo,tablefunc,tsearch2,unaccent,postgis,postgis_topology,fuzzystrmatch,postgis_tiger_geocoder,plperl,pltcl,plv8,plls,plcoffee,"uuid-ossp"'  
rds_enable_admin_user_as_super=on  

可以看到对云化做的一些修改:

digoal@db-172-16-3-150-> cat postgresql.conf  
#  
#Thu Jun 11 22:34:23 CST 2015  
checkpoint_segments=64  
autovacuum_vacuum_scale_factor=0.1  
maintenance_work_mem=16MB  
log_min_duration_statement=1000  
archive_mode=on  
log_lock_waits=on  
max_prepared_transactions=800  
log_rotation_size=10MB  
timezone='PRC'  
archive_command='cp %p /u02/rds427ugtq91telkvtwap/arch/%f'  
track_functions=all  
port=3011  
max_replication_slots=10  
max_connections=100  
hot_standby=on  
lc_numeric='zh_CN.UTF-8'  
pg_stat_statements.max=1000  
checkpoint_completion_target=0.9  
lc_monetary='zh_CN.UTF-8'  
log_line_prefix='\1\n\t%p\t%r\t%u\t%d\t%t\t%e\t\t\t\t\t\t'  
datestyle='iso, ymd'  
wal_keep_segments=80  
superuser_reserved_connections=10  
default_text_search_config='pg_catalog.simple'  
max_wal_senders=5  
track_io_timing=on  
autovacuum_analyze_scale_factor=0.1  
work_mem=4MB  
wal_buffers=7864kB  
pg_stat_statements.track=all  
rds_enable_proxy=on  
logging_collector=on  
log_timezone='UTC'  
log_directory='pg_log'  
listen_addresses='*'  
shared_preload_libraries='pg_stat_statements'  
log_filename='postgresql-%Y-%m-%d_%H%M%S.log'  
wal_level=hot_standby  
hot_standby_feedback=on  
log_truncate_on_rotation=on  
log_rotation_age=0  
lc_time='zh_CN.UTF-8'  
lc_messages='C'  
default_statistics_target=100  
autovacuum_max_workers=5  
rds_available_extensions='plpgsql,pg_stat_statements,btree_gin,btree_gist,chkpass,citext,cube,dblink,dict_int,earthdistance,hstore,intagg,intarray,isn,ltree,pgcrypto,pgrowlocks,pg_prewarm,pg_trgm,postgres_fdw,sslinfo,tablefunc,tsearch2,unaccent,postgis,postgis_topology,fuzzystrmatch,postgis_tiger_geocoder,plperl,pltcl,plv8,plls,plcoffee,"uuid-ossp"'  
log_temp_files=100000  
log_statement='all'  
shared_buffers=256MB  
effective_cache_size=768MB  
autovacuum_vacuum_cost_delay=0  
bgwriter_delay=20ms  
rds_enable_admin_user_as_super=on  

.2. 从pg_hba.conf可以看出,阿里云的RDS 基于流复制的standby 节点有两个,本地一个,异地一个。
但是实际上只开了一个异地的流复制节点。并且没有使用slot,原因可能是准备用于同步流复制的。

digoal@db-172-16-3-150-> grep "^[a-z]" pg_hba.conf   
local   all             all                                     trust  
host    all             all             127.0.0.1/32            trust  
host    all             all             ::1/128                 trust  
local replication pgrds427ugtq91telkvtwap   trust  
host all all 0.0.0.0/0 md5  
host replication xx xx.xx.xx.xx/32 md5  

实际开了1个流复制节点:

postgres=> select * from pg_stat_replication ;  
  pid  | usesysid |  usename   | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | sta  
te | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state   
-------+----------+------------+------------------+-------------+-----------------+-------------+---------------+--------------+----  
---+---------------+----------------+----------------+-----------------+---------------+------------  
 27049 |    16384 | replicator | standby1         |             |                 |             |               |       561915 |      
   |               |                |                |                 |               |   
(1 row)  
postgres=> select * from pg_replication_slots ;  
 slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn   
-----------+--------+-----------+--------+----------+--------+------+--------------+-------------  
(0 rows)  

[参考]

  1. http://www.postgresql.org/docs/9.4/static/recovery-config.html
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
4天前
|
关系型数据库 MySQL Apache
**ADB MySQL湖仓版能够平滑迁移到湖仓**,阿里云提供了相应的迁移工具和服务来简化这一过程。
**ADB MySQL湖仓版能够平滑迁移到湖仓**,阿里云提供了相应的迁移工具和服务来简化这一过程。
19 2
|
4天前
|
SQL 关系型数据库 Serverless
阿里云关系型数据库RDS
阿里云关系型数据库RDS
16 2
|
4天前
|
运维 监控 关系型数据库
Serverless 应用引擎产品使用之在阿里云函数计算(FC)中,要访问另一个账号的rds配置rds的白名单如何解决
阿里云Serverless 应用引擎(SAE)提供了完整的微服务应用生命周期管理能力,包括应用部署、服务治理、开发运维、资源管理等功能,并通过扩展功能支持多环境管理、API Gateway、事件驱动等高级应用场景,帮助企业快速构建、部署、运维和扩展微服务架构,实现Serverless化的应用部署与运维模式。以下是对SAE产品使用合集的概述,包括应用管理、服务治理、开发运维、资源管理等方面。
51 0
|
4天前
|
运维 容灾 关系型数据库
阿里云关系型数据库RDS
阿里云关系型数据库RDS概述
252 2
|
4天前
|
关系型数据库 MySQL 数据库
阿里云rds数据库简介和如何使用
该内容是一个关于如何在阿里云上准备和使用MySQL数据库的教程。首先,你需要注册阿里云账号并购买阿里云数据库MySQL服务,选择合适的配置如地域、版本和存储类型。然后,通过DMS(Data Management Service)管理数据库,包括查看数据库实例、登录、切换数据库、创建数据库和数据库导出。此外,还介绍了如何使用MySQL Workbench或命令行工具连接到RDS MySQL实例。教程详细提供了每一步的链接和操作指南。
|
4天前
|
存储 关系型数据库 数据库
超1/3中国500强企业都在用的「汇联易」,为什么选用阿里云RDS?
迎峰而上:汇联易依托阿里云RDS通用云盘,加速业务智能化升级
超1/3中国500强企业都在用的「汇联易」,为什么选用阿里云RDS?
|
4天前
|
弹性计算 运维 监控
解密阿里云弹性计算:探索云服务器ECS的核心功能
阿里云ECS是核心计算服务,提供弹性云服务器资源,支持实例按需配置、集群管理和监控,集成安全防护,确保服务稳定、安全,助力高效业务运营。
81 0
|
4天前
|
存储 弹性计算 固态存储
阿里云服务器CPU内存配置详细指南,如何选择合适云服务器配置?
阿里云服务器配置选择涉及CPU、内存、公网带宽和磁盘。个人开发者或中小企业推荐使用轻量应用服务器或ECS经济型e实例,如2核2G3M配置,适合低流量网站。企业用户则应选择企业级独享型ECS,如通用算力型u1、计算型c7或通用型g7,至少2核4G配置,公网带宽建议5M,系统盘可选SSD或ESSD云盘。选择时考虑实际应用需求和性能稳定性。
149 6
|
4天前
|
域名解析 弹性计算 Linux
阿里云购买云服务器、注册域名、备案及绑定图文教程参考
本文为大家介绍了2024年购买阿里云服务器和注册域名,绑定以及备案的教程,适合需要在阿里云购买云服务器、注册域名并备案的用户参考,新手用户可通过此文您了解在从购买云服务器到完成备案的流程。
阿里云购买云服务器、注册域名、备案及绑定图文教程参考
|
2天前
|
存储 安全 数据库
阿里云服务器计算型、通用型、内存型主要实例规格特点、适用场景及最新价格参考
在阿里云服务器的实例规格中,有共享型也有企业型,一般用户选择较多的企业级实例规格有计算型、通用型、内存型,每一种实例规格又有多个实例规格族可选,不同的云服务器实例规格在架构、计算、存储、网络、安全等方面有着不同,因此,其适用场景也有所不同。本文来详细介绍一下阿里云服务器计算型、通用型、内存型主要实例计算、存储等性能及其适用场景,以供参考。
阿里云服务器计算型、通用型、内存型主要实例规格特点、适用场景及最新价格参考

相关产品

  • 云数据库 RDS MySQL 版