Archive

Archive for May, 2010

Table level auto-vacuum settings

May 31, 2010 Leave a comment

While working with users on custom auto-vacuum settings, one of the real problems I saw with PostgreSQL 8.3 and below was making them understand the ways to update pg_autovacuum as sometimes little mistakes could cause you a lot of trouble.

I had some annoying experiences with those column values for pg_autovacuum at times as well, like once I had mistakenly autovacuum_freeze_min_age value set to zero while autovacuum_enabled set to false the auto-vacuum thread still kept doing vacuuming on that table. After doing a little bit of research found out that it had to be set to -1 so it can use the system-wide values and then it would all work. This was all a little confusing for the new users and then the other problem was as you a dump and restore of the database, all those settings are just gone so you will have to do it all over again.

I had been using pg_autovacuum with version 8.3 and just today while I was writing an upgrade plan for a client upgrading databases to 8.4, I just came across this improved custom auto-vacuum feature which lets you simply do a CREATE/ALTER TABLE command and set anything as you like for auto-vacuuming per table using the WITH option and it will also copy all these settings in the dump file (pg_dump output) as well which makes my life so easy while moving databases.

An example of it can be:

postgres=# \d test
Table "public.test"
Column |       Type        | Modifiers
--------+-------------------+-----------
a      | integer           | not null
b      | character varying |
Indexes:
"test_pkey" PRIMARY KEY, btree (a)
postgres=# ALTER table test SET (autovacuum_vacuum_threshold=50, 
autovacuum_analyze_threshold=50);
ALTER TABLE
--
-- PostgreSQL database dump
--
CREATE TABLE test (
a integer NOT NULL,
b character varying
)
WITH (autovacuum_vacuum_threshold=50, autovacuum_analyze_threshold=50);
--
-- PostgreSQL database dump complete
--

You can find details for the storage settings that can be set with the CREATE/ALTER TABLE at http://www.postgresql.org/docs/8.4/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS


Shoaib Mir
shoaibmir[@]gmail.com

Joomla (Wiki) setup in 10 minutes!

May 28, 2010 1 comment

A friend at work asked for setting up Joomla as a Wiki so that the team can have a centralized place to share stuff. I had never done the Joomla setup (but had heard a lot about it at different conferences) before so was a little bit worried that it might chew up a lot of my time while doing the setup (playing around with configuration files and DB setup) as I was busy with some other critical tasks.

So finally when I got the time, I logged into a fresh Ubuntu VM that was already setup and started with installing the pre-requisite packages first which were:

– Apache2

– PHP5

– MySQL Server (it was 5.1 I guess)

– php-mysql (php connector for MySQL)

– PhpMyAdmin (in order to do MySQL database administration)

Which was all with a simple ‘apt-get’ command.

Next I downloaded the latest stable release of Joomla from http://www.joomla.org/ which was version 1.5.17. Unzipped it into  /var/www/joomla folder  (web folder for apache) and just pointed my browser at:

http://localhost/joomla

….and I was surprised to see a really nice interface for installation which will do the initial setup all automatically for you that includes setting up database tables, configs and admin user.  Installation was a very simple seven step process and then I was told to get rid of the installation folder and start using my site… easyyy!!

Next thing for me was to find a way so that I can setup a Wiki in there, so I logged in as admin user to:

http://localhost/joomla/administrator

…and was presented with a very nice control panel for administrator and found the “Article Manager”. It was really simple to start adding articles for the Wiki in there, I added a few for for the demo in there and I could see them in the Joomla site (Wiki) right away which was  just sweeeeeet! as the whole thing from setup to having a doc in Wiki was all done in just a few minutes!

Then came another requirement as I was told that we need to do attachments with the articles, I did a little bit of research and found a really nice extension for Joomla which is called “Attachments for Joomla” and can be found at –> http://extensions.joomla.org/extensions/directory-a-documentation/downloads/3115

Installation for this extension was just pointing the front-end to the downloaded extension through the administrator control panel…. so just with a click we now have this new functionality where users can add attachments as well for Wiki articles.

The dudes who wanted to use it are all happy with the setup and I am just so impressed with the simple and quick install steps that I will be recommending it to people now 🙂


Shoaib Mir
shoaibmir[a]gmail.com

Categories: General Tags: ,

Merging VMWare files to single file

May 27, 2010 Leave a comment

A few days back I had to use a VMWare image, being not really a fan of VMWare I wanted to use it with VirtualBox (http://www.virtualbox.org/) and found out that .vmdk files from VMWare are supported in VirtualBox which was a sigh of relief but then got hit with another requirement…

VirtualBox supports single VMWare .vmdk file and the VM I had was using multiple small vmdk files which means it will not work in VirtualBox.

Next step for me was to find a way if possible to convert those multiple files into a single “big” .vmdk file that I can use with VirtualBox and that was in the end a single VMWare commad from the folder where I had all the small vmdk files:

# vmware-vdiskmanager -r smallfiles.vmdk -t 0 bigsinglefile.vmdk
Creating disk 'bigsinglefile.vmdk'
Convert: 100% done.
Virtual disk conversion successful.
….Once this was done it was just a matter of adding this new .vmdk file as a disk in VirtualBox and booting a VM from that.
Shoaib Mir
shoaibmir[@]gmail.com
Categories: Operating Systems Tags: , ,

Reading XML file using shell script

May 2, 2010 2 comments

This was like the first time where I had to write something that will be able to read something out of a XML file using a shell script. Usually I would use Python/Perl as my favorite choices in such a scenario but in this one I really *had* to do all within a shell script.

This is an example of the type of XML file I had to read:

<Logentry>
<changelog>Fixed a new bug</changelog>
<name>Shoaib Mir</name>
<date>Sun, 02 May 2010</date>
<email>shoaibmir[@]gmail.com</email>
</Logentry>

I ended up having a shell script like this:

#!/bin/bash

#Looking for four keywords in here
for key in changelog name date email
do
OUTPT=`grep $key log.xml | tr -d '\t' | sed 's/^\([^<].*\)$/\1/' `
eval ${key}=`echo -ne \""${OUTPT}"\"`
done

# Getting the results in four specific arrays
changelogarr=( `echo ${changelog}` )
namearr=( `echo ${name}` )
datearr=( `echo ${date}` )
emailarr=( `echo ${email}` )

#Print all Arrays
echo ${changelogarr[@]}
echo ${namearr[@]}
echo ${datearr[@]}
echo ${emailarr[@]}

Which gives me an output:

shoaib@shoaib-desktop:~/Desktop$ ./readxml.sh
Fixed a new bug Shoaib Mir
Sun, 02 May 2010
shoaibmir[@]gmail.com


Shoaib Mir
shoaibmir[@]gmail.com

Using SchemaSpy for DB diagrams

May 1, 2010 10 comments

A couple of months back I came across this very useful tool “SchemaSpy” ( http://schemaspy.sourceforge.net/ ) which really helped me out in generating automated (nightly) database diagrams but never got enough time to share it with everyone.

“SchemaSpy is a Java-based tool that analyzes the metadata of a schema in a database and generates a visual representation of it in a browser-displayable format. It lets you click through the hierarchy of database tables via child and parent table relationships as represented by both HTML links and entity-relationship diagrams. It’s also designed to help resolve the obtuse errors that a database sometimes gives related to failures due to constraints”

It works great with PostgreSQL and very easy to cron as you just need to execute a single command like this:

java -jar schemaSpy.jar -t dbType -db dbName [-s schema] -u user [-p password] -o outputDir

…that will generate very nice and user-friendly DB diagrams and a whole lots of information about the database. You can have a quick look at the sample on http://schemaspy.sourceforge.net/sample/

Update: Also found an easy to use GUI for SchemaSpy http://www.joachim-uhl.de/projekte/schemaspygui/ where you don’t need to remember all the option but you can easily pick them up from the frontend.

Make sure you grab the latest beta from http://schemaspy.sourceforge.net/schemaSpy.jar as mentioned by John Currier.


Shoaib Mir
shoaibmir[@]gmail.com

Categories: PostgreSQL Tags: ,