synchronize two pg databases

Question

I have a postgresql server process each running in my desktop and the laptop.

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.

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.

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

I need both update for the modified records and insert for new records. But how?

score 4 · Answer 1 · answered Aug 18 '09 at 07:31

4

It looks like rubyrep would work for you.

answered Aug 18 '09 at 07:31

Tometzky

22,573
5
59
73

@yjfuk: and what is the problem with rubyrep? – Aug 18 '09 at 10:06
1

`rubyrep` seems to be quite outdated now. It doesn't seem to be very robust: I cannot get it syncing between two databases easily. See http://stackoverflow.com/questions/13941430/rubyrep-with-postgres-pgerror-connection-is-closed-when-trying-to-scan – fatuhoku Feb 01 '13 at 11:35
Rubyrep https://github.com/rubyrep/rubyrep/issues seems to have a lot of open issues, it has been hard time to get even a simple example running, https://github.com/rubyrep/rubyrep/issues/71. – hhh Aug 10 '18 at 14:45

score 1 · Answer 2 · answered Aug 18 '09 at 09:17

1

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.

answered Aug 18 '09 at 09:17

Well, it will work the way you'll write it. Generally everything depends on what your applications does with database. Simplistic approach: check newest id replicated to another database, and copy over all new ones. – Aug 19 '09 at 10:30
and what about modified data? i can't figure out how to sync it in a simplistic approach – sacabuche May 13 '11 at 17:20
add "last modified" timestamp set by trigger to "now()", and you're done. now just instead of comparing ids, compare the maximum last_modified – May 16 '11 at 08:40

score 1 · Answer 3 · answered Aug 18 '09 at 18:15

1

Use Slony-I to do postgreSQL synchronization.

answered Aug 18 '09 at 18:15

nont

9,322
7
62
82

Slony-I does not support multi-master replication, so it will not work here. – Tometzky Aug 20 '09 at 13:06

score 0 · Answer 4 · answered Aug 18 '09 at 13:24

0

Please refer to PostgreSQL documentation for High Availability, Load Balancing, and Replication

answered Aug 18 '09 at 13:24

Space

7,049
6
49
68

score 0 · Answer 5 · answered Jul 19 '16 at 18:13

To add to user80168's answer, here is a potential table merger:

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;
    }

}

synchronize two pg databases

5 Answers5