What are the basic skills and requirements a DevOps candidate should have when working with PostgreSQL? How to monitor it, deploy, etc.?
In my opinion an Operations candidate (DevOps is a culture, not a role) should be capable to perform at least the following actions:
- deploy postgres
- backup postgres
- restore postgres
- run some psql commands like select and some innerjoins
- knows what the effect on memory usage would be if a
select *is issued
- No manual labour, only automation. Deploy the postgres using a pipeline like gitlab
- infrastructure as code
- monitor it, disk usage, memory, cpu
- the difference between a relational and non-relational database
- see also this Q&A
- there are tools like Flyway that are capable of applying version control to databases. This could facilitate to run it in a CI/CD.
To add to 030's answer, and as a devops engineer who has had to do this a few times, learning how to set up replication and failover in an automated fashion is very important.
If you're learning, start by setting up replication by hand on some test servers
Then craft a method for automating the configuration. There are multiple ways to set up replication (they can run in parallel) and restoring a replicated database is pretty simple, but testing it out once and understanding how the file system is set up, what kinds of permissions users need to share data and what kinds of ACLs you need open between nodes to facilitate data transfer is very important.
You'd also want to craft a plan for what to do for promoting a new read/write master in case your primary node goes down.