Adding vdev to raidz pool

There is one fundamental flaw in raidz ZFS pools: you can't add devices to raidz (as opposed to usual raid5). So, could we do anything to expand our pool without full rebuilding? Yes, if we are ready to sacrifice one more disk.
From zpool man page:

A pool can have any number of virtual devices at the top of the config‐
uration (known as "root vdevs"). Data is dynamically distributed across
all top-level devices to balance data among  devices.  As  new  virtual
devices are added, ZFS automatically places data on the newly available
devices.

So, to add some capacity to our pool, we can add one more raidz to it. Let's look at this process. Firstly, let's create test pool from three "disks":

freebsd# foreach i ( 1 2 3 4 5 6)
foreach? dd if=/dev/zero of=/tmp/disk$i bs=100M count=1
foreach? end
freebsd# zpool create testpool raidz /tmp/disk1 /tmp/disk2 /tmp/disk3

Now, let's see what we have:

freebsd# zpool status testpool
  pool: testpool
 state: ONLINE
 scrub: none requested
config:

        NAME            STATE     READ WRITE CKSUM
        testpool        ONLINE       0     0     0
          raidz1        ONLINE       0     0     0
            /tmp/disk1  ONLINE       0     0     0
            /tmp/disk2  ONLINE       0     0     0
            /tmp/disk3  ONLINE       0     0     0

errors: No known data errors
freebsd# zpool list testpool
NAME       SIZE   USED  AVAIL    CAP  HEALTH  ALTROOT
testpool   286M   156K   286M     0%  ONLINE  -
freebsd# zfs list testpool
NAME       USED  AVAIL  REFER  MOUNTPOINT
testpool  95.9K   158M  28.0K  /testpool

Now let's expand this pool:

freebsd# zpool add testpool raidz /tmp/disk4 /tmp/disk5 /tmp/disk6

So, we recieved the following configuration:

freebsd# zpool status testpool
  pool: testpool
 state: ONLINE
 scrub: none requested
config:

        NAME            STATE     READ WRITE CKSUM
        testpool        ONLINE       0     0     0
          raidz1        ONLINE       0     0     0
            /tmp/disk1  ONLINE       0     0     0
            /tmp/disk2  ONLINE       0     0     0
            /tmp/disk3  ONLINE       0     0     0
          raidz1        ONLINE       0     0     0
            /tmp/disk4  ONLINE       0     0     0
            /tmp/disk5  ONLINE       0     0     0
            /tmp/disk6  ONLINE       0     0     0

errors: No known data errors
freebsd# zpool list testpool
NAME       SIZE   USED  AVAIL    CAP  HEALTH  ALTROOT
testpool   572M   210K   572M     0%  ONLINE  -
freebsd# zfs list testpool
NAME       USED  AVAIL  REFER  MOUNTPOINT
testpool   114K   349M  28.0K  /testpool

Let's compare this to raidz from 6 disks:

freebsd# zpool create testpool raidz /tmp/disk1 /tmp/disk2 /tmp/disk3 /tmp/disk4 /tmp/disk5 /tmp/disk6
freebsd# zpool status testpool
  pool: testpool
 state: ONLINE
 scrub: none requested
config:

        NAME            STATE     READ WRITE CKSUM
        testpool        ONLINE       0     0     0
          raidz1        ONLINE       0     0     0
            /tmp/disk1  ONLINE       0     0     0
            /tmp/disk2  ONLINE       0     0     0
            /tmp/disk3  ONLINE       0     0     0
            /tmp/disk4  ONLINE       0     0     0
            /tmp/disk5  ONLINE       0     0     0
            /tmp/disk6  ONLINE       0     0     0

errors: No known data errors
freebsd# zpool list testpool
NAME       SIZE   USED  AVAIL    CAP  HEALTH  ALTROOT
testpool   572M   147K   572M     0%  ONLINE  -
freebsd# zfs list testpool
NAME       USED  AVAIL  REFER  MOUNTPOINT
testpool   112K   443M  34.9K  /testpool

So, we lost more then 21% of space, but expanded our pool without downtime. Firstly, the lost of more then 20% space suprised me, but 20% was expectable (in second case only 5 disks, in first - 4, so 20% is the difference).
Let's see, where did 1 percent go. 443 MB is useful in second case, it means about 11.4 MB (100 /*disk size*/ - 443/*useful space*/ / 5 /*useful disks*/) metadata per disk. In first case we have 349 MB. It is provided by two pools with 2 useful disks in each. So, wasted space per disk is 12.75 MB (100 - 349/2/2). It seems, in this configuration we have a bit more metadata.

PostgreSQL 9.1 create extension and database upgrade

Yesterday I was eager to try some new 9.1 features, in particular to experiment with exclude constraints (I've read they were improved since 9.0). So, I decided to upgrade my test 9.0 installation. It worth mentioning that it was 8.4 installation, previously upgraded to 9.0.
Everything went as expected, even my C functions were successfully recompiled without any warnings.
So, I decided to try extensions. I had hstore module installed in my db, so I issued "create extension" and received the following error:

db=# CREATE EXTENSION hstore WITH SCHEMA public FROM unpackaged;
ERROR:  function hstore_recv(internal) does not exist

I looked at hstore definition in /usr/local/share/postgresql/extension/hstore--1.0.sql, copied definition of this function, created it, but received error saying that some other function doesn't exist.
So I run full script to create unpackaged extension:

# sed -e 's|MODULE_PATHNAME|$libdir/hstore|' < /usr/local/share/postgresql/extension/hstore--1.0.sql | su pgsql -c 'psql -d db ' 

After that it was possible to create extension:

db=# CREATE EXTENSION hstore WITH SCHEMA public FROM unpackaged; 

It seems that the reason for this behavior is that I didn't upgrade hstore module after upgrading database from 8.4 to 9.0...

Extracting domain name from uri with SQL

I don't know if it is effective, but this ugly SQL (in PostgreSQL dialect) extracts domain name part from uri (i.e. translates protocol://something/page into "something"):

SELECT uri, substring (uri from position('//' in uri)+2 for ( char_length(uri) - char_length(substring (uri from position ('/' in substring ( uri from position('//' in uri)+2 )) ) )))  from proxy_data

It seems, it would look better with regexp:

 
select uri, substring ( substring (uri from '//[[:alnum:]\._\-]+') from  3) from proxy_data

However, in later case we should list all possible symbols in domain name in our regexp.