Help me with some hints of what kind of automation I can do here in a simple postgres setup.
I would suggest you find at least one good reference book on PostgreSQL administration that covers everything involved with being a DBA. You will need to be aware of those regardless of automation you may choose to implement to avoid doing them manually. Also, automated metric collection tends to be important, and setting appropriate alerts (this include OS system administration scoped stuff like disk space) - regardless of whether you'd want to automated or manually resolve any events raised by those tools.
