postgresql 同步两个 pg 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1292107/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
synchronize two pg databases
提问by yjfuk
I have a postgresql server process each running in my desktop and the laptop.
我有一个 postgresql 服务器进程，每个进程都在我的台式机和笔记本电脑上运行。
Both servers have a database called MG with exactly same scheme/layout. Now I enter the data in to similar tables but at differing times.
两台服务器都有一个名为 MG 的数据库，其方案/布局完全相同。现在我将数据输入到类似的表中，但时间不同。
I generally keep the primary keys separate so that they don't clash with each other. eg: oddnumber pkey for laptop and even number for desktop.
我通常将主键分开，这样它们就不会相互冲突。例如：笔记本电脑的奇数 pkey 和台式机的偶数。
Now how do I synchronize the data between the desktop and laptop cleanly?
现在如何在台式机和笔记本电脑之间干净地同步数据？
DESK:ADDRESS ----- LAP:ADDRESS
DESK:TO_DO ----- LAP:TO_DO
DESK uses pkeys like 1001... for inserts
LAP uses pkeys like 2001... for inserts
DESK 使用 1001... 之类的 pkeys 进行插入
LAP 使用 2001... 之类的 pkey 进行插入
I need both update for the modified records and insert for new records. But how?
我需要更新修改后的记录并插入新记录。但是如何？
回答by Tometzky
The simplest way would be to write a custom script/program. It's not difficult, and definitely you will know and understand how it works, so extending it will be trivial.
最简单的方法是编写自定义脚本/程序。这并不难，你肯定会知道并理解它是如何工作的，所以扩展它是微不足道的。
回答by Motes
To add to user80168's answer, here is a potential table merger:
要添加到 user80168 的答案，这里是一个潜在的表合并：
package merge.tables;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class PostgreTableMerger {
private static String dbOrigin = "jdbc:postgresql://server1:5432/DB";
private static String dbDest = "jdbc:postgresql://server2:5432/DB";
private static String tableToMerge = "important_results";
public static void main(String[] args) throws Exception {
Connection dbConnOrigin = DriverManager.getConnection(dbOrigin, "pgadmin", "pgadmin");
Statement dbOriginStat = dbConnOrigin.createStatement();
Connection dbConnDest = DriverManager.getConnection(dbDest, "pgadmin", "pgadmin");
Statement dbDestStat = dbConnDest.createStatement();
String sqlToExecute = "SELECT * FROM " + tableToMerge;
ResultSet assets = dbOriginStat.executeQuery(sqlToExecute);
ResultSetMetaData rsMeta = assets.getMetaData();
while(assets.next()){
String insertSQL = "INSERT INTO " + tableToMerge + " VALUES(";
for(int i = 1; i <= rsMeta.getColumnCount(); i++){
String value = assets.getString(i);
if(assets.wasNull()){
insertSQL += "NULL,";
}else{
insertSQL += "'" + value + "',";
}
}
insertSQL =insertSQL.substring(0, insertSQL.length()-1) + ")";
try{
dbDestStat.executeUpdate(insertSQL);
}catch(SQLException e){
//TODO: attempt to update the row in the event of duplicate key
}
}
return;
}
}
回答by Space
Please refer to PostgreSQL documentation for High Availability, Load Balancing, and Replication
请参阅 PostgreSQL 文档以了解高可用性、负载平衡和复制
