8.4: PostgreSQL (Advanced Internet Programming)

PostgreSQL is a modern SQL database management system. In this section, I explain how to install, initialize and use PostgreSQL.

Installation

If you haven’t already installed Postgres, follow the directions on the Postgres website or the instructions below.

Ubuntu (or Windows Subsystem for Linux): You can use the command sudo apt install postgresql
Mac: You can use homebrew to install with the command brew install postgresql

PATH configuration for Ubuntu

On Mac and many Linux distributions, PostgreSQL should be ready to use immediately after installation.

Test the installation in a terminal:

$ postgres --version
postgres (PostgreSQL) 12.3
$

If the command succeeds, then your installation is complete (do not worry if the version number is different).

If you see an error, such as Command 'postgres' not found, did you mean: or bash: postgres: command not found despite having install PostgreSQL, then you will need to configure your system so that the PostgreSQL binaries are on your PATH.

This should be done by adding export PATH=/usr/lib/postgresql/12/bin/:$PATH to your ~/.bashrc file.

You can do this from a terminal, by the following steps:

$ ls /usr/lib/postgresql/
12
$ echo export PATH=/usr/lib/postgresql/12/bin/:\$PATH >> ~/.bashrc
$

Note: the number that appears when running ls should match the number in the echo command (i.e., you may need to adjust the number 12, depending on your computer’s exact configuration).

You will need to close and reopen any terminals for the new setting to take effect.

Disabling systemd services

If you are using Ubuntu, you should also stop PostgreSQL from automatically starting on bootup. Please enter the following two commands.

You only need to do this once. You may safely ignore any errors that appear: ‘systemd’ or the ‘postgresql’ service may already be disabled.

$ sudo systemctl disable postgresql
Synchronizing state of postgresql.service with SysV service script with /lib/systemd/systemd-sysv-install.
Executing: /lib/systemd/systemd-sysv-install distable postgresql
Removed /etc/systemd/system/multi-user.target.wants/postgresql.service.
$ sudo systemctl stop postgresql
$

Initializing and starting Postgres

In production, PostgreSQL typically runs as a background service (or “daemon”). However, during development, I prefer to run it manually from the command line. Manually starting PostgreSQL gives me the flexibility to specify exactly where the database files are stored, and allows me to start and stop the server with ease.

To initialize a Postgres database (i.e., to create an empty database file), use the initdb ./pgdb/ command:

$ initdb ./pgdb/
The files belonging to this database system will be owned by user "benjamin".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory ./pgdb ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D ./pgdb/ -l logfile start

$

Tip

The initdb command generates the internal data files used by PostgreSQL. A single PostgreSQL database server can host multiple databases. The internal data files store all of the databases. The initdb command generates two databases: an internal database named template1, as well as default database named postgres for use by users and applications. ^[1]

The pg_ctl command suggested by initdb will run the server as a service/daemon. Instead, I like to manually start the database by running postgres -D ./pgdb/ -k '' directly (or /usr/lib/postgresql/12/bin/postgres -D ./pgdb/ -k '' if you are using Ubuntu Linux): ^[2]

$ postgres -D ./pgdb/ -k ''
LOG:  starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (Arch Linux 9.2.1+20200130-2) 9.2.1 20200130, 64-bit
LOG:  listening on IPv6 address "::1", port 5432
LOG:  listening on IPv4 address "127.0.0.1", port 5432
LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
LOG:  database system was shut down at 2020-04-20 13:16:15 UTC
LOG:  database system is ready to accept connections

You can stop the database by pressing Control+C.

Interacting with the database shell

You can interact with the database by opening a new terminal and using the psql -h localhost postgres command: ^[3]

$ psql -h localhost postgres
psql (12.2)
Type "help" for help.

postgres=# create table shopping_item(id serial primary key, description text not null, quantity numeric not null);
CREATE TABLE
postgres=# insert into shopping_item (description, quantity) values ('Chocolate bar', 5);
INSERT 0 1
postgres=# insert into shopping_item (description, quantity)  values ('Pasta', 1);
INSERT 0 1
postgres=# select * from shopping_item;
 id |  description  | quantity
----+---------------+----------
  1 | Chocolate bar |        5
  2 | Pasta         |        1
(2 rows)

postgres=# \d
                  List of relations
 Schema |         Name         |   Type   |  Owner
--------+----------------------+----------+----------
 public | shopping_item        | table    | benjamin
 public | shopping_item_id_seq | sequence | benjamin
(2 rows)

postgres=# \d shopping_item
                               Table "public.shopping_item"
   Column    |  Type   | Collation | Nullable |                  Default
-------------+---------+-----------+----------+-------------------------------------------
 id          | integer |           | not null | nextval('shopping_item_id_seq'::regclass)
 description | text    |           | not null |
 quantity    | numeric |           | not null |
Indexes:
    "shopping_item_pkey" PRIMARY KEY, btree (id)

postgres=# drop table shopping_item;
DROP TABLE
postgres=# \q
$

In the transcript above, after starting psql, I entered SQL commands to create a table, insert data and then query the database using select. The \d command shows all declarations in the database and the \d shopping_item command describes the shopping_item table. Finally, I entered the SQL drop command to delete the table, before quitting the client using \q.

Warning

Don’t forget to add a semicolon (;) to the end of each SQL statement. If you forget the semicolon, it will look like psql is doing nothing. The psql client will keep collecting lines of input until you terminate the statement with a semicolon.

psql can run SQL commands saved in a text file. For example, I can create a file named shopping.sql with the following contents:

create table shopping_item(id serial primary key, description text not null, quantity numeric not null);
insert into shopping_item (description, quantity) values ('Chocolate bar', 5);
insert into shopping_item (description, quantity) values ('Pasta', 1);
select * from shopping_item;
drop table shopping_item;

The script is executed from within psql using the \i command:

$ psql -h localhost postgres
psql (12.2)
Type "help" for help.

postgres=# \i shopping.sql
CREATE TABLE
INSERT 0 1
INSERT 0 1
 id |  description  | quantity
----+---------------+----------
  1 | Chocolate bar |        5
  2 | Pasta         |        1
(2 rows)

DROP TABLE
postgres=# \q
$

Alternatively, the script is run directly from the command line with the -f flag:

$ psql -h localhost postgres -f shopping.sql
CREATE TABLE
INSERT 0 1
INSERT 0 1
 id |  description  | quantity
----+---------------+----------
  1 | Chocolate bar |        5
  2 | Pasta         |        1
(2 rows)

DROP TABLE
$

1. You can generate additional databases in the data files by starting the PostgreSQL server and issuing the create database SQL command.

2. The postgres -D ./pgdb/ -k '' starts the PostgreSQL database server using the ./pgdb/ directory as the location of internal data files to store/read information in databases. The -k '' option disables Unix-domain sockets: I have only included this option to avoid startup errors that may occur if the default setting is not suitable on your computer.

3. The psql -h localhost postgres command connects to the PostgreSQL server running on localhost and then uses the default database named postgres that was generated by initdb.