some frequently used PostgreSQL DBA scripts

Database administrators often use various scripts to monitor, manage, and troubleshoot PostgreSQL databases. Below are some frequently used PostgreSQL DBA scripts:

1. Check PostgreSQL Version:

 

SELECT version();

2. List all databases:

 

\l

3. Connect to a Database:

 

\c database_name

4. Show Tables in a Database:

 

\dt

5. Show Table Structure:

 

\d table_name

6. Display Indexes on a Table:

 

\di table_name

7. Check Database Size:

 

SELECT pg_size_pretty(pg_database_size(current_database()));

8. Check Table Size:

 

SELECT pg_size_pretty(pg_total_relation_size('table_name'));

9. List Active Connections:

 

SELECT * FROM pg_stat_activity;

10. Check Locks:

 

SELECT * FROM pg_locks;

11. Analyze a Query:

 

EXPLAIN ANALYZE SELECT * FROM table_name WHERE condition;

12. View Query Plans:

 

EXPLAIN SELECT * FROM table_name WHERE condition;

13. Check Autovacuum Status:

 

SELECT * FROM pg_stat_bgwriter;

14. Monitor Long-running Queries:

 

SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE now() - pg_stat_activity.query_start > interval '5 minutes';

15. Check Replication Status:

 

SELECT * FROM pg_stat_replication;

16. View PostgreSQL Configuration:

 

SHOW ALL;

17. Reload Configuration:

 

SELECT pg_reload_conf();

18. Backup Database using pg_dump:

 

pg_dump -U username -h localhost -d database_name -F c -f backup_file.dump

19. Restore Database using pg_restore:

 

pg_restore -U username -h localhost -d new_database -c backup_file.dump

20. Check Vacuum Status:

 

SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_all_tables;

These scripts cover a range of tasks from basic information retrieval to more advanced tasks like query optimization, performance monitoring, and database maintenance. Make sure to customize these scripts based on your specific requirements and PostgreSQL version. Always exercise caution when running scripts in a production environment.