Server-Side Application Development
A Basic Course
12
Copyright
© Postgres Professional, 2017–2021
Authors: Egor Rogov, Pavel Luzanov
Translated by Liudmila Mantrova
Usage of Course Materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed on an unrestricted basis. Commercial use is only possible with prior
written permission of Postgres Professional company. Modification of course
materials is forbidden.
Contact Us
Please send your feedback to: edu@postgrespro.ru
Disclaimer
In no event shall Postgres Professional company be liable for any damages
or loss, including loss of profits, that arise from direct or indirect, special or
incidental 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
For Developers
DEV1, 4 days
A basic course for
server-side application developers
DEV2, 4 days
An advanced course for developers
QPT, 2 days
Query performance tuning
SQL Basics
E. Morgunov,
PostgreSQL. SQL Basics
We offer several courses for server-side application developers.
All these courses require background knowledge of SQL fundamentals. We
do not have a special course on SQL, but there are a lot of books and other
resources that can help you learn it. We can recommend a book by Evgeny
Morgunov called PostgreSQL. SQL Basics (available in Russian):
DEV1 is a basic course for developers.
DEV2 expands on specifics of server internals that should be taken into
account in application code. It also thoroughly discusses extensibility—the
ability to extend server mechanisms with your own code, which makes
PostgreSQL suitable for a wide variety of tasks.
The QPT course provides a detailed review of query planning and
execution, performance tuning of the server instance, as well as tracing and
optimizing slow queries.
3
For DBAs
DBA1, 3 days
Basic PostgreSQL administration
DBA2, 4 days
Configuration and monitoring
QPT, 2 days
Query performance tuning
DBA3, 2 days
Replication and backups
SQL Basics
E. Morgunov,
PostgreSQL. SQL Basics
For DBAs, we offer the following courses.
The basic DBA1 course provides general information about PostgreSQL
architecture, installation process, basic server configuration and
management. It covers the main database administration tasks and access
control, as well as provides an overview of replication and backup.
DBA2 explains how to set up various configuration parameters based on the
understanding of server internals; it also discusses the questions of
database monitoring and iterative tuning of PostgreSQL configuration based
on the collected data. Besides, it touches upon localization settings,
extension management, and server upgrades.
The DBA3 course covers backups, as well as configuration and usage
scenarios of physical and logical replication. It also provides a general
overview of how to build scalable high-availability clusters and discusses the
related challenges.
The QPT course is common for both developers and DBAs.
DBA courses can be interesting to developers if they would like to deepen
their knowledge of PostgreSQL internals, or if there is no separate
administrator role in their project.
4
About DEV1
Duration: 4 days
Background knowledge required
SQL fundamentals
experience with any procedural programming language
basic knowledge of Unix OS
Knowledge and skills gained
general information about PostgreSQL architecture
working with the main database objects: tables, indexes, views
programming in SQL and PL/pgSQL on the server side
using the main data types, including records and arrays
setting up client-server communication
This basic course introduces server-side application developers to
PostgreSQL fundamentals and writing stored procedures and functions in
SQL and PL/pgSQL.
5
Provided Resources
A pre-configured virtual machine
Xubuntu OS
PostgreSQL 12 with documentation in English
a sample web application Bookstore
pgAdmin 4
Course materials
Student’s Guide
presentations, demos, practical assignments with keys
(in html and pdf formats)
reference materials: PostgreSQL functions and data types,
a chart of the main system catalog tables with psql commands,
some Unix commands
If you are taking the course on your own, take a look at the Student’s Guide
first. Among other things, it provides download links for the virtual machine
and other resources, and explains how to use them. All course materials are
available at:
Doing practical assignments is very important for gaining the skills of
working with PostgreSQL. Always try completing the tasks on your own, and
then look through the provided keys, even if you did not have any questions:
you can find there some more theory, which is not covered in presentations
and demos.
Course materials (presentations, demos, practical assignments, and their
keys) are available in two formats. The html format is convenient for
browsing online and allows copying text and code snippets. The pdf format
works best for printing.
Additional reference materials will help you quickly find the required
information.
There is pgAdmin 4 installed on the virtual machine. Although we use psql
throughout the course, you can try this GUI tool if you like.
6
Organizational Questions
Each course day includes
~8 classroom hours (45 min each)
a lunch break (1 hour)
For each lecture, we usually provide
a presentation with demos: ~25–60 min
practical assignments: ~20–30 min, including a break
7
Day 1
Basic tools
01. Installation and management; psql
Architecture
02. A general overview of PostgreSQL
03. Isolation and MVCC
04. Buffer cache and WAL
Data organization
05. Logical structure
06. Physical structure
In the first day, we mainly cover theoretical questions. We present
PostgreSQL architecture fundamentals, which is indispensable for efficient
use of database features. The gained knowledge will be applied to practice
in the upcoming days.
8
Day 2
Bookstore application
07. Application schema and interface
SQL
08. Functions
09. Procedures
10. Composite types
PL/pgSQL
11. Overview and programming structures
Starting from the second day, there are two sets of practical assignments for
each topic: the tasks related to the Bookstore application and some
additional ones. It is impossible to complete all the practice within the
allotted time (about 30 minutes). Use additional tasks for self-study.
9
Day 3
PL/pgSQL (continued)
12. Executing queries
13. Cursors
14. Dynamic commands
15. Arrays
16. Error handling
10
Day 4
PL/pgSQL (continued)
17. Triggers
18. Debugging
Access control
19. Access control overview
Backup
20. Logical backup
