beautifying the version

Our environment has PostGreSQL databases running on separate servers from the application. And there are many more application (client) servers than there are database servers.
We have a perl script that is run by various development, QA and production release folks from client servers to upgrade (change) schemas on a database server.
I had a need to modify the script to handle the pg_class.reltriggers column rename to relhastriggers in PG version 8.4.

To accomplish this, I had to test the database version.
This is easy enough, just use the DBD::Pg function:
my $pgver = $dbh->{'pg_server_version'};  
Which returns '80401' for version 8.4.1.
If the $pgver ge 80400, then use the new column name, no problemo.

Additionally, we have had other issues with the client pg version being too old to handle certain newer features in the functions, for example double-dollar $$ quoting, or just major versions in psql which is often used from the application servers.
So I decided to enhance the script to check the client version as well and compare to the server version, then possibly give the user a warning.

However, I didn't want to display the 'raw' version strings ala "hey, your client is version 80304 and your database version is 80401 -- could be some ungoodness happnin'".
Besides, the last number (patch version) doesn't make a difference - just the first two numbers, like 8.3 different than 8.4, because 8.4.0 and 8.4.1 will work just fine together

So I went to work on the beautifying the version numbers for those non-DBA folks that use the script to understand what this potential warning meant.

For this code example, let's say that the version for the client is 8.3.3 and for the database is 8.4.1.

First get the 'raw' versions:

my $pgver_srv = $dbh->{'pg_server_version'}; # returns '80401'

my $pgver_lib = $dbh->{'pg_lib_version'}; #returns the client library version '80303'

Then drop the last 2 digits (the values after the second decimal):

my $pgver_srv_maj = sprintf '%.3g',($pgver_srv /10000); #returns '8.04'

my $pgver_lib_maj = sprintf '%.3g',($pgver_lib /10000); #returns '8.03'

Then remove the meaningless leading zero after the decimal:

$pgver_srv_maj =~ s/\.0*/\./; #returns '8.4'

$pgver_lib_maj =~ s/\.0*/\./; #returns '8.3'

Now these variables can be compared and displayed to the user if necessary.

make it faster

That's my goal as a DBA is to make it, being 'everything', better. Make queries faster. Make the databases secure and keep them available with little-to-no downtime.

cool psql prompts

I developed this as my standard prompt a few years back (double-click to see better):




- - - - - - - - - -

Here is the code to put in a .psqlrc file which should be created in your HOME directory:


--%M = database server
--%/ = database name
--%n = database user
--%> = port number
\echo '\n\033[33;1m-> Prompt is DBServer(LocalHost).DBName.DBServer.PortNumber [Local OS Timestamp]\033[0m\n'
\set PROMPT1 '\033[33;1m%M(from '`hostname`').%/.%n.%> [%`date`]\033[0m\n> '
\set PROMPT2 '> '

- - - - - - - - - -


You can change the colors by changing the [33 string to another number in the ASCII foreground list:

• black 30
• red 31
• green 32
• yellow 33
• blue 34
• magenta 35
• cyan 36
• white 37

- - - - - - - - - -


If you have scripts that call psql and you don't want this extra text showing up in the output, just add the -X option to the psql call to not use a .psqlrc file.

PG dup indexes

A problem that occurs in our environment with several different databases where manual changes have been made, then automated schema changes are applied is duplicate indexes.
This isn't a major problem per-se, but does incur overhead for DML and of course takes up additional disk space and requires more work when the autovacuum maintains the table.
So, here's how you can find 'em and decide which to get rid of based on usage denoted in the idx_scan column:


SELECT
ct.relname AS tablename,
ci.relname AS indexname,
s.idx_scan, s.idx_tup_read, s.idx_tup_fetch,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS indexsize,
pg_get_indexdef(i.indexrelid)
FROM pg_index i
JOIN ( SELECT COUNT(0), indrelid, indkey, indpred, indexprs
FROM pg_index
GROUP BY 2,3,4,5
HAVING COUNT(0)>1) AS x
ON (x.indrelid=i.indrelid AND x.indkey=i.indkey)
JOIN pg_class ct ON ct.oid=i.indrelid
JOIN pg_class ci ON ci.oid=i.indexrelid
JOIN pg_stat_all_indexes s ON i.indexrelid=s.indexrelid
ORDER BY ct.relname, i.indkey, s.idx_scan;

launch

Between a full-time job, being a new parent, owning/managing a rental home, why am I starting to blog?
How will I find the time? the energy?

We'll see...