15 Dec 2014

PostgreSQL Certification

Recently, someone asked for some inputs before going ahead with a PostgreSQL Certification.

From what I know, outside Japan, EnterpriseDB is the only Consultancy that offers Certification services consistently over the past few years. Having recently cleared one, I had a few things to say therein, and thought I'd rather enlist them as a full-blown post here.

Disclaimer: I reside in India, and thus the feedback below is quite possibly local to my own experience, and / or, to this geographical location. Also, probably things have changed since I gave this Certification a few months back, and so as mentioned below, YMMV:

  • Concept
    • The PostgreSQL Certification (from EnterpriseDB) is split into two parts
      • Associate Certification
        • Cost: $200
        • Scope: Basic PostgreSQL training
        • Online Training
          • Cost: $1200
          • Online Training recommended but not mandatory, to clear the Certification
      • Professional Certification
        • Cost: $200
        • Scope: Advanced PostgreSQL training
        • Online Training
          • Cost: $1200
          • Online Training mandatory, before clearing the Certification
      • Online Trainings
        • Essentially On-Demand Training sessions
          • Live-Recordings of training sessions
        • Allow at the maximum 42 days to learn at your own pace
      • Tests
        • Allow at the maximum 42 days to attempt at your own pace
  • Value
    • EnterpriseDB is (now) a well known name in the PostgreSQL consulting world. Amongst most others that I know, I've seen only this Consultancy offer Certification services consistently over the past few years.
    • Although PostgreSQL is decades old, (for a large part of the closed-source world) it is relatively nascent in the Enterprise arena, and thus there is an utter shortage of good DBAs. As is with most other job hunts, the cream (of PostgreSQL DBAs) get hand-picked from the mailing lists, some get picked-up by references, but most others have to prove their mettle in technical rounds. In such situations, when a candidate is to compete with a big-pile of resumes, a 'Certification' certainly puts you on top of the stack.
  • Cost
    • I come from India and am quite amazed at the scope of bargaining possible when trying to go for one of the available online Training courses (mind you, only trainings, the certification costs were non-negotiable). YMMV in other countries, but I certainly would recommend at least attempting negotiations, (especially, if you're paying for the certification yourself).
  • Quality
    • The Online variant of the training, are recorded sessions and having read about a few of the trainers, I think I have a fair idea why there is heavy MTI (mother tongue influence) on the Pronunciations, despite the trainer desperately trying to anglicize it. Similarly one could see repetitive grammatical / MTI related issues which come in form when the speaker isn't primarily English spoken. This isn't a show-stopper, but is something that could help in building expectations before enrolling.
      • For e.g. Instead of going to its 'backside', I'd rather go 'behind' it.
    • Another aspect of Quality is that the sessions are aimed at Novices. Its quite understandable that the trainer takes things pretty slow at places, but if you're already a DBA and aiming to just 'brush through' this training to give the certification, then one must mention that you're going to torture yourself to frustration :). Putting it more accurately, you're probably going to really miss that the video doesn't allow you to play the video at 2x or 4x speed.... (a-la Coursera).
      • Another aspect of this, is that it seems the training materials are conducted with trivial amount of editing. That sounds economical, but at times you feel that the editing team, could have clipped the odd 'aaaa... I think the internet is down, or probably some maintenance is going on at AWS...' during a hands-on class-session. These are rare, but some avoidable clips could be edited out, since it just adds to the wait time.
    • The instructors probably did a good job explaining the system. I wasn't expecting Tom :) or any of Greg or Josh to start with, but they get the job done, and that's what counts.
    • The slides on the other hand, seem to be prepared by someone knowledgeable and with some decent experience handling PostgreSQL.
    • To be fair, the training tries to cover a large ground, and in a short training like this, one obviously can't cover all aspects of such a huge topic. All-in-All, it does give essentials to get a Novice DBA going.
  • Test
    • The Test (attempt) is "not" conducted in a Pearson Vue / Prometric center as you'd probably expect. You can give the Test from anywhere. I am sure, I was quite impressed knowing about this convenience. I chose to give my attempt from my office at a lean hour, and it was done in a jiffy.
    • For those, who aren't very confident about giving the Test and instead prefer to 'Google' answers (now that they know its in a convenient location), be fair-warned, as in most cases, the people who set the tests (I know at least one!) are some pretty smart people and probably they've already accounted for this, by giving you 'just about enough time' that you'd effectively be penalizing yourself by expecting yourself to 'search' your way through for all answers.
    • On the flip-side, to recommend, I must say that Googling a trivial doubt or two, is probably okay, as long as you are 'very' sure about the timer that ticks along pretty fast. The key to this recommendation, is, as always, be very thorough about the PostgreSQL online Documentation. If you know where to find things, you're probably going to do just fine.
  • Test Interface:
    • The interface is a no-brainer, and gets out of the way to enable you to focus on the Test. To note, just like any other test-interface, I quite liked the practise-test to get you to feel the Test, before the actual attempt.
    • Ensure you try the 'check later' option to see how it works, at least I found it helpful / useful to review things at the end.
    • For those with a bad-connectivity, it does allow you to 'continue' a test, if you get disconnected. Although I didn't get to try it myself, a close friend did find himself unable to proceed in the test and promptly emailed the Training Coordinators about it. They (took a while to get back but) recommended a better internet-connection and gave him a re-attempt.
      • The only caveat with 'continuing' is that the questions already attempted remain 'fixed' and unmodifiable. The candidate was only allowed to attempt the 'remaining' questions with the 'remaining' time on the test-timer.
  • Test Results
    • The results are generally out within a few business-hours. Mine came within 2 (US) business-hours. Another friend, who similarly did attempt the test, didn't hear a word for a little more than a weekend. Her fears were proven correct when she realised that she didn't clear. Re-attempts are possible, but they'd not just cost extra (just the certification cost) but currently only three attempts are possible for 'any' Test.
  • Recommendation
    • Associate
      • Officially, a Training isn't required to give this Test.
        • However, if you take a Training session, an Test-attempt is free.
      • My recommendation? If you're a seasoned PostgreSQL DBA, you'd probably do okay without it... if you are able to brush through the training, it certainly wouldn't hurt.
    • Professional
      • Officially, a Training is mandatory.
        • As with Associate, you get an Test-attempt for free.
      • I'd say that the Training is just an extension of what the Associate Training was. 60% of the modules are similar, with extra Labs + additional modules depending on the topic.

All the best :) !

21 Nov 2014

Sqsh / FreeTDS with SQL2012 using the instance argument


This is a corner-case advise to anyone looking for a solution as to why sqsh / tsql / freetds combination is working perfectly in one SQL2012 instance but unable to login to a newly configured SQL2012 instance, the details for which just came in.

For starters, Sqsh / FreeTDS is pretty helpful if you're dumping-off of MSSQL Server to any CSV format (to, lets say dump to a PostgreSQL instance).


Symptoms
  • Sqsh / Tsql / FreeTDS is perfectly configured
    • The setup logs in to another SQLServer perfectly well
  • All this when you are able to login to both servers via SSMS from the same box
    • So it isn't a password issue
    • Neither is it a firewall issue
  • The new SQL Server keeps giving following error messages: 


 Error Messages
"Open Client Message"Layer 0, Origin 0, Severity 78, Number 41
Unable to connect: Adaptive Server is unavailable or does not exist

OR

#  /opt/freetds/bin/tsql -S "Q4DEV2" -U 'PostgreSQL' -P 'Password@123'
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20009 (severity 9):
        Unable to connect: Adaptive Server is unavailable or does not exist
        OS error 111, "Connection refused"
There was a problem connecting to the server


Login Details (as given by MSSQL Server Admin)
OLD_SERVER: q3.thatguyfromdelhi.com (this works via sqsh / tsql)
Login:PostgreSQL
Port: 1433 (default)
Password:Password@123

NEW_SERVER: q4.thatguyfromdelhi.com\SQL2012 (this isn't working via sqsh / tsql)
Login:PostgreSQL
Port:9999 (non-default)
Password:Password@123


Solution
# cat /opt/freetds/etc/freetds.conf
[Q3DEV1]
        host = q3.thatguyfromdelhi.com
        port = 1433
        tds version = 7.0
        charset = UTF-8

[Q4DEV2]
        host = q4.thatguyfromdelhi.com
        instance = SQL2012
        tds version = 7.0
        tcp = 9999 # required only if port is not default (i.e. port other than 1433)
        charset = UTF-8

The *MAGIC* to the mix is the 'instance' keyword in the configuration. As things turn out, MSSQL Server does not just identify an Instance via a Port number (like how all sane Server configurations do) they also allow a named instance, which does not work with just a PORT number.

In fact, you may have noticed that the the Port Number is missing in the Q4DEV2 configuration, and that is because PORT is mutually exclusive with INSTANCE keyword. And yes, to confirm the obvious, value of the Instance parameter should be the text after the backslash (\) in the SQLServerName field, that the MSSQL Server Admin provided earlier.

Alternatively, if you do have the new MSSQL Server running on a non-default port (lets assume port 9999) then use the 'tcp' argument as shown above.

A few useful hours wasted at my end... hope this saves someone else's !!

5 Nov 2014

PostgreSQL Explain Plan-Nodes Grid


While reading up about the various PostgreSQL EXPLAIN Plan nodes from multiple sources, I realized a clear lack of a consolidated grid / cheat-sheet, from which I could (in-one-view) cross-check how the plan nodes perform on a comparative basis. While at it, I also tried to attribute what their (good / bad) characteristics are.

Hope this (Work-In-Progress) lookup table helps others on the same path. Any additions / updates are more than welcome.

Update:
The image below is an old-snapshot of the document. The updated web document is available here.



23 Aug 2014

Magnifier in Chromebook

Off lately I've started to use the C720 (one of Acer's first Chromebooks) and am I in love with it or what.

While using the Chromebook to view an online Training that was created in a Flash environment (probably using Adobe Connect) I couldn't help myself get frustrated with the fact that I can't do simple things such as Zoom-in on the training video (which happened to be a Shell session) and had to really concentrate on a small part of the screen to read what the font wanted to say.

Most browsers that aren't from the prehistoric era, support zoom-in / zoom-out but the presentation application is 'smart' enough to ensure that that doesn't work as expected. So although the HTML does get enlarged but the presentation stays the same size (just re-centered) to the new zoomed in/out screen space.

A second option (I knew existed in Ubuntu since its early days) had a nifty feature for these situations, where you could just zoom-in and enlarge the screen (unbeknownst to the browser and all applications below the UI layer) this meant that anything rendered on the screen was magnified.

Now Chromebook was smart, pretty compact and an awesome little product, but I really thought expecting graphics related jugglery would be an overkill... and I couldn't find such a feature either.

... Until I read the 'Accessibility' feature in Chromebook (which is a simple little checkbox) and Voila!

Steps:

  1. Click User-Image (bottom right)
  2. Click Settings
  3. Click 'Advanced Settings'
  4. Under 'Accessibility' options -> Enable Screen Modifier
  5. Zoom-in using .... Ctrl-Alt- button
  6. Zoom-out using .... Ctrl-Alt- button
Am still to find things that I can't do on this ultra-cheap / ultra-light / long-lasting-battery / neat laptop!

12 Jul 2014

A Life ... that just happens to have a name.


Zohra Sehgal (1912-2014)

Ae kafir, qadr ki qadr na ho to na sahi,
Ae nadaan, Umr ki fikr na ho to na sahi,
Gar ranjish ho is sadi se ya pichla koi,
To rukh kar, Ja ban ke dikha Zohra koi.

25 Apr 2013

PgAdmin doesn't (directly) support restoring from PLAIN format backups (yet)


Was working with my team at office recently and had a request from the team to learn how to 'Restore' an existing backup using PgAdmin. I thought this was easy, since PgAdmin allows for both 'Backup' and 'Restore' but investigating further, I found that PgAdmin only allows for Restore in formats other than 'PLAIN'.

Cross-checked this on the internet and found references here, here and here saying the same thing. 

PgAdmin doesn't (directly) support Restore'ing from plain text backups. To restore a backup, backed up using PLAIN format, you'd need to pass it through a command line utility called 'psql' that is available in both PgAdmin as well as postgres server installations.

I think it's high time that PgAdmin support this feature, especially when all the building blocks are already in-place. Lets see if I could take out time and submit a patch for this.

... till later.

27 Mar 2013

Christian Marriage Registration (Delhi, India)

(Please scroll down to the read the entire-post to know more)



Documents required for Christian Marriage Registration
(a.k.a. Documents required for Solemnization and Registration of Marriages under Indian Christian Marriage Act, 1872)

Workflow to get your Christian Marriage
Registered with the Delhi Government

What's in an empty table?

How much storage does an empty table in Postgres take? This is a post about Postgres tables that store ... well basically ...  Nothing . The...