javascript – How to maintain multiple connections to postgresql in Node.js?

Question:

I'm using Node.js in a task where I need to migrate data between two PostgreSQL databases. The idea is more or less as follows:

  1. Connect to Bank A.
  2. Connect to Bank B.
  3. Return all records of A.
  4. Insert in B all the Records of A not yet present.
  5. Update a field in all records that have been copied.
  6. Close all connections.

My question is how to keep both connections open simultaneously. I believe that I need to do this because the data, as I said, will transit between two banks, and I don't think it's a good idea to keep opening and closing the connection in bank B for each record of A found. I'm using pg package as an interface with DB. Has anyone ever had to do something like this and/or could tell me how to proceed?

Answer:

Hi. I believe this can be solved more or less like this:

var pg = require('pg');
//bancoA
var conString = "postgres://username:password@localhost/database";
var client1 = new pg.Client(conString);
//bancoB
var conString = "postgres://username:password@localhost/database";
var client2 = new pg.Client(conString);

You now have connection to both banks.

To return all A records:

    var query = client1.query('select * from table');
    //Acho que isso teria que ser feito tabela por tabela
    var rows = [];
    query.on('row', function(row) {
      rows.push(row);
    });
   //Esse codigo precisa ser testado. 
   //Nao tenho absoluta certeza de que isso funciona dessa forma. 
   //Teoricamente a variavel rows estaria com todos os registros
   //que o postgres tinha na tabela.

From that point on, we have to define the logic that we are going to use to define which values ​​will be inserted in bank B, since they will only be the records not yet present.

I believe the best way to do this is to do the same select on client2 . From there we would have a value for the rows variable with the values ​​already inserted in the BancoB table. And then just a simple logic would be needed to delete the values ​​of rowsDeA that already exist in rowsDeB .

It is worth remembering that, depending on the size of the tables and the frequency that this will happen, this code can be extremely expensive. If the idea is to make this a continuous run (to maintain a backup server, or something like that), maybe it's better to create a logic that inserts the values ​​in BankB at the same time in BankA.

One more disclaimer: Not sure this will work, but worth a try 🙂

Post Views: 6
Scroll to Top