Improve Your PostgreSQL Skills

(Course #10) Improve Your PostgreSQL Skills

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.

Module 1

Stored procedures
The plpgsql language
The DO block
Glance at plperl
Triggers
DML Trigger Types
Implementing triggers with plpgsql
Cursors
Introduction of cursors
Example of usage of a cursor

Module 2

Users and Permission Management
Users, Groups and Roles
Allowing permissions and denying permissions
Row Level Security
Rules
Introduction to the Query Rewrite System
An example of rule
Views
Dynamic views
Materialized Views
Test your skills

Questions

• 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?

Exercises

• 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.

Module 3

Common Table Expression
Introduction to CTEs
An example of move
Recursive CTEs
Window Functions
Introduction to Window Functions
A few useful window functions

Module 4

Indexes
Configuring the Server
Monitor the database activity
pg_stat_activity
pg_locks
Autovacuum
Test your skill

Questions

• What is the purpose of a recursive CTE?
• What does the OVER clause does?
• What information does the pg_stat_activity contain?

Exercises

• 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.

Module 5

Point in Time Recovery
Streaming Replication
Glance at Logical Replication
Test your skills

Questions

• 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?

Exercises

• 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

REF: www.bsdmag.orgbsd