PostgreSQL CE 7.4 Gold

Detailed Exam Scopes [ PostgreSQL CE 7.4 Gold Exam ]

• Administration Section
  • Database deployment
    • estimating table/index size
    • secure TCP/IP connections
  • Commands and tools for database administration
    • backup
    • VACUUM, ANALYZE, REINDEX
    • error logging
  • Structure of PostgreSQL database cluster
    • files in cluster directory($PGDATA)
    • initlocation
  • and Scope of PostgreSQL CE 7.4 Silver Administration Section
• Performance-tuning Section
  • Access statistics
    • GUC's about statistics collector
    • pg_locks
    • pg_stat_activity, pg_stat_database
    • pg_stat_all_tables
    • pg_statio_all_tables
  • Table/columns statistics
  • pg_class
  • pg_stats
  • Execution plan
    • reading EXPLAIN / EXPLAIN ANALYZE
    • items of plan
      • Seq Scan, Index Scan
      • Nested Loop, Merge Join, Hash Join
      • Sort, Unique, Group, Aggregate, HashAggregate
      • Append, SetOp Intersect, SetOp Except
      • Subquery, Subscan, Function Scan, Result, Materialize, Limit
  • GUC parameters (GUC items of grouping-comments of postgresql.conf fellow)
    • RESOURCE USAGE
    • WRITE AHEAD LOG
    • QUERY TUNING
    • RUNTIME STATISTICS
    • LOCK MANAGEMENT
  • Tools in contrib
    • pg_controldata
    • oid2name, pgbench, pgstattuple, dbsize
  • internal architecture
    • MVCC, btree-index, WAL, shared buffer, path of a query
• Trubleshooting Section
  • Truble caces
    • (based on various causes) Dealing with performance degradation
    • (based on various causes) Dealing with server-down, data disappearance
    • Operation System's resource exhaustion
    • XID wraparound
    • OID wraparound
  • Recover techniques
    • Reset write-ahead log
    • REINDEX system tables

Go back to the top of this page