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:
- Connect to Bank A.
- Connect to Bank B.
- Return all records of A.
- Insert in B all the Records of A not yet present.
- Update a field in all records that have been copied.
- 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 🙂