Asterisk Extended

Playing around with Asterisk, it was inevitable for me to stumble upon AGI.

AGI is a protocol quite like CGI which allows third party applications to be plugged into asterisk, giving them full control over the call handling. That way, even non-asterisk-developers are able to write interesting telephony applications.

One thing I always wanted to do is to set the CallerID on incoming calls. Some numbers are stored in our customer database. There is no reason not to show the customer names on the phones displays instead of only the number.

The snom phones do have a little addressbook, but it’s very limited in both amount of memory and featureset, so it was clear that I’ll have to set the CallerID via Asterisk (SIP allows for transmission of a caller-id. And so does AGI)

Additionally, I thought, it would be very nice to use the swiss phone book at or even the non-free ETV to try and guess numbers not already in our database.

That scenario is exactly what AGI is for.

As AGI works like CGI, it creates a new process for every call to AGI applications. This is not an option if you want to use interpreted languages. Well. it *is* an option considering our low amount of calls we are getting per time unit, but still. I don’t like to deploy solutions with obvious drawbacks.

Besides, launching a PHP interpreter (I’d have written this in PHP) can easily take a second or so – not acceptable if you want the AGI script to be mandatory on each call. Think of it. You don’t want the caller to wait for your application.

The solution to this is FastAGI, which works like FastCGI: A server keeps running and answers AGI-requests. Like this, you start the interpreter once and just serve the calls in the future. You save the startup-time of the interpreter.

Even better: It allows to run the AGI applications on a different machine than the PBX. This is good because you want the PBX to have as much CPU time slices as possible.

Unfortunately, this made PHP quite unsuitable for me: While it is possible to write a socket server in PHP (ext/posix does exist), I never managed to get it to work as I wanted to. It was slow, unstable and created zombies.

Then I found RAGI which was even better. For quite some time now, I have been looking for an excuse to do something with Ruby on Rails. With RAGI, I finally got it.

Getting the sample provided with RAGI to work was very easy (look at the README file). And reading through that sample file, I was very pleased to see the simplicity of writing a AGI-Application in Ruby (RAGI uses FastAGI, of course).

Now I can finally start hacking away in Rails to create my internal-customer-database / external-phone-lookup application (with some nice caching/timeout handling) to finally show the name behind the calling phone number on the displays of our SNOM phones.

Of course I’m going to provide the sourcecode here once I’m done.

Lots of fun with OpenVPN

OpenVPN may seem to you as being “just another VPN solution”. And maybe you are right.

However, OpenVPN has some distinct advantages over other VPN-solution that makes it quite interesting for deployment:

  • NAT traversal. OpenVPN uses plain old UDP-Packages as a transport medium. Every NAT router on this world can forward them correctly out-of-the-box. If not, create the usual port-forwarding rule and be done with it. If that fails too (whyever it could fail), use the TCP-protocol.
  • Ease-of-use: Install, create two certificates, use the VPN. It’s as easy as 1-2-3
  • Designed with small installations in mind. OpenVPN is not a big slow beast like IPSec for example. While it may not be as secure, it does not have all the problems associated with IPSec.
  • User-Space. OpenVPN runs completely in userspace (while using the TUN device provided by the kernel). This way the installation is non-critical and does require no reboots. Updates in case of security problems do not require reboots either.

So after this unexpected praise: What brings me to writing this posting?

Well. I’ve just deployed one of the coolest things on earth: Using OpenVPN, I have connected my home network to the network in the office. Both ends see each other and allow for direct connections. I’m not only able to print on the offices printers from home (which admittedly is as useless as it is cool), but I’m also able to – for example – stream music from home to the office over a secured channel. All using straight IP connections without any NAT-trickery or other things.

Actually not even one port is forwarded through my NAT-gateway (a ZyAir B-2000 – as the Airport-Basestation does not allow for static routes (see below), I was forced to cross-grade).

I already had some of this functionality using my previously deployed PPTP-setup, though this had some disadvantages:

  • Flacky support in Linux. Maintaining the beast across windows- and mac versions was not easy as something always broke on new versions.
  • Suboptimal security. You know: PPTP has flaws – quite like WEP. Though I’ve tried to work around them by using very very long passwords.
  • Suboptimal usability: When I wanted to connec to the office, I had to dial into the VPN, so user interaction was needed. Additionally, the default-gateway was redirected (I could have turned that off), so all open TCP connections got disconnected when I dialled.

My current solution does not have any of those problems (I don’t know about the security of course – no one does. For now, OpenVPN is said to be secure): No dialling is required, no problems with changing software-versions are to be expected (as it runs on a dedicated router which I don’t intend on changing), and I don’t have to dial in. The default gateway is not changed either of course, so the usual internet-connections go out directly. This way I’m unaffected from the office’s suboptimal upstream of 65KBytes/s (unless I use services from the office of course – but this is unavoidable).

So. What did I do?

At the very first, I had to recompile the kernel on the server side once. I have not included TUN-support when I created my .config last year. After this, emerge openvpn was all that was needed. I kept the default configuration-file somewhat intact (install with the “examples” USE-flag and use the example-server.conf), but made some minor adjustments:

local x.x.x.x
push "route"
client-config-dir ccd
#push "redirect-gateway"

(just the changed lines)

and the /etc/openvpn/ccd/Philip_Hofstetter:


Now, what does this configuration do?

  • Bind to the external interface only. This has only cosmetical reasons
  • Push the route to the internal network to the client. Using the default configuration, all OpenVPN-Addresses are in the network which allows me for nice firewall-settings on the server-side. The network is our office-network
  • Tell OpenVPN that there are some client-specific configuration options to reach the net which is my home network
  • Comment out the option to let OpenVPN set the default gateway. We really don’t want all the traffic in my home net going through the office

Then we create this client-configuration file. It’s named after the CN you use in the SSL-certificate, while replacing spaces with underscores. You can see the correct value by setting up everything and then connecting to the server while watching the logfile.

In the client specific configuration-file we confirm the additional route we want to create.

The configuration file on the client router is unchanged from the default.

The only thing you need now is the SSL-certificate. Create one for the server and more for each client. I won’t go into this in this article as it’s somewhat complicated on itself, but you’ll find lots of guides out there.

I used our companies CA to create the certificates for both the server and the client.

After this, it’s just a matter of /etc/init.d/openvpn start on both machines (the path to the certificates/keys in the configuration files must match your created files of course).

Just watch out for the routing: On the server I had to change nothing as the server was already entered as default gateway on all the clients in the office network.

In the client network, I had to do some tweaking as the default-gateway was set to the Airport Basestation, which (understandably) knew nothing about the network, so was unable to route the IP-packets to the VPN-gateway in the internal network (my Mac Mini).

Usually you solve that by installing a static route on the default gateway in your network. Unfortunately, this is not possible on an airport basestation. A problem I have solved by replacing it with a ZyAir B-2000 from Zyxel which allows for setting static routes.

On that new access-point I created a route equivalent to this unix-command:

route add -net netmask gw

Where is the address of my Mac Mini on which OpenVPN was running as client.

Then I issued “echo 1 > /proc/sys/net/ipv4/ip_forward” on the Mac Mini to allow the packets to be forwarded.

So whenever I send packets to one of the offices computers – let’s say, this is what happens:

  1. The client uses it’s IP and netmask to find out that the packet cannot be delivered directly. It sends it to the default gateway (my ZyAir)
  2. The ZyAir consults its routing table to watch for the route to and finds as gateway for that network (every other address would have been routed thorugh my cable modem)
  3., shion, watches it’s own roting table where OpenVPN has created a route thorugh the VPN-interfaces (10.8.0.x) to the network. It delivers the packet there.
  4. On the other end of the tunnel, the OpenVPN-Server delivers the packet to the destination server.

The path of the reply-packets is the same – just from the bottom to the top.

After getting the routing as I wanted it (verifyable by pinging petween computers in both networks), the next step was pure cosmetics:

  • Create an internal DNS-server. Use it as a slave for the office’s DNS-server to allow for DNS-lookups to work without crossing the VPN each time
  • Use said DNS-server to create entries for the computers in my home network
  • Make the office DNS-server a slave for that home-zone (to reach my computers by name)

All of this was most interesting to implement and went much more smootly than anything else I’ve tried so far VPN-wise. Finally, I have the optimum solution concering connectivity to my office.

And besides that: It was fun to implement. Just worthy of a “Supreme nerd” – the title I got here for my 92 points.



FreeNX is the GPLed variant of NoMachines NX product.

While exporting X-Sessions never has been a problem, it was kind of slow especially on connections with limited bandwidth. NX tries to solve this by using some tricks at the X11-protocol level, a little proxy-server and a big local bitmap cache. They promise fluently working X-Sessions even over a 56K modem.

Well. I have installed KDE and now FreeNX on my Mac Mini, which I bought for the sole purpose of being a little home-server/VPN-Gateway. My NSLU2 while being a really nice little thing, does not work with OpenVPN due to the kernel lacking TUN-support.

Installation was easy and flawless – besides forcing me to forward port 5000 to the NATed mac mini as the commercial (freeware) windows-client seems to have problems with the FreeNX-server when tunneling the X-Session over ssh.

The client works very well too. And I can say: It’s fast. Very, very fast.

Some more things to note about the screenshot:

  • While I usually had the policy to name servers after persons and then locations from “lord of the rings”, I somewhat run out of names, so I began using names from RPGs. My Mac Mini is called Shion, after Shion Uzuki of Xenosaga.
  • I’m running Gentoo, of course.
  • Installing FreeNX is as easy as emerge nxserver-freenx on Gentoo.
  • The screenshot is of a session exported at 800×600 pixels. Using more pixels does not slow down the session siginficantly, but those 800×600 where comfortable to use on my current display so I can have other things besides the session.

Praise to ZSH

Jochen Maes talks about zsh today. (I found that blog via

I wholeheartly agree with Jochen here.

Finally someone else writing good stuff about zsh.

I’m using this shell since 2000 where I did my first serious steps with Unix. This mainly has three reasons:

One is the “User Friendly Users Guide” available here. Besides this being an excellent introduction to zsh it is one to unix shells in general. When you’re learning unix shells using this guide, you’ll somewhat automatically stay with zsh.

The other reason is the great flexibility and expandibility. Zsh had a programmable autocomplete-feature long before bash had (or at least long before it was generally known) and even better: It came with some autocompletition functions already enabled for some tools (like tar or even scp). Programmable autocompletition allows you create special autocompletitions depending on the context you are hitting tab.

So let’s say if you are beginning to type

$ scp gnegg.dat

and then hit tab, zsh will actually autocomplete on the remote server(!) and create

$ scp gnegg.dat

for you (assuming that directory exists)

The same goes for tar (even with .gz or .bz2 compressed ones). Or cvs or svn

While gentoo provides bash-completition-config which does the same for bash, zsh was there first. And it provides many senseful completitions.

The third reason for me going with zsh is the syntax of the shell-scripts which can be configured to be much more intuitive to a C-programmer than the default-syntax, while still being more like ksh/bash than (t)csh.

So for me, switching from bash to zsh was a no-brainer back in 2000. And as with the text-editors: Once you use a certain tool, you will not change it afterwards.

I strongly recommend you to take a look at zsh too.

Asterisk – it’s getting real

Last week I talked about me and Christoph installing Asterisk on my thinkpad to do a little VoIP-Experiment.

While we were able to create a should-be-working configuration, actually calling to the outside PSTN network did not work. Read the details in my other article.

Last saturday, we fixed that.

There seems to be a problem somehwere between the AVM CAPI Driver and the CAPI layer of the 2.6.11 kernel. After we downgraded to 2.6.10, the problem solved itself without we doing anything more.

So… this was getting interesting…

The first thing I did was to annoy my wonderful girlfriend:

exten => s,1,Wait,1 ; Wait a second, just for fun
exten => s,2,Answer
exten => s,3,MP3Player(/home/pilif/mp3/3.mp3)

(included into or used as the default context)

Where 3.mp3 is that endlessly stupid song “Tell me” (or whatever it’s called) by britney spears (this is an insider-joke – both of us just hate that song). Then I told her to call that number…

While this example is completely pointless, it was fun to watch my girlfriend connecting and listening to the song (which soon ended in a disconnection log entry)

exten => s,1,Wait,1
exten => s,2,Dial(SIP/12345,60,tr)
exten => s,3,Congestion

This makes much more sense and directs all incoming calls to the SIP-Phone 12345 as configured in sip.conf. After 60 seconds, it sends back a congestion signal. The first entry would not be necessary, but I hate it when I call somewhere and the phone is answered just at the first ring. So in my PBX, the answering party will wait one second before directing to the sip-phone.

In musiconhold.conf I’ve configured madplay as my MP3-Player for music on hold:

default => custom:/home/pilif/mp3/,/usr/bin/madplay --mono -R 8000 --output=raw:-

madplay is much better than mpg123 used per default as it accepts VBR encoded input and bitrates > 128 kbit which is what nearly all of my MP3’s are encoded with.

In zapata.conf enable music on hold with musiconhold=default in [channels]

The next thing was an optimization of the SIP-Phone used…

X-Lite is nice, but in the end it’s just a demo for other products by the same vendor. Call transferring is not possible for example, which is what we wanted to try next.

The best soft phone we’ve seen so far is SJPhone. A configuration guide is here

But the real clou is the Zyxel 2000W phone that’s currently on my desk: The phone has a WLAN interface (unfortunately no WPA support) and can perfectly well speak with asterisk.

The phone has some problems though: it’s slow, it has no support for call transferring, nor holding, neraly every configuration change causes it to reboot,… In the end I really hope Zyxel will further improve the firmware, which is what they seem to be doing – the current release is from the end of february, so quite current.

The next thing will be trying to install a webbased frontend to asterisk and creating a real dialplan with voice mail. Then, our experiment will be over and we’ll see how it can be put into practical use (like finally getting rid of the old, proprietary PBX from alcatel of our landlords)

Fun with VoIP

When I read for then n-th time about Asterisk, an Open Source PBX solution, I deceided to team up with Christoph and tame the beast.

I have actually two problems with asterisk as it stands now:

  1. There’s not much really useful newbie-documentation or tutorials. There are some sample configurations, but they are not very useful because…
  2. the tool has a incredibly intransparent and difficult to understand syntax for it’s main configuration file (extension.conf). I’t just like it’s with sendmail: Many extremely low-level things to care of for getting complex high-level results.

I thought, that teamed up with Christoph, we’ll more likely to see some results.

The first thing was defining the parameters of our experiment. Here’s what we wanted to do:

  • Act as a SIP-Proxy, so two softphones (we did not want to buy too much actual hardware yet) could talk to each other.
  • Provide a gateway to the ISDN-Network, so both SIP-Phones can dial out to the rest of the world.
  • The same gateway should be able to receive incoming calls and direct them to one of the Phones (just one for now).

In the next session, we want more advanced features, like voicemail and waiting music. A third session should provide us with a webbased frontend (I know there are some). But for now, we wanted to concentrate on the basics.

The next step was to get the required hardware. I already have Gentoo running on my Thinkpad, so that was a good base. Furthermore, we needed any ISDN-Solution being supported by Asterisk. As we had a plain old BRI interface and a very limited budget (it was just an experiment after all), we went with the Fritz Card USB by AVM which has Linux CAPI drivers, albeit only binary ones (we could also have used the PCMCIA-version, but this is three times as expensive as the USB one).

Said piece of hardware proved to be a real pearl: It’s very compact, does not need a power adaptor and was very easily installed under Linux. I would not be using this for a real-world solution (which most likely requires PRI support and absolutely would require open sourced drivers), but for our test, this was very, very nice.

Installing the needed software is where gentoo really shined as everything needed was already in the distribution: After hooking up all the stuff, we emerged net-dialup/fritzcapi, net-misc/asterisk and net-misc/asterisk-chan_capi which suked in some more dependencies.

The next step is to reconfigure the kernel for the CAPI-stuff to work. Just include everything you find under “Device Drivers / ISDN Support / CAPI” – even the one option marked as Experimental (as the CAPIFS is needed and only available when enabling “CAPI2.0 Middleware support”)

Then, we made sure that CAPI (a common ISDN access API) was running by issuing capiinit start.

Then we went on to asterisk.

The fist thing, you have to do is to set up the phones you’re using. As we worked with SIP-Phones, we used sip.conf:

port = 5060
bindaddr =
tos = none
realm =
srvlookup = yes

context = theflintstones
dtmfmode = rfc2833
disallow = all
allow = gsm
callerid = "Fred Flintstone" <12345>
secret = blah
auth = md5
host = dynamic
reinvite = no
canreinvite = no
nat = no
qualify = 1000
type = friend

accountcode = 12346
dmtfmode = rfc2833
host = dynamic
auth = md5
secret = blah
canreinvite = no
context = theflintstones
qualify = 2000
type = friend
disallow = all
allow = gsm

This worked with our two test-phones running X-Lite

Interesting are the following settings:

realm The realm. I used our internal domain here. The default is asterisk. Your VoIP-Address will be identifier@[realm].
accountcode This is the username you’re going to use on the phone
context The context will be used when we create the dial plan in the feared extension.conf

Then, we configured CAPI in capi.conf



Those settings are said to work in Switzerland. Interesting is the setting for msn. This is where you enter the MSNs (phone numbers) assigned to your NT. I somewhat X-ed it out. Just don’t use any leading zeroes in most countries. You can enter up to five using commas as separator.

The next thing is to update modules.conf. In the [modules]-Section, add load =>, in the [global]-section, add

Without those entries, asterisk will complain about unresolved symbols when loading the CAPI modules and will finally terminate with a “broken pipe”-Error. Thrust us. We tried. ;-)

The best thing now is that you can already test your setup so far. Launch asterisk with asterisk -vvvvvc (each v adds a bit of verbosity, while -c tells it not to detach from the console). If it works well, you’ll end up at a console. If not, make sure, that capiinit did not report any error and that you’ve really added those lines to module.conf.

Now for the fun of it, call one of your MSNs with any phone.

Asterisk should answer and provide you with a demo-menu

The next step is configuring extensions.conf. This is somewhat complex and I will go into more detail, as soon as I’ve figured out, what’s wrong with our test-configuration. We’ve added this to the end:

exten => _0[1-9].,1,Dial(CAPI/44260XXXX:b${EXTEN},30)
exten => _0[1-9].,2,Hangup

include => ch-fest-netz

Just look that you enter one of the MSNs you have configured in capi.conf.

Now what this configuration should do is to allow those SIP-phones (recognize the “context” we used in sip.conf?) to dial out via CAPI.

You best learn how to configure this beast by calling the demo-voicebox and then comparing the log output of Asterisk with the entries in extension.conf. Basically, exten => defines a dial plan to execute. Then comes the pattern of numbers dialed to recognize. After that comes a (BASIC-like) sequence-number, followed by the action to execute.

The format of the number-pattern is explained in one of the comments in extension.conf

Now, this configuration does not work for us: When I dial on the SIP-Phone, Asterisk notices this, actually connects the ISDN-line (the target phone actually rings), but does not seem to notice when the target phone is answered.

If I answer the phone, it’s just silence in the line. The SIP-phone is still in the “trying to connect”-state.

This stays this way until I cancel the dial attempt in the SIP-phone. After that, asterisk prints more log entries – one of them the notice that the connection was successfully established.

A question in the malinglist was promptly answered: My configuration is correct, but maybe I’m running into a bug of Kernel 2.6.11. I was told to downgrade to 2.6.10, which is what I’m going to do next.

After this, I will extend the dial plan so I can call the internal SIP-phones both from another softphone or from a real phone over the ISDN

It’s hacky, it’s just somewhat working, but it’s a lot of fun!

I’ll keep you updated.


For the last five years or so, I’ve been using ModLogAn for my/our web analyzing needs: The tool is fast and much more powerful than Webalizer which I was using before modlogan

Getting it to run was a bit difficult at first (requiring a hacked GD library and all that), but this gradually got better. Since then the tool does a wonderful job (except one broken release about three years ago).

With all this buzz about the incident which happened because of a hole in AWStats, I wanted to give said tool (in a fixed version – of course) a shot.

The gentoo ebuild is tightly integrated into webapp-config which I’ve not used before, so the installation was somewhat difficult for me, but some symlinks here and there soon brought me a working setup.

I must say that I’m impressed of the tools capabilities: It’s quite fast (not as fast as modlogan, but fast enough), its CGI user interface profits from its dynamical nature (filtering long lists in realtime for example), the plugins provided with it are very cool (geoip, whois,…) and as soon as one understands how it ticks, it’s really easy to configure and manage.

Useful for some people is its possibility to update the statistics in realtime by analyzing the current rotation of the logfile. Another thing, modlogan isn’t capable of.

And finally it’s the looks – as always. awstats looks much more pleasant than modlogan does (even when using the template-plugin which has the nicest look of all of them).

I’ve not deceided yet whether I should replace the currently well-working modlogan-setup or not, but I’ve certainly analyzed the whole backlog of (link to the tool removed due to redesign).

IRC Clients

When my favourite game movies site (written about it here and here) went offline last week, I ventured a look into its IRC channel to find out what’s going on.

Chatting with the guys there was so much fun that I deceided that it’s time to get into IRC after all (I never really used it before, so I did not really have a big insight into this part of the net)

Soon after this decision, I began learning the ins and outs of IRC and the first thing I did was setting up a bouncer (IRC-proxy – let’s you be logged into a channel despite your client machine being offline. Very useful for getting an overview on what happened while you were away). There are quite many available, but the only one that seems to be still maintained is ctrlproxy

If you plan on using mIRC with it, go and install the current pre-release 2.7pre2. Older versions don’t let you connect.

Next was the question which client to use.

While mIRC is nice it has two problems: a) it’s single-platform. As I’m constantly using all three of Win/Mac/Linux, a single program would be nice so I don’t have to relearn all the shortcuts for each platform. b) It does not look very polished and cannot be made to do so.

Klient looks much better, but is still single-platform and has problems recognizing the state when reconnecting to the ctrlproxy (it sometimes does not notice that you are already in a channel).

virc looks better than mirc, but worse than Klient. Plus, it seemed a bit unstable to me. And it was slow displaying the backlog. Very slow. It’s single-platform too (and written in Delphi it seems)

irssi is single-platform too, but I could work around that by running it on our webserver and using screen.

A program that warns with

17:43 -!- Irssi: Looks like this is the first time you've run irssi.
17:43 -!- Irssi: This is just a reminder that you really should go read
17:43 -!- Irssi: startup-HOWTO if you haven't already. You can find it
17:43 -!- Irssi: and more irssi beginner info at
17:43 -!- Irssi:
17:43 -!- Irssi: For the truly impatient people who don't like any automatic
17:43 -!- Irssi: window creation or closing, just type: /MANUAL-WINDOWS

before starting it and with no obvious way to exit it (Ctrl-C, quit, exit – neither did work) is something I’m afraid of (quite like vim, though I learnt to love that one). So: no-go

Finally I ended up with X-Chat. It looks good, has all features I need, a big userbase, is maintained and is multiplatform after all.

There was this fuss about the windows version becoming shareware, but I can live with that as the tool is very, very good. For supporting it’s author, I gladly payed those $20 (I see it as a packaging fee – just like with those linux distributions), though you can get a windows binary for free here.

So for me, it’s X-Chat. And much fun in #nesvideos

Why I love the command line

Today I had the task to join together quite some mp3-files.

I had about 100 radio plays, each devided in three to six files which I wanted to have joined to one file per play so I can better organize them on my iPod

There are tools out there doing exactly that. mp3surgeon being one of them. All these tools a) have a non-scriptable GUI (meaning lots and lots of clicks) and b) cost money

b) would not be a pronlem if those tools would work for me, but because of a) they do not.

Then I found mpgedit a command line tool capable of joining MP3’s (respecting VBR-headers, but without recoding the new file)

As it’s usable from the command line, I could write a small script doing exactly what I wanted:


$dir = dir(".");
while (false !== ($entry = $dir->read())) {
	if (preg_match('/^.+$/', $entry)) continue;
	$path = '.\'.$entry;
	if (is_dir($path))

function doJoin($dir){
	echo "Looking in $dirn";
	$of = escapeshellarg("..\".basename($dir).".mp3");
	$files = array();
	$d = dir(".");
	while (false !== ($entry = $d->read())) {
	   if (!preg_match('/.mp3$/', $entry)) continue;
	   $files[] = $entry;
	$files = array_map('escapeshellarg', $files);
	system("c:mp3mpgedit_nocurses.exe -o $of -e- ".implode(' ', $files));

Note that it’s written in PHP as this is the language I currently do most of my work with. And note that it’s very customized to just my needs. None the less it works very well and saves me from about 200’000 clicks

Now this is exacltly why I love the command line.

ALTER TABLE in PostgreSQL 8.0

I’ve just discovered my new favourite feature of the upcoming PostgreSQL 8.0: Let’s say, you have forgotten a column when creating the schema of a table. Let’s also say there already exist foreign kays referencing this table, so dropping and recreating it with the updated schema from your text-editor won’t work (or force you to recreate all other tables too).

So, you need alter table

Here’s what Postgres < 8 needs to add a column cdate which must be not null and have a default-value of current_timestamp:

alter table extart_prods add cdate timestamp;
update extart_prods set cdate = current_timestamp;
alter table extart_prods alter column cdate set not null;
alter table extart_prods alter column cdate set default current_timestamp;

And here’s what it takes to do it in PostgreSQL 8:

alter table extart_prods add cdate timestamp not null
    default current_timestamp;

When typing this into psql, you’re so much faster. This is actually the only feature I really missed when going from MySQL to PostgreSQL for all bigger work

Oh and did I mention that in Postgres 8 (currently running Beta 4) the statement is executed noticably faster than in Postgres 7.4 (though this doesn’t really matter – you should not be altering production tables anyway)