Mastering PostgreSQL Administration [pdf]
1–10 of 56 posts
Re: Mastering PostgreSQL Administration [pdf]
#2I debated running my own Postgres cluster for a side project, but ended up paying for Digitalocean’s managed offering.
I’d like to hear from anyone running their own Postgres. Why did you choose to do that vs pay for a managed instance? How much of a hassle has it been? Is it something you really need significant Postgres experience to do well (to achieve security, backups, high availability, etc)? Biggest gotchas to watch out for? Best material to read?
Thanks in advance.
Re: Mastering PostgreSQL Administration [pdf]
#3I debated running my own Postgres cluster for a side project, but ended up paying for Digitalocean’s managed offering. I’d like to hear from anyone running their own Postgres. Why did you choose to do that vs pay for a managed instance? How much of a hassle has it been? Is it something you really need significant Postgres experience to do well (to achieve security, backups, high availability, etc)? Biggest gotchas to…
I run both Postgresql and Oracle databases and Postgresql gives me no trouble whatsoever. It just works. I learnt as I went along and never had any trouble.
I keep a copy via streaming replication and do daily backups.
Re: Mastering PostgreSQL Administration [pdf]
#4I debated running my own Postgres cluster for a side project, but ended up paying for Digitalocean’s managed offering. I’d like to hear from anyone running their own Postgres. Why did you choose to do that vs pay for a managed instance? How much of a hassle has it been? Is it something you really need significant Postgres experience to do well (to achieve security, backups, high availability, etc)? Biggest gotchas to…
1) costs,
2) ultimate observability and performance tuning - with self-managed Postgres, you have full control and can tune it better, monitor all the details, put pgBouncer or Odyssey on database nodes, there is no need to spend hours and sometimes days or even weeks with support engineers trying to understand what's happening under the hood and how to fix/improve it (but you need skills -- if you don't, managed service support may be beneficial, of course if you're lucky to reach experienced engineer),
3) specific need: have replication connection from outside, control backups, etc.
That being said, the benefits of managed offerings are clear and sound: you don't waste your time for many DBA tasks, paying some extra and losing some control. Many companies choose this. Some change their mind over time and move back to self-managed, or use hybrid approach.
One more thing. If you need to maintain dozens or more databases, you need automation. This leads to either managed offerings, or k8s (look at StackGres.io).
Re: Mastering PostgreSQL Administration [pdf]
#5I debated running my own Postgres cluster for a side project, but ended up paying for Digitalocean’s managed offering. I’d like to hear from anyone running their own Postgres. Why did you choose to do that vs pay for a managed instance? How much of a hassle has it been? Is it something you really need significant Postgres experience to do well (to achieve security, backups, high availability, etc)? Biggest gotchas to…
Those are questions where the answer depends a lot on what you're using the database for and what your requirements are. I'm not the best person to evaluate how much of a "hassle" setting up things has been, since I'm quite comfortable administering PostgreSQL nowadays, so I'm biased.
When setting up backups you'll want to know what you're backing up and how to restore it; Do you need to be able to restore to an arbitrary point in time, or to a snapshot of the data at the time of backup? Or do you need to be able to perform partial restores?
For HA, you'll at least need to specify if any data loss is acceptable; whether the system should perform automatic recovery in the event of a real failure, or if the HA merely exists for operational purposes where failovers are controlled events.
For example. PostgreSQL streaming replication is reliable and easy to understand, but it's asynchronous by default and you do need to monitor that the replica stays current with the primary. It's just fine as is for basic operational HA so that you can perform maintenance with minimal (seconds) downtime, but if you want a fancy autohealing system that guarantees commits are never lost, you'll likely need a more complicated setup with synchronous replication and real clustering software.
Generally, the stricter your requirements get (and the more data you deal with), the more effort it takes to implement it yourself.
Re: Mastering PostgreSQL Administration [pdf]
#6I debated running my own Postgres cluster for a side project, but ended up paying for Digitalocean’s managed offering. I’d like to hear from anyone running their own Postgres. Why did you choose to do that vs pay for a managed instance? How much of a hassle has it been? Is it something you really need significant Postgres experience to do well (to achieve security, backups, high availability, etc)? Biggest gotchas to…
1) costs, 2) ultimate observability and performance tuning - with self-managed Postgres, you have full control and can tune it better, monitor all the details, put pgBouncer or Odyssey on database nodes, there is no need to spend hours and sometimes days or even weeks with support engineers trying to understand what's happening under the hood and how to fix/improve it (but you need skills -- if you don't, managed ser…
There is also https://github.com/CrunchyData/postgres-operator which seems more mature.
Re: Mastering PostgreSQL Administration [pdf]
#7I debated running my own Postgres cluster for a side project, but ended up paying for Digitalocean’s managed offering. I’d like to hear from anyone running their own Postgres. Why did you choose to do that vs pay for a managed instance? How much of a hassle has it been? Is it something you really need significant Postgres experience to do well (to achieve security, backups, high availability, etc)? Biggest gotchas to…
Most managed offerings run on virtual machines where performance is 1/10th of what you'd get on a bare-metal machine rented for the same cost. As an example, I'm currently working on a project where we have an Azure managed Postgres DB for a staging environment. It costs ~50$/month and is an order of magnitude slower than my entry-level MacBook. That amount of money would give you fast NVME storage, 8 core dedicated CPU (none of this cloud "vCPU" bullshit) and 64GB of RAM on bare-metal: https://www.soyoustart.com/en/offers/2009sys23.xml
Re: Mastering PostgreSQL Administration [pdf]
#8Earlier quoted context omitted.
1) costs, 2) ultimate observability and performance tuning - with self-managed Postgres, you have full control and can tune it better, monitor all the details, put pgBouncer or Odyssey on database nodes, there is no need to spend hours and sometimes days or even weeks with support engineers trying to understand what's happening under the hood and how to fix/improve it (but you need skills -- if you don't, managed ser…
There is also https://github.com/CrunchyData/postgres-operator which seems more mature.
Right, and operator from Zalando https://github.com/zalando/postgres-operator (fresh good comparison, in Russian, but google translate should work well on such texnical texts https://habr.com/ru/company/flant/blog/520616/).
All of them look interesting and developing rapidly.
Re: Mastering PostgreSQL Administration [pdf]
#9I debated running my own Postgres cluster for a side project, but ended up paying for Digitalocean’s managed offering. I’d like to hear from anyone running their own Postgres. Why did you choose to do that vs pay for a managed instance? How much of a hassle has it been? Is it something you really need significant Postgres experience to do well (to achieve security, backups, high availability, etc)? Biggest gotchas to…
I posted this link on HN because I am hosting a Postgres instance at our HPC cluster for some of our users storing genomic data in it for analysis. I’d like to learn more about managing it. So far, my experience has been that I can either dip my toe in admin details and have it running and choose to dive deeper as needed. It has been a pleasant experience operating a Postgres cluster. Production experience is always a different thing.
Re: Mastering PostgreSQL Administration [pdf]
#10I debated running my own Postgres cluster for a side project, but ended up paying for Digitalocean’s managed offering. I’d like to hear from anyone running their own Postgres. Why did you choose to do that vs pay for a managed instance? How much of a hassle has it been? Is it something you really need significant Postgres experience to do well (to achieve security, backups, high availability, etc)? Biggest gotchas to…
The first consideration should be backups. This can be pretty easy if your database is small and you're okay with losing a few hours or a day of data if something happens. Then it's simply a matter of having a cron job dumping the database and backing those dumps up via some common backup tool. A proper backup with point-in-time recovery is not that hard to set up, but it does take some time to read up on the topic and configure it. It's certainly a bit of work compared to a managed version, but not that much.
High availibility is a bit more complicated to set up with Postgres, and I don't have much experience with that part. Plain replication isn't too difficult as far as I understand, but automated failover is not in the core tools, so you would use an external tool that does this as far as I understand.
My experience with Postgres in general is that it doesn't really need any attention if you're at a reasonably low level of traffic. And reasonably low doesn't mean toy database, if you're not doing anything horribly inefficient. If you have a high write volume you might have to pay attention to vacuum settings and tweak them.
If you have a high income, might just make sense to pay for a managed version for a side project if you aren't specifically interested in learning more about managing Postgres. If you're e.g. a student I think you can get pretty far with managing your own Postgres on a small VM.
The Postgres documentation is very good, I'd certainly read the parts about backups and replication there.