• Features of OSS-DB
• License
• Community and information retrieval
• General knowledge about relational database
• Basic concept of relational data model
• Role of database management system
• General knowlede about SQL, sorts of SQL(DDL, DML and DCL)
• Database design and normalizatio

Installation

• initdb command
• Concept of database cluster
• Template database

Usage of server management tools

• pg_ctl, createuser, dropuser, createdb, dropdb, psql

Configuration files

• postgresql.conf

              ・ Setting parameters

              ・ Connections and authentication

              ・ Client connection defaults

              ・ Error reporting and logging

• pg_hba.conf
• Usage of SET/SHOW commands

Backup and restore

• pg_dump, pg_dumpall, pg_restore and psql commands
• File system level backup
• Point-in-Time Recovery(PITR), including specific operations
• COPY command and \copy meta-command

Basic server administration 

• Adding, deleting and modifying database users
• VACUUM and ANALYZE
• Autovacuum
• System information functions (ex. version, current_user)
• Information schema
• System catalogs(ex. pg_roles, pg_authid), not including statistics views
• Manipulating table privileges with GRANT/REVOKE

SQL commands

• SELECT statement

              ・ Specifying columns and tables
                  LIMIT, OFFSET and ORDER BY clauses

              ・ DISTINCT, GROUP BY, HAVING, subqueries

              ・ JOIN (including outer joins), EXISTS, IN

• INSERT, UPDATE and DELETE statements

• Datatypes
  Boolean, character strings, numbers, date and time,
  NULL, serial, OID, arrays

• Table definition
  Constraints, default values
  ALTER TABLE and DROP TABLE

• Index

• View

• Rule and Trigger

• Schema

• Function definition and PL/pgSQL 

 Functions and operators

• Aggregate functions (count, sum, avg, max, min)
• Mathematical functions and operators
• String functions (char_length, lower, upper, substring, replace, trim)
• String operators (||, ~, LIKE, SIMILAR TO)
• Date/time functions (age, current_date, current_timestamp, current_time, extract, to_char)

Transactions 

• Syntax (BEGIN, COMMIT, ROLLBACK, SAVEPOINT and their equivalences)
• Transaction isolation level
• Locks (implicit locks, LOCK statement, row-level locks, table locks)

Setting up database server

• Estimating table and index sizes
• Security

              ・ Encrypting communication channels

              ・ Encrypting data

              ・ Client authentication

 Commands for database administration

• Backup and PITR
• VACUUM, ANALYZE and REINDEX
• Autovacuum
• Managing server log files
• Monitoring disk spaces

 Database architecture

• Architecture of database cluster
• Database processes
• Physical structure of data and data files

Access statistics

• Related parameters
• pg_locks

• pg_stat_activity, pg_stat_databases

•  

  Row-level statistics, ex. pg_stat_all_tables

•  

  Block-level statistics, ex. pg_statio_all_table

Statistics for tables and columns

• pg_class
• pg_stats 

Execution plans

• Output from EXPLAIN/EXPLAIN ANALYZE

• Detecting slow queries

• EXPLAIN and tuning 

Related parameters

• Analysis using contrib tools

Analysis using contrib tools

• oid2name, pgbench, pgstattuple

Parameters related to performance

• Resource consumption
• Write ahead log
• Query planning
• Run-time statistics
• Lock management

 Tuning database and queries

• Tuning parameters
• Tuning execution plan
• Tuning SQL
• Distributing disk I/O

 Troubleshooting common failures

• Server failure
• Dubious behaviors
• Data lost
• OS resource shortage

 Recovering damaged cluster

• Recovering transaction log
• Recovering indexes of system tables
• Options for developers

Recovering hot-standby databases