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

Question

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

Navigation

#1 by (2 votes)

7

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:

Connect to Bank A.

Connect to Bank B.

Return all records of A.

Insert all A's Registers not yet present in B.

Refresh a field in all records that have been copied.

Close all connections.

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

sql javascript node.js postgresql

asked by anonymous 27.10.2015 / 14:05

1 answer

Can denying access to a folder with mod_rewrite be insecure? PHP - Make 3 INSERT on different tables with relationship between them

score 2 · Answer 1

Hi. I think 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 a 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 this point on, we have to define what logic we are going to use to define which values will be entered in Bank B, since only records that are not present will be present.

I think the best way to do this is to do the same select in client2 . From there we would have a value for the variable rows with the values already inserted in the table of BancoB. And it would only take a simple logic to delete the values of rowsDeA that already exist in rowsDeB .

Remember that depending on the size of the tables and the periodicity that this will happen, this code can be extremely costly. If the idea is to make it a continuous run (to maintain a backup server, or anything like that), it might be better to create a logic that inserts the values in BancoB at the same time as in BancoA.

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