Date:Posted By:Category:Cassandra, CouchDB, Gazetteer, mariadb, mysql, OpenStreetMaps, PostgreSQL

Hellow Postgres

One of the things MySQL forced me into is looking for serious alternatives. Finding MariaDB was an amazing relief. But it’s still not all cake and cookies yet. I experiment a lot with OpenStreetMaps lately, a great project everyone of all makes should look into seeing what it can do for you and vice versa. This is where we step into the PostgreSQL realm. Almost all about OSM is backed by PostgreSQL
I’ve built Tile and Gazetteer services to provide maps an geocoding features. This put me in the spot I had to get to know PostgreSQL.
Like a lot of early hour MySQL fans I kept far to the left side of PostgreSQL for as long as I can remember. I didn’t need it, it was for strange, weird techies. Even more than me. As if admitting I L.O.V.E working with vi(m) is not enough.

But now we are probably all on an awesome inspiring path, ready to choose our next idol. With the rising of new sort of mainstream technologies(=what lots of people can use instead of a few) that perform, it’s exiting to see that PostgreSQL is so stable and useful. Not only do we have stuff like Cassandra, CouchDB and Nginx ready for production for some time. There’s a place for old school solid stuff.

Anyway, so after a few weeks I’m loving the damn thing. I need to get used to it a bit more. Specifically those shorthand commands. But it’s so fast if you do everything right. I’m actually able to tune it now running fast with not-so-smart options set and I actually NOTICED the results of it for a change.

I had a power failure on one of the machine I was building a giant DB on and re-indexing gazetteer fails now, so trying to repair postgresql DB is just an awesome way into getting to know the inner workings.
I’m very pleased with it, I will sure use it more. The PostGIS stuff is kick-ass. Even the crash was predicted by the manual and the fact that I have some nasty corruption preventing me from completing the gazetteer indexing. I’m still trying to make the best from it, it’s not THAT critical so I want to cease the opportunity to look at some of the nastiest error messages in postgreSQL 😉

So, here’s a few things I learned along the way

– Dig into VACUUM FULL VERBOSE ANALYSE. It’s a cornerstone of fixing/keeping the DB.
– REINDEX TABLE. Same here, it’s a lifesaver.
– Depending on YOUR version (do the google thing), set this: zero_damaged_pages = true , if it isn’t set yet and you want to fix stuff you need this in your postgresql.conf

Doing it for all tables

I really love this oneliner too:

FOR tbl IN `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" gazetteer` ; do psql -c "REINDEX TABLE $tbl" -d gazetteer; done

Or

FOR tbl IN `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" gazetteer` ; do psql -c "alter table $tbl owner to "www-DATA"" gazetteer; done

That last one comes from the Gazetteer install wiki page for ubuntu.