PostgreSQL Administration Training (5 days)

PostgreSQL Administration Training Overview

Students taking this PostgreSQL training class will learn to effectively administer PostgreSQL .

PostgreSQL Administration Training Course Goals

• Learn to compile source code.
• Learn about backup and disaster recovery.
• Learn about inheritance, partitioning, and Tablespaces.
• Learn to manage free space.
• Learn about security in PostgreSQL.
• Learn to create high availabilty and high performance solutions.

PostgreSQL Administration Training Course Prerequisites

Experience in the following areas is required:

• Familiarity with databases.

PostgreSQL Administration Training Course Outline

1. Source Code Compile
   1. Understanding the configure switches
   2. Compiling contrib modules
   3. Compiling and installing 3rd party procedural languages
      1. Java
      2. Ruby
2. Backup and Disaster Recovery
   1. pg_dump
   2. pg_dumpall
   3. pg_restore
   4. PITR
   5. DRDB
   6. Backup Strategies
3. Inheritance, Partitioning and Tablespaces
   1. Using Inheritance
   2. Partitioning Types
      1. Range
      2. List
      3. Hash
      4. Composite
   3. Administrating Partitions
   4. Administrating Tablespaces
   5. Best Practices
4. Managing Free Space
   1. SQL commands
      1. VACUUM
      2. CLUSTER
      3. REINDEX
   2. autovacuum
   3. Tuning free space map settings, postgresql.conf
5. Security
   1. Roles
   2. Testing password strength
   3. Restricting access on the localhost using UNIX DOMAIN SOCKETS
   4. Encrypted Sessions
      1. SSH tunnels using port forwarding
      2. SSL
   5. Athenticated Sessions
      1. IDENT
      2. LDAP
      3. KERBEROS
      4. PAM
      5. SSL certficates
   6. Encrypting Data In The Database
      1. The md5 function
      2. The chkpass contrib module
      3. The pgcrypto contrib module
6. High Availability & High Performance Solutions
   1. dblink
   2. pgmemcache
   3. SLONY
   4. PGPOOL
7. Performance
   1. basics
      1. Understanding the EXPLAIN statement
   2. Monitoring
      1. UNIX utilities: iostat, vmstat, sar, systat
      2. PostgrSQL runtime statistics, pg_stat views and tables, using psql and pgadmin
   3. Tuning
      1. Identifying operations to tune bulk loading operations DML operations DDL operations
      2. Editing postgresql.conf
      3. Configuring kernel parameters at runtime using sysctl
   4. Best Practices
      1. Server startup paramaters & options (pg_ctl)
      2. Benchmarking with pgbench
      3. Working with indexes
         1. Definition
         2. Expression
         3. Partial
      4. Simplifying tuning by crafting user roles
         1. SQL statements of interest
            1. SHOW
            2. SET CONSTRAINTS (setting user/role parameters)
            3. SET ROLE / ALTER ROLE
            4. SET SESSION AUTHORIZATION
      5. Scripting performance analysis

PostgreSQL Administration Training Course Materials

In addition to a comprehensive set of materials, including course notes and all the programming examples, each student will also receive a one-year subscription to Webucator's online reference library, which contains hundreds of the most current electronic technology books - a $149.95 per student value.

PostgreSQL Administration Training Technical Requirements

1. PostgreSQL
   • Download
2. pgAdmin III
   • Download