PostgreSQL – Administration

Maintenance Tasks

• Keep autovacuum enabled most of the time
• VACUUM regularly as well
• Check for unused indexes

Warnings: (unless you know what you are doing …)

• Avoid using VACUUM FULL
• REINDEX CONCURRENT does not exist (yet)
• Do not use HASH INDEXES
• Do not use fsync = off

Read the manual

• PostgreSQL docs are about 2000 pages
• Technically accurate
• Written and maintained by the developers

Security

• Superuser is too powerful for most use cases (SECURITY DEFINER functions)
• Use a distinct userid for replication
• GRANT minimal access rights

Upgrades

• Maintenance releases happen about every 3 months
• For best security – upgrade to latest maintenance release
• Major release upgrades are harder
• UDR technology (thats UniDirectional Replication) will make Major release upgrades much easier from 9.4+

Extensions

• PostgreSQL is designed to be extensible
• Many new features enabled via extensions (EG: pgaudit, postgis)
• Use them!

Scripts

• GUIs do not allow you to apply changes in a transaction or easily record your actions
• Use scripts for any administrative changes
• Test them, before applying

Schema Change: adding a foreign key now split into two parts :-

• ALTER TABLE foo ADD FOREIGN KEY … REFERENCES bar NOT VALID;
• ALTER TABLE foo VALIDATE CONSTRAINT fook;

1. Apply constraint going forward (with quick write-lock)
2. Check data already in the table (a background task)