Basic Tools
Installation and Management, psql
16
Copyright
© Postgres Professional, 2017–2025
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk
Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova
Photo by: Oleg Bartunov (Phu monastery, Bhrikuti summit, Nepal)
Use of Course Materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
Disclaimer
Postgres Professional assumes no responsibility for any damages and losses,
including loss of income, caused by direct or indirect, intentional or accidental
use of course materials. Postgres Professional company specifically disclaims
any warranties on course materials. Course materials are provided “as is,” and
Postgres Professional company has no obligations to provide maintenance,
support, updates, enhancements, or modifications.
2
Topics
PostgreSQL Installation Types
Server Management
Server Message Log
Configuration Parameters
Using psql
3
Database Cluster
PostgreSQL
database
database cluster
client
driver
connection
Let’s start with the general concepts.
PostgreSQL is a program that belongs to the class of database management
systems.
When this program is running, we call it a PostgreSQL server or a server
instance. So far, the server seems to be a “black box” for us, but gradually
we will get acquainted with how it works.
The data managed by PostgreSQL is stored in databases. One instance of
PostgreSQL simultaneously manages several databases. This set of
databases is called a database cluster. We will talk more about databases in
the “Data organization. Logical structure” lesson.
The server interacts with clients. Clients are external applications that can
connect to server databases and send queries for execution.
To summarize: a database cluster is the data in files; a server or a server
instance is a program that manages the database cluster; a client is an
application that “talks” to the server.
4
Installation
Installation types
pre-build packages (preferred)
installation from source code
without installation (cloud services)
Extensions
provide additional features
installed separately
shipped with the server as modules and programs (~50 extensions)
The preferred option for installing PostgreSQL is via a package manager
(such as apt or rpm) using pre-built packages. This gives you a
comprehensive installation that is easy to support and upgrade. There are
packages available for most operating systems.
Another option is to build PostgreSQL from source code. This may be
necessary if you want to set up a non-standard configuration or deploy on an
exotic platform.
Pre-built packages and source codes: http://www.postgresql.org/download/
Besides, you can work with cloud-based managed databases that do not
require any installation at all. Such ready-to-use services are provided by all
major cloud platforms (Amazon RDS, Google Cloud SQL, Microsoft Azure).
In this course, we are going to use a virtual machine with Xubuntu 24 OS;
PostgreSQL 16 is installed from the package for this OS. In addition, the
installation is set up to start and stop PostgreSQL when the OS starts and
stops.
There are a lot of PostgreSQL extensions that add new database
functionality “on the fly”, without modifying the system core. About 50
extensions are included into the PostgreSQL distribution itself.
The list of available extensions and their installation status can be accessed
using the pg_available_extensions view.
5
Server Management
Management tools
Main operations
start the server
stop the server
reload configuration parameters
pg_ctlcluster
pg_ctl
The main server management tasks are: initializing the database cluster,
starting and stopping the server, reloading configuration parameters, and a
few others. To perform these actions, use the pg_ctl utility, which comes
together with PostgreSQL.
In the Ubuntu PostgreSQL package, pg_ctl is not run directly, but through a
special wrapper pg_ctlcluster. The help information for pg_ctlcluster can be
viewed using man:
$ man pg_ctlcluster
Information about existing clusters and their status can be obtained using
the following commands:
$ pg_lsclusters
$ pg_ctlcluster status
For more details about server management for database administrators,
see:
7
Server Message Log
Server message log contains
server messages
user session messages
application messages
Log configuration
log file location
message format
events to log
Database operations are tracked in the server message log. The log keeps
records about starting and stopping the server, as well as various signal
messages, including messages about possible issues.
It can also store records of executed commands, their running time, locks
that occur, etc. It can be used to trace user sessions.
Application developers can generate and log their own messages.
PostgreSQL allows you to flexibly configure which messages and in which
format should be recorded into the log.
For example, the CSV and JSON output formats are convenient for
automating log analysis.
9
Configuration Parameters
For the entire instance
the main configuration file is postgresql.conf
ALTER SYSTEM modifies postgresql.auto.conf
For the current session
SET/RESET
set_config()
View the current setting
SHOW
current_setting()
pg_settings
\dconfig
The PostgreSQL server configuration is governed by a variety of parameters
that are used to manage resource consumption, tune system processes and
user sessions, manage the server log, and handle many other tasks. It’s
important to know how to check and update the parameter values.
Server configuration is usually stored in configuration files. The main
configuration file is postgresql.conf, it has to be edited manually. The second
configuration file is postgresql.auto.conf. Its contents are set using the
ALTER SYSTEM command. The parameters set via ALTER SYSTEM take
precedence over the parameters in postgresql.conf.
File and directory inclusion directives include and include_dir allow splitting
large postgresql.conf into manageable parts. This may come in handy when
managing multiple servers with similar configurations.
Most configuration parameters can be adjusted within a session with no
server restart. You can define custom parameters and manage them in the
same manner as with the system parameters.
Different ways of setting and updating parameters are described here:
And the current parameter values are shown in the pg_settings view.
11
The psql Client
Terminal client for working with PostgreSQL
Comes with the DBMS
Used by administrators and developers for interactive work and
script execution
There are other third-party tools available, but they are not considered in the
scope of the course.
In the course, we will use the psql terminal client:
1. psql is the only client supplied with the DBMS.
2. The knowledge of psql will be useful to both developers and DB
administrators, regardless of which tool they choose to work with at the end
of the day.
For interactivity, psql provides built-in support for readline and pager
programs (such as less and pspg), as well as the option to use external
editing tools. Using psql, you can interact with the operating system, browse
through the system catalog, and write scripts to automate routine tasks.
13
Takeaways
Installing PostgreSQL from pre-built packages is the preferred
installation type
Pre-packaged distributions bring some OS specifics that you
should be aware of:
how to start and stop the server
location of configuration files
location of the server message log
psql is a client application for working with PostgreSQL
14
Practice
1. Set the parameter work_mem to 8MB for all sessions.
Reload the configuration and verify that the changes have taken
effect.
Restore the default value.
2. Put the command to create a table to the ddl.sql file.
Put the commands to insert rows into this table to the
populate.sql file.
Run both scripts and verify that the table has been created and
populated.
3. Find today’s records in the server message log.
To launch psql in the terminal window, type psql without parameters.
student:~$ psql
It is convenient to have separate databases for tasks that are related to
different topics:
student/student=# CREATE DATABASE tools_overview;
CREATE DATABASE
student/student=# \c tools_overview
You are now connected to database "tools_overview" as user
"student".
student/tools_overview=#
1. Use the ALTER SYSTEM command.
2. If the output does not fit into one terminal screen, psql forwards it to the
tool called less for page-by-page output. To return to the prompt, press q.
