# PostgreSQL for Applications > Connect applications to PostgreSQL using PHP, Perl, Python, and Java, with examples of queries and transaction handling. --- LLMS index: [llms.txt](/en/llms.txt) --- ## A Separate User {#intro-section-6-1} 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. ```psql postgres=# CREATE USER app PASSWORD 'p@ssw0rd'; ``` ```sql CREATE ROLE ``` ```psql postgres=# CREATE DATABASE appdb OWNER app; ``` ```sql 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: ```psql postgres=# \c appdb app localhost 5432 ``` ```text Password for user app: *** You are now connected to database "appdb" as user "app" on host "127.0.0.1" at port "5432". ``` ```psql 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: ```psql appdb=> CREATE TABLE greeting(s text); ``` ```sql CREATE TABLE ``` ```psql appdb=> INSERT INTO greeting VALUES ('Hello, world!'); ``` ```sql INSERT 0 1 ``` ## Remote Connections {#intro-section-6-2} 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: ```text #listen_addresses = 'localhost' ``` We have to replace it with: ```text 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): ```bash # 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: ```bash # 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: ```text host appdb app all md5 ``` After changing the configuration files, don’t forget to make the server re-read the settings: ```psql postgres=# SELECT pg_reload_conf(); ``` To learn more details about authentication settings, see [postgrespro.com/doc/client-authentication.html]() ## Pinging the Server {#intro-section-6-3} 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: ```text C:\> chcp 1251 ``` ```text Active code page: 1251 ``` ```text C:\> set PGCLIENTENCODING=WIN1251 ``` ### PHP {#intro-subsection-6-3-1} 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: ```bash $ 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: ```text ;extension=php_pgsql.dll ``` A sample program (test.php): ```php ``` Let’s execute this command: ```bash $ php test.php Hello, world! ``` You can read about this PostgreSQL extension in PHP documentation: [php.net/manual/en/book.pgsql.php.]() ### Perl {#intro-subsection-6-3-2} 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: ```bash $ 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): ```perl 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: ```bash $ perl test.pl ``` ```text Hello, world! ``` The interface is described in the documentation: [metacpan.org/pod/DBD::Pg.]() ### Python {#intro-subsection-6-3-3} 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: ```bash $ 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): ```python 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: ```bash $ python3 test.py ``` ```text Hello, world! ``` ### Java {#intro-subsection-6-3-4} In Java, databases are accessed via the JDBC interface. Java SE 11 is required along with a JDBC driver package. ```bash $ 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): ```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): ```bash $ javac Test.java ``` ```bash $ java -cp .:/usr/share/java/postgresql-jdbc4.jar \ Test ``` ```text Hello, world! ``` ## Backup {#intro-section-6-4} 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: ```bash $ 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: ```bash $ createdb appdb2 ``` ```bash $ 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: ```bash $ 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? {#intro-section-6-5} 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.