“And once again, I will be… In a march to the sea.”
Happy May Day to one and to all!
This week’s expedition continued with our first mate Slonik through the relational database version of the Galápagos Islands. Last week, we rendezvous with our old friend the “ SQL Authority “ who gave us a mere beginner’s guide to the world of PostgreSQL.
Now, no longer a neophyte to Postgres, we needed to kick it up a notch and touch on some more advanced topics like Database architecture, Logical Replication, Streaming Replication, Monitoring of Replication scenarios, and a migration path from other relational databases i.e. MS SQL Server to Postgres.
So once again, we turned back to Pluralsight for some insights on these sophisticated areas. Only to find zilch in this realm!
So now where to turn? Who can help us navigate these uncharted territories?
Well… Google of course.. Or I should say the Google’s Streaming video Service A.K.A Youtube.com. There much to our delight we found a treasure trove of riches of the high-level topics related to Postgres.
However, we didn’t have our true Eureka moment until we encountered Creston not be confused with The Amazing Kreskin. Although great possibly equally as amazing or possible better? This RDBS enthusiast put together a bunch of spectacular videos which included for a wide variety of nuggets on Postgres. So now armed with these knowledge bombs it was full speed ahead!
On Saturday, we kicked it off with quick jolt of architectural review and then we dove right into an overview on High Availability. To finish it off we successfully implemented logical replication and we were feeling pretty good about our first day in deep blue sea…
“Like a red morn that ever yet betokened, Wreck to the seaman, tempest to the field, Sorrow to the shepherds, woe unto the birds, Gusts and foul flaws to herdmen and to herds.” — William Shakespeare
..Or more eloquently phrased “Red sky at night, sailor’s delight. Red sky in morning, sailor’s warning” — Some unknown Sailer Dude
Well, that warning came quite quickly on Sunday when we took the plunge and went right to Streaming Replication. We immediately queued up the Creston video on the subject and began to follow along. …And follow along… and follow along …and follow along …and so on… After bleary 15 hours of trying we were left with what turned out to a red herring of erroneous error messages in the Postgres DB log but more importantly no replication in place…
We finished Sunday and continued into wee hours on Monday with no progress, a disappointing end to 80 consecutive day step goal, and no exercise done.. In fact, I did less 500 steps all day as I had my gluteus maximus glued to the chair and Mac Book. Feeling dejected, I decided to call it quits and start over after some shut eye. On Monday, I spent the entire day trying to troubleshoot the issue with more re-watching of the videos and countless google searches but to no avail.
The sun’ll come out Tomorrow…Bet your bottom dollar.. That tomorrow There’ll be sun — Orphan Annie
…And then Tuesday had arrived, but the Sun actually didn’t show up until later in the day (~3:30 PM). My plan was to tear it all down and start from scratch and rebuilt a shiny new pristine environment and follow the video methodically step by step. When I finished I was back where I was before no replication but this time I had written off the meaningless error messages in the log and just focused on why replication was not working.. Back to google and even a cry
out for help to the community on DB Stack exchange
And then finally it hit me “like the time I was standing on the edge of my toilet hanging a clock, the porcelain was wet, I slipped, hit my head on the sink, and when I came too.. I came up with the flux capacitor”. Actually, that’s a different story but I realized that I had inadvertently put the recovery.conf file in the wrong directory. Doh! Once I placed the file in its proper place and restarted my Postgres servers and the magic began.
Overwhelmed, with utter jubilation I decided it was time to celebrate with a
victory lap or 15. To my amazement I actually set my best PR for a mile run and solid 7:02 per mile for 3.6 miles ran… But that’s something I can write about somewhere else…
Not here not now..
After Tuesday’s afternoon catharsis, it was time further the mission on Wednesday with more on Replication Slots and Replication monitoring.. Then on to migrating SQL Server sample database AdventureworksDW2012 to Postgres. First step was generate the full table schema from MS SQL Server and then modify the script to translate so Postgres can interrupt it. See the below log for more details.
On Thursday, before we could pick up before we left off we needed to recover our Ubuntu Server which hosted our Primary and local Replica from a dreaded disk crash.
In effort to simulate latency during our replication monitoring test we wrote a an INSERT statement using generate_series which worked great as the Replica's started to fall behind as we continuously pump in data that until we ran out of space and our Ubuntu server sh*t the bed. Now, we had to flex our Virtual Box and Linux skills to get our system back online
First, we had to increase the size of the disk in our VDI which of course is unsupported in the UI and needs to be done at the command line. Now with an increased Disk we needed to boot up VB straight to the Ubuntu Installer ISO and run our trusted gpart command to extend our volume so Ubuntu could see our newly added free space.
After a quick reboot our system was back online. We re-enable our streaming replication and now we ready we picked were we left off this time migrating the data from MS SQL Server to Postgres. Of course, just like Data types conversion it’s not so intuitive. Our conclusion is that if you going to migrate from MS SQL Server to Postgres its best to invest in a third party tool.
However, as a POC we were able to migrate a small table. We were able to accomplish this by using BCP to export all the table data out to individual text files and then import it into Postgres.
On Friday, we got ambushed with 8 th grade Algebra and Science homework but we managed to find some time test to drive one of the third party tools used for MS SQL Server to Postgres migrations with some success. We used Ispirer Migration and Modernization Toolkit to migrates the all tables from AdventureworksDW2012 and transfer all data from Microsoft SQL Server to PostgreSQL. Unfortunately, we weren’t so successful with the Views and Functions as it requires further code re-writes but that was to be expected. Here is the detailed log of my week’s journey
Below are some topics I am considering for my exploration next week:
- Partitioning
Stay safe and Be well
-MCS
Originally published at http://sqlsquirrels.com on May 2, 2020.