Parameters max_worker_processes: parallel processes. Usually, default value is low. Load stress with pgbench Reference pgbench is provided with postgres-client package. 1 pgbench -h <host> -U <user> -T 60 -c 10 -j 2 <dbname> First step: Initialise db The pgbench_branches table in the target database. This table is automatically created during initialisation with pgbench. To solve this problem, you need to initialise the database with the following command: -i initialise pgbench tables (pgbench_branches, pgbench_accounts, pgbench_tellers, etc....

Databases and Schemas in PostgreSQL Reference DBs In PostgreSQL, a “cluster” does not refer to a group of networked servers (as it might in other databases or distributed systems). Instead, a cluster in PostgreSQL refers to a set of databases that share the same data directory, a common configuration, and are managed by a single PostgreSQL server instance. Dedicated Data Directory The cluster is defined by a data directory initialized with initdb....

AUTOVACUUM in PostgreSQL Configuring Autovacuum The autovacuum process in PostgreSQL ensures that tables remain healthy by automatically performing maintenance tasks. For large tables, it is recommended to adjust the autovacuum_work_mem parameter. Example Configuration: 1 2 3 postgresql: parameters: autovacuum_work_mem: '1GB' What is VACUUM in PostgreSQL? VACUUM is a command and operation in PostgreSQL used for cleaning and maintaining tables in the database. Its primary goals are: 1. Recovering Space When operations like DELETE or UPDATE are performed, records are not physically deleted or overwritten immediately....

Write-Ahead Log (WAL) in PostgreSQL Reference WAL (Write-Ahead Log) files are an essential part of the transaction logging system. They are used to ensure the integrity and consistency of the database, especially in cases of failures or unexpected interruptions. WAL stands for Write-Ahead Logging. It is a mechanism that ensures any changes to the database data are first recorded in a log file (WAL) before being physically applied to the tables or indexes on disk....

Enable additional context information in logs Adjust the following parameters in postgresql.conf or with ALTER SYSTEM: 1 ALTER SYSTEM SET log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '; Explanation of the formats: %t → Timestamp. %p → Process ID. %u → Database user. %d → Database the user connected to. %a → Application name. %h → Client address. Reload configuration Reload PostgreSQL configuration to apply the changes: 1 SELECT pg_reload_conf(); Disable connection and disconnection logging 1 2 3 4 5 6 7 ALTER SYSTEM SET log_connections = 'off'; ALTER SYSTEM SET log_disconnections = 'off'; SELECT pg_reload_conf(); SHOW log_connections; SHOW log_disconnections; If you prefer to do it manually in the postgresql....

Kill transactions To review and terminate (kill) active transactions in PostgreSQL, you can use the system views pg_stat_activity and the function pg_terminate_backend. 1 2 3 4 5 6 SELECT pid, usename, datname, application_name, client_addr, backend_start, state FROM pg_stat_activity; SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = <your_pid>; If you want to kill all inactive transactions that have been open for more than 10 minutes, you can do something like this: 1 2 3 4 5 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND now() - query_start > interval '10 minutes' AND pid <> pg_backend_pid(); pg_stat_activity can show connections that are waiting for locks....

Active connections 1 SELECT pid, usename, datname, application_name FROM pg_stat_activity WHERE usename = 'user_name'; Force user logout 1 2 3 4 5 6 REVOKE CONNECT ON DATABASE db_name FROM PUBLIC; SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'db_name' AND pid <> pg_backend_pid(); Change Password 1 ALTER ROLE rol_name WITH PASSWORD 'nueva_contraseña'; If you are changing the password for the role you are connected with, you can use: 1 \password role_name To verify that the password has been changed successfully, you can check the roles in the pg_roles table:...

HAProxy for Patroni HAProxy checks the nodes’ status using the httpchk option, but the replicas of your Patroni cluster do not respond to the HTTP check on port 8008 in the same way as the primary node. In a Patroni cluster, only the leader should respond as active on this HTTP port, while the replicas might not, causing HAProxy to mark them as “down.” Adjust the HAProxy configuration to properly check the nodes’ status based on their roles within the cluster (leader or replica)....

Overview Keepalived, which is mainly used to provide high availability by implementing the VRRP (Virtual Router Redundancy Protocol). Your configuration is commonly used to monitor services like HAProxy and automatically switch between servers in case of failures. 1 2 3 4 5 6 7 apt update && sudo apt upgrade -y apt install keepalived -y vi /etc/keepalived/keepalived.conf systemctl restart keepalived systemctl enable keepalived INTERFACES : Ensure that ethX is the correct interface on your server....

Overview Reference While PostgreSQL’s pg_hba.conf is the file responsible for restricting connections, when listen_addresses is set to * (wildcard), it is possible to discover the open port on 5432 using nmap and learn the database exists, thereby possibly opening the server up for an exploit. Setting it to the an IP address prevents PostgreSQL from listening on an unintended interface, preventing this potential exploit. Monitoring and Continuous Optimization Use tools like pg_stat_activity and pg_stat_database to monitor connection usage and adjust the values as needed:...