This project showcases the setup of a PostgreSQL cluster configured with master-slave streaming replication. It features a monitoring and alerting system using Prometheus, Grafana, PostgreSQL Exporter, Node Exporter and Alertmanager to maintain high performance and enable proactive issue detection.
Two deployment approaches are supported:
- Docker Compose.
- Ansible across three separate Ubuntu 22.04 nodes:
- A primary database node
- A replica database node
- A dedicated monitoring node
create a .env and specify your passwords:
PG_MASTER_PASS=
PG_REPLICA_PASS=
GF_SECURITY_ADMIN_PASSWORD=
You also need to fill the alertmanager config file with your personal data.
Run docker-compose up.
├── ansible.cfg
├── inventory
│ ├── group_vars
│ │ ├── all.yml
│ │ └── postgres_servers.yml
│ ├── host_vars
│ │ ├── pg_primary.yml
│ │ ├── pg_replica_01.yml
│ │ └── prometheus_grafana.yml
│ └── main.yml
├── log
│ └── ansible.log
├── playbooks
│ └── main.yml
├── roles
│ ├── common
│ │ └── tasks
│ │ └── main.yml
│ ├── grafana
│ │ ├── files
│ │ │ └── tmp
│ │ │ └── gpg.key
│ │ └── tasks
│ │ └── main.yml
│ ├── node_exporter
│ │ ├── tasks
│ │ │ └── main.yml
│ │ └── templates
│ │ └── node_exporter.service.j2
│ ├── postgres_exporter
│ │ ├── tasks
│ │ │ └── main.yml
│ │ └── templates
│ │ └── postgres_exporter.service.j2
│ ├── postgresql
│ │ ├── tasks
│ │ │ └── main.yml
│ │ └── templates
│ │ └── postgresql.conf.j2
│ └── prometheus
│ ├── tasks
│ │ └── main.yml
│ └── templates
│ ├── prometheus.service.j2
│ └── prometheus.yml.j2
Configure youre IP addresses here
For sake of simplicity, the postgresql passwords have been set to 123456. You can change it here
to run the playbook:
cd postgresql_replication_ansible
ansible-playbook playbooks/main.yml
prometheues: http://{your ip address}:9090
Grafana: http://{your ip address}:3000
postgres_exporter: http://{your ip address}:9187/9188