Tuesday, June 26, 2007

Initial Postgres Setup for Ubuntu

After installing postgresql, here are the first few things I usually do. I'm usually running Ubuntu, but these would be necessary for any postgres install.

  1. Create database users
    $ sudo su - postgres        # become the postgres user
    $ createuser -P lance       # I'm usually a super user
    $ exit                      # exit out of the postgres user
    $ createuser account1 ...   # Create the standard accounts

  2. Setup conf files
    $ sudo vi /etc/postgresql/8.2/main/pg_hba.conf
    $ sudo vi /etc/postgresql/8.2/main/postgresql.conf

  3. Move Data to separate partition (/data)
    $ sudo /etc/init.d/postgresql-8.2 stop
    $ sudo mv /var/lib/postgresql/8.2 /data/postgresql/8.2
    $ sudo ln -fs /data/postgresql/8.2/main /etc/postgresql/8.2/main/pgdata
    $ sudo /etc/init.d/postgresql-8.2 start

  4. Setup admin pack
    $ sudo -u postgres psql < /usr/share/postgresql/8.2/contrib/adminpack.sql

plr for postgres 8.2 on Ubuntu Feisty using Gutsy deb-src

The only reason I have not upgraded to postgresql 8.2 is lack of a plr package.

After yesterday's dissection of the plr install for postgres 8.1, I decided to create a 8.2 install.

I noticed plr 8.2 is already available for Gutsy (which is still in development at the time of this writing).
http://packages.ubuntu.com/gutsy/libs/postgresql-8.2-plr

So I tried the Gutsy .deb (binary), but it would not install b/c of a unmet gcc dependency. So I tried building it from the deb sources, but plr.c would not compile as is. After hacking around I was able to get it to build.

Using Gutsy deb-src to build a Feisty .deb (binary) for postgesql 8.2 plr
$ wget http://mirrors.easynews.com/linux/ubuntu/pool/universe/p/plr/plr_8.2.0.1-1.diff.gz
$ wget http://mirrors.easynews.com/linux/ubuntu/pool/universe/p/plr/plr_8.2.0.1-1.dsc
$ wget http://mirrors.easynews.com/linux/ubuntu/pool/universe/p/plr/plr_8.2.0.1.orig.tar.gz
$ dpkg-source -x plr_8.2.0.1-1.dsc
$ cd plr-8.2.0.1
$ vi +185 plr.h

Remove the ", R_NilValue" from the end of the line. Line should read:
#define R_PARSEVECTOR(a_, b_, c_) R_ParseVector(a_, b_, (ParseStatus *) c_)
$ dpkg-buildpackage -rfakeroot -b
$ cd ..
$ sudo dpkg --install postgresql-8.2-plr_8.2.0.1-1_amd64.deb

Monday, June 25, 2007

Ubuntu: postgresql-8.1-plr for Feisty missing plr.so and plr.sql

I am trying to get plr installed. However after running apt-get install postgresql-8.1-plr I get the dreaded:
ERROR: could not access file "$libdir/plr": No such file or directory
I double check the package list for plr
$ dpkg-query --listfiles postgresql-8.1-plr
/.
/usr
/usr/share
/usr/share/doc
/usr/share/doc/postgresql-8.1-plr
/usr/share/doc/postgresql-8.1-plr/index.html
/usr/share/doc/postgresql-8.1-plr/plr-aggregate-funcs.html
/usr/share/doc/postgresql-8.1-plr/plr-data.html
/usr/share/doc/postgresql-8.1-plr/plr-func-naming.html
/usr/share/doc/postgresql-8.1-plr/plr-funcs.html
/usr/share/doc/postgresql-8.1-plr/plr-global-data.html
/usr/share/doc/postgresql-8.1-plr/plr-install.html
/usr/share/doc/postgresql-8.1-plr/plr-license.html
/usr/share/doc/postgresql-8.1-plr/plr-module-funcs.html
/usr/share/doc/postgresql-8.1-plr/plr-overview.html
/usr/share/doc/postgresql-8.1-plr/plr-pgsql-support-funcs.html
/usr/share/doc/postgresql-8.1-plr/plr-spi-rsupport-funcs.html
/usr/share/doc/postgresql-8.1-plr/plr-trigger-func.html
/usr/share/doc/postgresql-8.1-plr/stylesheet.css
/usr/share/doc/postgresql-8.1-plr/copyright
/usr/share/doc/postgresql-8.1-plr/changelog.Debian.gz
Hmm.. where's the .so???

I check around on the ubuntu packages and confirm that other releases have the the .so file:
http://packages.ubuntu.com/dapper/libs/postgresql-8.1-plr
http://packages.ubuntu.com/edgy/libs/postgresql-8.1-plr

So I file a bug and start looking into a way to fix it.

I search for how to build a package: and come up the official guide.
$ mkdir -p ~src/plr/feisty
$ cd ~src/plr/feisty
$ apt-get source postgresql-8.1-plr
$ sudo apt-get build-dep postgresql-8.1-plr
$ cd plr-0.6.2
$ dpkg-buildpackage -rfakeroot -uc -b
$ cd ..

The resulting postgresql-8.1-plr_0.6.2-4ubuntu1_amd64.deb still is missing the files. $ dpkg --contents postgresql-8.1-plr_0.6.2-4ubuntu1_amd64.deb

After a lot of poking at the problem I notice that the binary files are created in a directory called install-8.1. However in that dir, are directories for 8.2.

After tracking the problem down, I created a diff file:
diff -u debian/rules /home/lance/rules.patched > /home/lance/rules.patch
And then I uploaded to launchpad.

--- debian/rules 2007-06-25 15:37:43.000000000 -0700
+++ /home/lance/rules.patched 2007-06-25 15:37:23.707021486 -0700
@@ -47,7 +47,7 @@
dh_testroot

# 8.1
- $(MAKE) -C build-8.1/plr -f Makefile.pgxs R_HOME=/usr/lib/R install DESTDIR=$(HERE)/install-8.1 PGXS=/usr/lib/postgresql/8.1/lib/pgxs/src/makefiles/pgxs.mk
+ $(MAKE) -C build-8.1/plr -f Makefile.pgxs R_HOME=/usr/lib/R install DESTDIR=$(HERE)/install-8.1 PGXS=/usr/lib/postgresql/8.1/lib/pgxs/src/makefiles/pgxs.mk datadir=/usr/share/postgresql/8.1 docdir=/usr/share/doc/postgresql-doc-8.1 pkglibdir=/usr/lib/postgresql/8.1/lib

# install files
dh_installdocs -a


Download the patch file to your home directory as rules.patch, then do:

$ mkdir /tmp/plr
$ cd /tmp/plr
$ apt-get source postgresql-8.1-plr
$ sudo apt-get build-dep postgresql-8.1-plr
$ cd plr-0.6.2
$ patch -p0 -i ~/rules.patch
$ dpkg-buildpackage -rfakeroot -uc -b
$ cd ..
$ sudo dpkg --install postgresql-8.1-plr_0.6.2-4ubuntu1_amd64.deb

Transfering multiple files with netcat

Today I needed to backup some files from one server to another. The servers sit next to one another on the network. Usually I use scp or rsync to move files around, but today I felt like trying something different.

First I considered using FTP. However, I do not like running FTP servers and didn't feel like configuring the daemon.

Then I thought of netcat. A quick Google search got me this page: http://www.oreillynet.com/pub/h/1058

However I want to transfer multiple files (all in the same directory). So I decided to use tar, and instead of creating a file, I would pipe it's output through netcat.

On the Receiving Computer (Server):
nc -v -w 30 -p 5600 -l | tar -x


And on the Sending Computer (Client):
tar -c * | netcat -v -w 2 10.0.0.2 5600;


The files transfered fast (2 min 20 seconds for 1.6 Gigs). I ran a md5sum on the client and server to confirm, and everything was perfect.

Sunday, June 24, 2007

Guides - To Read

WEB

Guides and Tools

List of Guides and tools I've found on the Net.

(This is like a bookmarks.)

Web Site Administration:


Breaking Stuff:

WEBertainment

First Post

Another blog. After years of poking at blogging software (Drupal, different Nukes, MT, GeekLog, etc), I've decided not to invest anymore time is investigating software. Instead, my limited blogging time, I should invest in content.

And while I've always know content to be king, I've never practiced what I preach. Instead I usually get caught up in the solving the puzzle of installing and running different pieces of software. And while that is a worth while endeavor, I no longer have the extra amount for those pursuits.

I've started using Google's services for some of my mail hosting, and have been pleased. So, it's about time I tried out blogger.com/Google's blogging software.

So here I come Brave new world of blogging. This blog is primarily a tool for myself, but I hope anything I contribute will be useful for others.