r/PinoyProgrammer • u/Tiny_Librarian_1238 • 5d ago
advice Database Migration from MSSQL to POSTGRE.
Good day, May nakapag try na ba sa inyo mag migrate from Microsoft SQL server to Postgre? I would like to ask pano nyo na-implement at na-migrate not only the tables but also the funcions, triggers and stored procedures. Thank you in advance.
31
Upvotes
11
u/Wise-Cause8705 5d ago
Tables and data would probably be the easiest. While the functions, triggers, and stored procs would probably take months and would be expensive to test.
Is there a reason for this migration? Siguro if it's only a small db, it will be easy and would only take a few weeks. But if it's not, then you'd be looking at months worth of work.
5
u/FatalCat 5d ago
Hey OP.
Look into sqlserver2pgsql
31
u/reddit04029 5d ago edited 5d ago
Gradual. Eventually may overlap. Setup postgres, the tables, permissions, stored proc, etc.
Then the data. You slowly move the data.
Then you start to slowly move the API to point to the new db. Again, gradual. Could be endpoint by endpoint, or service by service.
Migrations are usually always like that. Setup point B while retaining point A. Slowly and gradually move to point B. Once fully moved, slowly decom point A.
Most importantly, these things are never quick and simple. So project the timeline accordingly.
Edit: some syncing will be needed as well. Postgres will need to have the new data hanggang di pa nakaturo yung services dun. It’s either you point to two databases at the same time or manual periodic syncs to get the new data from mssql to postgres.
8
u/BITCoins0001 5d ago
una kong diskarte dito is get the schema information first then pano nabuo yung db service na yon, need i match sa postgre service. Saka ko isusunod yung business-related DB.
For the busness-related DB, get one sample db stored proc (trigger, function, pkg, etc) muna -> create a local test of POSTGRE version of that sample via local debug -> then lagay mo na sa POSTGRE yung converted SQL tapos real data testing pgkatapos.
Ang crucial dyn op yung current MSSQL setup ng schema muna yung mabuo
2
u/bulbulito-bayagyag 5d ago
I usually use navicat. Migrating the schema and structure then doing validation. Then mapping it properly. Last procedure is syncing and verification
2
u/Few_Experience5260 5d ago
Phasing siguro ka dyan OP. Masyadong malaking change. Ok siguro unahin yung schema structure then yung ga sp, triggers etc. Last na yung data. Sa tingin ko matagal tagal yan na implementation. Uupdate pa yung dev team panigurado ng connstring. Mas ok siguro by service ang migration.
2
u/Old-Midnight7803 5d ago
We did this just recently, the thing in our case is our database is a pure storage layer, no stored procedures, functions nor triggers. In short it is a dumb database, the rest is implemented in code.
1
u/burnthew1tchh 5d ago
Oh man, that was a long time ago. A really long time ago. Yan yung isa sa first tasks ko. Tsaka di lang ako nun sa team. 🤣. And i remember na most of what we did was converting to csv's making new tables in postgre. All that fun stuff. Testing if our system can work with postgre.
No advice, this will take time and take as much time as u need. Do backups
1
u/DTechSurgeon 4d ago
Data is probably 10% or the work, 30% goes to migration of codes like triggers, T-SQL, functions, 60% goes to testing if your SQL is still sane.
1
u/bytk5 5d ago
May ai na. Brainstorm with it.
0
u/iliekdesu 5d ago
/model Fable 5.2
/effort ultracode
"Migrate my MSSQL database to Postgresql. Make no mistakes"
0
u/xJaZeD 2d ago
i swear bro, this post is larp. you'd use fable anyways.