6 PostgreSQL for Applications

Connect applications to PostgreSQL using PHP, Perl, Python, and Java, with examples of queries and transaction handling.

A Separate User

In the previous chapter, we showed how to connect to the database server on behalf of the postgres user. This is the only database user available right after the PostgreSQL installation. Since the postgres user is a superuser, it should not be used to connect to the database from an application. It is better to create a new user and make it the owner of a separate database; then its rights will be limited to this database.

postgres=# CREATE USER app PASSWORD 'p@ssw0rd';

CREATE ROLE

postgres=# CREATE DATABASE appdb OWNER app;

CREATE DATABASE

To learn about users and privileges, see: postgrespro.com/doc/user-manag and postgrespro.com/doc/ddl-priv.

To connect to a new database and start working with it on behalf of the newly created user, run:

postgres=# \c appdb app localhost 5432

Password for user app: ***
You are now connected to database "appdb" as user
"app" on host "127.0.0.1" at port "5432".

appdb=>

This command takes four parameters, in the following order: database name (appdb), username (app), node (localhost or 127.0.0.1), and port number (5432).

Note that the database name is not the only thing that has changed in the prompt: instead of the hash symbol (#), the greater than sign is displayed (>). The hash symbol indicates the superuser rights, similar to the root user in Unix.

The app user has full privileges within the appdb database. For example, this user can create a table:

appdb=> CREATE TABLE greeting(s text);

CREATE TABLE

appdb=> INSERT INTO greeting VALUES ('Hello, world!');

INSERT 0 1

Remote Connections

In our example, both the client and the database are located on the same system. You can install PostgreSQL onto a separate server and connect to it from a different system (for example, from an application server). In this case, you must specify your database server address instead of localhost. But it is not enough: for security reasons, PostgreSQL only allows local connections by default.

To connect to the database from the outside, you must edit two files.

First of all, modify the postgresql.conf file, which contains the main configuration settings. It is usually located in the data directory.

Find the line defining network interfaces for PostgreSQL to listen on:

#listen_addresses = 'localhost'

We have to replace it with:

listen_addresses = '*'

Next, edit the pg_hba.conf file with authentication settings.

When a client tries to connect to the server, PostgreSQL searches this file for the first line that matches the connection by four parameters: connection type, database name, username, and client IP address. This line also specifies how the user must confirm their identity.

For example, on Debian and Ubuntu, this file includes the following setting among others (the top line starting with the hash symbol is a comment):

#   TYPE   DATABASE   USER   ADDRESS   METHOD
   local        all    all               peer

It means that local connections (local) to any database (all) on behalf of any user (all) must be validated by the peer authentication method (clearly, an IP address is not required for local connections).

The peer method means that PostgreSQL requests the current username from the operating system and assumes that the OS has already performed the required authentication check (prompted for the password). This is why on Linux-like operating systems users usually don’t have to enter the password when connecting to a local server.

But Windows does not support local connections, so this line looks as follows:

#   TYPE   DATABASE   USER        ADDRESS   METHOD
    host        all    all   127.0.0.1/32      md5

It means that network connections (host) to any database (all) on behalf of any user (all) from the local address (127.0.0.1) must be checked by the md5 method. This method requires the user to enter the password.

To allow the app user to access the appdb database from any address upon providing a valid password, add the following line to the end of the pg_hba.conf file:

host      appdb    app            all      md5

After changing the configuration files, don’t forget to make the server re-read the settings:

postgres=# SELECT pg_reload_conf();

To learn more details about authentication settings, see postgrespro.com/doc/client-authentication.html

Pinging the Server

To access PostgreSQL from an application, you have to find an appropriate library and install the corresponding database driver. A driver is usually a wrapper for libpq (a standard library that implements the client-server protocol for PostgreSQL), but other implementations are also possible. The library provides application developers with a convenient way to access low-level features of the protocol.

The following sections contain simple code snippets in several popular languages. These examples can help you quickly check the connection with the database system that you have installed and set up.

These code snippets are minimal examples and should not be used in production; there is nothing else, not even error handling functionality.

If you are using a Windows system, to ensure the correct display of extended character sets, you may need to switch to a TrueType font (such as “Lucida Console” or “Consolas”) in the Command Prompt window and change the code page. For example, for the Russian language, run the following commands:

C:\> chcp 1251

Active code page: 1251

C:\> set PGCLIENTENCODING=WIN1251

PHP

PHP interacts with PostgreSQL via a special extension. On Linux, apart from the PHP itself, you also have to install the package with this extension:

$ sudo apt-get install php-cli php-pgsql

You can install PHP for Windows from the PHP website: windows.php.net/download. The extension for PostgreSQL is already included in the binary distribution, but you must find and uncomment (by removing the semicolon) the following line in the php.ini file:

;extension=php_pgsql.dll

A sample program (test.php):

<?php
  $conn = pg_connect('host=localhost port=5432 ' .
                     'dbname=appdb user=app ' .
                     'password=p@ssw0rd') or die;
  $query = pg_query('SELECT * FROM greeting') or die;
  while ($row = pg_fetch_array($query)) {
    echo $row[0].PHP_EOL;
  }
  pg_free_result($query);
  pg_close($conn);
?>

Let’s execute this command:

$ php test.php
Hello, world!

You can read about this PostgreSQL extension in PHP documentation: php.net/manual/en/book.pgsql.php.

Perl

In the Perl language, database operations are implemented via the DBI interface. On Debian and Ubuntu, Perl itself is pre-installed, so you only need to install the driver:

$ sudo apt-get install libdbd-pg-perl

There are several Perl builds for Windows, which are listed at perl.org/get.html. Popular builds such as ActiveState Perl and Strawberry Perl come with the PostgreSQL driver pre-installed.

A sample program (test.pl):

use DBI;
use open ':std', ':utf8';
my $conn = DBI->connect(
  'dbi:Pg:dbname=appdb;host=localhost;port=5432',
  'app','p@ssw0rd') or die;
my $query = $conn->prepare('SELECT * FROM greeting');
$query->execute() or die;
while (my @row = $query->fetchrow_array()) {
  print @row[0]."\n";
}
$query->finish();
$conn->disconnect();

Let’s execute this command:

$ perl test.pl

Hello, world!

The interface is described in the documentation: metacpan.org/pod/DBD::Pg.

Python

Python typically uses the psycopg library (pronounced as “psycho-pee-gee”) to interact with PostgreSQL.

On modern versions of Debian and Ubuntu, Python 3 is pre-installed, so you only need to add the corresponding driver:

$ sudo apt-get install python3-psycopg2

You can download Python for Windows from the python.org website. The psycopg library is available at initd.org/psycopg (choose the version that corresponds to the version of Python installed). You can also find all the required documentation there.

A sample program (test.py):

import psycopg2
conn = psycopg2.connect(
  host='localhost', port='5432', database='appdb',
  user='app', password='p@ssw0rd')
cur = conn.cursor()
cur.execute('SELECT * FROM greeting')
rows = cur.fetchall()
for row in rows:
  print(row[0])
conn.close()

Let’s execute this command:

$ python3 test.py

Hello, world!

Java

In Java, databases are accessed via the JDBC interface. Java SE 11 is required along with a JDBC driver package.

$ sudo apt-get install openjdk-11-jdk
$ sudo apt-get install libpostgresql-jdbc-java

You can download JDK for Windows from oracle.com/technetwork/java/javase/downloads. The JDBC driver is available at jdbc.postgresql.org (choose the version that corresponds to the JDK installed on your system). You can also find all the required documentation there.

Let’s consider a sample program (Test.java):

import java.sql.*;
public class Test {
  public static void main(String[] args)
  throws SQLException {
    Connection conn = DriverManager.getConnection(
      "jdbc:postgresql://localhost:5432/appdb",
      "app", "p@ssw0rd");
    Statement st = conn.createStatement();
    ResultSet rs = st.executeQuery(
      "SELECT * FROM greeting");
    while (rs.next()) {
      System.out.println(rs.getString(1));
    }
    rs.close();
    st.close();
    conn.close();
  }
}

Compile and execute the program specifying the path to the JDBC class driver (on Windows, paths are separated by semicolons, not colons):

$ javac Test.java

$ java -cp .:/usr/share/java/postgresql-jdbc4.jar \
Test

Hello, world!

Backup

Although our database contains only one table, ensuring data durability is still important. While your application has little data, the easiest way to create a backup is to use the pg_dump utility:

$ pg_dump appdb > appdb.dump

If you open the resulting appdb.dump file in a text editor, you will see standard SQL commands that create all the appdb objects and fill them with data. You can pass this file to psql to restore the contents of the database. For example, you can create a new database and import all the data into it:

$ createdb appdb2

$ psql -d appdb2 -f appdb.dump

This is the format in which the demo database described in the previous chapter is distributed.

The pg_dump utility offers many features worth checking out: postgrespro.com/doc/app-pgdump. Some of them are available only if the data is dumped in a custom format. In this case, you have to use the pg_restore utility instead of psql to restore the data.

In any case, pg_dump can back up the contents of a single database only. To make a backup of the whole cluster, including all the databases, users, and tablespaces, you should use a different command: pg_dumpall.

Large-scale projects require an elaborate and comprehensive backup strategy. A better option here is a physical binary copy of the cluster, which can be taken by the pg_basebackup utility:

$ pg_basebackup -D backup

This command will create a backup of the whole database cluster in the backup directory. To restore the cluster from this backup, move it to the data directory and restart the server.

To learn more about the available backup and recovery tools, see the documentation: postgrespro.com/doc/backup.

Built-in PostgreSQL features enable you to implement almost everything you need, but you have to complete multi-step workflows that lack automation. That’s why many companies create their own backup tools. Postgres Professional also has such a tool called pg_probackup. The free version of the tool enables you to perform incremental backups at the page level, ensure data integrity, use parallel execution and compression when working with big volumes of information, and implement various backup strategies.

Its full documentation is available at postgrespro.com/doc/app-pgprobackup.

What’s next?

Now you are ready to develop your application. Regarding the database, the application will always consist of two parts: server and client. The server part comprises everything that relates to the database system: tables, indexes, views, triggers, stored functions and procedures. The client part holds everything that works outside of the database and connects to it; from the database point of view, it doesn’t matter whether it’s a thick client or an application server.

An important question that has no clear-cut answer: where should we place business logic?

One popular approach is to move the logic out of the database and implement it all on the client. It often happens when developers are unfamiliar with all the capabilities provided by a relational database system and prefer to rely on what they know well, that is, on the application code.

In this case, the database becomes somewhat secondary to the application and only ensures data persistence, its reliable storage. Database systems can be often isolated by an additional abstraction level, such as an ORM tool that automatically generates database queries from the constructs of the programming language familiar to developers. Such solutions are sometimes justified by the intent to develop an application that is portable to any database system.

This approach has the right to exist: if such a system works and addresses all business objectives, why not?

However, this solution also has some obvious drawbacks:

Data consistency is ensured by the application. Instead of relying on the database system to ensure data consistency (and this is exactly what relational database systems are especially good at), all the required checks are performed by the application. Rest assured that sooner or later your database will contain inconsistent data. You have to either fix these errors, or teach the application how to handle them. If multiple applications use the same database, enforcing data consistency without database-level constraints becomes impractical.
Performance leaves much to be desired. ORM systems allow you to create an abstraction level over the database, but the quality of SQL queries they generate is rather questionable. As a rule, multiple small queries are executed, and each of them is quite fast on its own. Such a model can cope only with low load on small data volumes and is virtually impossible to optimize on the database side.
Application code gets more complicated. Using application-oriented programming languages, it’s impossible to write a really complex query that could be properly translated to SQL in an automated way. Thus, complex data processing (if it is needed, of course) has to be implemented at the application level, with all the required data retrieved from the database in advance, but it requires an extra data transfer over the network. Furthermore, such algorithms as scans, joins, sorting, and aggregation provided by database systems are guaranteed to perform better than the application code, as they have been improved and optimized for years.

To fully utilize database features, including integrity constraints and stored procedures, a thorough understanding of its capabilities is necessary. You have to master the SQL language to write queries and learn one of the server programming languages (typically, PL/pgSQL) to create functions and triggers. In return, you will get a reliable tool, one of the most important building blocks for any information system architecture.

In any case, you have to decide for yourself where to implement business logic: on the server side or on the client side. We’ll just note that there’s no need to go to extremes, as the truth often lies somewhere in the middle.