ERROR: type “hstore” does not exist

I was getting this error though I issued CREATE EXTENSION hstore;. When I used \dx+ in psql to see installed extensions, I found out that extensions are installed into schemas instead of being installed globally in the database. So the reason I got this error was that the schema in which I installed hstore was not on my search path. If you have a database with more than one schema and you want to use hstore extension, you can either put on search path the schema in which you installed hstore or you can reference hstore using schema in your SQL statements.

SET search_path = primary_schema, schema_with_hstore;

or

CREATE TABLE test (col1 schema_with_hstore.hstore);

Tags:

PostgreSQL Unix Socket vs TCP/IP Performance

I just finish a test that compares PostgreSQL speed using Unix socket and TCP/IP connection to localhost with SSL enabled. The test was performed on import on OpenStreetMap PBF file (around 17GB of binary data). The result is that loading the data via TCP/IP connection with SSL enabled was about 17% slower than loading data via Unix socket.

Unix socket:
Processing: Node(1542670k 261.2k/s) Way(146203k 26.07k/s) Relation(1518310 44.75/s)  parse time: 45446s

TCP/IP:
Processing: Node(1542670k 257.1k/s) Way(146203k 18.02k/s) Relation(1518310 38.92/s)  parse time: 53122s

Tags: ,

Upgrading PostgreSQL Database Schemas

If you are developing applications that work with PostgreSQL or database generally, you often do changes in the development database that you need to transfer to production server one day. Tracking these changes is not that trivial and you cannot use tools like diff on schema dumps. That’s why I created tool that I named Another PostgreSQL Diff Tool, hosted at sourceforge.net (sf.net claims the project was founded 2006-03-23).

How does it work? You generate schema dump of production and development databases, and then let apgdiff to generate for you DDL SQL statements that you can use to upgrade your production schema to the latest development schema. You can find more detail description at project website.

Here is little bit larger picture how the tool fits in my development process. For the projects I code I created Hibernate like library that generates schema in database when application runs for the first time. If I change some objects after the first release, I direct my app to different database that is clean, let app generate new schema, then dump old and new schema, run apgdiff to get the update commands, check them to see everything looks ok, and put the update file to my app’s automated update procedure. Then I run the app on my development database, see if schema upgrade was performed without issues, and then continue with development. Before I release my code on production server, I can repeat this cycle several times. Then, when I put the app on production server, all updates are performed while the app is being deployed.

There is a chance that apgdiff will work even for other database servers (and “servers”). Recently somebody posted info that he was able to get apgdiff output even from SQLite dumps. Maybe it’s time to move the app to more general design so developers using other database servers could benefit from this free app too.

This app can be directly installed in Gentoo, Debian, Ubuntu, and probably in some other distros too, though the packages in those distros do not have to be necessarilly the latest ones.

Tags: ,