The PostgreSQL Database System and Tools

Database System Concepts
Seventh Edition

Avi Silberschatz
Henry F. Korth
S. Sudarshan

Downloading and Installing PostgreSQL

PostgreSQL can be downloaded from the postgresql.org download page. Note that PostgreSQL comes preinstalled on most linux distributions, although the version may not be the latest PostgreSQL version; to get permissions to that installation you may need to use the postgres user login. If you download your own copy, you do not need to worry about the postgres user login, but you will have to carry out several setup operations, detailed below. We also recommend you install the pgAdmin4 user interface. On Windows, the PostgreSQL installer also install pgAdmin.

Setting up PostgreSQL

After installing PostgreSQL, you need to configure the server and create a database.

On windows, the PostgreSQL installer will prompt you for configuration of PostgreSQL, including the database directory, port on which the database listens for commands, passwords for superuser and service accounts.
On Linux:
- If you use the preinstalled PostgreSQL, all the above configuration parameters are set by default, and you just need to create an account and connect to PostgreSQL. PostgreSQL listens for connections on port 5432 by default.
- If you installed PostgreSQL, you will have to configure the above parameters by editing configuration files. In particular, you will have to change the default port from 5432 to something else (say 11432) to avoid port clashes, as detailed below.
  - Find out the directory where the PostgreSQL binaries were installed. Replace PGDIR below by that full path (e.g. /usr/lib/postgresql/11/bin)
  - cd to a directory inside which you want to create the database directory.
  - Create a database (called dbbook in this example) using the command line by executing
```
 PGDIR/initdb -D dbbook 
```
    This creates a directory dbbook.
  - Edit dbbook/postgresql.conf and replace the line
```
#port = 5432
```
    by
```
port = 11432
```
    (any unused port number can be used, 11432 is just an example).
  - Run
```
PGDIR/postmaster -D dbbook 
```
    (if you run this command from any other directory, replace dbbook by the full path of the PostgreSQL directory you created

By default, PostgreSQL allows access from IDEs on the same computer. If you want to access it from another machine, you have to configure it to accept remote connections. It it best to allow access only from a selected set of IP addresses, or range of IP addresses, to provide better security. You will also need to create users on the PostgreSQL database in order to use it securely.

Connecting to and Executing Queries on PostgreSQL

Once PostgreSQL is set up, you can connect to it using a tool such as pgAdmin4 to administer the system, browse databases, and execute queries. You can also browse the database and execute queries using the command line tool psql, or any of a variety of IDEs such as Eclipse, Idea4J, Netbeans, etc. You will have to use the host as localhost, port number you created, database name and username and password.

SQL Tips on PostgreSQL

PostgreSQL provides good support for basic SQL syntax. Most queries in the book run as-is on current versions of PostgreSQL. PostgreSQL also has support for some advanced features such as recursive WITH queries, window queries, and some non-standard syntax such as the limit and offset clauses to fetch a specified number of rows from a specified starting point.