Postgres and I

SQLServer 2000 Error

2011-09-07T07:44:00.000+01:00

So this was a nasty little surprise I got from MS SQL Server 2000 after multiple batch job failures...

SqlDumpExceptionHandler: Process 83 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 09/05/11 08:53:10 spid 83
*
* Exception Address = 10029D26
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred writing address 00000000
* Input Buffer 740 bytes -
* BEGIN TRANSACTION DROP TABLE branch_stocktake.dbo.postgres_desc_1; SEL
* ECT * INTO branch_stocktake.dbo.postgres_desc_1 FROM OPENQUERY([MONAR],
* 'SELECT stockno, stkgroup, description FROM mssql_import.postgres_descri
* ptions limit 500000'); ALTER TABLE branch_stocktake.dbo.postgres_desc_1
* ALTER COLUMN stockno varchar(15) COLLATE SQL_Latin1_General_CP1_CS_AS N
* OT NULL;
*
*
* MODULE BASE END SIZE
*
* Edi: 00000000:
* Esi: 172E79E0: 3750534C 30303939 00503235 00393937 20474B34 4F54454D
* Eax: 0000000C:
* Ebx: 0BB49FD8: 0000000B 00000000 00000000 00000000 00000000 00000000
* Ecx: 00000003:
* Edx: 00000000:
* Eip: 10029D26: C88BA5F3 F303E183 5B5E5FA4 909090C3 90909090 90909090
* Ebp: 00000008:
* SegCs: 0000001B:
* EFlags: 00010206: 00610050 00680074 0043003D 005C003A 00490057 0044004E
* Esp: 0BB3B4F4: 0A030398 00000000 0A837260 10004E68 0BB49FD8 172E79E0
* SegSs: 00000023:
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
* 10029D26 Module(UNKNOWN+00000000)
* -------------------------------------------------------------------------------

Along with a lot of other garbled rubbish I got:

CImageHelper::Init () Failed load of dbghelp.dll - Not enough storage is available to process this command.
***Symbol load failed - Proceed to dump without any symbols
*Stack Dump being sent to M:\Program Files\Microsoft SQL Server\MSSQL\log\SQLDump0009.txt

This actually caused another failure in the chain...

WARNING: Failed to reserve contiguous memory of Size= 65536.

(alongside a lot of other memory based errors...)

After much head scratching, and a few google queries without any real light being shed, I thought about this a bit and changed my google query a little.

It turns out that the fault was disk space, despite there being over 3gb available on C:, about 57gb on M: and 57gb on T:, it was the C: drive at fault.

How? Well the pagefile settings on this server were set to a manual value using the C: Drive. The values? 2048-4096 meg. Ah. That'd do it. Windows wants 4gb of page space on a drive that only has 3gb remaining.

A simple move to M: cured it.

A personal note...

2011-08-24T07:07:00.000+01:00

Ok, so here's the thing...

I've been hammering the gym and various other highly active pursuits in an attempt to be fitter and lose my body fat whilst building muscle. I've even radically changed my diet to assist in my endeavours..

So, not a normal business as usual DBA task obviously, but something I am committed to nonetheless.

Can I shift the remaining fat and become "ripped"? No.

I still stand 76.7Kg (12st 1lb) BMI of 26, Body fat 21.1% Visceral fat 9.

You know, maybe I should change careers to something where I'm more active....

...or maybe not!

I guess the call of the databases is too strong for me to ignore!

Installing Postgres 9 from source

2011-08-22T08:31:00.000+01:00

Ok, so serious post time here. This morning I need to build a new postgres reporting database on RHEL 6.1. The RPM's that ship with RHEL 6.1 only provide 8.4.7, so it's source code time...

It's not that I'm against a 9.0 RPM install, I just don't like the default installation path, and much prefer to set it the same as many other servers, simply for consistency.

So, here we go:

Step one - Create the postgres user
Whilst logged in as a superuser.
[root@pgreporting ~]# id
uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel)
[root@pgreporting ~]# useradd -c "PostgreSQL Server" -d /var/lib/pgsql -m postgres
[root@pgreporting ~]# grep postgres /etc/passwd
postgres:x:500:501:PostgreSQL Server:/var/lib/pgsql:/bin/bash
Step 2 - Create the necessary mount points.
There are a series of default mount points that I use, and these need to be set up.
A quick edit of the fstab, followed by a mount <%s> command where <%s> is the mount name is done (forgive me for not providing the full details here! Security and all!)

Ouch!! " mount.nfs: Remote I/O error " - This can't be good... My cifs mount worked, but nfs mounts didn't. Is nfs installed?
[root@pgreporting sqlbackups]# rpm -qa | grep nfs-utils
nfs-utils-1.2.3-7.el6.x86_64
nfs-utils-lib-1.1.5-3.el6.x86_64
Restart nfslock then...
[root@pgreporting sqlbackups]# /etc/init.d/nfslock restart
Stopping NFS statd: [ OK ]
Starting NFS statd: [ OK ]

Nope - didn't work.

rpcbind perhaps?
[root@pgreporting sqlbackups]# /etc/init.d/rpcbind restart
Stopping rpcbind: [ OK ]
Starting rpcbind: [ OK ]

Nope - Didn't work! Hmm....

Ok - got it. Normally I'd apply the "defaults" option to an nfs mount, however, it seems there's a bug in this version on RHEL 6, as it uses version 4, so adding the option vers=3 solved it.
Step 3 - Get the source, unzip and get ready to configure it!
I already have the source located on a cifs mount, so a quick cp to /var/lib/pgsql/src:
[root@pgreporting ~]# cd /var/lib/pgsql
[root@pgreporting pgsql]# cp /sqlbackups/postgres/PostgresSource/Postgres/9.0.1/openlogic-postgresql-9.0.1-all-src-1.zip ./src

[root@pgreporting pgsql]# cd ./src
[root@pgreporting pgsql]# unzip openlogic-postgresql-9.0.1-all-src-1.zip
[ ... snip ... ]

And we now have source code!

Step 4 - Prerequisites.
Now I plan to run Postgres with some bits and pieces enabled, so I'm going to need to add some packages to the machine:

Pam:
[root@pgreporting pgsql]# yum list available | grep pam-devel
pam-devel.i686 1.1.1-8.el6 rhel61
pam-devel.x86_64 1.1.1-8.el6 rhel61
[root@pgreporting pgsql]# yum install pam-devel.x86_64

Tcl:
[root@pgreporting pgsql]# yum list available | grep tcl-devel
tcl-devel.i686 1:8.5.7-6.el6 rhel61
tcl-devel.x86_64 1:8.5.7-6.el6 rhel61
[root@pgreporting pgsql]# yum install tcl-devel.x86_64

Krb5
Oooh - Hang on...
[root@pgreporting pgsql]# yum list installed | grep krb5-devel
krb5-devel.x86_64 1.9-9.el6 @anaconda-RedHatEnterpriseLinux-201105101844.x86_64/6.1
Already installed!

ssl
[root@pgreporting pgsql]# yum list available | grep openssl-devel
openssl-devel.i686 1.0.0-10.el6 rhel61
[root@pgreporting pgsql]# yum list installed | grep openssl-devel
openssl-devel.x86_64 1.0.0-10.el6 @anaconda-RedHatEnterpriseLinux-201105101844.x86_64/6.1
Again - Already installed

[root@pgreporting pgsql]# yum list available | grep libxml2-devel
libxml2-devel.i686 2.7.6-1.el6 rhel61
[root@pgreporting pgsql]# yum list installed | grep libxml2-devel
libxml2-devel.x86_64 2.7.6-1.el6 @anaconda-RedHatEnterpriseLinux-201105101844.x86_64/6.1
Already installed.

[root@pgreporting pgsql]# yum list available | grep openldap-developenldap-devel.i686 2.4.23-15.el6 rhel61openldap-devel.x86_64 2.4.23-15.el6 rhel61[root@pgreporting pgsql]# yum install openldap-devel.x86_64

So let's configure!

Step 5 - Configure
Now this is the bit where I get all anal about file/folder locations. I have a load of databases scattered over all sorts of places. Typically they're all located in /var/lib/pgsql (unless I'm hiding them) so we need to configure with these options. The easiest way I find to get the correct locations is by running "pg_config --configure" on a server already installed and running:
[pgsql1 ~]# pg_config --configure
'--build=i686-redhat-linux-gnu' '--host=i686-redhat-linux-gnu' '--target=i386-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include' '--libdir=/usr/lib' '--libexecdir=/usr/libexec' '--localstatedir=/var' '--sharedstatedir=/usr/com' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--disable-rpath' '--with-perl' '--with-tcl' '--with-tclconfig=/usr/lib' '--with-python' '--with-openssl' '--with-pam' '--with-krb5' '--enable-nls' '--enable-thread-safety' '--sysconfdir=/etc/sysconfig/pgsql' '--datadir=/usr/share/pgsql' '--with-docdir=/usr/share/doc' 'CFLAGS=-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -march=i386 -mtune=generic -fasynchronous-unwind-tables' 'build_alias=i686-redhat-linux-gnu' 'host_alias=i686-redhat-linux-gnu' 'target_alias=i386-redhat-linux-gnu'

[root@pgreporting src]# ln -s /usr/lib64/tclConfig.sh /usr/lib/tclConfig.sh

[root@pgreporting src]# make install

[root@pgreporting src]# which postgres

/usr/bin/postgres

Tells me it worked!

Step 6 - Initdb
for this next step we need to be the user "postgres", so a quick "su - postgres" sorts that for us.

I prefer to set some variables for postgres here, typically "PGDATA" and some aliases for pg_ctl, I'm not going to insult anyones intelligence here, I'm sure that this is a common task for all, so I'll cut out the posting of the edit here. I will however state that my default data directory is set as:
/var/lib/pgsql/data

So lets initdb!

Success. You can now start the database server using:

postgres -D /var/lib/pgsql/data

pg_ctl -D /var/lib/pgsql/data -l logfile start

Exactly what I wanted to see! A quick edit of postgresql.conf, pg_hba.conf and a few other symlinks etc, and we're all done!

Happy installations!

DR Replication woes...

2011-08-21T20:30:00.001+01:00

So here we are again, only this time on a Sunday. Joy.

Recent disk space issues on a production machine caused me some major issues:
pg_xlog (on a seperate physical disk) ran out of space due to what can only be described as a "blip" in the amount of data that was batch processed. Postgres panicked and gave up archiving. This is not normally an issue, however, it meant that I had to do some recovery work as the main data tables were destroyed.

After the recovery work was completed, a quick inspection of the DR machine, pg_standby had complained about file inconsistencies, and so I broke the replication link.

so this evening, both live and DR databases have been stopped, and I'm taking a base backup using scp.

Now I know there are several different ways to provide DR replication, but in this instance I'm using log shipping. Ok, so I should base backup once a week, however, the disk requirements for this are too great, and so here I am, doing it manually... Again...

Back In The Office!

2011-07-22T07:39:00.000+01:00

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!

Another Graveyard Shift

2011-07-20T22:53:00.000+01:00

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!

pg_hba.conf

2011-07-20T01:50:00.004+01:00

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

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...

2011-07-20T00:46:00.000+01:00

<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?

2011-07-20T00:12:00.004+01:00

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?

Welcome to my new blog...

2011-07-19T23:42:00.000+01:00

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.