Realdolmen Education

Details

PostgreSQL

Deze cursus is momenteel niet ingepland op de open kalender, maar kan op aanvraag georganiseerd worden.

Cursus aanvragen

Aantal dagen

3 day(s)

Audience

The IT professional who wants to get started with PostgreSQL and learn some hands-on recipes for common tasks and issues.

Prerequisites

Knowledge of relational database principles and SQL. Hands-on experience with a relational database, such as Oracle, MySQL, … is a plus.

Objectives

Understand what PostgreSQL is and what makes it different from other relational databases. Know how to administer the PostgreSQL database.

Methods

Instructor-led workshop with practical exercices.

Description

PostgreSQL is an open source database server which requires little or none maintenance and provides a very low total cost of ownership. It is innovative and offers unique concepts such as inheritance, handling dates and timestamps, handling null values, …

The first part of this course handles the basic database functionalities with a focus on what distinguishes PostgreSQL from other relational databases.

The second part handles the database administration and provides some hands-on-recipes (such as scripts and queries) to handle common database administrator tasks.

At the end of the training you should be able to develop and administer your first PostgreSQL database.

Contents

  • What is PostgreSQL?
    • Brief history
    • Licensing
    • Getting PostgreSQL
  • First steps
    • Connecting to PostgreSQL server
    • Using command line scripting and query tool (psql)
    • Using a graphical query tool (PgAdmin III and alternatives)
  • Exploring the database
    • Querying for database information
    • Understanding object dependencies
  • Database configuration
    • Planning a new database
    • Exploration of the most important configuration parameters
  • Server control
    • Starting / stopping the server
    • Restricting database access
    • Give users their private database
    • Running multiple schemas and servers
    • Setting up a connection pool
  • Tables and data
    • Basic query syntaxes
    • PostgreSQL specific data types
    • Table inheritance
    • Guidelines to for a correct database schema
    • Generating and sampling test data
    • Importing data
  • Security
    • Set and save a password securely
    • Check if all users have a secure password
    • Revoking / Granting user access
    • Removing a user without dropping their data
    • Giving limited superuser powers to specific users
    • Auditing DLL and data changes
    • Encrypting sensitive data
    • Connecting using SSL
    • Integrating with LDAP
  • Database administration
    • Writing scripts
    • Adding / removing the columns of a table
    • Changing datatype of a column
    • Adding / removing schemas
    • Moving objects between schemas
    • Adding / removing tablespaces
    • Moving objects between tablespaces
    • Making views updatable
    • Accessing objects in other PostgreSQL databases
  • Monitoring and diagnosis
    • Monitoring database connections and sessions
    • Searching for unused database tables
    • Why is my database slowing down?
    • Producing a daily summary of logfile errors.
  • Regular maintenance
    • Controlling automatic database maintenance.
    • Avoiding database corruptions
    • Maintaining indexes
    • Planning maintenance
  • Performance and concurrency
    • Identifying and resolving slow SQL statements
    • Detecting and resolving index issues
    • Using optimistic locking
    • Reporting performance issues
  • Backup and recovery
    • Hot logical backup
    • Backup of database object definitions
    • Standalone hot physical database backup
    • Hot physical backup and continuous archiving
    • Recovery
  • Replication and upgrades
    • Understanding replication concepts
    • Replication strategies
    • Load balancing with pgpool-II
    • Performing minor upgrade
    • Performing major upgrades