[go: up one dir, main page]
Scribd
Upload
33 views3 pages

PostgreSQL DBA Windows Guide

This document outlines essential PostgreSQL DBA tasks on Windows, including installation, monitoring, replication configuration, and backup management. It also covers security measures, performance tuning, and resolving production data issues. Additionally, it provides guidance on tablespace management and scheduling maintenance tasks.

Uploaded by

remotarthur
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
33 views3 pages

PostgreSQL DBA Windows Guide

This document outlines essential PostgreSQL DBA tasks on Windows, including installation, monitoring, replication configuration, and backup management. It also covers security measures, performance tuning, and resolving production data issues. Additionally, it provides guidance on tablespace management and scheduling maintenance tasks.

Uploaded by

remotarthur
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

PostgreSQL DBA Tasks on Windows

1. Install, Monitor, and Maintain PostgreSQL & Configure Repmgr

- Install PostgreSQL:
1. Download PostgreSQL from official site (https://www.postgresql.org/download/windows/).
2. Run the installer and follow the setup.
3. Open pgAdmin or psql (SQL Shell).

- Monitor PostgreSQL:
SELECT * FROM pg_stat_activity;
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database;

- Configure Replication using pg_basebackup:


1. Enable replication in postgresql.conf:
wal_level = replica
max_wal_senders = 3
2. Restart PostgreSQL service.
3. Run backup:
pg_basebackup -h primary_host -U postgres -D "C:\pg_replica" -P -X stream

2. Identify Potential Issues in the Database

- Check logs at: C:\Program Files\PostgreSQL\<version>\data\log


- Detect long-running queries:
SELECT pid, age(clock_timestamp(), query_start), query FROM pg_stat_activity WHERE state != 'idle';

3. Maintain Backups & Perform Disaster Recovery

- Backup:
pg_dump -U postgres -F c -b -v -f C:\backup.dump mydatabase

- Restore:
pg_restore -U postgres -d mydatabase -v C:\backup.dump
4. Monitor Security & Prevent Unauthorized Access

- Restrict Remote Access in pg_hba.conf:


host all all 192.168.1.0/24 md5

- Enable SSL in postgresql.conf:


ssl = on

5. Schedule Consistent Maintenance

- Run vacuum analyze:


VACUUM ANALYZE;

- Automate using Task Scheduler:


"C:\Program Files\PostgreSQL\<version>\bin\vacuumdb.exe" -U postgres --all --analyze

6. Performance Tuning for Servers and Queries

- Optimize Queries:
EXPLAIN ANALYZE SELECT * FROM large_table WHERE column = 'value';

- Adjust postgresql.conf:
shared_buffers = 512MB
work_mem = 64MB
maintenance_work_mem = 256MB

7. Resolve Production Data Issues

- Fix bloated tables:


VACUUM FULL table_name;

- Check index usage:


SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;

- Fix corrupt data:


REINDEX DATABASE mydatabase;

8. Tablespace Management
- Create a Tablespace:
CREATE TABLESPACE fast_storage LOCATION 'C:\pg_tablespaces';

- Assign a Tablespace:
CREATE TABLE my_table (id SERIAL, data TEXT) TABLESPACE fast_storage;

You might also like