8.2. PostgreSQL

The PostgreSQL database (DB) language uses a client server model [Posa]. A PostgreSQL session consists of a series of programs that cooperate:

Generally, the client and the server are on different hosts and communicate through a TCP/IP connection. The server can accept multiple requests from different clients, activating a process that will attend to the user's request exclusively and transparently for each new connection. There is a set of tasks that can be performed by the user or by the administrator, as appropriate, and that we describe as follows.

8.2.1. How should we create a DB?

The first action for checking whether the DB server can be accessed is to create a database. The PostgreSQL server can handle many DBs and it is recommended to use a different one for each project. To create a database, we use the createdb command from the operating system's command line. This command will generate a CREATE DATABASE message if everything is correct. It is important to take into account that for this action we will need to have a user enabled to create a database. In the section on installation (1.4) we will see that there is a user, the one that installs the database, who will have permissions for creating databases and creating new users who in turn can create databases. Generally (and in Debian) the default user is postgres. Therefore, before running createdb, we need to run postgres (if we are the root user, we do not need a password, but any other user will need the postgres password) and then we will be able to run createdb. To create a DB named nteumdb:

createdb nteumdb

If we cannot find the command, it may be that the path is not properly configured or that the DB is not properly installed. We can try with the full path (/usr/local/pgsql/bin/createdb nteumdb), which will depend on our specific installation, or check references for problem-solving. Other messages would be could not connect to server when the server has not initiated or CREATE DATABASE: permission denied when we do not have authorisation to create the DB. To eliminate the DB, we can use dropdb nteumdb.

8.2.2. How can we access a DB?

After we have created the DB, we can access it in various ways:

Tip

In order to access the DB, the database server must be running. When we install Postgresql the appropriate links are created so that the server initiates when the computer boots. For more details, consult the section on installation (1.4).

For reasons of simplicity, we will use psql to access the DB, meaning that we will have to enter psql nteumdb: some messages will appear with the version and information and a similar prompt to nteumdb =>. We can run some of the following SQL commands:

SELECT version();

or also

SELECT current date;

psql also has commands that are not SQL and that start with '\', for example \h (list of all available commands) or \q to finish.

Example 8-1. Example

Access the DB nteumdb: psql nteumdb [enter] nteumdb =>

8.2.3. SQL language

The purpose of this section is not to provide a tutorial on SQL, but we will analyse some examples to see this language's capabilities. They are examples that come with the PostgreSQL distribution in the InstallationDirectory/src/tutorial directory; in order to access them, change to the PostgreSQL directory (cd InstallationDirectory/ src/tutorial) and run psql -s nteumdb and once inside \i basics.sql. The parameter \i reads the commands of the specified file (basic.sql in this case).

PostgreSQL is a relational database management system (RDBMS), which means that it manages data stored in tables. Each table has a specific number of rows and columns and every column contains a specific type of data. The tables are grouped into one DB and a single server handles this collection of DB (the full set is called a database cluster).

To create, for example, a table with psql, run:

CREATE TABLE weather ( 
      city                varchar(80),
      min_temp            int,
      max_temp            int,
      real                rain,
      day                 date
);

Example 8-2. Example

Create table. Inside psql:

CREATE TABLE NameTB (var1 type, var2 type,...);

The command ends when we type ';' and we can use blank spaces and tabs freely. varchar(80) specifies a data structure that can store up to 80 characters (in this case). The point is a specific type of PostgreSQL.

To delete the table:

DROP TABLE table_name ;

We can enter data in two ways, the first is to enter all the table's data and the second is to specify the variables and values that we wish to modify:

Example 8-3. Example

A second example could be:

CREATE TABLE city( name varchar(80),       place       point );

INSERT INTO weather VALUES ('Barcelona', 16, 37, 0.25, '2007-03-19');
INSERT INTO weather (city, min_temp, max_temp, rain, day)

VALUES ('Barcelona', 16, 37, 0.25, '2007-03-19'); This method can be simple for small amounts of data, but when a large amount of data has to be entered, it can be copied from a file with the sentence:

COPY weather FROM '/home/user/time.txt'; (this file must be on the server, not on the client).

Example 8-4. Recommend Reading

We recommend studying chapter 3 of PostgreSQL on advanced characteristics (Views,Foreign Keys, Transactions, http://www.postgresql.org/docs/8.2/static/tutorial-advanced.html

[Pos03d]

To look at a table, we could type:

SELECT * FROM weather;

where * means all the columns.

Example 8-5. Examples

  • Enter the data into the table. Inside psql:

INSERT INTO TBName (valueVar1, ValueVar2,...);

  • Data from a file. Inside psql:

COPY TBName FROM 'FileName';

  • Visualising data. Inside psql:

SELECT * FROM TBName;

Examples of more complex commands (within psql) would be:

  • Visualises the column city after typing:

SELECT city, (max_temp+min_temp)/2 AS average_temp, date FROM weather;

  • Visualises everything where the logical operation is fulfilled:

SELECT * FROM weather WHERE city = 'Barcelona'

      AND rain \verb+>+ 0.0;

  • Joining tables:

SELECT * FROM weather, city WHERE city = name;

  • Functions, in this case maximum:

SELECT max(min_temp) FROM weather;

  • Nested functions:

SELECT city FROM weather WHERE min_temp = (SELECT max(min_temp) FROM weather);

  • Selective modification:

UPDATE weather SET max_temp = max_temp 2, min_temp = min_temp 2 WHERE day > '19990128';

  • Deleting the register:

DELETE FROM weather WHERE city = 'Sabadell';

8.2.4. Installing PostgreSQL

This step is necessary for DB administrators [Posa]. The DB administrator's functions include software installation, initialisation and configuration, administration of users, DBs and DB maintenance tasks.

The database can be installed in two ways: through the distribution's binaries, which is not difficult, since the distribution scripts carry out all the necessary steps for making the DB operative, or through the source code, which will have to be compiled and installed. In the first case, we can use the kpackage (Debian) or the apt-get. In the second case, we recommend always going to the source (or to a mirror repository of the original distribution). It is important to bear in mind that the installation from the source code will then be left outside the DB of installed software and that the benefits of software administration offered, for example, by apt-cache or apt-get will be lost.

Installation from source code step by step:

8.2.4.1. Post-installation

Initialise the variables, in bash, sh, ksh:

LD_LIBRARY_PATH = /usr/local/pgsql/lib;
PATH = /usr/local/pgsql/bin:$PATH;
export LD_LIBRARY_PATH PATH;

or, in csh:

setenv LD_LIBRARY_PATH /usr/local/pgsql/lib;
set path = (/usr/local/pgsql/bin $path)

We recommend locating this initialisation in the user configuration scripts, for example /etc/profile or .bashrc for bash. To have access to the manuals, we need to initialise the MANPATH variable in the same way:

MANPATH = /usr/local/pgsql/man:$MANPATH;
export MANPATH

Once the DB is installed, we will need to create a user that will handle the databases (it is advisable to create a different user from the root user so that there is no connection with other services of the machine), for example, the postgres user using the useradd, command for example.

Next, we will need to create a storage area for the databases (single space) on the disk, which will be a directory, for example /usr/local/pgsql/data. For this purpose, execute initdb -D /usr/local/pgsql/data, connected as the user created in the preceding point. We may receive a message that the directory cannot be created due to no privileges, meaning that we will first have to create the directory and then tell the DB which it is; as root, we have to type, for example:

mkdir /usr/local/pgsql/data chown postgres /usr/local/pgsql/data su postgres initdb -D /usr/local/pgsql/data

Initiate the server (which is called postmaster), to do so, use:

postmaster -D /usr/local/pgsql/data

to run it in active mode (in the foreground); and to run it in passive mode (in the background) use:

postmaster -D /usr/local/pgsql/data < logfile 2 >&1 &.

Reroutings are done in order to store the server's errors. The package also includes a script (pg_ctl) so as not to have to know all the postmaster syntax in order to run it:

/usr/local/pgsql/bin/pg_ctl start -l logfile \ -D /usr/local/pgsql/data

We can abort the server's execution in different ways, with pg-ctl, for example, or directly using:

kill -INT 'head -1 /usr/local/pgsql/data/postmaster.pid'

8.2.4.2. DB users

DB users are completely different to the users of the operating system. In some cases, it could be interesting for them to maintain correspondence, but it is not necessary. The users are for all the DBs that the server controls, not for each DB. To create a user, execute the SQL sentence:

Example 8-6. Note

Creating, deleting users:

createuser [options] name

dropuser [options] name

CREATE USER name

To delete users:

DROP USER name

We can also call on the createuser and dropuser programs from the command line. There is a default user called postgres (within the DB), which is what will allow us to create the rest (to create new users from psql -U postgres if the user of the operating system used for administrating the DB is not postgres).

A DB user can have a set of attributes according to what the user is allowed to do:

  • Superuser: this user has no restrictions. For example, it can create new users; to do this, run:

    CREATE USER name CREATEUSER

  • DB creator: is authorised to create a DB. To create a user with these characteristics, use the command:

    CREATE USER name CREATEDB

  • Password: only necessary if we wish to control users' access when they connect to a DB for security reasons. To create a user with a password, we can use:

    CREATE USER name PASSWORD 'password'

    where password will be the password for that user.

  • We can change a user's attributes by using the command ALTER USER. We can also make user groups that share the same privileges with:

    CREATE GROUP GroupName

    And to insert the users in this group:

    ALTER GROUP GroupName ADD USER Name1

    Or to delete it :

    ALTER GROUP GroupName DROP USER Name1

Example 8-7. Example

Group operations inside psql:

CREATE GROUP GroupName;

ALTER GROUP GroupName ADD USER Name1...; ALTER GROUP GroupName

DROP USER Name1...;

When we create a DB, the privileges are for the user that creates it (and for the superuser). To allow another user to use this DB or part of it, we need to grant it privileges. There are different types of privileges such as SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE, USAGE, and ALL PRIVILEGES (consult the references for their meaning). To assign privileges, we can use:

GRANT UPDATE ON object TO user

where user must be a valid PostgreSQL user and object, a table, for example. This command must be executed by the superuser or table owner. The PUBLIC user can be used as a synonym for all users and ALL, as a synonym for all privileges. For example, to remove all of the privileges from all of the users of an object, we can execute:

REVOKE ALL ON object FROM PUBLIC;

8.2.5. Maintenance

There is a set of tasks that the DB administrator is responsible for and that must be performed periodically:

1) Recovering the space: periodically we must execute the VACUUM command, which will recover the disk space of deleted or modified rows, update the statistics used by the PostgreSQL scheduler and improve access conditions.

2) Reindexing: In some cases, PostgreSQL can give problems with the reuse of indexes, therefore it is advisable to use REINDEX periodically to eliminate pages and rows. We can also use contrib/reindexdb in order to reindex an entire DB (we need to take into account that, depending on the size of the DBs, these commands can take a while).

3) Change of log files: we need to prevent the log files from becoming too large and difficult to handle. This can be done easily when the server is initiated with:

pg_ctl start | logrotate

logrotate renames and opens a new log file and it can be configured with /etc/logrotate.conf.

4) Backup copy and recovery: there are two ways of saving data, with the sentence SQL Dump or by saving the DB file. The first will be:

pg_dump DBFile> BackupFile

For recovery, we can use: psql DBFile< BackupFile

In order to save all of the server's DBs, we can execute:

pg_dumpall > TotalBackupFile

Another strategy is to save the database files at the level of the operating system, for example using:

tar -cf backup.tar /usr/local/pgsql/data

There are two restrictions that can make this method unpractical:

Example 8-8. Summary of the installation of PostgreSQL:

./configure gmake su gmake install adduser postgres mkdir /usr/local/pgsql/data chown postgres /usr/local/pgsql/data su - postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data > logfile 2>&1 & /usr/local/pgsql/bin/createdb test /usr/local/pgsql/bin/psql test

8.2.6. Pgaccess

The application pgaccess [DBName] (http://www.pgaccess.org/) allows us to access and administer a database with a graphic interface. The easiest way of accessing (from KDE for example) is from a terminal, the DB administrator will have to do, (if not the postgresql user) xhost+ which will allow other applications to connect to the current user's display

su postgres pgaccess [DBName]&

If configured in 'Preferences' it will always open the last DB. Figure 15 shows the pgaccess interface.

In a typical session the administrator /user could, firstly, Open DataBase, indicating here for example, Port = 5432, DataBase = nteum (the other parameters are not necessary if the database is local) and then Open. As of this moment, the user will be able to work with the bidimensional space selecting what it wants to do in the Y axis (tables, consultations, views etc.) and with that element highlighted, and selecting one of that type within the window, using the X axis above for New (add), Open or Design. For example, if we select in Y Users and in X, New, the application will ask for the username, password (with verification), timeout and characteristics (for example, Create DB, Create other users). In DataBase we could also select Preferences, so as to change the type of font, for example, and select the possibility of seeing the system's tables.

Users' personal configurations will be registered in the file ~/.pgaccessrc. The interface helps to perform/facilitate a large amount of the user/administrator's work and it is recommended for users who have just started in PostgreSQL, since they will not need to know the syntax of the command line as in psql (the application itself will request all of a command's options through several windows).

A simpler tool is through the corresponding webmin module (we need to install the packages webmin-core and required modules, for example, in this case webmin-postgresql), but in many distributions it is not included by default (for more information visit http://www.webmin.com/). During the installation, webmin will warn that the main user will be the root and will use the same password as the root of the operating system. To connect, we can do so from a navigator for example, https://localhost:10000, which will ask to accept (or deny) the use of the SSL certificate for the SSL communication, and next it will show all of the services that can be administered, among them the PostgreSQL Data Base Server.