Archive
Interesting Data Links, Week Ending 2015-06-27
The most interesting data and database-related articles to come my way this week.
Oracle’s biggest database foe: Could it be Postgres? via Postgres Weekly.
Oracle has such a huge head start, I doubt that PostgreSQL is really a threat. Still, I’m glad to see PostgreSQL so popular among start-ups.
Don’t Let Your Data Out of the Database
Another excellent post by Pat Shaughnessy.
Introducing HypoPG, hypothetical indexes for PostgreSQL
Being able to test the usefulness of an index without having to create it on disk is a fantastic tool for developers. Over-indexing can be as bad as not indexing at all.
Compiling and Installing PostgreSQL 9.1 from Source on Fedora 15 (64-bit)
Prerequisites
Last week I discussed various reasons why you would want to compile your open-source database from the project’s source code. I wanted to give an example here of the process for compiling a particular database, PostgreSQL. Version 9.1 of PostgreSQL was released on 2011-09-11, so the example below works with that code base.
I’m assuming in the following instructions that you’re starting with a sparkling, new Fedora installation. In other words, you haven’t installed any of the dependencies (e.g., gcc, make, bison) that you’ll need for building the PostgreSQL source code. I used Fedora 15, 64-bit.
$ sudo yum install git
$ sudo yum-builddep postgresql
$ sudo yum install gcc
Check out the Source Code using Git
Grabbing the code via git is a very simple one-line command.
$ git clone git://git.postgresql.org/git/postgresql.git
Configure the Build
$ cd postgresql $ ./configure --with-libxml --with-libxslt
Make and Install
$ gmake
$ sudo gmake install
Post-Installation and Database Configuration
You’ll want to run PostgreSQL as a specific, non-root user, postgres. So, create this user.
$ sudo adduser postgres $ sudo passwd postgres
Now, change user to the newborn postgres user to run the database initialization.
$ su - postgres
First, set the environment variables for locating needed libraries and executables.
$ LD_LIBRARY_PATH=/usr/local/pgsql/lib; export LD_LIBRARY_PATH $ PATH=$PATH:/usr/local/pgsql/bin; export PATH
Now, create a directory to contain the server’s data files.
$ mkdir <directory_for_database_data_files>
Run the initdb command to initialize the PostgreSQL server.
$ initdb -D <directory_for_database_data_files>
I recommend creating a specific directory for the server log files.
$ mkdir <directory_for_server_log_files>
Starting the Server
Start the server as the postgres user, indicating the location of data files and the location of the server log files.
$ postgres -D <directory_for_data files> ><directory_for_server_log_files>/server.log 2>&1 &
There shouldn’t be any errors that prevent the server from starting, but if you inspect the log file you should see the following messages.
LOG: database system is ready to accept connections LOG: autovacuum launcher started
Your server should be up and running, ready to accept your client connections!
Why Compile MySQL?
I’ve got an upcoming post that discusses the steps for compiling MySQL from source. Before I get to that topic, though, I thought a preliminary post on the reasons for doing so would be helpful.
Almost every open-source database project provides an installer for various platforms. Why then would you bother to compile MySQL (or any other database)? There are number of good reasons.
- You have an opportunity to read the code
If you have the time and inclination, grabbing and compiling the source code is the perfect excuse to spend some time reading the code, particularly the parts of the code that interest you. How exactly does PostgreSQL implement k-nearest-neighbor indexing (new in version 9.1)?
Sometimes the source code is installed along with binary versions when you use an installer, but this is often not the case. If you download the source to compile it, you know you’ll have it available to read at your leisure.
- Some options are only available by compiling the source
You might want to try out an atypical storage engine, but you can only do that by mixing in support for that engine when you compile the code itself. For example, you want to use the FEDERATED engine in MySQL, but that doesn’t come as part of the installer-produced installation. You really have no choice but to compile MySQL and include support for the FEDERATED engine as part of the configure-and-compile process.
- You can more easily set some defaults
You might not care for the default database character set. Sometimes DBAs or developers forget to set a character set when creating a table. For those times, you may want to enforce a standard that all database tables use the UTF-8 character set (unless specifically overridden). When configuring MySQL for compilation use the -DDEFAULT_CHARSET=utf8 command line option.
- You want to get an up-to-date version, including the latest patches
Want the latest and greatest version of the server, including the most recently committed changes for bugs and security holes? No problem! Just download and compile the most current source, and you will automatically get the latest bug fixes and security patches.
In future posts, I’ll discuss the steps for compiling MySQL and PostgreSQL from source code.
