-
Learning by doing
-
Trainers with practical experience
-
Classroom training
-
Detailed course material
-
Clear content description
-
Tailormade content possible
-
Training that proceeds
-
Small groups
In the course PostgreSQL Administration participants learn to configure and manage PostgreSQL databases. PostgreSQL is a powerful and robust open source object-relational database system with a good reputation for reliability and performance. The course uses the latest version of the PostgreSQL database and the pgAdmin graphical user interface.
The course PostgreSQL Administration starts with a discussion of the installation options and the tools pgAdmin and psql. Also the main features of PostgreSQL are covered such as table inheritance, sophisticated locking, nested transactions and asynchronous replication.
Subsequently the PostgreSQL Architecture and the processes that play a role in it, such as the Postmaster Daemon Process, Background and Backend Processes and Client Processes, are treated. Tablespaces and vacuum are also covered.
Attention is also paid to the most commonly used server and database objects that PostgreSQL provides. It is important to get to know these objects, such as Server Service and Database Object, in order to be able to use their functionality.
Then it's time for the PostgreSQL backup tools pg_dump and pg_dumpall. Point-in-Time Database Restoration and Setting up WAL archiving are then explained.
The use of indices in PostgreSQL and the differences between the indices B-Tree, Hash, GiSY, GIN and BRIN is also part of the course PostgreSQL Administration.
Securing a PostgreSQL Server is of course of great importance. In that respect access permissions and client authentication control are covered and Data Encryption and the pg_crypto module as well.
Finally attention is paid to High Availability and Load Balancing by placing multiple PostgreSQL servers in a cluster.
The course PostgreSQL Administration is intended for persons who need to administer, monitor and support PostgreSQL databases and servers.
To join the course PostgreSQL Administration knowledge of the SQL query language and databases is needed.
The subject matter is treated on the basis of presentations. Demos are used to clarify the theory and exercises are used to bring the theory into practice. The course times are from 9.30 up and to 16.30.
Participants receive an official certificate PostgreSQL Administration after successful completion of the course.
Module 1 : PostgreSQL Intro |
Module 2 : PostgreSQL Architecture |
Module 3 : Server and Database Objects |
|
What is PostgreSQL? PostgreSQL Features User-defined types Table inheritance Sophisticated locking mechanism Foreign key referential integrity Nested Transactions and Savepoints Multi-version Concurrency Control Asynchronous replication Install and Connect to PostgreSQL pgAdmin Application and psql Shell |
Shared Memory Shared and WAL Buffer PostgreSQL Process Types Postmaster Daemon Process Background Processes Backend Processes Client Processes Database Structure Create User Database What are Tablespaces? What is Vacuum? |
Server Service Database Object Table Object and Schema PostgreSQL Tablespaces pg_default and pg_global View as Virtual Tables Functions and Operators Server Configuration Logging Parameters Memory Parameters WAL Parameters |
Module 4 : Backup and Restore |
Module 5 : Indexes |
Module 6 : Database Management |
|
PostgreSQL Backup Tools pg_dump and pg_dumpall Point-in-Time Database Restoration Setting up WAL archiving Data and/or Structure pg_dump Parameters Backup Object Definitions pg_restore and psql Ignore and Stop on Errors pg_store Parameters Restore only Structure |
What are Indexes? Index Types B-Tree, Hash, GiSY GIN and BRIN Index Differences Create and Drop Index List indexes Unique Index Index on Expression Partial index and Reindex Multicolumn Indexes |
Options to Create Databases Modify Databases Rename Databases Change Owner and Tablespace Change Session Defaults Delete Databases Check Activity with pg_stat_activity Copy a Database Using pg_dump Get Database Object Sizes Using pg_size_pretty |
Module 7 : Role Management |
Module 8 : Securing PostgreSQL |
Module 9 : Cluster Management |
|
Access Permissions pg_hba.conf file Creating PostgreSQL Roles Privileges and Restrictions superuser Login Privilege pg_roles System Catalog Role Attributes Groups Role Membership User Role and Group Inheritance |
Client Authentication Control Rule Specification Server Configuration Changing Parameters Role Strategies Super User Management Data Encryption One and Two Way Encryption pg_crypto Logging pg_stat_statements Module |
High Availability Cluster Performing Replication Primary Server Promoting Standby Server Load Balancing HAProxy Configuration Xinetd Setup HAProxy in ClusterControl Reslaving Standby Server Chained Replication Keepalived |
These companies already made use of our courses:
These companies used our courses:
