PostgreSQL Backend Development

Introductory Course

Authors Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk

Translated by Liudmila Mantrova and Alexander Meleshko

Photo by Oleg Bartunov (Tukuche 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:

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.

Developers

DEV1, 4 days

Basic Course

on Backend Development

DEV2, 4 days

Advanced Course

QPT, 3 days

Query Performance Tuning

SQL Basics

Any textbook of your choice

PGPRO, 3 days

Features of

Postgres Pro Enterprise

For backend developers, we offer a special range of courses.

Prior knowledge of the basics of the SQL language is required to complete

these courses. There is no course on the SQL language in our line of

courses, but there are many books and other educational resources with

which you can master SQL.

DEV1 is a basic course for developers.

The DEV2 course examines the features of the server's internal structure

that affect the development of application code, and comprehensively

discusses extensibility: the ability to extend server mechanisms with your

own code, which allows you to use PostgreSQL to solve a wide variety of

tasks.

The QPT course examines in detail the mechanisms for planning and

executing queries, discusses the configuration parameters related to

performance, and examines the options for finding problematic queries and

optimizing them.

The PGPRO course discusses additional features provided by the Postgres

Pro Enterprise DBMS.

https://postgrespro.com/education/courses

Administrators

DBA1, 3 days

Basic Course

on PostgreSQL Administration

DBA2, 4 days

Configuration and Monitoring

QPT, 3 days

Query Performance Tuning

DBA3, 2 days

Backup and Replication

SQL Basics

Any textbook of your choice

PGPRO, 3 days

Features of

Postgres Pro Enterprise

For administrators, we offer the following courses.

The DBA1 basic course provides general information about the PostgreSQL

architecture, installation process, basic server configuration and

management. It also considers basic administration tasks and access

control issues. Lastly, it provides an overview of backup and replication

mechanisms.

The DBA2 course focuses on determining appropriate values of

configuration parameters based on an understanding of the internals of the

server. It also talks about monitoring the server and using feedback for

iterative parameter tweaking. Besides, it touches upon localization settings,

extension management, and server upgrades.

The DBA3 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 and PGPRO courses are intended for both developers and

administrators.

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.

About this course

Duration: 2 days

Background knowledge required

SQL fundamentals

experience with any procedural programming language

basic knowledge of Unix OS

Knowledge and skills gained

working with the main database objects: tables, indexes, views

backend programming in SQL and PL/pgSQL

using the main data types, including records and arrays

setting up client-server interface

This course introduces backend application developers to writing

PostgreSQL stored procedures and functions in SQL and PL/pgSQL. It is

based on DEV1 course shortened to 2 days.

Provided resources

Pre-configured virtual machine

Xubuntu 24.04 OS

PostgreSQL 17 with documentation in English

a sample web application Bookstore

DBeaver

Course handouts

presentations, demonstrations, practical assignments and assignment keys

(in html and pdf)

reference materials: PostgreSQL functions and data types,

a chart of the main system catalog tables with psql commands,

some Unix commands

Completing practical assignments is very important to gain the basic skills in

working with PostgreSQL. Be sure to try to complete the assignments

yourself first, and then review the provided keys, even if the assignment did

not raise questions. Assignment keys may contain additional information that

is not mentioned in presentations and demonstrations.

Course handouts (presentations, demonstrations, practical assignments and

keys) are available in two formats. HTML is good for browsing and copying

code snippets. PDF is divided into pages and is easy to print.

Additional reference materials will help you quickly find the information you

need.

The virtual machine comes with DBeaver pre-installed. Although we use psql

throughout the course, you can use this GUI tool if you like.

Course outline and schedule

Each training day is approximately 6 hours + lunch (1 hour)

Each topic usually consists of

a presentation with demos: ~25-60 min

practical assignments: ~20-30 min, including a break

Day 1

The Bookstore App

01. Data Schema and API

SQL

02. Functions and Procedures

03. Composite Types

PL/pgSQL

04. Overview and Programming Structures

Day 2

PL/pgSQL (continued)

05. Query Execution

06. Dynamic Commands

07. Arrays

08. Error Handling

09. Triggers

Practice

To get used to the VM environment, execute some SQL queries.

1. Сonnect to bookstore database.

2. Get version of PostgreSQL.

3. Get value of the search_path parameter.

1. You can run psql in terminal or launch Dbeaver. Use current_database

function to check the database you are connected to.

2. Use version function.

3. Use SHOW command.