Well I'm back in the office this morning, which I actually don't mind, it gives me the human conversation that has been missing over the last couple of days (other than that endured in the gym, which usually consists of out of breath ramblings and red faced sweaty "Hello"'s).
Coming back into the office after a couple of days on the night shift reminds me of two very serious points:
1. I'm not built for night shifts... They really take it out of me
2. When working from home, you tend to put in way more hours than necessary
Don't get me wrong I'm not complaining, but I do find it very strange that we feel it necessary to do that little bit extra when working at home, as if we're going to be overly scrutinised by both peers and management because we've not been in the office! I do actually get more done at home, possibly because of less interruptions, and more comfort! Not to mention the ability to actually get a break from work when needed (I'll halt that point in favour of explaining another day!).
It's a short day today anyway, so I'm not going to grumble much more... A technical post will follow shortly, as I feel that I'm posting more rambling than technical posts to this blog at the moment!
Friday, 22 July 2011
Wednesday, 20 July 2011
Another Graveyard Shift
Well, after limited success on my last graveyard shift, I've now been commissioned for another overnight shift...
So - what does a DBA do whilst babysitting a batch run? Simple... set up more reporting and statistics from other databases of course! As bad as that sounds, it's really not. It means a new database for one, and for another, it means I can keep my development skills sharp, in this case in Perl, Shell scripting and PG/PLSQL, as well as some statistical analysis skills I learnt a while back to do some pretty nifty 6 sigma reporting!
So, what why and how?
What:
Ok, so I intend to record all batch run stats for all the live/DR PostgreSQL databases I administer, as well as setting up nagios notifications.
Why:
At my company there has previously been no "serious" reporting, monitoring and notifications on database status and performance, this is something I intend to remedy
How:
Using a combination of Nagios, Bucardo's excellent Check_Postgres Perl script, shell scripting and PLPGSQL, I plan to use a series of functions on a database that will be called remotely before, during and after batch scripts to log run times, errors and faults. These snippets of data will also be pushed through Nagios with text messaging installed.
It sounds a bit naff to be fair, however; I'll post updates on where I'm at with it as and when I make progress!
So - what does a DBA do whilst babysitting a batch run? Simple... set up more reporting and statistics from other databases of course! As bad as that sounds, it's really not. It means a new database for one, and for another, it means I can keep my development skills sharp, in this case in Perl, Shell scripting and PG/PLSQL, as well as some statistical analysis skills I learnt a while back to do some pretty nifty 6 sigma reporting!
So, what why and how?
What:
Ok, so I intend to record all batch run stats for all the live/DR PostgreSQL databases I administer, as well as setting up nagios notifications.
Why:
At my company there has previously been no "serious" reporting, monitoring and notifications on database status and performance, this is something I intend to remedy
How:
Using a combination of Nagios, Bucardo's excellent Check_Postgres Perl script, shell scripting and PLPGSQL, I plan to use a series of functions on a database that will be called remotely before, during and after batch scripts to log run times, errors and faults. These snippets of data will also be pushed through Nagios with text messaging installed.
It sounds a bit naff to be fair, however; I'll post updates on where I'm at with it as and when I make progress!
pg_hba.conf
So, the first of my technical posts to serve as me "taking in" the information I'm putting down, and to help me understand by explaining it!
Before anyone comments that this is taken from the documentation, I have done this on purpose as a learning aid!
So the pg_hba.conf file (host based authentication) is located in the $PGDATA directory, and is used to allow and disallow access to the database by host. Now this is something I like about PostgreSQL.
Each entry comprises of the following format:
#TYPE DATABASE USER ADDRESS METHOD
Without going into too much detail, this breaks down as follows:
TYPE:
local (Unix domain sockets from the local machine)
host (TCP/IP connections from remote hosts)
hostssl (As above but only with SSL enabled)
hostnossl (Opposite of above)
DATABASE
Name of the database that the entry pertains to. Can be set to "all" for any, or "sameuser" to connect to a database with the same name as the user connecting. "samerole"|"samegroup" to connect to a database that has the same name as role|group allocated to the connecting user. can be a comma separated list, or a file with a list of databases located on the host by specified @<filename>
USER
"all" meaning all users, a "+<groupname>" to allow users assigned to a particular group, again comma serperated, or file with: "@<filename>"
ADDRESS
This can be a CIDR-Address.
(eg: 192.168.1.1/32,
192.168.0.0/16 or
0.0.0.0/0 etc.Although this can be set to any bit range 0-32, however, it's typically easier to set within 8 bit ranges in my opinion. Also, this will accept IPV6 addresses too... 0 is all hosts)
or an IP and Mask.
(e.g 192.168.1.1 255.255.255.255,
192.168.0.0 255.255.0.0 or
0.0.0.0 0.0.0.0)
These only apply to non Unix socket connections
METHOD
This is where things get a little more complex!
options are:
trust
A straight allow reject
A straight denial md5
Encrypted password crypt
Legacy Encrypted password method password
Clear text password - Errm... not advised!
krb5
Kerberos V5 authentication, libraries required ident
Check against pg_ident map - requires <mapname> after "ident" pam
Pluggable authentication Module - OS Verification, libraries required
So a typical set of entries being:
#TYPE DATABASE USER ADDRESS METHOD
local all all trust
# localhost connections are all trusted
host all dba 192.168.1.1/32 trust
#dba connection trusted
host all user1 194.216.0.0/16 md5
#passwords from 194.216* for user1
host postgres postgres 194.216.0.0/16 trust
#trust from 194.216* for postgres database connection by postgres
# all other users pam authenticate
The rows in the file are processed top to bottom, and when a rule is matched it is used. so in this example:
host all user2 0.0.0.0/0 reject
host all user2 194.218.65.9/32 trust
User2 will *always* be rejected access from every ip as the second rule will never be hit. In this instance, I always arrange the file in the following order:
and this seems to work for me!
In a nutshell, the permissions aren't actually that difficult to work out and can be quite secure... Well, only as secure as the server itself of course!
Before anyone comments that this is taken from the documentation, I have done this on purpose as a learning aid!
So the pg_hba.conf file (host based authentication) is located in the $PGDATA directory, and is used to allow and disallow access to the database by host. Now this is something I like about PostgreSQL.
Each entry comprises of the following format:
#TYPE DATABASE USER ADDRESS METHOD
Without going into too much detail, this breaks down as follows:
TYPE:
local (Unix domain sockets from the local machine)
host (TCP/IP connections from remote hosts)
hostssl (As above but only with SSL enabled)
hostnossl (Opposite of above)
DATABASE
Name of the database that the entry pertains to. Can be set to "all" for any, or "sameuser" to connect to a database with the same name as the user connecting. "samerole"|"samegroup" to connect to a database that has the same name as role|group allocated to the connecting user. can be a comma separated list, or a file with a list of databases located on the host by specified @<filename>
USER
"all" meaning all users, a "+<groupname>" to allow users assigned to a particular group, again comma serperated, or file with: "@<filename>"
ADDRESS
This can be a CIDR-Address.
(eg: 192.168.1.1/32,
192.168.0.0/16 or
0.0.0.0/0 etc.Although this can be set to any bit range 0-32, however, it's typically easier to set within 8 bit ranges in my opinion. Also, this will accept IPV6 addresses too... 0 is all hosts)
or an IP and Mask.
(e.g 192.168.1.1 255.255.255.255,
192.168.0.0 255.255.0.0 or
0.0.0.0 0.0.0.0)
These only apply to non Unix socket connections
METHOD
This is where things get a little more complex!
options are:
trust
A straight allow reject
A straight denial md5
Encrypted password crypt
Legacy Encrypted password method password
Clear text password - Errm... not advised!
krb5
Kerberos V5 authentication, libraries required ident
Check against pg_ident map - requires <mapname> after "ident" pam
Pluggable authentication Module - OS Verification, libraries required
So a typical set of entries being:
#TYPE DATABASE USER ADDRESS METHOD
local all all trust
# localhost connections are all trusted
host all dba 192.168.1.1/32 trust
#dba connection trusted
host all user1 194.216.0.0/16 md5
#passwords from 194.216* for user1
host postgres postgres 194.216.0.0/16 trust
#trust from 194.216* for postgres database connection by postgres
host all baduser 0.0.0.0/0 reject
# baduser is banned from all dbs
host all all 0.0.0.0/0 pam# all other users pam authenticate
The rows in the file are processed top to bottom, and when a rule is matched it is used. so in this example:
host all user2 0.0.0.0/0 reject
host all user2 194.218.65.9/32 trust
User2 will *always* be rejected access from every ip as the second rule will never be hit. In this instance, I always arrange the file in the following order:
- local entries Then
- 32 bit host entries, rejects first, then auths, then trusts
- 24 bit host entries, rejects first, then auths, then trusts
- 16 bit host entries, rejects first, then auths, then trusts
- 8 bit host entries, rejects first, then auths, then trusts
- 0 bit host entries, rejects first, then auths, then trusts
and this seems to work for me!
In a nutshell, the permissions aren't actually that difficult to work out and can be quite secure... Well, only as secure as the server itself of course!
Developing code against Production databases...
<rant>
You have to be kidding right? Wrong. It happens.
This is something that really perplexes me, I mean, come on, I'm from a development background and this is something I've never done, but, this actually happens. In big business too!
It's certainly not a good idea in my opinion, but what are the other options? More hardware? Greater resource requirements? all with a hefty cost attachment, so I understand why this happens. This is something I can never condone, and will never get used to.
What more can I say? other than: Do you not value your data? Then by all means, carry on!
</rant>
You have to be kidding right? Wrong. It happens.
This is something that really perplexes me, I mean, come on, I'm from a development background and this is something I've never done, but, this actually happens. In big business too!
It's certainly not a good idea in my opinion, but what are the other options? More hardware? Greater resource requirements? all with a hefty cost attachment, so I understand why this happens. This is something I can never condone, and will never get used to.
What more can I say? other than: Do you not value your data? Then by all means, carry on!
</rant>
AIX & PostgreSQL - Say wha?
So I am currently supporting and administering a real mixed bag of installations.
The current versions of PostgreSQL I'm supporting are from 7.4 up to 8.4 in production, with two 9.0 databases on their way through potential upgrade (and hopefully many more should I get my own way).
The operating systems I have to contend with include RHEL 4, 5 and 6, AIX 5.3 on PowerPC and even Centos, all of these in a mix of 32 & 64 bit installations. I'm pretty sure I've even seen a Debian installation lying around somewhere...
So why is AIX a problem? Well, it's not; technically speaking of course. The only real annoyance is the endianness. It's well documented in the PostgreSQL installation platform notes that AIX is, shall we say, Quirky? Basically, AIX makes PostgreSQL a real pain to compile and install. This wasn't something that I had to do, but I did inherit it, and as such, any thoughts of upgrading PostgreSQL on AIX seem to turn me to nightmares. I just don't fancy hours of battling with compiler errors. Not that I'm not used to compiler errors you understand; having been a C/C++ in a previous incarnation they make (some) sense to me. I did, however, leave that profession, and as such was hoping to not have to worry about endianness and it's resulting problems at any point in this role.
Alas, I fear I'm going to have to dig in and get on with it! The question is; how soon?
The current versions of PostgreSQL I'm supporting are from 7.4 up to 8.4 in production, with two 9.0 databases on their way through potential upgrade (and hopefully many more should I get my own way).
The operating systems I have to contend with include RHEL 4, 5 and 6, AIX 5.3 on PowerPC and even Centos, all of these in a mix of 32 & 64 bit installations. I'm pretty sure I've even seen a Debian installation lying around somewhere...
So why is AIX a problem? Well, it's not; technically speaking of course. The only real annoyance is the endianness. It's well documented in the PostgreSQL installation platform notes that AIX is, shall we say, Quirky? Basically, AIX makes PostgreSQL a real pain to compile and install. This wasn't something that I had to do, but I did inherit it, and as such, any thoughts of upgrading PostgreSQL on AIX seem to turn me to nightmares. I just don't fancy hours of battling with compiler errors. Not that I'm not used to compiler errors you understand; having been a C/C++ in a previous incarnation they make (some) sense to me. I did, however, leave that profession, and as such was hoping to not have to worry about endianness and it's resulting problems at any point in this role.
Alas, I fear I'm going to have to dig in and get on with it! The question is; how soon?
Tuesday, 19 July 2011
Welcome to my new blog...
Well I've been meaning to set this up for a while, but just haven't got round to it until now.
So, as I'm pulling a graveyard shift, I decided that there probably won't be a more appropriate time to do this.
I'm hoping to use this blog as a reference mainly for myself, to allow recollection of issues, problems and resolutions amongst many other little snippets that come to mind. My main focus here will be the Voodoo that is PostgreSQL, but I may occasionally post about MSSQL or even miscellaneous items... I guess we'll see!
I'm hoping to update at least once a week, maybe more frequently should the need occur.
Watch this space.
So, as I'm pulling a graveyard shift, I decided that there probably won't be a more appropriate time to do this.
I'm hoping to use this blog as a reference mainly for myself, to allow recollection of issues, problems and resolutions amongst many other little snippets that come to mind. My main focus here will be the Voodoo that is PostgreSQL, but I may occasionally post about MSSQL or even miscellaneous items... I guess we'll see!
I'm hoping to update at least once a week, maybe more frequently should the need occur.
Watch this space.
Subscribe to:
Posts (Atom)