本地PostgreSQL和云端PostgreSQL实例同时工作

huangapple go评论63阅读模式
英文:

Simultaneous work of local PostgreSQL and cloud PostgreSQL instances

问题

我目前正在使用本地的PostgreSQL数据库实例,希望在云中的虚拟机(VM),具体来说是Azure上创建另一个PostgreSQL数据库实例。<br>
我的目标是在这两个实例之间同步数据,并使数据库能够同时运行。<br>
我该如何做呢?<br>
提前感谢您。

英文:

I am currently using a local PostgreSQL database instance and would like to create another PostgreSQL database instance on a virtual machine (VM) in the cloud, specifically Azure. <br>
My goal is to synchronize data between these two instances and enable simultaneous operation of the databases.<br>
How can I do it?<br>
Thank you in advance

答案1

得分: 0

同时工作于本地PostgreSQL和云PostgreSQL实例的解决方案是复制。

我没有找到适合我的好指南。

附上我创建的指南:

配置主服务器

  1. 修改postgresql.conf
    1. listen_addresses = '*'
    2. wal_level = replica
    3. max_wal_senders = 100
    4. max_replication_slots = 10
  2. 为复制创建用户
    1. 在<数据库名称>上运行以下命令,使用具有管理员权限的<用户名>:
      psql -d <database name> -U <user name with admin permissions> -c "CREATE ROLE <user for replication> LOGIN REPLICATION ENCRYPTED PASSWORD '<password>';"
      
  3. 修改pg_hba.conf
    1. 在文件末尾添加以下内容:
      host replication <user for replication> <replica server ip>/32 scram-sha-256
      
  4. 重新启动主服务器
    1. 运行以下命令:
      pg_ctl -D "<primary server data folder>" restart
      

配置副本服务器

  1. 从主服务器复制数据
    1. 运行以下命令:
      pg_basebackup -h <primary server ip> -U <user for replication> --checkpoint=fast -D <replica server data folder> -R --slot=<some slot name> -C --port=<primary server port>
      
  2. 修改postgresql.conf
    1. 修改端口号
    2. primary_conninfo = 'host= port= user= password='
  3. 启动副本服务器
    1. 运行以下命令:
      pg_ctl -D "<replica server data folder>" start
      

测试

  1. 主服务器
    1. 运行以下命令:
      psql -d postgres -U postgres --port=<primary server port> -c "SELECT * FROM pg_stat_replication;"
      
  2. 副本服务器
    1. 运行以下命令:
      psql -d postgres -U postgres --port=<replica server port> -c "SELECT * FROM pg_stat_wal_receiver;"
      
英文:

The solution of Simultaneous work of local PostgreSQL and cloud PostgreSQL instances is replication.<br>
I didn't find any good guide that worked for me.<br>
Attach a guide that I built.<br>

Configure primary server
1. Modify postgresql.conf
	1. listen_addresses = &#39;*&#39;
	2. wal_level = replica
	3. max_wal_senders = 100
	4. max_replication_slots = 10
2. Create user for replication
	1. psql  -d &lt;database name&gt;   -U &lt;user name with admin permissions&gt;   -c &quot;CREATE ROLE &lt;user for replication&gt; LOGIN REPLICATION ENCRYPTED PASSWORD &#39;&lt;password&gt;&#39;;&quot;
3. Modify pg_hba.conf
	1. add at the end of file
		1. host &#160; &#160;replication &#160; &#160; &lt;user for replication&gt; &#160; &#160; &#160;&lt;replica server ip&gt;/32 &#160; &#160; &#160; scram-sha-256
4. Restart primary server
	1. pg_ctl -D &quot;&lt;data folder of primary server&gt;&quot; restart

Configure replica server
1. Copy the data from primary server
	1. pg_basebackup -h &lt;primary server ip&gt; -U &lt;user for replication&gt; --checkpoint=fast -D &lt;data folder for replica server&gt; -R --slot=&lt;some slot name&gt; -C --port=&lt;port of primary server&gt;
2. Modify postgresql.conf
	1. Change the port number
	2. primary_conninfo = &#39;host=&lt;primary server ip&gt; port=&lt;primary server port&gt; user=&lt;user for replication&gt; password=&lt;password for the user for replication&gt;&#39;
3. pg_ctl -D &quot;&lt;data folder for replica server&gt;&quot; start

Testing
1. Primary server
	1. psql -d postgres -U postgres --port=&lt;primary server port&gt; -c &quot;SELECT * FROM pg_stat_replication;&quot;
2. Replica server
	1. psql -d postgres -U postgres --port=&lt;replica server port&gt; -c &quot;SELECT * FROM pg_stat_wal_receiver;&quot;

huangapple
  • 本文由 发表于 2023年6月1日 16:52:14
  • 转载请务必保留本文链接:https://go.coder-hub.com/76380186.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定