Brilliant Corners

Since I’ve read so much about PostgreSQL being the other great open-source database server, I thought I’d try it out on a new Rails application I’m starting up. Right now, MySQL still seems to be the darling of the Rails development folks, so it’s a little more difficult to find information on installing and getting PostgreSQL working with Rails. So, here are some pointers I’ve collected to get PostgreSQL up and running.

Please note: These instructions were used to build PostgreSQL on my MacBook Pro (Core 2 Duo), so some of the below may not apply to anyone else. I am not responsible for any mess you get yourself into.

Getting PostgreSQL

First, of course, you’ll need to download PostgreSQL, which you can do here: http://www.postgresql.org/ftp/source/ (Follow the link with the highest version number, and choose the postgresql-8.X.X.tar.gz file).

Building PostgreSQL

Next, you need to build the source. I have a ‘tmp’ directory just off my personal directory for just this sort of thing.

 billturner:~/tmp bill$ tar zxvf postgresql-8.X.X.tar.gz
 billturner:~/tmp/ bill$ cd postgresql-8.X.X/

If you’ve already run through Dan Benjamin’s wonderful instructions on getting Ruby, Rails, MySQL, etc. running on a Mac, then you’re already set with the necessary Readline libraries. If not, jump over to that tutorial and see how to get the readline libraries installed.

I’ll assume that you have readline installed in /usr/local like Dan suggests. If not, change the file paths below on the configure command.

billturner:~/tmp/postgresql-8.X.X bill$ ./configure \
> --with-includes=/usr/local/include \
> --with-libraries=/usr/local/lib
billturner:~/tmp/postgresql-8.X.X bill$ make
billturner:~/tmp/postgresql-8.X.X bill$ sudo make install

Building the Ruby gem for PostgreSQL

Here’s where I initially ran into problems. The postgres ruby gem just wouldn’t compile or install. After some searching online, I found a solution (this comment) that worked.

First, let’s attempt to install the gem:

billturner:~ bill$ sudo gem install postgres

And if you have a Mac similar to mine, you may get an error like the following:

Building native extensions.  This could take a while...
postgres.c: In function 'pgconn_s_escape_bytea':
postgres.c:222: warning: pointer targets in passing argument 1 of 'PQescapeBytea' differ in signedness

It will actually say “Successfully installed postgres-0.X.X” at the end, but if you get that error, it wasn’t actually installed.

If you do not get this error, then hooray for you and you can skip the following instructions. If you do get that error, you’ll need to do some fixin’.

Fixing the Ruby gem for PostgreSQL

You’ll need to get into the directory where “gem” put the source.

billturner:~ bill$ cd /usr/local/lib/ruby/gems/1.8/gems/postgres-0.X.X

Now, run the configuration again, since the Makefile wasn’t created when running “gem install postgres”.

billturner:/usr/local/lib/ruby/gems/1.8/gems/postgres-0.X.X bill$ sudo ruby extconf.rb \
> --with-pgsql-include-dir=/usr/local/pgsql/include \
> --with-pgsql-lib-dir=/usr/local/pgsql/lib

Then you need to open up the file “postgres.c” in a text editor (most likely with the sudo command):

billturner:/usr/local/lib/ruby/gems/1.8/gems/postgres-0.X.X bill$ sudo mate postgres.c

Jump to line 222, and change it from this:

to = (char *)PQescapeBytea(from, from_len, &to_len);

to this:

to = (char *)PQescapeBytea((unsigned char *)from, from_len, &to_len);

Save that and we can now compile and install.

billturner:/usr/local/lib/ruby/gems/1.8/gems/postgres-0.X.X bill$ sudo make
billturner:/usr/local/lib/ruby/gems/1.8/gems/postgres-0.X.X bill$ sudo make install

Setting up PostgreSQL

If you take a look through this helpful tutorial on the Apple.com website, about a page and a half down, you’ll see a section with a screenshot explaining how to create a user for the PostgreSQL server. It mentions in that article that the user needs to be an “administrative user,” but I haven’t found that to be the case. I created a basic “postgres” user, and that’s it.

Now that you’ve created the system user for the database server (we’ll assume it’s “postgres” from here on out), let’s get the data directory set up for the initial data:

billturner:~ bill$ sudo mkdir /usr/local/pgsql/data
billturner:~ bill$ sudo chown postgres /usr/local/pgsql/data

Now, “su” into the postgres account to set things up the rest of the way with this initializing command:

billturner:~ bill$ su -l postgres
billturner:~ postgres$ /usr/local/pgsql/bin/initdb -E UTF-8 -D /usr/local/pgsql/data

Here, there’s a possibility you may get another error at this step. The first time I ran the initdb command I left off the “-E UTF-8” option and it ran without a problem. Since, I’ve decided to specify UTF-8 encoding. When I added the “-E UTF-8” option, I received an error similar to this:

FATAL: could not create shared memory segment: Cannot allocate memory
DETAIL: Failed system call was shmget(key=1, size=1081344, 03600).
HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 1081344 bytes), reduce PostgreSQL's shared_buffers parameter (currently 50) and/or its max_connections parameter
(currently 10).

If you get this error, you’ll have to make a change to the system that requires a reboot. Thanks to some Google searches you’ll need to edit, the /etc/rc file (you’ll need to use “sudo”). Find a line that looks like this:

sysctl -w kern.sysv.shmmax=4194304 kern.sysv.shmmin=1 kern.sysv.shmmni=32 kern.sysv.shmseg=8 kern.sysv.shmall=1024

and change the value of “kern.sysv.shmmax” to 167772160 and “kern.sysv.shmall” to 65536. The line should now look like this:

sysctl -w kern.sysv.shmmax=167772160 kern.sysv.shmmin=1 kern.sysv.shmmni=32 kern.sysv.shmseg=8 kern.sysv.shmall=65536

Save the file and reboot. Once your Mac comes back, you’ll need to run the “su -l postgres” and the “initdb” commands above this error fix. The initdb command should complete without errors this time.

If you don’t feel comfortable making this change, just omit the “-E UTF-8” from the last command above, and you shouldn’t have any problems.

Starting PostgreSQL

It appears that there are loads of different ways to start the PostgreSQL daemon. Here’s the one I went with:

billturner:~ bill$ su - postgres -c '/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start'

Wallah! PostgreSQL is now running. However, once you shut down or reboot your Mac, it won’t start up on its own. Yet.

Launching PostgreSQL on demand

I’m including a section below on how to have PostgreSQL start up automatically when rebooting, but until I get a little further into developing with this database, I’ll just be able to run it when I want it. So, in order to make starting and stopping the server easier, I added the following two lines to the bottom of my ~/.bash_login file:

alias pgstart="su - postgres -c '/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start'"
alias pgstop="su - postgres -c '/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop'"

Note that in each case, you’ll be prompted for the “postgres” user’s password. You can avoid something like this if you set up PostgreSQL to automatically launch at boot.

Launching PostgreSQL automatically at reboot

Here I’ve ran into so many, many different approaches, I’m not sure which one to highlight, so I’ll just tell you what I’ve done. Since MySQL can now install a StartupItem (that is handled by the launchd service on the Mac), I went to find an appropriate one for PostgreSQL.

As a base, I downloaded the StartupItem installer package from Marc Liyanage’s excellent resource. Here’s a direct link to the package (that may or may not work in the future). From my searching, this seemed to be the simplest, and closest approach to what I needed.

Once you install the StartupItem, it won’t work as installed. It puts the right files in the right place, but the command directives point to the wrong locations. So, with your favorite text editor open the StartupItem command file:

 billturner:~ bill$ sudo vi /Library/StartupItems/PostgreSQL/PostgreSQL

Find the line that starts the server(in the StartService() section), which looks like this:

su - postgres -c '/usr/local/bin/pg_ctl start -D /usr/local/pgsql/data -l /usr/local/pgsql/logfile -o -i'

and change it to this:

su - postgres -c '/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start'

Next, find the line that stops the server (in the StopService() section), which looks like this:

/usr/local/bin/pg_ctl stop -D /usr/local/pgsql/data

and change it to this:

su - postgres -c '/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop'

If you’d like to try another approach, this search should be a decent starting point.

Some PostgreSQL applications

• pgAdminIII - a nice, multi-platform application for PostgreSQL management
• phpPgAdmin - a somewhat similar web application to the popular phpMyAdmin tool for MySQL databases.
• PostgreSQL Tools for MacOS X - several GUI tools (haven’t tried these yet)

Some helpful links

• http://developer.apple.com/internet/opensource/postgres.html
• http://www.oreillynet.com/pub/a/mac/2002/06/07/postgresql.html
• http://blog.invisible.ch/2006/01/10/start-postgres-with-launchd-on-os-x/
• http://www.robbyonrails.com/articles/2006/05/29/install-ruby-rails-and-postgresql-on-osx
• http://www.entropy.ch/software/macosx/postgresql/
• http://www.4ied.net/articles/show/23
• http://www.postgresql.org/docs/8.2/static/index.html

Conclusion

Whew! Okay, that’s a lot to take in, as I know it was a lot to write out. Hopefully these instructions have helped someone, that like me, was stuck at various steps in getting PostgreSQL installed on my Mac.

If you have some pointers or tips for me, I’d be glad to see them.

General Italy travel tips:

• You mentioned it already, but you probably won’t have many problems with the language. Almost all the restaurants and hotels and stores has someone who speaks English. Even if they don’t, it’s easy to point to something on a menu or display case and ask for it. They know what you mean. :)
• Every morning: cappucino and a brioche. A brioche is like a sweetened croissant. Some have fruit in them, some have a flavored glaze, but they’re all wonderful, and you can get them everywhere. Great way to start the day.
• Try the gelato. In gelato shops, or from the guy in the wheelcart. Anywhere. It’s all good.
• Some of the sights require long pants, and will turn you away if you’re wearing shorts. I can’t tell you how many people I saw getting denied at the entrance to St. Peter’s because they had shorts on. To travel all that way only to get turned away at the gate would SUCK. Stick to long pants, though for women, skirts to the knees I think are okay, but I’d double check.
• If you have a guidebook, you may want to steer clear of the restaurants listed in there, or at least don’t plan your meals around places in the books. It was kinda disheartening to go to one of the restaurants in Rome that was suggested by the Rick Steves Italy book (recommended as a nice family place out of the way), only to have other couples and groups walk up with their Rick Steves books in their hands. It seemed like it was less a family owned local restaurant since all the people eating got there because of their guidebooks. That was the first night, and the last time we used the Steves book. Just wander into some place that looks decent or a hole in the wall. Or ask the clerk at your hotel for recommendations. (See bottom of email for a couple of stories!)
• Oh, how I hope you like wine. It’s good, plentiful and usually reasonably cheap. Even the “house” wines are all good.

In Rome

The big sites:

• The Pantheon

  Not a whole lot to see, but just the fact that it’s so old and still standing is a feat in itself.

• Piazza Navona

  This is just a short walk west of the Panteon, and while there isn’t a whole lot to see, it has 3 (I think) pretty ornate fountains, but it’s huge, and filled with artists and sometimes street performers. A nice place to sit and relax.

• St. Peter’s/Sistine Chapel/Vatican Museum

  The first two of these are definite must sees. If you’re religious or not, they’re both beautiful and awe-inspiring. There’s a lot to see, so either plan a huge chunk of the day to these, or when going down the looong, large hallways to get down to the Sistine Chapel, don’t stop and look at everything too closely. It’s all great art and tapestries and armory, but its a long walk down and back. And save a good chunk of time for St. Peter’s. It’s a HUGE church with amazing art & sculpture on every column and in every corner. If you have some spare time, and aren’t afraid of narrow spaces, try climbing to the top of the cupola (I think there is another name for this). If you go all the way up, you get a great view of Rome.

• Trevi Fountain

  A very large and impressive fountain

• The Colosseum

  I think it’s the fact that this is so old, and still relatively in one piece that makes this more impressive. Sadly, when we were there they had this runway-looking platform in the middle of the place that kind of detracted from the whole experience. Here’s my pic of it: http://brilliantcorners.org/gallery/display/7/8/

• The Roman Forum

  This is pretty close to the Colosseum, but we didn’t have time to check it out. It looked pretty neat though.

Other Rome tips:

• Be prepared to walk. A lot. Rome is huge, and a lot of times, it’s probably easier just to walk everywhere.
• We missed a bunch of the main sights mainly due to lack of time, or just being tired from walking. Check a guidebook for more recommended places if you’re looking for something to do.

In Florence

The big sites:

• The Ponte Vecchio

  This is the famous bridge over the river Arno. Mainly it’s just a bunch of silver and gold shops. Neat stuff to look at, some seems pretty cheap, but with the weak dollar, you may not be getting that great of a deal.

• The Duomo

  You can’t miss this one, since it’s basically in the center of town. When we were there it was pretty empty inside, but still neat.

• Accademia

  This is the museum where Michelangelo’s David is located. There are some other interesting pieces in here, but oh boy, you have to go just to see David at least. You may have to wait in a somewhat long line.

• Uffizi

  The other big art museum in Florence. Holds such masterpieces as Botticelli’s famous “The Birth of Venus” and “La Primavera”. This place is HUGE, so expect to walk a lot inside. But, it has many more interesting pieces than the Acaddemia in general.

• Mercato Centrale

  Just a great and huge indoor market, with shops selling everything from meats and cheeses to seafood and trinkets. If you’re hungry, some of the places will even put together a sandwich for you. Yum!

• Other museums

  The Barghello & Medici palace are all supposed to be great, but we didn’t get the chance to see them.

• Boboli Gardens

  Also supposed to be a beautiful place, but again, didn’t have the time.

Other Florence tips

• It’s not nearly as big as Rome, but it’s still fairly big. However, it isn’t too big that you can’t conceivably walk everywhere. The buildings are great, and the main open air palazzo in the center of the city is a great place just to kind of hang out and people watch when you need a break.
• If you cross the Arno (like over the Ponte Vecchio) from the main part of town, turn right (and I think there is stuff on the left too) on the first street you come to (not the street that runs right up against the river) and follow that down a couple of long blocks. There are some neat shops & restaurants along there that you may miss if you just stick to the main side of the river.

Restaurant suggestion:

• Trattoria Angiolino

  Via S. Spirito 36/R This was the only card I could find from a restaurant in Florence. It’s located on the other side of the river from the rest of the main city (our room was on this side of the river). But, it’s pretty small, great atmosphere, and the food, like just about everywhere else, was fantastic!

In Venice

The big sights:

• San Marco square

  The big square you’ll recognize from commercials and movies. Not a lot to see, other than the milling around of other tourists.

• Palazzo Ducale (Doge’s Palace)

  This is situated in the square. It’s a pretty impressive palace with lots of great art and an amazing armoury.

• The city itself

  There are so many small and winding streets everywhere with hundreds of bridges over all the little (and big) canals. Just wandering around was fun here. Plenty to see and do without having to pay a thing.

• Harry’s Bar

  Close to the square, and supposedly one of Ernest Hemingway’s favorite places to go. Looked crowded and a little upscale when we looked in, so we didn’t go in.

Suggested restaurants

• Madonna

  It was closed the night we tried to go here, but we’ve heard from so many people how good it is.

• Al Peoceto Risorto

  Here’s a restaurant that was recommended to us by a local street artist when the Madonna was closed. This was the best meal we ate while in Italy. Rialto (Mercato del Pesce) S. Polo, 249 Tel #: 0415225953 My wife, brother-in-law, and I split the “grillo misto” (we think that’s it)(in addition to getting our own pasta dishes), which was a catch of the day grilled seafood sampler, many different kinds of fish. very delicious.

Other Venice tips

• On a lot of the major streets, there are arrow signs near the top of many buildings, that point to some of the larger landmarks (San Marco, train station, etc). Keep an eye on these if you get lost, or you’re trying to get your bearing again. There are so many small and narrow streets and paths in Venice, it’s real easy to get lost, or just turned around.
• Taking a gondola can be real expensive, especially if it’s just for 2 people. We just avoided them when there and either took the large canal boats or just walked. Venice isn’t that big, so walking everywhere isn’t too bad. It looks like your hotel was in a pretty central location too, which would help.
• In the square, avoid the restaurants that line the sides, as you’ll pay WAY too much for a meal or even a drink. We learned this the hard way, but still, it’s nice to be able to sit and watch all that goes on in the square (like watching morons pay a person at a booth 1euro for a bag of seeds, which they sprinkle on their family to have them swarmed with pigeons for that special kodak moment)

The restaurant stories:

• After the first night in Rome and being disappointed with the restaurant, we asked the clerk at our hotel for a good local place to have dinner. He wrote down the name of the place, and gave us easy directions (it was close by). We thanked him and headed out. When we found the place and went in, the owner of the place came over to our table and asked if we were the ones that [can’t remember his name] sent over from Hotel Petit. He was a great, jolly guy with very good english, and really treated us super well all night. We ordered our dinners, but he also threw in a little sampling of the night’s special for free, plus I think he bought one of the carafes of wine we had. He told us great stories and suggested other places to see in the city.
• We had trouble finding the one restaurant we were told to try in Venice, so we stopped and talked to a local artist who was situated at the foot of the Rialto bridge. We asked him where the Madonna restaurant (see above) was. He told us where it was but then he also said that they were closed that night. So, we asked him for some other place to go. He suggested two. The first we went to looked way too fancy and modern. So we went to find the other. This was a little more hole-in-the-wall, but it ended up being the best meal we ate while in Italy. Wonderful seafood and pasta dishes.

But above anything and everything else you’ll hear, just have a good time. Italy is a beautiful country. I was only there for 9 days, but already I know I want to retire there, if not move over there (somehow) to live and work before then. You’re gonna love it. :)