fbpx

Course PostgreSQL Administration

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

    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.

    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

    Course Forms

    All our courses are classroom courses in which the students are guided through the material on the basis of an experienced trainer with in-depth material knowledge. Theory is always interspersed with exercises.

    Customization

    We also do custom classes and then adjust the course content to your wishes. On request we will also discuss your practical cases.

    Course times

    The course times are from 9.30 to 16.30. But we are flexible in this. Sometimes people have to bring children to the daycare and other times are more convenient for them. In good consultation we can then agree on different course times.

    Hardware

    We take care of the computers on which the course can be held. The software required for the course has already been installed on these computers. You do not have to bring a laptop to participate in the course. If you prefer to work on your own laptop, you can take it with you if you wish. The required software is then installed at the start of the course.

    Software

    Our courses are generally given with Open Source software such as Eclipse, IntelliJ, Tomcat, Pycharm, Anaconda and Netbeans. You will receive the digital course material to take home after the course.

    Lunch

    The course includes lunch that we use in a restaurant within walking distance of the course room.

    Locations

    The courses are planned at various places in the country. A course takes place at a location if at least 3 people register for that location. If there are registrations for different locations, the course will take place at our main location, Houten which is just below Utrecht. A course at our main location also takes place with 2 registrations and regularly with 1 registration. And we also do courses at the customer’s location if they appreciate that.

    Evaluations

    At the end of each course, participants are requested to evaluate the course in terms of course content, course material, trainer and location. The evaluation form can be found at https://www.klantenvertellen.nl/reviews/1039545/spiraltrain?lang=en. The evaluations of previous participants and previous courses can also be found there.

    Copyright

    The intellectual property rights of the published course content, also referred to as an information sheet, belong to SpiralTrain. It is not allowed to publish the course information, the information sheet, in written or digital form without the explicit permission of SpiralTrain. The course content is to be understood as the description of the course content in sentences as well as the division of the course into modules and topics in the modules.

  • Course PostgreSQL Administration : Reviews

  • Course PostgreSQL Administration : Certificate