Thread: What makes a Postgres DBA?
Hullo List, Following up on a recent thread (http://archives.postgresql.org/pgsql-general/2007-11/msg00064.php) ... Next question and one that I'm not sure how to phrase: how does one become a Postgres-savvy* DBA? Just by working with it as a developer and then moving "up the ranks"? (i.e working with larger, more active setups/datasets?) There's the training options listed on http://www.postgresql.org/about/eventarchive, but I'm personally skeptical at just how much one can retain from a 1-to-5 day course. My question is not from a certification standpoint; rather it stems from an actual-understanding standpoint and "what do I need to able to do to be able to honestly say that 'I am a Postgres DBA' on my resume" standpoint. (I'm not actually planning to become a DBA; I'm just curious.) Thanks, Kevin * Am I assuming too much already by not defining what a DBA is in general?
On Sun, 4 Nov 2007, Kevin Hunter wrote: > Am I assuming too much already by not defining what a DBA is in general? Probably. I'd startby looking at the list of DBA duties at http://en.wikipedia.org/wiki/Database_administrator and considering which of those are database-specific for a second. Two examples of the extremes here. "Installation" is a very product based thing. I assure that even if you can know everything in the world about how to install every other type of database system, you might still fail miserably to bring a new Oracle system up. On the opposite side, most of the work for "data modeling" is very similiar for any SQL-based database. I like to think of this as a grid. Across the top I put the various tasks DBAs work on. Vertically I go from generic to specific knowledge at various levels. Consider the task of deleting data from a table. I'd classify understanding of that subject like this: -generic new DBA: can use DELETE properly -experienced DBA: understands how dead rows get left behind by deletes -generic expert DBA: can comment on whether the expected balance of insert vs. delete operations will impact the optimal B-tree fill factor -new PostgreSQL DBA: knows to run VACUUM to clean up dead rows -experienced PG DBA: tunes autovacuum and monitors/adjusts the FSM parameters to keep dead rows under control -expert PG DBA: runs reports against pg_stattuple to instrument vacuum > "what do I need to able to do to be able to honestly say that 'I am a > Postgres DBA' on my resume" Organizing things as above, this turns into a somewhat fuzzy question about how much of the grid one has to cover before achieving that goal. Consider this; who will be more effective as a PostgreSQL DBA: -A person with many years of large-scale DBA experience with another database, but who just starting using PostgreSQL a few months ago -Someone who has been using PostgreSQL for a few years but only on small projects There's understanding the breadth of this field, and there's knowing some depth about each of the topic, and the exact mix of the two varies from person to person. There's so many aspects to this type of work that drawing a line and saying "if you know X, Y, and Z you can consider yourself a Postgres DBA" doesn't make a lot of sense. You mentioned training and certification. Part of the value of going through either of those is that you end up with some baseline idea of what someone who has gone through the class/test has been exposed to. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
The bigger danger is hiring an *Oracle* Financials or *Oracle* Manufacturing person to become a DBA because they have 'Oracle' on their resume This is the most comprehensive analysis of DBA requirements I have seen thus far For myself I'm not tied to any specific Database having worked in mySQL and Postgres this year but I do lean towards Oracle as I know it has the raw horsepower to accomplish distributed transactions in their entire suite of DB Product offerings (also I was an Oracle DBA in years past) The questions on a DBA should add 1)what is a cluster 2)what is a borken chain? 3)when are Btree indexes used 4)Tell me how to performance optimise a multiple condition predicate using the principles of boolean logic Thanks Greg Martin-- ----- Original Message ----- Wrom: DDJBLVLMHAALPTCXLYRWTQTIPWIGYOKSTTZ To: "Kevin Hunter" <hunteke@earlham.edu> Cc: "Postgres General List" <pgsql-general@postgresql.org> Sent: Sunday, November 04, 2007 4:18 AM Subject: Re: [GENERAL] What makes a Postgres DBA? > On Sun, 4 Nov 2007, Kevin Hunter wrote: > > > Am I assuming too much already by not defining what a DBA is in general? > > Probably. I'd startby looking at the list of DBA duties at > http://en.wikipedia.org/wiki/Database_administrator and considering which > of those are database-specific for a second. > > Two examples of the extremes here. "Installation" is a very product based > thing. I assure that even if you can know everything in the world about > how to install every other type of database system, you might still fail > miserably to bring a new Oracle system up. On the opposite side, most of > the work for "data modeling" is very similiar for any SQL-based database. > > I like to think of this as a grid. Across the top I put the various tasks > DBAs work on. Vertically I go from generic to specific knowledge at > various levels. Consider the task of deleting data from a table. I'd > classify understanding of that subject like this: > > -generic new DBA: can use DELETE properly > -experienced DBA: understands how dead rows get left behind by deletes > -generic expert DBA: can comment on whether the expected balance of > insert vs. delete operations will impact the optimal B-tree fill factor > > -new PostgreSQL DBA: knows to run VACUUM to clean up dead rows > -experienced PG DBA: tunes autovacuum and monitors/adjusts the FSM > parameters to keep dead rows under control > -expert PG DBA: runs reports against pg_stattuple to instrument vacuum > > > "what do I need to able to do to be able to honestly say that 'I am a > > Postgres DBA' on my resume" > > Organizing things as above, this turns into a somewhat fuzzy question > about how much of the grid one has to cover before achieving that goal. > Consider this; who will be more effective as a PostgreSQL DBA: > > -A person with many years of large-scale DBA experience with another > database, but who just starting using PostgreSQL a few months ago > > -Someone who has been using PostgreSQL for a few years but only on small > projects > > There's understanding the breadth of this field, and there's knowing some > depth about each of the topic, and the exact mix of the two varies from > person to person. There's so many aspects to this type of work that > drawing a line and saying "if you know X, Y, and Z you can consider > yourself a Postgres DBA" doesn't make a lot of sense. You mentioned > training and certification. Part of the value of going through either of > those is that you end up with some baseline idea of what someone who has > gone through the class/test has been exposed to. > > -- > * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
On 11/4/07, Kevin Hunter <hunteke@earlham.edu> wrote: > Following up on a recent thread > (http://archives.postgresql.org/pgsql-general/2007-11/msg00064.php) ... > > Next question and one that I'm not sure how to phrase: how does one > become a Postgres-savvy* DBA? Just by working with it as a developer > and then moving "up the ranks"? (i.e working with larger, more active > setups/datasets?) There's the training options listed on > http://www.postgresql.org/about/eventarchive, but I'm personally > skeptical at just how much one can retain from a 1-to-5 day course. I would suggest that in order for someone to get that most coveted of titles 'PostgreSQL DBA' on should: Administration: * Understand tradeoffs of autovacuum and scheduled vacuum (increasingly, this question will morph into autovacuum configuration and monitoring) * Understand backups: when to use pitr, pg_dump * Basic configuration: shared_buffers, etc etc * Be able to do task specific tuning: understand difference between oltp and olap environments * Be versatile with psql shell (most of the best DBAs use psql almost exclusively) * No how to troubleshoot and deal with locking problems and runaway queries * Should be able to setup postgresql manually without using packages * Be able to implement a replication technology (Slony) * Understand some of the specific challenges involved in dealing with large databases, which I define as over 10 times the memory in the server. * Understand the challenges involved with 24/7 environments * Understand the differences in low and high security environments and how to set up for both Development * Should be very comfortable with pl/sql, pl/plpgsql (pl/perl or alternative is bonus) * Understand triggers, views, functions, rules, constraints, domains and type and be able to give examples of when they should be used (or not) * Understand good normalization strategies * Be able to discuss pros and cons of surrogate keys * Understand MVCC * C language experience * Strong familiarity with bash or perl (preferably both) Personal * A DBA often anchors a development team. 'Works well with others' is a cliché, but still applies. * At least superficially familiar with the technologies that interface with the database * Be cool and calm under pressure * Be established on the appropriate mailing lists and use good etiquette * Understand that developers are out to get your database merlin
Merlin Moncure wrote: > On 11/4/07, Kevin Hunter <hunteke@earlham.edu> wrote: > >> Following up on a recent thread >> (http://archives.postgresql.org/pgsql-general/2007-11/msg00064.php) ... >> >> Next question and one that I'm not sure how to phrase: how does one >> become a Postgres-savvy* DBA? Just by working with it as a developer >> and then moving "up the ranks"? (i.e working with larger, more active >> setups/datasets?) There's the training options listed on >> http://www.postgresql.org/about/eventarchive, but I'm personally >> skeptical at just how much one can retain from a 1-to-5 day course. >> > > I would suggest that in order for someone to get that most coveted of > titles 'PostgreSQL DBA' on should: > > Administration: > * Understand tradeoffs of autovacuum and scheduled vacuum > (increasingly, this question will morph into autovacuum configuration > and monitoring) > * Understand backups: when to use pitr, pg_dump > * Basic configuration: shared_buffers, etc etc > * Be able to do task specific tuning: understand difference between > oltp and olap environments > * Be versatile with psql shell (most of the best DBAs use psql almost > exclusively) > * No how to troubleshoot and deal with locking problems and runaway queries > * Should be able to setup postgresql manually without using packages > * Be able to implement a replication technology (Slony) > * Understand some of the specific challenges involved in dealing with > large databases, which I define as over 10 times the memory in the > server. > * Understand the challenges involved with 24/7 environments > * Understand the differences in low and high security environments and > how to set up for both > > Development > * Should be very comfortable with pl/sql, pl/plpgsql (pl/perl or > alternative is bonus) > * Understand triggers, views, functions, rules, constraints, domains > and type and be able to give examples of when they should be used (or > not) > * Understand good normalization strategies > * Be able to discuss pros and cons of surrogate keys > * Understand MVCC > * C language experience > * Strong familiarity with bash or perl (preferably both) > > Personal > * A DBA often anchors a development team. 'Works well with others' is > a cliché, but still applies. > * At least superficially familiar with the technologies that interface > with the database > * Be cool and calm under pressure > * Be established on the appropriate mailing lists and use good etiquette > * Understand that developers are out to get your database > > merlin > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > An excellent summary ! I would add that the core of being a DBA does have some platform independent aspects. I took some introductory Informix classes years ago on data normalization and the like, which combined with some long-ago computer science classes (data structures, mostly) at UCB to help broaden my understanding. And a lot of OJT and other classes since then, of course. Some of the issues that are general (but which require understanding of each platform's unique aspects). All of these tend to be more meaningful the more you understand about the domain as a whole. Merlin noted them but they are pillars on which a lot of useful databases can be built. * data normalization -- what data goes where * indexing -- what it does, what it helps, what it doesn't help * referential integrity and why it matters (and when it doesn't) * authentication and access issues * locking and related issues * backups and safety nets I particularly like your last points, Merlin, on developers. I've been on both sides of the fence and databases are a specialized animal that usually needs specialized care. Being able to work well with designers and developers is crucial in delivering a useful end result. Greg Williamson Senior DBA GlobeXplorer LLC, a DigitalGlobe company Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.)
Kevin Hunter wrote: >> "what do I need to able to do to be able to honestly say that 'I am a >> Postgres DBA' on my resume" Greg Smith wrote: > Organizing things as above, this turns into a somewhat fuzzy question > about how much of the grid one has to cover before achieving that goal. > Consider this; who will be more effective as a PostgreSQL DBA: ... > There's understanding the breadth of this field, and there's knowing > some depth about each of the topic, and the exact mix of the two varies > from person to person. There's so many aspects to this type of work > that drawing a line and saying "if you know X, Y, and Z you can consider > yourself a Postgres DBA" doesn't make a lot of sense. You mentioned > training and certification. Part of the value of going through either > of those is that you end up with some baseline idea of what someone who > has gone through the class/test has been exposed to. Rephrase the claim. Since "being a Postgres DBA" is not a binary state, you should honestly say where you fall in the spectrum. Instead of "I'm a PG DBA", one could say, "I have some experience as a PG DBA and I am comfortable with the role." Variations might include, "I have a lot of DBA experience, but somewhat more limited PG-specific knowledge", or, "I've been honing my PG DBA skills, and I have a solid but basic competence", and the like. Chances are you will be able to make such judgments with much more confidence than claiming to be or not to be a DBA. -- Lew