Gradually migrate from SQL Server to PostgreSQL -


company has numerous applications running on sql server. database bit of mess.

the goal gradually move sql server postgresql (another sql server instance not option)

an ideal scenario if new applications connect postgresql, create new table structure, still able use/interact data legacy sql server (an app connecting 2 database servers not option).

foreign data wrappers not seem option, since technology immature , in case of postgresql, foreign tables read-only.

another wild idea connect sql server instance postgresql, new applications connect sql server, use postgresql's foreign database. foreign database ( guess ) have access host's database objects. , @ 1 point developers switch new apps sql server postgresql.

and of course there possibility try , sync data.

which best option?

everything suggest recipe pain , failed migrations. people rant , rave how awful, slow , unreliable postgresql if try use approach. it'd great political move wanted keep sql server, not way migrate postgresql.

there's read/write foreign data wrapper coming newer pg versions, it'll support other postgresql servers. supporting ms sql lot harder due need translate sqlstates , error messages, search conditions, , more, wrapper no doubt quite limited , have less great performance. say, fdw support immature @ point anyway.

there many things lose trying hybrid this:

  • no foreign key integrity enforcement

  • data types on each side might not behave 100% same data ok on 1 side , not on other. think timestamps/dates.

  • efficient joins require extremely sophisticated foreign data wrapper - what'll happen whole table fetched joined against locally. performance terrible.

  • writing queries becomes nightmare when you're doing trivial task. function names differ, etc.

  • you lose or weaken many acid properties and/or must use 2 phase commit, sucks performance.

seriously, don't this.

syncing dbs worse - unless it's 1 way, it's going recipe lost updates, deleted rows reappearing, , worse. two-way sync extremely hard.

start preparing apps move making them able run on both servers, 1 @ time. once you've got app ready run on pg, start doing load testing , reliability testing migrated copy of live data. then think migrating, have plans how reverse move if find last minute problems force delay.

if you're adding entirely new parts app might reasonable have them in pg if don't interact other data in db @ all. that's pretty unlikely, though, , sysadmins still hate when tell them need atomic snapshot across 2 separate databases...


Comments

Post a Comment

Popular posts from this blog

image - ClassNotFoundException when add a prebuilt apk into system.img in android -

i'm in trouble add prebuilt apk system.img based taiwan mtk android phone platform, when push apk onto device,after reboot phone,the apk can installed succecessfully,but if add apk alps/out/target/product/$prj/system/app or vendor/mediatek/$prj/artifacts/out/target/product/$prj/system/app ,and produce system.img,the apk can't installed normally,the logcat say: a.lang.runtimeexception: unable instantiate activity componentinfo{com.sec.android.widgetapp.ap.hero.accuweather.widget/com.sec.android.widgetapp.ap.hero.accuweather.widget.weatherclock}: java.lang.classnotfoundexception: com.sec.android.widgetapp.ap.hero.accuweather.widget.weatherclock. can tell me what's problem ? when package manager installs apps part of system partition different installing apps on data partition. looks app using libraries somewhere else. when install on data lib directory created in apps data directory , symlinked /data/app-lib/. when apps installed on system it's expecte...
Read more

I need to import mysql 5.1 to 5.5? -

i have mysql 5.1 database , need import localhost running 5.5. how import 5.5 or downgrade mysql? install phpmyadmin 5.1 os: http://www.wikihow.com/install-phpmyadmin-on-your-windows-pc or https://www.digitalocean.com/community/articles/how-to-install-and-secure-phpmyadmin-on-ubuntu-12-04 then login, click on database, hit export, , export in sql format (quick fine). import .sql file 5.5 server (after installing phpmyadmin) logging in clicking database , hit import. hope helps!
Read more

Java, Hibernate, MySQL - store UTC date-time -

i saw here on few related questions (like this one , of course, this one )... essentially, want store date-time utc, , let application user choose time zone wants display date-time in. since seems date-time fields affected underlying jdbc driver, wonder if acceptable way go storing utc date-time: set both mysql , application server machine utc time zone (no need separate) both mysql , jvm should pick underlying system time settings (if not instructed otherwise) use datetime table columns on mysql side use java.util.date corresponding mapping on hibernate side (i guess java.sql.timestamp used too) let application worry interpreting date-time fields - i.e. let user choose preferred time zone is ok? edit clarify - here meant refer timestamps created strictly on server (e.g.date-time of record creation). application server instantiates date objects (new date() equals current date-time on server, , time zone agnostic). if client user wants supply date searching/filte...
Read more