Basic Tools
Installation and Management; psql
15
Copyright
© Postgres Professional, 2023
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov
Translated by Liudmila Mantrova
Cover photo by Oleg Bartunov (Phu monastery and Bhrikuti peak, 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
Agenda
PostgreSQL installation types
Managing the server
Server log file
Setting up configuration parameters
Using psql
3
Database Cluster and Server
PostgreSQL
database
database
cluster
Let’s start with the main 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 server
instance. A server is still a “black box” for us, but soon we’ll learn how it
works.
All data managed by PostgreSQL is stored in databases. A single
PostgreSQL instance can work with several databases at the same time.
This set of databases is called a database cluster. We'll discuss databases
in more detail in lecture “Data organization. Logical structure”.
To sum it up: a database cluster stores data in files; a server or a server
instance is a program that manages the database cluster.
4
Installation
Installation Types
pre-built packages (preferred)
installation from source code
managed databases (cloud services)
Extensions
provide additional features
are installed separately
are shipped with the server as modules and programs (~50 extensions)
The preferable way to install PostgreSQL is via package managers (such as
rpm or dpkg) using pre-built packages. In this case, you get a
comprehensive installation that is easy to support and upgrade. Pre-built
packages are available for most operating systems.
Another option is to build PostgreSQL from source code. It can be
necessary if you would like to use PostgreSQL in a non-standard
configuration or run it on an exotic platform.
Pre-built packages and source code are available at
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),
as well as Yandex.Cloud and Mail.ru Cloud Solutions.
In this course, we are going to use a virtual machine with Xubuntu OS;
PostgreSQL is installed from the package for this OS.
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.
You can look up the list of available extensions and check if they are already
installed in the pg_available_extensions view.
5
Managing the Server
Server management utility
Primary tasks
start the server
stop the server
reload server configuration
pg_ctlcluster
pg_ctl
The main server management tasks are: initializing the database cluster,
starting and stopping the server, reloading configuration parameters, and
a couple of others. To perform these actions, use the pg_ctl utility, which is
provided together with PostgreSQL.
For Ubuntu OS, the package distribution has no direct access to pg_ctl:
it provides the pg_ctlcluster wrapper for this utility. To view reference
documentation for pg_ctlcluster, run the following command:
$ man pg_ctlcluster
For more information about managing the server that can be useful for
DBAs, see:
7
Server Log File
A log file can contain:
server signal messages
user session messages
application messages
You can configure:
log file location
message format
events to log
Database operation is tracked in the server log. It records the information
about starting and stopping the server, as well as various signal messages
about possible issues.
Log files can also contain the information about the executed commands,
their execution time, locks, etc. It can be used for tracing user sessions.
Application developers can direct their own messages to the server log.
PostgreSQL settings enable you to fine-tune the scope and format of logged
error messages.
For example, the CSV output format is convenient for automating log
analysis.
9
Configuration Parameters
For the whole instance:
the main configuration file is postgresql.conf
ALTER SYSTEM creates postgresql.auto.conf
For the current session:
SET/RESET
set_config()
To view the current setting:
SHOW
current_setting()
pg_settings
PostgreSQL server is set up using various configuration parameters. They
define how to manage resource consumption, tune system processes and
user sessions, manage the server log, and handle many other tasks. We will
deal with some of these parameters later in this course. But now it’s
important to figure out how to check and update the current settings.
Server settings are usually defined in configuration files. The main
configuration file is postgresql.conf; it has to be edited manually. Another
configuration file is postgresql.auto.conf; it is updated by the ALTER
SYSTEM command. Parameter values set via ALTER SYSTEM override
those that are defined in postgresql.conf.
Most settings can be changed in user sessions without a server restart.
Different ways of setting and updating parameters are described here:
Current parameter values are displayed in the pg_settings view:
11
psql Client
A command-line client for working with PostgreSQL
Shipped together with the database system
Used by DBAs and developers for running scripts
and interacting with the server
There are various third-party clients for PostgreSQL, but their evaluation is
beyond the scope of this course.
We are going to use psql, a command-line client:
- It is the only client shipped with the server.
- The experience of working in psql will be useful to both DBAs and
developers, regardless of which tool they are going to use in the future.
To enable the interactive mode, psql provides built-in support for readline
and pager programs (such as less). 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 know:
how to start and stop the server
where configuration files are located
where to find the server log
psql is a client application for working with PostgreSQL
14
Practice
1. Set the work_mem parameter to 8 MB in the postgresql.conf file.
Reload the server configuration and check that the changes have
come into effect.
2. Create a file called ddl.sql. In this file, write the CREATE TABLE
command that creates an arbitrary table.
Create another file called populate.sql; it should contain some
commands that insert rows into this table.
Start psql, run both scripts, and check that the table is created and
contains the specified rows.
3. Find today’s entries in the log file.
To do practical assignments, you have to log in to the operating system on
behalf of the student user (the password is student).
Start psql in the terminal by typing psql, without parameters. The connection
will be established with the default settings.
student:~$ psql
It is convenient to have separate databases for tasks that are related to
different topics. Let's create one:
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=#
Task 1. You can use any text editor. The virtual machine provides
mousepad, gedit, vim, nano.
Note: if you launch an editor from the GUI environment instead of the
terminal, it will be started on behalf of the student OS user.
