This course will allow readers to get a better understanding of PostgreSQL. The course aims to present the readers with a solid knowledge of PostgreSQL building blocks, including the plpgsql language and how it can be used to build stored procedures and triggers. Advanced features like Common Table Expression and Window Functions will be presented, allowing the user to improve her SQL skills and know how to write better and more readable queries.
The reader will know how to manage and understand its database cluster thanks to glance at the PostgreSQL catalog and statistic collector. Last, readers will learn how to handle master-slave replication, a core feature of PostgreSQL.
The plpgsql language
The DO block
Glance at plperl
DML Trigger Types
Implementing triggers with plpgsql
Introduction of cursors
Example of usage of a cursor
Users and Permission Management
Users, Groups and Roles
Allowing permissions and denying permissions
Row Level Security
Introduction to the Query Rewrite System
An example of rule
Test your skills
- How is a group of users implemented in PostgreSQL?
- What is the difference between a DO INSTEAD and a DO ALSO rule?
- How many type of views does PostgreSQL support?
- Create a table foo with exactly two columns: pk an interger auto-increment primary key and t as unlimited string. Fill the table with a couple of records and then create a dynamic view and a materialized one. Populate the materialized view, then delete the contento of foo and see what changes in the views.
- Open a transaction, place a couple of records into foo and revert the changes.
- Create a user group developers, and the following users into the group: dev_a, dev_b, dev_c. Configure PostgreSQL to allow all developers but dev_c to connect to your database.
Common Table Expression
Introduction to CTEs
An example of move
Introduction to Window Functions
A few useful window functions
Configuring the Server
Monitor the database activity
Test your skill
- What is the purpose of a recursive CTE?
- What does the OVER clause does?
- What information does the pg_stat_activity contain?
- Suppose you have the table dir defined and populated as follows:
pk | name | child_of | dir
1 | / | | t
2 | bin | 1 | t
3 | tmp | 1 | t
4 | home | 1 | t
5 | luca | 4 | t
6 | Desktop | 5 | t
7 | emacs | 2 | f
8 | cat.png | 6 | f
- Write a recursive CTE that builds the full path of each entry where dir = f.
- Begin a transaction on a terminal, without closing such transaction open a new terminal and extract the start time and backend pid of the opened transaction.
- Create a CTE that deletes the content of the above dir directory showing thru a SELECT the deleted rows.
Point in Time Recovery
Glance at Logical Replication
Test your skills
- What is a physical backup and what do you need to get it working?
- Beginning a base/physical backup with pg_base_backup() is dangerous with respect to normal operativity of the cluster?
- What is the main difference between physical and logical replication?
- Set up a base backup of the cluster with pg_basebackup command line tool.
- Configure a streaming replication from your main cluster to another instance running on a different TCP/IP port on the very same machine.
Instructor: Luca Ferrari
Luca lives in Italy with his beautiful wife, his great son and two female cats.
Computer science passionate since the Commodore 64 age, he holds a Master Degree and a PhD in Computer Science. He is a PostgreSQL enthusiast, and has adopted this such database back at version 7.3. He was a co-founder of the Italian PostgreSQL Users’ Group, being also a former president of the latter. He has written several articles about PostgreSQL and teaches at several events and training courses.
Luca is also a Perl lover, an Operating System passionate, a Unix fan and performs as much tasks as possible within Emacs. He considers the Open Source the only sane way of doing software and services. His web site is available at http://fluca1978.github.io