Praktik pengelolaan relational database management systems pada PostgreSQL. Dilengkapi dengan pembahasan studi kasus terkait perintah dasar query SQL, pembuatan desain database, stored procedure, trigger, dan event. Di akhir akan menerapkan teknik optimasi, pengamanan, backup, restore, dan replikasi database.
CONTENT
1. Cluster Management Fundamentals
1.1 Installing PostgreSQL 13 using RPMs on CentOS
1.2 Initializing a PostgreSQL cluster using initdb
1.3 Starting a PostgreSQL cluster using pg_ctl
1.4 Shutting down a PostgreSQL cluster using different shutdown modes
1.5 Identifying a PostgreSQL data directory and its contents
1.6 Moving pg_wal to another location
1.7 Running the psql client and some psql shortcuts
1.8 Locating the Postgres configuration file
1.9 Modifying the location of a postgresql.conf file in PostgreSQL
1.10 Modifying the postgresql.auto.conf file in PostgreSQL
1.11 Enable archiving in PostgreSQL
2. Cluster Management Techniques
2.1 Creating and dropping databases
2.2 Locating a database and a table on the file system
2.3 Creating a schema in PostgreSQL
2.4 Checking table and index sizes in PostgreSQL
2.5 Creating tablespaces
2.6 Moving tables to a different tablespace
2.7 Creating a user in PostgreSQL
2.8 Dropping a user in PostgreSQL
2.9 Assigning and revoking a privilege to/from a user or a role
2.10 Creating a group role for role-based segregation
2.11 MVCC implementation and VACUUM in PostgreSQL
3. Backup and Recovery
3.1 Backing up and restoring a database using pg_dump and pg_restore
3.2 Backing up and restoring one or more tables using pg_dump and pg_restore
3.3 Backing up and restoring globals or an entire cluster using pg_dumpall and psql
3.4 Parallel backup and restore using pg_dump and pg_restore
3.5 Backing up a database cluster using pg_basebackup
3.6 Restoring a backup taken using pg basebackup
3.7 Installing pgBackRest on CentOS/RedHat OS
3.8 Installing pgBackRest on Ubuntu/Debian OS
3.9 Backing up a database cluster using pgBackRest
3.10 Restoring a backup taken using pgBackRest
4. Advanced Replication Techniques
4.1 Setting up streaming replication in PostgreSQL 13
4.2 Adding a delayed standby for faster point-in-time recovery
4.3 Promoting a standby to a master
4.4 Adding a cascaded streaming replica
4.5 Promoting a standby in a replication cluster with multiple standby servers
4.6 Using pg_rewind to re-synchronize a demoted master
4.7 Enabling synchronous streaming replication
4.8 Setting up logical replication in PostgreSQL 13
5. High Availability and Automatic Failover
5.1 Automatic failover using Patroni
5.2 Enabling distributed consensus using etcd
5.3 Avoiding split-brain using Watchdog/softdog
5.4 Installing Patroni along with its Python dependencies
5.5 Creating a Patroni configuration file
5.6 Starting Patroni as a service using systemd
5.7 Initializing a PostgreSQL primary database using Patroni
5.8 Adding a standby to a Patroni cluster
5.9 Performing a manual switchover using Patroni
6. Connection Pooling and Load Balancing
6.1 Installing pgBouncer on a Linux server
6.2 Creating a pgBouncer configuration file
6.3 Configuring the pool settings on pgBouncer
6.4 Starting and stopping the pgBouncer service
6.5 Installing HAProxy on Linux servers
6.6 Using xinetd to detect a primary or a standby
6.7 Creating an HAProxy configuration file
6.8 Starting and stopping the HAProxy service
6.9 Building a robust HA cluster using Patroni, pgBouncer, and HAProxy
7. Securing through Authentication
7.1 Securing client connections using the pg_hba.conf file
7.2 Performing authorization using roles and privileges
7.3 Setting up row-level security
7.4 Configuring encryption of data over the wire using SSL
7.5 Enabling certificate authentication using SSL
7.6 Auditing PostgreSQL through logging
7.7 Auditing PostgreSQL using pgaudit
7.8 Setting up object-level auditing using pgaudit
8. Logging and Analyzing PostgreSQL Servers
8.1 Setting up slow query logging in PostgreSQL
8.2 Logging runtime execution plans in PostgreSQL using auto_explain
8.3 Logging locks, waits, and temp in PostgreSQL
8.4 Logging autovacuum and analyzing activity in PostgreSQL
8.5 Generating a pgBadger report
8.6 Configuring pg_stat_statements as an extension
8.7 Query analysis using pg_stat_statements
8.8 Getting the kernel-level statistics of a query using pg_stat_kcache
9. Critical Services Monitoring
9.1 Installation of Grafana and its dependencies
9.2 Prometheus as a data source on the monitoring server
9.3 Configuring Node Exporter on Postgres servers to monitor operating system metrics
9.4 Adding metrics being collected using node_exporter to Prometheus
9.5 Collecting PostgreSQL metrics using postgres_exporter
9.6 Adding metrics exposed by postgres_exporter to Prometheus
9.7 Importing a dashboard for monitoring Linux metrics
9.8 How to import a dashboard for monitoring Postgres metrics
9.9 Adding custom queries to postgres_exporter
10. Extensions and Performance Tuning
10.1 Installing and creating pg_repack to rebuild objects online
10.2 How to rebuild a table online using pg_repack
10.3 How to rebuild indexes of a table online using pg_repack
10.4 Moving a table or an index to another tablespace online
10.5 Warming up the cache using pg_prewarm
10.6 How to tune a function or a stored procedure using plprofiler
10.7 Capturing statements that require tuning using pg_stat_statements
10.8 Viewing the execution plans using EXPLAIN in PostgreSQL
11. Upgrades and Patches
11.1 Finding the difference between a major and minor release in PostgreSQL
11.2 Major version upgrade to PostgreSQL 13 using pg_dumpall
11.3 Major version upgrade to PostgreSQL 13 using pg_dump and pg_restore
11.4 Major version upgrade to PostgreSQL 13 using pg_upgrade with downtime
11.5 Major version upgrade to PostgreSQL 13 using pg_upgrade with hard links for seamless downtime
11.6 Installing the pglogical extension to upgrade older versions to PostgreSQL 13
11.7 Upgrading to PostgreSQL 13 using the pglogical extension
11.8 Upgrading to PostgreSQL 13 using logical replication and logical decoding
11.9 Updating the minor version of PostgreSQL 13
