36.1. Introduction to PostgreSQL

Like MySQL, PostgreSQL is a free database server that supports multiple databases and tables and allows clients to query them with SQL. It is most useful for programmers writing applications that need to use a database to store information. Popular languages like Perl, C, Java, and PHP all have APIs for accessing a PostgreSQL database.

A PostgreSQL server can host multiple databases, and each database can contain multiple tables. A table, in turn, contains fields, each of which has a type and size. Tables contain records, each of which usually contains information about some object—such as a person, product, or purchase. Fields can be thought of as the columns in a table, and the actual records of data as the rows. Some fields can also contain multiple values, like an array.

SQL (which stands for Structured Query Language) is a language for extracting data from or updating data in a database. Almost all databases use SQL, and its syntax is generally the same across all the different database packages, such as Oracle, PostgreSQL, and MySQL. Its syntax, however, is not covered in this chapter.

PostgreSQL has many features that other free databases (like MySQL) lack, such as transactions, array fields, views, and triggers. It is not quite as powerful as expensive commercial databases like Oracle, but it comes close. Because it can use multiple files to store each table, their sizes are not limited by the maximum file size on your system—instead, a single table can contain up to 16 TB of data.

Packages for PostgreSQL come with many Linux distributions, and they can be compiled and installed on almost all varieties of UNIX. Its operation is the same on all operating systems, and therefore so is the Webmin module for managing it.

PostgreSQL consists of a server process that reads and writes the actual database files and a set of client programs that communicate with the server. The most commonly used is the psql command, which allows a user to execute SQL queries and view the results. None of the clients access the database files directly—that is left entirely to the server.

All of the PostgreSQL database files are stored under a directory such as /var/lib/pgsql or /usr/local/pgsql. There are several text configuration files that affect the operation of the server and clients, as well. The most important is pg_hba.conf, which lists client hosts that are allowed to connect to the server. This is the only file that Webmin edits directly. All other database configuration is done by connecting to the database server, either directly or through the psql command.