javascript – How to keep multiple connections with 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 goes something like this:

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

My question is how to keep both connections open simultaneously. I believe 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 the pg package as an interface with the DB. Has anyone ever needed to do something like this and/or would they know how to tell me how to proceed?

Answer:

Hi. I believe this can be solved something 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 records from A:

    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 onwards, we have to define the logic that we will use to define which values ​​will be inserted in bank B, since they will only be records that are 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 variable rows with the values ​​already inserted in the BancoB table. And then it would only take a simple logic 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 with which this will happen, this code can be extremely costly. 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 as in BankA.

One more disclaimer: I'm not sure this will work, but it's worth the test 🙂

Post Views: 0
Scroll to Top