How to Ride Elephants Safely: Working with PostgreSQL when your DBA is not around

Security

Here is the meta description: Master the art of PostgreSQL database management from scratch, covering essential concepts, configuration, and troubleshooting techniques to ensure database safety and performance when your DBA is unavailable.

Key takeaways

Always name your database and configure the search path to avoid confusion
Use pg_stat_activity to monitor database activity and identify slow queries
Set log_min_duration_statement to log queries that take more than a minute to execute
Use explain analyze to analyze query plans and identify performance bottlenecks
Vacuum databases regularly to maintain performance and prevent corruption
Configure workmem to allocate sufficient memory for queries
Use pg_basebackup to create database backups and pg_restore to restore them
Set max_wall_senders to control the number of concurrent queries
Use pg_ctl to start and stop the database
Configure hba files to control access to the database
Use pg_stat_statements to monitor query statistics and identify performance issues
Set log_line_prefix to customize the log format
Use pg_cancel_backend and pg_terminate_backend to cancel or terminate queries
Configure pg_hba.conf to control access to the database
Set log_min_error_statement to log error messages
Use pg_stat_activity to monitor database activity and identify slow queries
Set workmem to allocate sufficient memory for queries
Configure hba files to control access to the database
Use pg_cancel_backend and pg_terminate_backend to cancel or terminate queries
Use pg_stat_statements to monitor query statistics and identify performance issues
Set log_line_prefix to customize the log format
Configure pg_hba.conf to control access to the database
Set log_min_error_statement to log error messages
Set max_wall_senders to control the number of concurrent queries
Use pg_ctl to start and stop the database

How to Ride Elephants Safely: Working with PostgreSQL when your DBA is not around

More talks