OSS-DB SilverStudy guide

Survey the full feature set of PostgreSQL, the nature of OSS (open source software) and the PostgreSQL license, and how the community operates and how to participate in it. Also cover the distinction between major version and minor version, the roughly annual release cycle, the support policy (updates for five years), and how to submit a bug report to the community.

Organize the basic concepts of the relational data model (tables, rows, columns, primary keys, foreign keys) and the role a DBMS plays. Cover general knowledge of SQL, and its three-way classification by purpose: DDL (data definition language), DML (data manipulation language), and DCL (data control language). Also cover the idea of normalization in database design.

Learn initdb, the very first step in getting PostgreSQL running, and the concept and structure of the database cluster it creates. We also cover the two template databases automatically generated at cluster creation, template0 and template1, and how their roles differ.

Learn the commands bundled standard with PostgreSQL: pg_ctl for starting, stopping, and reloading the server; createuser/dropuser for role management; createdb/dropdb for database management; the psql interactive terminal; pg_config for build information; pg_controldata for cluster control information; pg_isready for checking server liveness; pg_resetwal for resetting the WAL; and psql's meta-commands (\d, \l, \dt, \du, \c, \?, \h, \timing).

Learn postgresql.conf, which governs overall server behavior (syntax, units, include; connection and authentication settings listen_addresses/port/max_connections; logging settings logging_collector/log_destination), pg_hba.conf, which defines per-client authentication methods (trust, md5, scram-sha-256, peer, and the significance of line order), and SET/SHOW for inspecting and changing values at runtime.

Learn PostgreSQL backup methods systematically: SQL-level pg_dump/pg_dumpall/pg_restore, filesystem-level backup, the concept of PITR (point-in-time recovery), WAL (write-ahead log) and WAL archiving (archive_command), the non-exclusive low-level backup approach, and data import/export via SQL COPY and psql's \copy.

Learn server start/stop, CREATE/ALTER/DROP for database roles/users (ROLE/USER), VACUUM/ANALYZE for managing table bloat and statistics, autovacuum which automates them, system information functions that report server state, the metadata sources information_schema and pg_catalog (the system catalog), and GRANT/REVOKE for privilege management.

Learn the clauses of the SELECT statement (WHERE, ORDER BY, GROUP BY, HAVING, DISTINCT, LIMIT, OFFSET), combining tables with JOIN (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN), embedding queries with subqueries, combining result sets with UNION/INTERSECT/EXCEPT, and modifying data with INSERT/UPDATE/DELETE.

Learn to design and alter tables with CREATE TABLE/ALTER TABLE/DROP TABLE, key data types (INTEGER/BIGINT/NUMERIC/VARCHAR/TEXT/BOOLEAN/DATE/TIMESTAMP/JSON/JSONB), integrity constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL), search-accelerating indexes, views/materialized views, triggers, sequences, schemas, tablespaces, partitioning, functions via PL/pgSQL, and the basics of streaming/logical replication.

Learn to summarize multiple rows into one value with aggregate functions (count, sum, avg, max, min), compute with arithmetic functions/operators, manipulate strings with string functions (char_length, length, lower, upper, substring, replace, trim, the || concatenation operator, the LIKE predicate), and handle dates/times with date/time functions (age, now, current_date, current_timestamp, extract, to_char).

Learn how transactions bundle multiple SQL statements into one unit of work (BEGIN, COMMIT, ROLLBACK), how isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) control what concurrent transactions can see, explicit locking with the LOCK statement, the difference between row locks and table locks, and deadlocks where multiple transactions wait on each other forever.