●General features of OSS-DB [Weight: 4]

- Description:

Candidates should be able to understand PostgreSQL's features, license, and role of the OSS community.

- Key Knowledge Areas:

General features of PostgreSQL

OSS-DB license and OSS general license

OSS-DB community, its activities and the way of  participation to the community

Major version / Minor version

Release Cycle / Support Policy / Bug Report

●General knowledge of relational databases [Weight: 4]

- Description:

Candidates should be able to understand basic concepts and general knowledge of relational databases.

- Key Knowledge Areas:

Basic concepts of the relational data model

Role of database management system

General knowledge about SQL

Sorts of SQL (DDL / DML / DCL)

Database design and normalization

●Installation [Weight: 2]

- Description:

Candidates should be able to understand how to install PostgreSQL, create a database cluster, etc.

- Key Knowledge Areas:

Usage of initdb command

Concept and structure of database cluster

Template database

- Important files, terms, and utilities:

initdb

pg_ctl initdb

PGDATA

template0

template1

●Usage of server management tools [Weight: 5]

- Description:

Candidates should be able to use management tools that can be invoked from the OS command prompt.

- Key Knowledge Areas:

Usage of database management commands

- Important files, terms, and utilities:

pg_ctl

createuser

dropuser

createdb

dropdb

psql

meta-command

●Configuration files [Weight: 5]

- Description:

Candidates should be able to use configuration files and basic configuration parameters.

- Key Knowledge Areas:

Following four items related to postgresql.conf

　　-Setting Parameters

　　-Connections and authentication

　　-Client connection defaults

　　-Error reporting and logging

How to set pg_hba.conf

Usage of SET / SHOW

- Important files, terms, and utilities:

postgresql.conf

pg_hba.conf

pg_ctl reload/restart

pg_settings

●Backup and restore [Weight: 7]

- Description:

Candidates should be able to understand how to backup PostgreSQL.

- Key Knowledge Areas:

Usage of various backup commands

File system level backup and restore

Concepts and procedures of Point-in-Time Recovery (PITR)

Transaction log (WAL) and WAL archive

pg_start_backup () / pg_stop_backup ()

Usage of COPY statement (SQL) and \copy command (psql)

- Important files, terms, and utilities:

pg_dump

pg_dumpall

pg_restore

psql

pg_basebackup

PITR

recovery.conf

COPY

\copy

● Basic server administration  [Weight: 7]

- Description:

Candidates should be able to know basic commands for database administration.

- Key Knowledge Areas:

Starting and stopping PostgreSQL

Concept of database role / user

Adding, deleting and modifying database role / user

Purpose and usage of VACUUM and ANALYZE

Autovacuum concepts and behavior

System information functions

Information schema and system catalogs

Table privileges (GRANT / REVOKE)

- Important files, terms, and utilities:

pg_ctl start / stop

CREATE/ALTER/DROP ROLE/USER

VACUUM

ANALYZE

vacuumdb

autovacuum

current_user

version

information_schema

GRANT

REVOKE

● SQL commands [Weight: 13]

- Description:

Candidates should be able to know basic SQL statements and database components.

- Key Knowledge Areas:

SELECT statement

INSERT statement

UPDATE statement

DELETE statement

Datatypes

Table definition

Index

View

Materialized view

Rule

Trigger

Sequence

Schema

Table space

Partition

Function definition / Procedure definition

PL / pgSQL

- Important files, terms, and utilities:

SELECT/INSERT/UPDATE/DELETE

FROM

JOIN

WHERE

INTO

VALUES

SET

LIMIT

OFFSET

ORDER BY

DISTINCT

GROUP BY

HAVING

EXISTS

IN

NOT

INTEGER

SMALLINT

BIGINT

NUMERIC

DECIMAL

REAL

DOUBLE PRECISION

CHAR

CHARACTER

VARCHAR

CHARACTER VARYING

TEXT

BOOLEAN

DATE

TIME

TIMESTAMP

INTERVAL

SERIAL

BIGSERIAL

BYTEA

NULL

CREATE/ALTER/DROP TABLE

PRIMARY KEY

FOREIGN KEY

REFERENCES

UNIQUE

NOT NULL

CHECK

DEFAULT

CREATE/ALTER/DROP INDEX/VIEW/MATERIALIZED

VIEW/RULE/TRIGGER/SCHEMA/SEQUENCE/TABLESPACE/FUNCTION/PROCEDURE

CREATE TABLE PARTITION BY/OF

ALTER TABLE ATTACH/DETACH PARTITION

● Functions and operators [Weight: 2]

- Description:
Candidates should be able to know functions and operators that are standard in the database.

- Key Knowledge Areas:
Aggregate functions
Mathematical functions
Operators
String functions
String operators
Date/Time functions

- Important files, terms, and utilities:
count
sum
avg
max
min
char(character)_length
lower
upper
substring
replace
trim
||
~
LIKE
SIMILAR TO
age
now
current_date
current_timestamp / statement_timestamp / clock_timestamp
current_time
extract
to_char

● Transactions [Weight: 1]

- Description:

Candidates should be able to know transaction features.

- Key Knowledge Areas:

Transaction syntax

Transaction isolation level (read committed, repeatable read, serializable)

LOCK statement

Row-level locks and table locks

Deadlock

- Important files, terms, and utilities:

BEGIN

COMMIT

ROLLBACK

SAVEPOINT

SET TRANSACTION

LOCK TABLE

●Setting up database server [Weight: 2]

- Description:

Candidates should be able to estimate required database size for server configuration, and should understand database security.

- Key Knowledge Areas:

Estimation of table / index size

Security

　　-Encryption of communication route (SSL)

　　-Data encryption

　　-Client authentication

　　-Audit log

Data type and size

Parameter setting for each user database

- Important files, terms, and utilities:

Checksum

pg_xact

pg_multixact

pg_notify

pg_serial

pg_snapshots

pg_stat_tmp

pg_subtrans

pg_tblspc

pg_twophase

ssl

pg_stat_ssl

pgcrypto

ALTER ROLE

ALTER DATABASE

initdb -data-checksums (-k)

log_statement

track_functions

track_activities

●Commands for database administration [Weight: 4]

- Description:

Candidates should understand advanced database server administration.

- Key Knowledge Areas:

Backup, PITR

VACUUM, ANALYZE, REINDEX

Autovacuum

Checkpoint

Server log management

Disk space monitoring

Difference between automatic VACUUM and manual VACUUM / ANALYZE

- Important terms, commands, parameters, etc.:

ALTER SYSTEM

ANALYZE

CLUSTER

REINDEX

VACUUM

CHECKPOINT

PITR

WAL

pg_dump

pg_dumpall

pg_basebackup

pg_start_backup()

pg_stop_backup()

postgresql.conf

recovery.conf

vacuumdb

pgstattuple

pg_cancel_backend()

pg_terminate_backend()

pg_isready

log_connections

log_disconnections

log_duration

●Database architecture [Weight: 2]

- Description:

Candidates should understand the physical architecture of the database.

- Key Knowledge Areas:

Architecture of database cluster

Database processes

Physical structure of data and data files

- Important terms, commands, parameters, etc.:

autovacuum

TOAST

FILLFACTOR

Archive log

Page header

Tuple header

postmaster process

postgres process

Background process

Cancelling SQL execution

Effects on server processes by signal (TERM / INT / HUP)

●Operation of hot-standby database [Weight: 1]

- Description:

Candidates should understand the settings and the steps of configuring replication environment. This includes replication mechanisms (processes and flows) and monitoring status etc.

- Key Knowledge Areas:

Overview of streaming replication feature and logical replication feature

Synchronous and asynchronous replication

Settings of postgresql.conf and recovery.conf

Definition of publications and subscriptions

- Important terms, commands, parameters, etc.:

wal_level

max_wal_senders

wal_sender_timeout

wal_receiver_timeout

synchronous_standby_names

synchronous_commit

max_logical_replication_workers

CREATE/ALTER/DROP PUBLICATION/SUBSCRIPTION

pg_stat_replication

pg_stat_wal_receiver

recovery_min_apply_delay

Inquiry conflict on standby

hot_standby_feedback

max_standby_streaming_delay

pg_wal_replay_pause ()

pg_wal_replay_resume ()

walsender process

walreceiver process

pg_receivewal

Transaction log (WAL)

Processing that is/isn’t propagated to the standby

Queries that can be executed on standby

●Access statistics [Weight: 3]

- Description:

Candidates should understand how to use and collect the statistics, that indicates the usage of the database.

- Key Knowledge Areas:

pg_locks

pg_stat_activity, pg_stat_database

Row-level statistics, ex. pg_stat_all_tables

Block level statistics, ex. pg_statio_all_tables

- Important terms, commands, parameters, etc.:

pg_stat_archiver

pg_stat_bgwriter

Wait event (pg_stat_activity.wait_event)

●Statistics for tables and columns [Weight: 2]

- Description:

Candidates should understand the statistics for tables and columns used by the planner.

- Key Knowledge Areas:

pg_class

pg_stats

Real file and path of table index

Statistics and parameters used during execution planning

- Important terms, commands, parameters, etc.:

pg_statistic

pg_stats

null_frac

n_distinct

most_common_freqs

histogram_bounds

correlation

default_statistics_target

effective_cache_size

●Execution plans [Weight: 3]

- Description:

Candidates should be able to read the execution plan output by EXPLAIN and perform tuning.

- Key Knowledge Areas:

EXPLAIN / EXPLAIN ANALYZE output

Planning type

Tuning from EXPLAIN

Type of join (Nested Loop, Hash, Merge) and performance characteristics

Execution plan corresponding to SQL syntax (JOIN / GROUP BY / ORDER BY / LIMIT)

Execution plan corresponding to SQL with aggregate function (sum / count)

Execution plan corresponding to SQL for partition

Execution plan corresponding to parallel query

Execution plan corresponding to SQL of window function (row_number / rank etc.)

- Important terms, commands, parameters, etc.:

●Other performance monitoring [Weight: 1]

- Description:

Candidates should know other techniques for performance monitoring.

- Key Knowledge Areas:

Detecting slow queries

Analysis using contrib tools

Performance degradation factors (resource exhaustion, lock contention)

- Important terms, commands, parameters, etc.:

shared_preload_libraries

auto_explain

auto_explain. *

log_min_duration_statement

pg_stat_statements

log_autovacuum_min_duration

log_lock_waits

log_checkpoints

●Parameters related to performance [Weight: 4]

- Description:

Candidates should understand database configuration parameters, especially those that affect performance and can be useful for performance tuning.

- Key Knowledge Areas:

RESOURCE USAGE

WRITE AHEAD LOG

QUERY TUNING

RUNTIME STATISTICS

LOCK MANAGEMENT

Light and heavy locks

- Important terms, commands, parameters, etc.:

shared_buffers

huge_pages

effective_cache_size

work_mem

maintenance_work_mem

autovacuum_work_mem

wal_level

fsync

synchronous_commit

checkpoint_timeout

checkpoint_completion_target

●Tuning database and queries [Weight: 2]

- Description:

Candidates should understand database and SQL tuning.

- Key Knowledge Areas:

Tuning parameters

Tuning the execution plan

Tuning SQL

Tuning the table configuration

Distributing disk I/O

How to reflect parameters (action required for parameter activation)

How indexes affect SQL performance

Index Only Scan and Visibility Map

- Important terms, commands, parameters, etc.:

Index Only Scan

●Troubleshooting common failures [Weight: 3]

- Description:

Candidates should be able to take appropriate actions  in the event of a failure such as SQL execution timeout in the database, server down, malfunction, data loss, OS resource exhaustion, etc., by identifying the cause from the error messages.

- Key Knowledge Areas:

Dealing with server down, malfunction and data loss

OS resource exhaustion

OS parameters

Server process status (idle, idle in transaction, active)

Effects on server process by signal (TERM / INT / HUP)

Server process crashes (segmentation faults, etc.) and scope of impact

- Important terms, commands, parameters, etc.:

statement_timeout

lock_timeout

idle_in_transaction_session_timeout

Inquiry conflict on standby

hot_standby_feedback

vacuum_defer_cleanup_age

max_standby_archive_delay

max_standby_streaming_delay

fsync

synchronous_commit

restart_after_crash

pg_cancel_backend()

pg_terminate_backend()

pg_ctl kill

max_locks_per_transaction

max_files_per_process

●Recovering damaged cluster [Severity: 2]

- Description:

Candidates should be able to take appropriate actions when the data file or transaction log file is damaged,  by identifying the cause from the error messages.

- Key Knowledge Areas:

Transaction log recovery

System table index recovery

Developer options

Real file and path of table index

Relfilenode and OID

Index corruption and REINDEX recovery

Checksum corruption detection and recovery

Transaction ID rounding error

- Important terms, commands, parameters, etc.:

PITR

pg_resetwal

ignore_system_indexes

ignore_checksum_failure

Commit log (pg_xact)

Single user mode

VACUUM FREEZE

●Recovering hot-standby databases [Weight: 1]

- Description:

Candidates should be able to take appropriate action when the primary or standby PostgreSQL stops or fails in a replication environment.

- Key Knowledge Areas:

Streaming replication and logical replication

Error messages in log files

Processing that is/isn’t propagated to the standby

How to restart primary PostgreSQL at stop/failure

How to restart standby PostgreSQL at stop/failure

Processing that is/isn’t propagated to logical replication subscribers

Conflicts at Logical Replication Subscribers

- Important terms, commands, parameters, etc.:

pg_ctl promote

pg_receivewal

pg_rewind