Sunday, November 23, 2008

postgresql - initialize

Links: PostgreSQL     pgAdmin     wxWidgets
I was interested in postgreql as an Amarok backend, after reading somewhere that the default sqllite and mysql databases were slower than postgresql once a collection became large. I read a primer at this Ubuntu forum, but it appeared that some of the commands there were outdated, or only applied in the Ubuntu environment. What I wanted to accomplish was a basic single-user implementation, that is to say, a home installation with some sort of GUI method to easily interact with it. That is the focus of this post; basic installation and interaction, not database design.

initialize as a user, NOT root (v. 9.04)

Databases are designed as scalable for distributed settings. Protocols for security and integrity are appropriately incorporated. On a standalone home system however, if I have to log-in to boot my system, log-in again as admin (root) to turn the server on and off, and then log-in again as a separate user to use the database, I simply will never use that database, PostgreSQL or otherwise. Away from work or, said otherwise, "on my own time", I use databases to attempt to save time, not to go insane. The method I implemented below is entirely insecure if used apart from a standalone, localhost environment, so be advised, but it requires no special logins or privileges and operates like any other CLI app one can use.
  • 1) Uninstall any distro versions (requires root) and compile PostgreSQL. It's a standard root "make install". Files went to /usr/local/pgsql/bin in my install.
Step 2 isn't strictly necessary, but updating one's PATH shortens subsequent shell commands. If one doesn't care about that, jump to Step 3 and include the /usr/local/pgsql/bin prefix to one's shell commands. Either way, be sure to remain a user beginning at Step 3.
  • 2) Root-up and add /usr/local/pgsql/bin to one's /etc/profile (Slackware) PATH statement. Do # source /etc/profile after updating one's PATH, and then check it (as user) with $ echo $PATH.
  • 3) Create some directory in one's home directory, eg $ mkdir /home/foo/pgdatabases
  • 4) Initialize databases into the directory created in Step 3: $ initdb --encoding=utf8 --locale=POSIX --pgdata=/home/foo/pgdatabases. It will provide a success message with status information. You can also look in /home/foo/pgdatabases to verify administrative files were created in there.
  • 5) Turn on PostgreSQL service:$ postgres -D /home/foo/pgdatabases -r somelogifyouwant.txt It will enter an interactive mode prompt.
  • 6) Create some databases or use a script. Eg, at the pgsql prompt, to create a database called "contacts", CREATE DATABASE contacts ENCODING 'utf8';.
    Alternatively, leave the postges terminal running, open another bash terminal, and enter commands from there. The same command from above, but in shell format, is: $ createdb -E utf8 -e contacts. This should give a success status message or some error.
  • 7) Similar command options exist for table creation. Or, if one has scripts, it's a similar process as with MySQL - run them from the pgsql terminal or from a separate shell.
  • 8) To shutdown the database, CTRL C in the pgsql terminal. The connection will close with status messages.
  • 9) Bathroom. Brush teeth and soft picks for gums.
  • 10) Depart for girlfriend's house. If you forgot to create a girlfriend, remain online and create and depart for that gym. Database project secondary or tertiary priority for you.

pgAdmin

I'd also install pgAdmin, which makes database administration easier via a GUI. Put it in a user directory also. However, pgAdmin is very finnicky to compile, as you will see; much more difficult to install that PostgreSQL. PgAdmin is a hog in that it requires the infamous wxWidgets for its Unicode support when reading strings from the datatbase. This wouldn't be so bad, but WxWidgets is also a pig. It relies on gtk, cairo, pango, atk, and a slew of other python dependencies, to install. This means wxWidgets itself is such a rabbit hole of dependencies that it can lead to entirely re-installing an OS. Unfortunately, when I attempted to install pgAdmin, configure found I had no wxWidgets already on my system. This meant I would have to install wxWidgets, and that I would probably face a great deal of associated frustration, time loss, and so forth, until it was accomplished. For example, it could be entirely possible that I might need to to back-up and completely reinstall my OS, just to solve this one dependency problem, perhaps 2 weeks of work. So, although installing the database was simple (less than an hour), GUI access to the database was an entirely different story.

wxWidgets (wxGTK 2.8.10)

After looking at the various versions of wxWidgets, and noting that my OS installation was from 2008, and that I already had versions of pango, cairo, and so on from around that period, I chose wxGTK2.8.10 (03/2009). I started with a basic:
$ configure
This compiled and installed nicely. Miracle. Back to pgAdmin: would this older version of wxWidgets be recent enough to support the latest pgAdmin?

pgAdmin round 2

As seen above, pgAdmin initially lacked wxWidgets. Now we had wxWidgets. Trying again...
$ configure: error: Your wxWidgets installation cannot support pgAdmin in the selected configuration. This may be because it was configured without the --enable-unicode option, or the combination of dynamic/static linking and debug/non-debug libraries selected did not match any installed wxWidgets libraries.

return to wxWidgets

Uninstall wxWidgets, make clean and
$ configure --enable-shared --enable-monolithic --with-gtk=2 --with-libpng=builtin --with-zlib=builtin --with-expat=builtin --with-libtiff=builtin --with-regex=builtin --with-libjpeg=builtin --enable-unicode --enable-debug
This compiles and installs. Let's see if that solves the "unicode" problems for pgAdmin. Seeing how this works yet?

pgAdmin round 3

Score: so far, wxWidgets had miraculously installed twice, pgAdmin has failed twice. So, lets try again with unicode...
$ configure: error: Your wxWidgets installation cannot support pgAdmin in the selected configuration. This may be because it was configured without the --enable-unicode option, or the combination of dynamic/static linking and debug/non-debug libraries selected did not match any installed wxWidgets libraries.
Ah-hah! PgAdmin can s*ck my d*ck.

return to wxWidgets

Uninstall wxWidgets, make clean and
$ configure --enable-shared --with-odbc=builtin --enable-unicode
This compiles and installs. Maybe having ODBC capacity will help with database communication.

pgAdmin round 4

Score: so far, wxWidgets had miraculously installed three times, pgAdmin has failed three times. So, lets try again with unicode...
$ configure: error: Your wxWidgets installation cannot support pgAdmin in the selected configuration. This may be because it was configured without the --enable-unicode option, or the combination of dynamic/static linking and debug/non-debug libraries selected did not match any installed wxWidgets libraries.
Of the hundreds of linked static and dynamic libraries, there is no way to know which combination would work. Noting this, we can say this error message from pgAdmin falls deeply into the "worthless" category of error messages.

return to wxWidgets (wxX11-2.8.12)

Hell, let's try this X11 version. Can't hurt, but didn't succesfully complete. Configure created a makefile, but running make crashed with the following error
./include/wx/filedlg.h:101: error: 'HasFlag' was not declared in this scope
make: *** [.pch/wxprec_coredll/wx/wxprec.h.gch] Error 1
Oh-kee....

return to wxWidgets (back to wxGTK 2.8.10)

Let's go back to the wxGTK 2.8.10 version of wxWidgets, since we previously were able to compile and install. After that, let's try a Slackware precompiled pgAdmin build instead of attempting to compile pgAdmin. So first wxGTK 2.8.10....
$ configure --enable-unicode
This configured, compiled, and installed successfully, so now we have wxWidgets with unicode support in place. Lets get a precompiled Slack .tgz build of pgAdmin and see if we can use installpkg to shove it in.

pgAdmin round 5 (pgadmin3-1.6.2.tgz)

So here goes nuttin'...
# installpkg pgadmin3-1.6.2.tgz
Installing package pgadmin3-1.6.2...
PACKAGE DESCRIPTION:
pgadmin3-1.6.2: pgAdmin III - PostgreSQL Tools
pgadmin3-1.6.2:
pgadmin3-1.6.2: pgAdmin III is the most popular Open Source management tool for
pgadmin3-1.6.2: the PostgreSQL Object Relational Database Management System.
pgadmin3-1.6.2:
pgadmin3-1.6.2: http://www.pgadmin.org/
pgadmin3-1.6.2: http://www.postgresql.org/
pgadmin3-1.6.2:
And now we're gonna see...
$ pgadmin3
So for the first time we're at least getting an image. However, the window crashed when I attempted to change anything about the window or to connect to the server. Upon crashing, the terminal revealed glibc memory errors which had to do with pgAdmin itself. To stop these errors, I added the glibc error prefix:
$ G_SLICE=always-malloc pgadmin3 &
It's clunky, but workable, preventing crashes, but still does not connect by itself.

final touches (summary of hacks)

The above opening command prevented crashes but still does not connect. For pgAdmin to connect to a database cluster, the server must be running, say with
$ postmaster -D /home/foo/pgdatabases
which can easily be terminated any time with a simple
$ killall postmaster
I therefore placed the startup postmaster command in my /home/foo/.profile. So here are the pieces of this working GUI PostgreSQL installation:
  • PostgreSQL installation (simple compile): v. 9.0.4
  • wxWidgets (compile with "--enable-unicode"): wxGTK-2.8.10
  • pgAdmin (slackware .tgz w/installpkg): v. 3-1.6.2
Configuration tweaks include...
  • starting the server in one's home .profile so it's running in the background for a CLI (psql) or pgAdmin to immediately connect
  • when starting pgAdmin, add glibC error hacks, such as G_SLICE=always-malloc
With these in place, one can easily install and interact with the database via a GUI. The discussion of building the database structure for specific need is much more complex. One needs to understand the kinds of fields, how they are related, the best ways to avoid duplication of effort, and so forth. One can receive a Bachelor's degree for a proper understanding of these design elements, and they cannot be covered in this simple installation post.

No comments: