postgresql

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

pgBouncer Column Description database The name of the database managed by PgBouncer. total_xact_count Total number of completed transactions processed since PgBouncer started. total_query_count Total number of completed queries processed since PgBouncer started. total_received Total bytes received from clients (incoming). total_sent Total bytes sent to clients (outgoing). total_xact_time Total time, in microseconds, that client connections spent processing transactions in the backend. total_query_time Total time, in microseconds, that client connections spent processing queries in the backend....

Databases and Schemas in PostgreSQL In PostgreSQL, databases and schemas are different but related concepts used to organize data: 1. Database The highest level of data organization in PostgreSQL. A database contains all the information, including schemas, tables, indexes, functions, views, and other data objects. Each database in PostgreSQL is independent; data in one database cannot be accessed from another database (unless using specific extensions like dblink or postgres_fdw). It serves as the main container for all related data objects....

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

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