PostgreSQL主从复制--物理复制

数据库干货铺 2023-03-28 原文

1、复制类型

PostgreSQL支持物理复制（流复制）及逻辑复制2种。通过流复制技术，可以从实例级复制出一个与主库一模一样的实例级的从库。流复制同步方式有同步、异步两种。

另一种复制方式为逻辑复制，区别于物理复制的是物理复制是基于实例级的复制，只能复制整个PostgreSQL实例，而不能基于部分库及表。从PostgreSQL10开始，出现了基于表级别的复制，即逻辑复制。

2、流复制

主库安装及从库编译此处就省略了，直接进入主从复制的安装环节。

（1）修改主库配置文件postgresql.conf

/*  除了基础参数,搭建备库至少需要配置如下参数 */
listen_address = '*'
wal_level = replica
archive_mode = on 
archive_command = 'cp %p /data/postgresql/archive/%f '
max_wal_senders= 10  
wal_keep_segments=1024
hot_standby = on

参数简要说明：

listen_address：按需设置,本次测试配置为所有主机均可以访问,生产环境可以按需配置网段或IP等。
wal_level：设置流复制模式至少设置为replica。
archive_mode: 本次启用归档。
archive_command：WAL日志归档命令,生产环境可以将归档拷贝到对应目录或其他机器上,本次测试配置为归档到本机的另一个目录下。
max_wal_senders：最大WAL发送进程数，此数量需大于等于从库个数且比max_connections小。
wal_keep_segments：pg_wal目录下保留WAL日志的个数,每个WAL文件默认16M,为保障从库能在应用归档落后时依旧能追上主库，此值建议设置较大一点。
hot_standby：此参数控制在恢复归档期间是否支持只读操作，设置为ON后从库为只读模式。

注意：上述参数中有涉及归档日志的路径，需手动创建

mkdir -p /data/postgresql/archive/

（2）创建复制账号

为了数据安全及便于权限控制，创建一个复制专用的数据库账号。

postgres=# create user repl  REPLICATION  LOGIN ENCRYPTED  PASSWORD 'repl123';
CREATE ROLE

（3）修改配置文件pg_hba.conf

添加复制账号的权限，因可能会主从切换，因此主从机器的IP均添加。也可以设置网段，以便于后期添加从库。

# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
host    replication     repl             192.168.56.33/24            md5
host    replication     repl             192.168.56.32/24            md5

（4）备份数据

从机上在线备份主库数据，并将数据放在指定路径，此路径建议与主库路径一致。可以使用pg_basebackup。

命令在线热备份，具体命令如下：

pg_basebackup -h 192.168.56.32 -U repl -p 5432 -F p -X s -v -P -R -D /data/postgresql/data/ -l postgres32

pg_basebackup命令中的参数说明：

-h 指定连接的数据库的主机名或IP地址，这里就是主库的ip。
-U 指定连接的用户名，此处是我们刚才创建的专门负责流复制的repl用户。
-F 指定生成备份的数据格式，支持p（plain原样输出）或者t（tar格式输出）。
-X 表示备份开始后，启动另一个流复制连接从主库接收WAL日志，有 f(fetch)和s (stream）两种方式，建议使用s方式。
-P 表示显示数据文件、表空间传输的近似百分比允许在备份过程中实时的打印备份的进度。
-v 表示启用verbose模式，命令执行过程中会打印各阶段日志，建议启用。
-R 表示会在备份结束后自动生成recovery.conf文件，这样也就避免了手动创建。
-D 指定把备份写到哪个目录，这里尤其要注意一点就是做基础备份之前从库的数据目录（/data/postgresql/data）目录需要手动清空。
-l 表示指定个备份的标识，运行命令后可以看到进度提示。

以上备份命令输出过程如下：

[postgres@PG33 data]$ pg_basebackup -h 192.168.56.32 -U repl -p 5432 -F p   -X s  -v -P -R -D /data/postgresql/data/ -l postgres32
Password: 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_17737"
56041/56041 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/20000F8
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

从以上日志信息看出pg_basebackup命令首先对数据库做一次checkpoint，之后基于时间点做一个全库基准备份，全备过程中会拷贝$PGDATA数据文件和表空间文件到备库节点对应目录。

（5）修改recovery.conf

以上备份命令中生成了recovery.conf 文件,因此简单修改即可。

standby_mode = 'on'
primary_conninfo = 'user=repl password=repl123 host=192.168.56.32 port=5432 sslmode=disable sslcompression=0 target_session_attrs=any'
## 添加如下信息
recovery_target_timeline = 'latest'

参数说明：

standby_mode：设置是否启用数据库为备库，如果设置成on，备库会不停地从主库上获取WAL日志流，直到获取主库上最新的WAL日志流
primary_conninfo：设置主库的连接信息，这里设置了主库IP、端口、用户名信息等，此处是明文密码，生产环境建议配置非明文密码，而是将密码配置在另一个隐藏文件中
covery_target_timeline：设置恢复的时间线（timeline），默认情况下是恢复到基准备份生成时的时间线，设置成latest表示从备份中恢复到最近的时间线，通常流复制环境设置此参数为latest，复杂的恢复场景可将此参数设置成其他值

（6）启动从库

直接使用pg_ctl或配置服务启动从库即可。

pg_ctl -D /data/postgresql/data/ -l pg33.log start

如果启动过程中出现如下错误。

waiting for server to start....2019-09-26 10:40:54.327 CST [10267] FATAL:  data directory "/data/postgresql/data" has invalid permissions
    2019-09-26 10:40:54.327 CST [10267] DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
     stopped waiting
    pg_ctl: could not start serve
    Examine the log output.

则需要先修改权限，再启动即可。

[postgres@PG33 data]$ chmod 0750 /data/postgresql/data/
[postgres@PG33 data]$ pg_ctl  -D /data/postgresql/data/  -l pg33.log  start
waiting for server to start.... done
server started

（7）测试主从同步

在主库创建表并新增数据。

[postgres@PG32 ~]$ psql
psql (11.4)
Type "help" for help.
postgres=# create table test2(id int primary key, name varchar(20));
CREATE TABLE
postgres=# insert into test2 values(1,'aaa'),(2,'abc');
INSERT 0 2

在从库查看。

[postgres@PG33 data]$ psql
psql (11.4)
Type "help" for help.
postgres=# select * from test2;
 id | name 
----+------
  1 | aaa
  2 | abc

数据已正常同步。

（8）查看复制状态

通过pg_stat_replication视图可以查看复制状态

postgres=# select  pid ,usesysid,usename,client_addr,state,sync_state  from  pg_stat_replication;
  pid  | usesysid | usename |  client_addr   |   state   | sync_state 
-------+----------+---------+----------------+-----------+------------
 25123 |    16797 | repl    | 192.168.56.33 | streaming | async
(1 row)

以上查询结果sync_state字段值为async，表示主备数据复制使用异步方式；state值为streaming,表示流复制方式。

（9）调整为同步复制

前面的步骤部署的为异步复制,如想配置为同步复制，则调整recovery.conf配置文件里的 synchronous_commit及synchronous_standby_names 后重启或reload即可。

synchronous_commit = remote_write
synchronous_standby_names = 'PG33'

之后再查看结果如下：

postgres=# select  pid ,usesysid,usename,client_addr,state,sync_state  from  pg_stat_replication;
  pid  | usesysid | usename |  client_addr   |   state   | sync_state 
-------+----------+---------+----------------+-----------+------------
 16265 |    16797 | repl    | 192.168.56.33 | streaming | sync
(1 row)

此时状态已变为同步复制了。

注：synchronous_commit 有多种方式，在流复制模式下,主要设置情况如下：

remote_write：当流复制主库提交事务时，需等待备库接收主库发送的WAL日志流并写入备节点操作系统缓存中，之后向客户端返回成功，这种情况下备库实例出现异常关闭时不会有已传送的WAL日志丢失风险，但备库操作系统异常宕机就有已传送的WAL丢失风险了，此时WAL可能还没完全写入备节点WAL文件中，简单地说remote_write表示本地WAL已落盘，备库的WAL还在备库操作系统缓存中，也就是说只有一份持久化的WAL，这个选项带来的事务响应时间较低
on：设置成on表示流复制主库提交事务时，需等待备库接收主库发送的WAL日志流并写入WAL文件，之后才向客户端返回成功，简单地说on表示本地WAL已落盘，备库的WAL也已落盘，也就是说有两份持久化的WAL，但备库此时还没有完成重做，这个选项带来的事务响应时间较高
remote_apply：表示表示流复制主库提交事务时，需等待备库接收主库发送的WAL并写入WAL文件，同时备库已经完成重做，之后才向客户端返回成功，简单地说remote_apply表示本地WAL已落盘，备库WAL已落盘并且已完成重做，这个设置保证了拥有两份持久化的WAL，同时备库也完成了重做，这个选项带来的事务响应时间最高，即性能最差。

PostgreSQL 复制 span style color 数据库 $PostgreSQL 主从复制

有关PostgreSQL主从复制--物理复制的更多相关文章

ruby-on-rails - 如何让 datamapper 与 postgresql 数据库一起工作？ - 2
我已经找到了几个使用datamapper的示例，并且能够让它们正常工作。不过，所有这些示例都是针对sqlite数据库的。我正在尝试将数据映射器与postgresql一起使用。我将datamapper中的调用从sqlite3更改为postgres，并且我已经安装了dm-postgres-adapter。但它仍然不起作用。我还需要做什么？最佳答案与SQLite不同，PostgreSQL不将数据库存储在单个文件中。在你拥有createdyourdatabase之后，尝试这样的事情:DataMapper.setup:default,{:
ruby - 如何在 ruby 中复制目录结构，不包括某些文件扩展名 - 2
我想编写一个ruby脚本来递归复制目录结构，但排除某些文件类型。因此，给定以下目录结构:folder1folder2file1.txtfile2.txtfile3.csfile4.htmlfolder2folder3file4.dll我想复制这个结构，但不包含.txt和.cs文件。因此，生成的目录结构应如下所示:folder1folder2file4.htmlfolder2folder3file4.dll 最佳答案您可以使用查找模块。这是一个代码片段:require"find"ignored_extensions=[".cs"
sql - 在 Rails Console for PostgreSQL 的表中显示数据 - 2
我找到了这样的东西:Rails:Howtolistdatabasetables/objectsusingtheRailsconsole?这一行没问题:ActiveRecord::Base.connection.tables并返回所有表但是ActiveRecord::Base.connection.table_structure("users")产生错误:ActiveRecord::Base.connection.table_structure("projects")我认为table_structure不是Postgres方法。如何列出Postgres数据库的Rails控制台中表中的所有
ruby - 在两个 ActiveRecord 类之间合并/复制属性的好方法？ - 2
之前有人问过这个问题，我发现了以下clip关于如何一次设置一个类对象的所有属性，但由于批量分配保护，这在Rails中是不可能的。(例如，您不能Object.attributes={})有没有一种很好的方法可以将一个类的属性合并到另一个类中？object1.attributes=object2.attributes.inject({}){|h,(k,v)|h[k]=vifObjectModel.column_names.include?(k);h}谢谢。最佳答案利用assign_attributes使用:without_prote
ruby-on-rails - Rails 迁移中的 PostgreSQL 点类型 - 2
我想使用PostgreSQL中的point类型。我已经完成了:railsgmodelTestpoint:point最终的迁移是:classCreateTests当我运行时:rakedb:migrate结果是:==CreateTests:migrating====================================================--create_table(:tests)rakeaborted!Anerrorhasoccurred,thisandalllatermigrationscanceled:undefinedmethod`point'for#/hom
ruby-on-rails - Ruby on Rails 单表继承(STI)和单元测试问题(使用 PostgreSQL) - 2
我正在使用带有单个“帐户”表的STI模型来保存用户和技术人员的信息(即用户...8)错误:test_the_truth(用户测试):ActiveRecord::StatementInvalid:PGError:ERROR:关系“技术人员”不存在:从“技术人员”中删除...从本质上讲，标准框架不承认Technicians和Users表(或PostgreSQL称它们为“关系”)不存在，事实上，应该别名为Accounts。有什么想法吗？我对RoR比较陌生，不知道如何解决这个问题而又不完全删除STI。最佳答案原来问题是由于存在:./te
ruby - rails 3.2.2(或 3.2.1)+ Postgresql 9.1.3 + Ubuntu 11.10 连接错误 - 2
我正在使用PostgreSQL9.1.3(x86_64-pc-linux-gnu上的PostgreSQL9.1.3，由gcc-4.6.real(Ubuntu/Linaro4.6.1-9ubuntu3)4.6.1，64位编译)和在ubuntu11.10上运行3.2.2或3.2.1。现在，我可以使用以下命令连接PostgreSQLsupostgres输入密码我可以看到postgres=#我将以下详细信息放在我的config/database.yml中并执行“railsdb”，它工作正常。开发:adapter:postgresqlencoding:utf8reconnect:falsedat
Ruby:我怎样才能复制这个数组？ - 2
(跟进我之前的问题，Ruby:howcanIcopyavariablewithoutpointingtothesameobject?)我正在编写一个简单的Ruby程序来在.svg文件中进行一些替换。第一步是从文件中提取信息并将其放入数组中。为了避免每次调用此函数时都从磁盘读取文件，我尝试使用memoize设计模式-在第一次调用后的每次调用中都使用缓存结果。为此，我使用了一个在函数之前定义的全局变量。但是，即使我在返回局部变量之前将该变量.dup为局部变量，调用该变量的函数仍在修改全局变量。这是我的实际代码:#memoizetokeepfromhavingtoreadoriginalfi
ruby-on-rails - 如何在文本字段rails postgresql中查询json数据 - 2
我有一个数据库表列(collection)，datatypetext我在collection中插入了json数据这是我的json数据{"name":"test","age":"25","country":"xxx"}但是现在我该如何查询这个json数据。有没有如下选项User.where(collection::name)#Herenamedenotestest 最佳答案由于JSON是一个字符串，您只能使用SQLite/MySQL(或任何其他数据库)进行字符串比较。此外，尽量将数据类型保持为二进制。您可以执行:LIKE操作以在您存
ruby-on-rails - Rails Postgresql 多个模式和相同的表名 - 2
我在两个不同的模式中有两个表，例如案例和事件。在每个模式中我都有基本表events.basiccases.basic这个表有关系:events.basic有一个cases.basic(cases.basic有多个events.basic)我的尝试失败了:文件cases_basic.rbclassCasesBasic'EventsBasic',:foreign_key=>'case_id'end文件events_basic.rbclassEventsBasic'CasesBasic',:foreign_key=>'case_id'end环境:Ruby1.9.3、Rails3.1.3、ge