postgresql

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

Replication overview

Patroni cluster for Postgresql databases

High Availability Postgresql cluster

Quick commands to common operations

pgBackRest is a reliable backup and restore solution for PostgreSQL that seamlessly scales up to the largest databases and workloads.

pgBackRest example

Overview PMM Quickstart PMM HAProxy OPS Change the password for the default admin user. 1 docker exec -t pmm-server change-admin-password <new_password> Update With Watchtower: watchtower 1 docker run -v /var/run/docker.sock:/var/run/docker.sock -e WATCHTOWER_HTTP_API_UPDATE=1 -e WATCHTOWER_HTTP_API_TOKEN=your_watchtower_token --hostname=your_watchtower_host --network=pmm_default docker.io/perconalab/watchtower PMM and clients: 1 pmm-admin update Check version 1 2 3 4 5 6 7 8 9 10 11 12 13 14 $ curl -ku admin:admin2024 https://localhost/v1/version { "version": "2.43.1", "server": { "version": "2....