Detailed Objectives

Version 1.0


OSS-DB is a qualification certification for engineers involved in OSS-DB system. OSS-DB system is commonly used and the number of companies relying on the system is increasing each year.

Objectives for "Version 1.0" adopt "PostgreSQL 9".
Objectives for "Version 2.0" adopt "PostgreSQL 10 and PostgreSQL 11".

You can take Version 1.0 exam "until March 31, 2021" and Version 2.0 exam "from June 1, 2020".
Therefore, until March 31, 2021, you can choose to take the exam of either version.
In addition, regardless of the version, if you pass the Silver and Gold exams, you will be certified as OSS-DB Gold.

OSS-DB Silver: Detailed Objectives

General Knowledge(20%)
  • 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
 Server Administration(50%)


  • 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
  • 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 and Development(30%)

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

  • 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)



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

OSS-DB Gold: Detailed Objectives

Advanced Server Administration(30%)


Setting up database server

  • Estimating table and index sizes
  • Security

              ・ Encrypting communication channels

              ・ Encrypting data

              ・ Client authentication

 Commands for database administration

  • Backup and PITR
  • Autovacuum
  • Managing server log files
  • Monitoring disk spaces

 Database architecture

  • Architecture of database cluster
  • Database processes
  • Physical structure of data and data files
 Operation of hot-standby database
 Monitoring Performance(30%)

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


  • Detecting slow queries

  • EXPLAIN and tuning 

Related parameters

  • Analysis using contrib tools

Analysis using contrib tools

  • oid2name, pgbench, pgstattuple
Performance Tuning(20%)

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