Generating a dependency graph for a PostgreSQL database
This post was mostly inspired by this one, which shows how to generate a dependency graph for a MySQL database. Here we do something similar for PostgreSQL.
This script will generate the required digraph data to pipe into graphviz dot which will generate a visual representation of dependencies in a database schema, based on foreign key constraints.
The script:
from optparse import OptionParser, OptionGroup
import psycopg2
import sys
def writedeps(cursor, tbl):
sql = """SELECT
tc.constraint_name, tc.table_name, kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu ON
tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu ON
ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name = '%s'"""
cursor.execute(sql % tbl)
for row in cursor.fetchall():
constraint, table, column, foreign_table, foreign_column = row
print '"%s" -> "%s" [label="%s"];' % (tbl, foreign_table, constraint)
def get_tables(cursor):
cursor.execute("SELECT tablename FROM pg_tables WHERE schemaname='public'")
for row in cursor.fetchall():
yield row[0]
def main():
parser = OptionParser()
group = OptionGroup(parser, "Database Options")
group.add_option("--dbname", action="store", dest="dbname",
help="The database name.")
group.add_option("--dbhost", action="store", dest="dbhost",
default="localhost", help="The database host.")
group.add_option("--dbuser", action="store", dest="dbuser",
help="The database username.")
group.add_option("--dbpass", action="store", dest="dbpass",
help="The database password.")
parser.add_option_group(group)
(options, args) = parser.parse_args()
if not options.dbname:
print "Please supply a database name, see --help for more info."
sys.exit(1)
try:
conn = psycopg2.connect("dbname='%s' user='%s' host='%s' password='%s'"
% (options.dbname, options.dbuser, options.dbhost, options.dbpass))
except psycopg2.OperationalError, e:
print "Failed to connect to database,",
print "perhaps you need to supply auth details:\n %s" % str(e)
print "Use --help for more info."
sys.exit(1)
cursor = conn.cursor()
print "Digraph F {\n"
print 'ranksep=1.0; size="18.5, 15.5"; rankdir=LR;'
for i in get_tables(cursor):
writedeps(cursor, i)
print "}"
sys.exit(0)
if __name__ == "__main__":
main()
You could run it as follows:
python postgres-deps.py --dbname some_database | dot -Tpng > deps.png
Note: for other options use:
python postgres-deps.py --help
That should spit out one of these:

Getting Git manpages on OS X
Posted by Greg in General, Unix / Linux on April 15th, 2010
For some reason the OS X install of Git doesn’t include the manpages. Here is how I installed them.
First off, find the appropriate manpath.
greg@codemine:~ %> cat /etc/manpaths /usr/share/man /usr/local/share/man
/usr/local/share/man looks good…
greg@codemine:~ %> VER=`git --version | awk '{print $3}'`
greg@codemine:~ %> curl -O http://www.kernel.org/pub/software/scm/git/git-manpages-$VER.tar.bz2
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 242k 100 242k 0 0 92051 0 0:00:02 0:00:02 --:--:-- 99k
greg@codemine:~ %> sudo tar xjv -C /usr/local/share/man -f git-manpages-$VER.tar.bz2
Password:
x ./
x ./man1/
x ./man1/git-add.1
[snip]
x ./man7/gitworkflows.7
greg@codemine:~ %> rm git-manpages-$VER.tar.bz2
greg@codemine:~ %>
“man git-add” should now work fine.
Extending Python with modules written in C
Using C (or C++) to create Python modules is really quite simple, providing you know a little C of course. I recently had to do some work around getting a bunch of legacy C code talking to a newer system and thought I’d post a nice simple example of how the Python extensions work.
This code gives you a single method “do()” that will print the output of a command, passed to it as a string, to stdout and return the exit code as a python int.
Dump this into “mycmd.c”:
#include <Python.h> static PyObject * mycmd_do(PyObject *self, PyObject *args) { const char *command; int sts; if (!PyArg_ParseTuple(args, "s", &command)) return NULL; sts = system(command); return Py_BuildValue("i", sts); } static PyMethodDef MyCmdMethods[] = { {"do", mycmd_do, METH_VARARGS, "Print output of 'cmd', return exit code."}, {NULL, NULL, 0, NULL} /* Sentinel */ }; PyMODINIT_FUNC initmycmd(void) { (void) Py_InitModule("mycmd", MyCmdMethods); } int main(int argc, char *argv[]) { Py_SetProgramName(argv[0]); Py_Initialize(); initmycmd(); return 0; }
Great, so we have some example code now, here is how you build an importable module with it:
greg@codemine:~/code/mycmd %> cc -dynamic -g -Wall -I/System/Library/Frameworks/Python.framework/Versions/2.6/include/python2.6 -c mycmd.c -o mycmd.o greg@codemine:~/code/mycmd %> cc -bundle -undefined dynamic_lookup mycmd.o -o mycmd.so
Note: Don’t forget to replace the include path above with the correct path to Python.h on your machine.
This should give you a mycmd.so on unix / linux and a mycmd.dll on windows. In the same directory, run a python interpreter and test it out.
greg@codemine:~/code/mycmd %> python
Python 2.6.3 (r263:75183, Nov 4 2009, 12:53:19)
[GCC 4.2.1 (Apple Inc. build 5646)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import mycmd
>>> mycmd.do('/usr/bin/false')
256
>>> mycmd.do('/usr/bin/true')
0
>>> mycmd.do('uname -a')
Darwin codemine.codelounge.int 10.2.0 Darwin Kernel Version 10.2.0: Tue Nov 3 10:37:10 PST 2009; root:xnu-1486.2.11~1/RELEASE_I386 i386
0
>>>
There is much more you can do around this, thankfully the documentation is remarkably good.
There is not much to the actual code. First, we define the C function that will handle our command “mycmd_do”. Then we set up an array of methods we want to expose to python “MyCmdMethods”. We then setup an initializer “initmycmd” to expose the module which is executed from “main” after the python initializer “Py_Initialize”.
Using an alternative mirror for FreeBSD port retrieval
Posted by Greg in Unix / Linux on October 12th, 2009
This is something I always search for which doesn’t seem to be very clear from the initial results. The mirrors included below are South Africa specific, so if you are not in South Africa then replace the hostname with something more appropriate for your location.
Add these to /etc/make.conf:
MASTER_SITE_BACKUP?= \
ftp://ftp.za.freebsd.org/pub/FreeBSD/ports/distfiles/${DIST_SUBDIR}/
MASTER_SITE_OVERRIDE?= ${MASTER_SITE_BACKUP}
Mirrors:
| Network | Hostname | Alias | IPv4 | IPv6 |
| Tenet | ftp.za.freebsd.org | freebsd.mirror.ac.za | 155.232.191.209 | - |
| CSIR | ftp2.za.freebsd.org | - | 146.64.8.4 | 2001:4200:7000:1::4 |
| MTN Business | ftp3.za.freebsd.org | - | 196.30.227.198 | - |
| Internet Solutions | ftp4.za.freebsd.org | ftp.is.co.za | 196.4.160.12 | - |
See this post for a far more detailed set of MASTER_SITE make variables. This references a lot more of the locally mirrored content.
Keyboard shortcuts in Google Wave
I was messing around a little with figuring out some of the keyboard shortcuts in Wave. Here is what I have so far:
While editing:
Shift + Enter: Shortcut for “Done”
Control + Enter: Add comment
Control + b: Bold
Control + i: Italics
Control + u: Underline
Control + g: Poor mans CSS editor (JavaScript text input)
Control + a: Beginning of line
Control + e: End of line
Control + k: Hyperlink
Control + 1/2/3/4: Convert to h1 / h2 / h3 / h4
Control + 5: Bullets
Control + 6: Drops all formatting
While viewing:
Control + r: Reply
Enter: Same as Control + r
Control + e: Edit
Shift + Enter: Start replying at the end
Control + Space: Mark all as read
I’ll update this post if I find any more…
Simple HTTP POST in Java
Today I was helping a friend debug a web service they had implemented. Their side was working correctly but the developer who was trying to interface with it seemed to be running into many problems. Since they were integrating an application written in Java, I whipped up a simple test for them. All we really needed to do was to send a few variables using HTTP POST to this resource and make sure it returned exactly what we were expecting.
This uses standard libraries only, and doesn’t require anything third party. It does nothing fancy at all, just simply posts data to a URL. Hopefully you find this useful at some point.
src/postit/Main.java
/*
* Java POST Example
*/
package postit;
import java.io.IOException;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.ProtocolException;
import java.net.URL;
import java.net.URLEncoder;
import java.io.DataOutputStream;
import java.io.DataInputStream;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
*
* @author Greg
*/
public class Main {
/**
* Pretend you're a script...
*/
public static void main(String[] args) {
final String server = "somewhere.ontheinter.net";
URL url = null;
try {
url = new URL("http://" + server + "/we-expect-post/data");
} catch (MalformedURLException ex) {
Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
}
HttpURLConnection urlConn = null;
try {
// URL connection channel.
urlConn = (HttpURLConnection) url.openConnection();
} catch (IOException ex) {
Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
}
// Let the run-time system (RTS) know that we want input.
urlConn.setDoInput (true);
// Let the RTS know that we want to do output.
urlConn.setDoOutput (true);
// No caching, we want the real thing.
urlConn.setUseCaches (false);
try {
urlConn.setRequestMethod("POST");
} catch (ProtocolException ex) {
Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
}
try {
urlConn.connect();
} catch (IOException ex) {
Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
}
DataOutputStream output = null;
DataInputStream input = null;
try {
output = new DataOutputStream(urlConn.getOutputStream());
} catch (IOException ex) {
Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
}
// Specify the content type if needed.
//urlConn.setRequestProperty("Content-Type",
// "application/x-www-form-urlencoded");
// Construct the POST data.
String content =
"name=" + URLEncoder.encode("Greg") +
"&email=" + URLEncoder.encode("greg at code dot geek dot sh");
// Send the request data.
try {
output.writeBytes(content);
output.flush();
output.close();
} catch (IOException ex) {
Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
}
// Get response data.
String str = null;
try {
input = new DataInputStream (urlConn.getInputStream());
while (null != ((str = input.readLine()))) {
System.out.println(str);
}
input.close ();
} catch (IOException ex) {
Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
Using a ZFS filesystem with Time Machine
Posted by Greg in Unix / Linux on October 4th, 2009
This simple how-to explains how to get your Time Machine backups working with a ZFS filesystem. This allows you to use the features of ZFS filesystems for your Time Machine backups.
Please note this is for Mac OS X – Snow Leopard.
1) Enable unsupported network volumes on your Mac by opening a Terminal and pasting this:
greg@macbook:~ %> defaults write com.apple.systempreferences TMShowUnsupportedNetworkVolumes 1
2) Create a new ZFS filesystem and enable CIFS access to it:
greg@opensolaris:~ %> zfs create tank/userbackups greg@opensolaris:~ %> zfs set sharesmb=on tank/userbackups greg@opensolaris:~ %> zfs set sharesmb=name=userbackups tank/userbackups greg@opensolaris:~ %> zfs set aclmode=passthrough tank/userbackups greg@opensolaris:~ %> zfs set aclinherit=passthrough tank/userbackups
You will probably want to setup the correct permissions on your new share, more details in [this post].
3) Make sure you can mount this share and write to it from your Mac.
4) Create the correct disk image:
greg@macbook:~ %> /bin/bash
greg@macbook:~ %> cd /Volumes/userbackups
greg@macbook:~ %> SYSNAME=`scutil --get ComputerName`
greg@macbook:~ %> hdiutil create -size 600G -fs HFS+J \
> -volname 'Time Machine Backups' -type SPARSEBUNDLE "${SYSNAME}.sparsebundle"
greg@macbook:~ %> UUID=`system_profiler | grep 'Hardware UUID' | awk '{print $3}'`
greg@macbook:~ %> cat << EOF > "${SYSNAME}.sparsebundle"/com.apple.TimeMachine.MachineID.plist
> <?xml version="1.0" encoding="UTF-8"?>
> <!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
> <plist version="1.0">
> <dict>
> <key>com.apple.backupd.HostUUID</key>
> <string>$UUID</string>
> </dict>
> </plist>
> EOF
greg@macbook:~ %>
5) and finally, open up Time Machine. You should now see your network share as an option. Choose it, configure any excludes you want and kick off your first backup!
I’ll post a little later on restoring these backups using one of these methods:
- Restore from Time Machine by using the boot disk
- or by doing a standard install then using the Migration Assistant.
Good luck!
New addition to the family
So after much anticipation, many tireless weeks of spec checking and a whole bunch of money I was really glad to be on the receiving end of a brand spanking new 17″ MacBook Pro!
Although it has been less than 72 hours this is (so far) truly the most amazing piece of computer equipment I have ever owned, honestly worth every single cent! The initial setup was completely flawless and only took a few minutes. The graphics, resolution and performance are exceptional, in fact I haven’t even had to switch over from the standard “on-board” video card to the higher end controller yet, despite putting the GPU through a thorough beating, testing out the Quake 4 demo :P
The specs in this machine are as follows:
- Intel(R) Core(TM)2 Duo CPU T9600 @ 2.80GHz
- 4GB RAM
- 500GB 5400rpm hard disk
- 17″ – 1920 x 1200 native resolution
I plan to upgrade the memory to 8GB and the drive to the 7200rpm model sometime in the future.
Here are a few more (slightly better quality) pics:
From the left, OpenBSD 4.5 gateway, proxy and firewall, ADSL router and OpenWRT wireless router on top of the 3com gigabit switch. The main workhorse (storage, virtual machines and build zones) is underneath that with a quad core processor, 4GB of RAM and just over 2TB of disk space in 2 ZFS pools (3 x 500GB and 3 x 640GB) running OpenSolaris and finally 2 x 2kVA UPSs.
Quick Solaris zone stats
Posted by Greg in Unix / Linux on August 13th, 2009
Add this:
alias zonestat="prstat -vZ 1 1 | grep -A50 '^ZONEID'"
to your ~/.profile and you should see something like this when running it:
root@tank:~# zonestat
ZONEID NPROC SWAP RSS MEMORY TIME CPU ZONE
0 58 1198M 1206M 30% 16:15:40 1.7% global
6 25 172M 175M 4.4% 0:03:14 0.0% cl-build
2 27 48M 31M 0.8% 0:00:47 0.0% mirror
Total: 110 processes, 534 lwps, load averages: 0.09, 0.08, 0.07
The basics of ZFS ACLs
This post was mostly inspired by reading this post in trying to get my head around the ZFS ACL and permission system.
Basically I have a pool set out as follows:
tank tank/media tank/zones
tank/media is served via CIFS and NFS to multiple clients on my network, each with their own unix account on the OpenSolaris server. tank/zones is used for extra zones running on the host.
Everything was working great until I found that files and directories created by clients ended up looking like this:
---------- 1 greg staff 734310400 2009-07-18 19:10 foo.txt d--------- 2 greg staff 19 2008-12-06 14:10 Bar
This sure didn’t go down well when other users needed to access those files or directories.
So in following the above mentioned post I did this:
# zfs set aclinherit=passthrough tank/media # zfs set aclmode=passthrough tank/media # /bin/chmod 0774 /tank/media # /bin/chmod -R A- /tank/media # /bin/chmod -R A=owner@:full_set:fd:allow /tank/media # /bin/chmod -R A+group@:full_set:fd:allow /tank/media # /bin/chmod -R A+everyone@:read_set:fd:allow /tank/media
A better description of what the flags / syntax mean can be found here and here
A simple breakdown:
- First off, we tell ZFS that all files or directories must inherit all acls / permissions from their parent.
- We use /bin/chmod as the chmod in the default path is the GNU chmod which does not understand ZFS acls.
- The second chmod “A-” will remove all acls currently set on the object.
- We then set the owners permission to the “full_set”, thus giving the owner all possible permissions.
- We do the same for the group.
- Finally, we give everyone else read access.






