6 ms·
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 somethi
by fabian2k 6y ago
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.
- throw0101a 6y ago> The first consideration should be backups. Useful to look at barman and see if it can meet one's needs: * https://www.pgbarman.org/ https://www.pgbarman.org/ * https://github.com/2ndquadrant-it/barman https://github.com/2ndquadrant-it/barman No sense re-inventing the wheel if it can be avoided.
- fabian2k 6y agoWAL-E/WAL-G are another option if you're looking for PITR.
- zomgwat 6y agoI've been very satisfied with Barman. I considered a variety of backup options including replication. Barman is a good alternative to replication if you can tolerate a small mount of downtime while you restore. For us, it takes less than 15 minutes to provision a new VM and restore from Barman. I’m ok with that downtime because it simplifies operations by eliminating the need to operate a secondary database sever and manage replication. Also, Barman offers point-in time recovery (PITR). Lastly, we do a nightly logical dump which gets uploaded to a bucket (located at another cloud service).
- 3np 6y ago> High availibility is a bit more complicated to set up with Postgres, and I don't have much experience with that part. There is Patroni[0], which has a Docker-based setup template in Spilo[1]. I don't have expertise enough to say anything about these, except that I've been running an HA setup with Spilo for several months without issues so far. Would be interesting to hear what more experienced postgres people have to say about it :) Also we've opted to just use traefik (based on spilo service registration) rather than pgbouncer. [0]: https://github.com/zalando/patroni https://github.com/zalando/patroni [1]: https://github.com/zalando/spilo https://github.com/zalando/spilo
- Wintereise 6y agoPatroni is rather rough around the edges to get going, but works fairly well once you do. We manage a rather large cluster using it and for the most part, it works. Getting functional PITR that you can restore rapidly is honestly our biggest gripe with PG. WAL-G/Barman certainly do work, but when your DB is measured in terabytes, even bootstrapping a replica is very slow.