Using the MySQL and PostgreSQL Open-Source Databases
| Now that you have a grounding in how SQL is used in all databases that support it (which is to say, almost all of them), you can turn your attention to the common open-source databases typically used under FreeBSD. These are MySQL and PostgreSQL. In recent years, the field of open-source database software packages has narrowed to include fewer, but more useful, solutions. It used to be the case that the administrator had to choose between dozens of different solutions, all of which had some basic functionality but seemed to be missing something crucialand each one was deficient in something different. But recently the development efforts on MySQL and PostgreSQL have turned them both into packages that are every bit as full-featured as the commercial solutions they propose to replace. Even the limitations that separate these two packages from each other have grown fewer and fewer. Today, whether you choose MySQL or PostgreSQL is largely a matter of personal taste and philosophy. MySQL is by far the more widely used and better supported open-source database system, and it's also considerably faster. That's why the examples shown in this book concentrate on MySQL syntax and usage. PostgreSQL, however, supports more advanced features than does MySQL, and it may be a better choice for high-end servers for which transactional data integrity and feature set are of critical importance. But either one will provide excellent support for all of a typical administrator's needs. How Do MySQL and PostgreSQL Differ?MySQL is developed by employees of MySQL AB, a commercial software company based in Sweden, whereas PostgreSQL is a more traditional open-source project. PostgreSQL has contributors from all over the globe, with varying areas of expertise. The two packages differ in many architectural details because of this. The most fundamental differences between the two databases are those of developmental philosophy; most other discrepancies can be traced to this root cause. The primary goal of MySQL is to focus on performance and reliability, instead of implementing all the newest features, which MySQL AB considers to be the source of an unacceptable risk of instability. PostgreSQL's priorities tend to be directed more toward a complete feature set. PostgreSQL development seeks to implement as much flexibility and functionality as possible, with the goal of creating a complete replacement for any top-end commercial product. MySQL tends to be developed more slowly than PostgreSQL for this reason. MySQL's roadmap for future development includes some features currently supported by PostgreSQL, but MySQL developers consider adoption of these features to be secondary in priority to ensuring code stability and performance. An example of this difference in philosophy is that PostgreSQL requires a periodic VACUUM operation in order to clean up the space that has been relinquished through DELETE and UPDATE commands. Because the VACUUM command tends to use non-trivial amounts of resource overhead, a PostgreSQL database may not be the best choice for a system that must have 24/7 availability. MySQL lacks some features found in PostgreSQL that may be of use to administrators with particular goals; but because MySQL's core feature set is more robustly designed, it does not have such a need for cleanup procedures and therefore is potentially more suited to mission-critical 24/7 service. The features PostgreSQL supports but that MySQL does not support include subselects, foreign keys, views, stored procedures, and triggers, among others. Some of these features are really very cool and provide an excellent level of functionality to the administrator. If you are interested in creating a site with which you can experiment and learn what SQL can do, PostgreSQL may be a good choice for you. If, however, you are interested in running a high-performance server where availability and speed are of critical importance, MySQL may be a better choice. Note Both the PostgreSQL and MySQL database projects are striving toward compliance with the ANSI SQL standard, but neither supports it fully. Finally, an operational difference between the two packages is that MySQL provides a complete command-line environment for interacting with the system directly through a terminal. The MySQL environment includes a number of commands that are not part of SQL but have special meaning within that environment, such as SHOW PROCESSLIST and SET PASSWORD. The PostgreSQL command-line interface is more austere; shell commands are given with terse backslash-prepended codes reminiscent of the command mode of the vi editor, and all other commands issued at the command line are interpreted as SQL queries. Note PostgreSQL is free under all circumstances, although commercial support is available for a fee. MySQL, however, has a licensing policy whereby if you run it in a commercial context in which you prefer not to be restricted by the terms of the GPL under which MySQL is licensed, MySQL AB requests that you pay a license fee. See the MySQL website, http://www.mysql.com, for details. Installing MySQLMySQL can be easily installed from the packages or ports, as discussed in Chapter 16, "Installing Additional Software." The port directory you will want, if you choose to build MySQL from the ports, is /usr/ports/databases/mysqlxx-server. Go into the directory and type make and then make install. Note For compatibility, several major versions of MySQL are available in parallel in the ports and packages. As of this writing you can install MySQL 3.2.3, 4.0, 4.1, 5.0, or 5.1, depending on your needs and your bravery. Earlier versions are more stable and well-established, but later versions support more features. Consult the MySQL website to see what the earliest version is that has all the features you need; as with FreeBSD itself, it's a good idea to install a recent version, but not the very most recent one. Caution Before installing MySQL, make sure your /var partition is sufficiently large to support the size database you anticipate running. The /var partition must be large enough for all your data as well as for the runtime internal table structures the database may create. A partition of at least 1GB is recommended for a medium-sized database with moderate complexity, but your needs might dictate a smaller or much larger data store. Refer to Chapter 20, "Adding Hard Disk Storage," for details on partitioning your disks. After the package has been compiled and installed and you have started the MySQL server (/usr/local/etc/rc.d/mysql-server start), you will need to set up the initial security on the root user. By default, root has full privileges in MySQL's access control system as well as a blank password. Needless to say, it's a bad idea to leave this unchanged! The first thing to do after installation completes is to issue the following commands: # mysqladmin -u root password new_passwordReplace new_password with the new root password for super-user access to your MySQL database. Caution When upgrading from an earlier version of MySQL, it is not usually necessary to back up and restore your database filesbut, as the installation script tells you, it's still a wise precaution. To begin this process, dump your databases (as explained later in the "Performing Database System Backups" section of this chapter) and shut down your MySQL server (/usr/local/etc/rc.d/mysql-server.sh stop). Next, perform a make install operation and then restart the server. If there are any problems, you can restore your databases from the dump files. Installing PostgreSQLPostgreSQL can be easily installed from the packages or ports, as discussed in Chapter 16. If you choose to build PostgreSQL from the ports, use the port directory /usr/ports/databases/postgresqlxx-server. Go into the directory and type make, make the choices you want in the full-screen option menus (or choose the defaults), and then perform a make install operation. Note For compatibility, several major versions of PostgreSQL are available in parallel in the ports and packages. As of this writing you can install PostgreSQL 7.0, 7.2, 7.3, 7.4, 8.0, or 8.1, depending on your needs and your bravery. Earlier versions are more stable and well-established, but later versions support more features. Consult the PostgreSQL website to see what the earliest version is that has all the features you need, and that is not exposed to security vulnerabilities; as with FreeBSD itself, it's a good idea to install a recent version, but not the very most recent one. Caution Unlike with MySQL, an upgrade from an earlier version of PostgreSQL requires that you dump your databases and restore them after the upgrade. Refer to "Performing Database System Backups," later in this chapter, for details on how this is done. After the initial installation, a new user (pgsql) will have been created, as will a new startup script (/usr/local/etc/rc.d/010.pgsql.sh). Don't run this script to start the server yet, though; you first need to set up a few things manually. First, run the initdb command; this sets up the database cluster, which is the base directory for all the database files that will be created. The port installation procedure suggests using the default location of /usr/local/pgsql/data for this, and so here is the procedure to follow: # su -l pgsql $ mkdir data $ initdb The files belonging to this database system will be owned by user "pgsql". This user must also own the server process. creating directory /usr/local/pgsql/data... ok creating directory /usr/local/pgsql/data/base... ok creating directory /usr/local/pgsql/data/global... ok creating directory /usr/local/pgsql/data/pg_xlog... ok creating directory /usr/local/pgsql/data/pg_clog... ok creating template1 database in /usr/local/pgsql/data/base/1... ok creating configuration files... ok initializing pg_shadow... ok enabling unlimited row size for system tables... ok creating system views... ok loading pg_description... ok vacuuming database template1... ok copying template1 to template0... ok Success. You can now start the database server using: /usr/local/bin/postmaster -D /usr/local/pgsql/data or /usr/local/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start Tip You may want to change PostgreSQL's database cluster directory to something like /var/db/pgsql, to correspond to MySQL's use of /var/db/mysql; after all, database content is variable and should properly be kept in /var. Another compelling reason you might choose to do this is if you have separate disks for the /usr and /var partitions. Having your database files and your nonvariable web content on separate drive spindles can provide a very substantial performance benefit, especially if they're also on different IDE channels. The PostgreSQL startup script calls for su -l, which changes to the pgsql user and executes the process that starts up the PostgreSQL server. The -l option reads in the .cshrc or .profile shell environment file for the pgsql user, and each of these files sets the PGDATA environment variable to /usr/local/pgsql/data. In order to change the database cluster location to /var/db/pgsql, you will not only have to create that directory and run initdb -D /var/db/pgsql instead of the initdb command in the example shown here, you will also have to edit ~pgsql/.cshrc and ~pgsql/.profile and change the PGDATA lines (which currently specify $HOME/data or ${HOME}/data) to /var/db/pgsql. You can now try to start the server by ignoring the lines at the end of the initdb process and instead running /usr/local/etc/rc.d/010.pgsql.sh start. If it starts successfully, congratulate yourself. However, if you get a message such as IpcSemaphoreCreate: semget(key=5432003, num=17, 03600) failed: No space left on device, along with an explanation that you need to raise your kernel's limits on semaphore sets, you will unfortunately need to recompile your kernel (see Chapter 18, "Kernel Configuration"). Here are the kernel options you will need to add or update: options SYSVSHM options SHMMAXPGS=4096 options SHMSEG=256 options SYSVSEM options SEMMNI=256 options SEMMNS=512 options SEMMNU=256 options SEMMAP=256 |
