CODEWITHSUNDEEP
postgresqlreplication
yjfuk
yjfuk

Reputation: 1445

synchronize two pg databases

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?

Upvotes: 11

Views: 27713

Answers (5)

Motomotes
Motomotes

Reputation: 4237

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

}

Upvotes: 0

nont
nont

Reputation: 9519

Use Slony-I to do postgreSQL synchronization.

Upvotes: 1

Space
Space

Reputation: 7259

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

Upvotes: 0

user80168
user80168

Reputation:

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.

Upvotes: 1

Tometzky
Tometzky

Reputation: 23890

It looks like rubyrep would work for you.

Upvotes: 4

Related Questions