A personal note...

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

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'
We then "steal" some of these values and run tham against ./configure, however, we need to rememeber that the above config is a 32 bit config, and we're using 64 bit, so /usr/lib needs to become /usr/lib64 !!

Something I always get issues with is Tcl, so I nip this in the bud before it even happens:
[root@pgreporting src]# ln -s /usr/lib64/tclConfig.sh /usr/lib/tclConfig.sh

Basically, if you have the x86_64 install of Tcl, then postgres can't find tclConfig.sh, even with lib64 specified!

Now we're all configured: it's Compile time!
[root@pgreporting src]# make install
And wait for about 10-15 minutes for it all to "come together"

A quick:
[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
or
    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...

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