postgresql

Overview Percona Distribution for PostgreSQL includes a set of extensions that have been tested to work together. These extensions enable you to efficiently solve essential practical tasks to operate and manage PostgreSQL. Reference The pg_stat_statements and pg_stat_monitor extensions are used to collect and monitor query statistics in PostgreSQL, but they have key differences in functionality, focus, and efficiency. pg_stat_statements: This is an official extension included with PostgreSQL. Its main purpose is to track the performance of queries executed in the database....

Parameters shared_buffers: The shared_buffers parameter defines how much RAM is reserved for PostgreSQL operations. Increasing it can improve performance, especially if you increase the number of connections. PostgreSQL recommends setting shared_buffers to between 25% and 40% of the total RAM. With 128 GB of RAM, you can configure it to a value close to 32 GB or 40 GB, depending on system requirements and workload. 1 ALTER SYSTEM SET shared_buffers = '32GB'; work_mem: Defines the memory allocated for temporary operations (such as sorting and joins) per session....

Backup db pg_dump -h -p -U postgres -d db_name -F c -b -v -f file.sql -d db_name -F c: more efficient -b: binary data incluyed (blobs or binary objects) -v: more detailed -f: file_name Backup roles 1 pg_dumpall -h <origin ip> -p <origin port> -U postgres --roles-only -f roles.sql Restore drop db before recovery 1 2 postgres=# DROP DATABASE db_name; DROP DATABASE create db 1 createdb -h <destination ip> -p <destination port> -U postgres db_name restore pg_restore -h -p -U postgres -d db_name -v file....

init postgresql db from scratch 1 2 3 4 5 systemctl stop postgresql rm -rf /var/lib/postgresql/15/main/* export PATH=$PATH:/usr/lib/postgresql/15/bin pg_ctl -D /var/lib/postgresql/15/main/ initdb init db manually 1 sudo -u postgres /usr/lib/postgresql/17/bin/postgres -D /var/lib/postgresql/17/main

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....