Fetch Data From Local Machine DB and Insert To Other Machine DB

Jes Sie

I need to fetch data from my laptop (Postgresql) and insert that to the main server (in a different machine) using also a PostgreSQL. In MSSQL I normally do this:

BEGIN TRY
BEGIN TRANSACTION
INSERT Main_Server.databasename.tbltransactions
SELECT * FROM Local_Server.databasename.tbltransactions
WHERE synchronized IS NULL
UPDATE Local_Server.databasename.tbltransactions SET synchronized = GETDATE() WHERE synchronized IS NULL
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RAISERROR('There was a problem connecting to the server database. The connection was lost.',16,1)
END CATCH

I need to do it using a SQL Transaction. Thank you in advance if you can show or point me to the right direction. I am very newbie in PostgreSql.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar6y ago
Hi Hold on,

Please use this link which will help you,
https://stackoverflow.com/questions/36476192/postgresql-copy-transfer-data-from-one-database-to-another
Jefferson S. Motta6y ago
May you need a GUI at your laptop, take a look at:

https://scalegrid.io/blog/which-is-the-best-postgresql-gui-2019-comparison/
Jes Sie6y ago
Hi Jeff, because I am selecting 33 tables from my laptop and do an insert or update the same 33 tables into my main server.

Your suggestion was my first option but I guess if I can do it in postgre, it will be lighter.
Jefferson S. Motta6y ago
Why did not you do a c# code, read from Laptop and update on server?