Pervasive Software's Postgres 8

Once the install wizard did its magic, a helpful configuration tool let me tweak settings for resource usage, query tuning, error reporting, logging and server statistics. This is one feature I wish MySQL had--where MySQL requires manual modification of configuration files, Postgres provides graphical tools to simplify the task.

I created a test user with the "pgAdmin" tool, a GUI for managing users, groups and databases that is much akin to MySQL's Command Center (mysqlcc). With pgAdmin, I was able to do almost everything the command-line tool "psql" would have allowed. Notable exceptions were starting and stopping the database, for which I ran '/opt/ pervasivepostgres/tools/pgAdminIII/bin/pgadmin3'.Next, I set up my test server. I had to specify a description in the "Add Server" window before I could click "OK," and pgAdmin crashed a few times while I was creating tablespaces, but all the other tasks I tried worked properly. Once connected, I created a test user and database.

New in version 8 also is improved Alter Table support. This lets you make changes to a table--for example, the data type of a column--so you don't have to re-create the table from scratch.

To test Alter Table, I first created a table with three columns, using the following command:

CREATE TABLE alter_test (idnum TEXT, data TEXT, stamp TIMESTAMP)

Oops, idnum should be a number. Instead of dropping the table and re-creating it, I simply used the Alter Table command ALTER TABLE alter_test ALTER COLUMN idnum TYPE INTEGER to fix it.Practical uses for this command are to increase the length of a field of the same type when new data is too large for the original column size, or if you want to use encryption tools. Generally, the column size will need to increase and the type change to support encrypted data. Until this release, these changes would have been much more difficult.

Another excellent new feature is improved support for server-side languages, including Perl, pgSQL, Tcl, Python and even C. The point of server-side languages is to move data processing from the application to the database server so you can build a database application more quickly, minimize errors in complex functions and, most important, simplify application development. Server-side language support is key to making Postgres an enterprise-class contender: MySQL doesn't have this kind of functionality, but other enterprise-class databases, such as Oracle and SQL Server, do.

Each database can use any or all of the available languages, as long as the administrator allows them. I let Perl code run on my test database by entering createlang plperl test_db. Next, I created a table called perl_test with two columns, integers a and b. I then created the function perl_max as follows:

CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$

return $_[0] if($_[0] > $_[1])return $_[1]

$$ LANGUAGE plperl

Once I had the function defined, I could use it in a query as though it were built-in. Here are my query and the results:

SELECT perl_max(a,b),a,b FROM perl_test

perl_max|a|b------------------+-----+-----

2|1|2

5|5|3

7|7|3

90|50|90104|104|102

Obviously, perl_max is a very simple function, but complex functions can also be written.

Another step toward the enterprise for Postgres is tablespace support. Tablespaces help administrators manage disk space for databases. Before tablespaces, we had to symlink directories to separate database locations, incurring a disk-resource hit.

My test server has a RAID setup, which I wanted to use for databases. I formatted the RAID and mounted it as /database. To tell Postgres about this space, I simply said CREATE TABLESPACE main_db OWNER test_user LOCATION '/database'. To use this new space, I could add an option to the Create Table command or set the default tablespace:

CREATE TABLE foo (i integer) TABLESPACE main_db;Setting the default tablespace:

SET default_tablespace = main_db;

Point in Time

The final feature I found notable is point-in-time recovery. The database is continuously backed up so that in the event of a system failure, minimal data will be lost and the system can be rolled back to a known good point in time.To test the feature, I set up a second computer to continuously insert data with an increasing ID number. After running the computer for about 30 seconds, I bravely simulated a catastrophic failure by pulling the plug on the Postgres server while the system was writing data. The last ID number to be inserted successfully was 10120. After restarting the server, I found that the last record successfully written to the database was ... 10120. No recovery necessary--Postgres managed to complete the transaction just fine.

Despite repeated plug pulling, I was unable to fail Postgres on this test. Point-in-time recovery is a great feature, but if Postgres is this reliable, it may not be needed.

Jeremy Baumgartner is a Unix systems administrator for the College of Engineering at the University of Wisconsin, Madison. Write to him at [email protected].