fbpx

Course PostgreSQL Administration

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.

Region:
  • Content
  • Training
  • Modules
  • General
    General
  • Reviews
  • Certificate
  • Course PostgreSQL Administration : Content

    PostgreSQL Intro

    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.

    PostgreSQL Architecture

    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.

    Server and Database Objects

    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.

    Backup and Restore

    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.

    Indexes

    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.

    Roles and Security

    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.

    Clustering

    Finally attention is paid to High Availability and Load Balancing by placing multiple PostgreSQL servers in a cluster.

  • Course PostgreSQL Administration : Training

    Audience PostgreSQL Administration Course

    The course PostgreSQL Administration is intended for persons who need to administer, monitor and support PostgreSQL databases and servers.

    Prerequisites Course PostgreSQL Administration

    To join the course PostgreSQL Administration knowledge of the SQL query language and databases is needed.

    Realization Training PostgreSQL Administration

    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.

    Certification Course PostgreSQL Administration

    Participants receive an official certificate PostgreSQL Administration after successful completion of the course.

    Course-PostgreSQL-Administration
  • Course PostgreSQL Administration : Modules

    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
  • Course PostgreSQL Administration : General

    Read general course information
  • Course PostgreSQL Administration : Reviews

  • Course PostgreSQL Administration : Certificate