HN Explorer

Ask HN: How do you automate PostgreSQL?

6 points by dcgoss ↗ HN
It seems that every Postgres tutorial on the internet involves SSHing into instances and arduously setting up the database by hand. The setups are fragile, and if the server went down you would have to set everything up by hand again and then deal with data loss.

How does one automate the creation and management of a Postgres deployment? Does anyone use Ansible or Docker?

Services like RDS and databaselabs.io offer Postgres with automatic backups, replication, etc via a console - how do they manage it?

How do you manage yours?

5 comments

[ 2.8 ms ] story [ 37.7 ms ] thread
stray
Anything you can do by hand -- you can do with a shell script. Anything you can do with a simple shell script can be done with ansible, chef, fabric, or whatever else you might want to use.

I use chef.

But years ago when before any of those existed, I used CFEngine. And before that, perl scripts. And before that, shell scripts.

They've all worked just fine.

Those tutorials are teaching you how PostgreSQL works. And you need to know that before you can effectively automate anything.

As my band teacher used to say back in school: "if you can't say it, you can't play it".

tylercubell
Vagrant with Ansible as the provisioner. All it takes is one command and the environment is ready. It would be good in your case (following tutorials) because you can destroy the box and start over without any consequences. I would also recommend pgAdmin.
aprdm
I like to use Ansible to automate a PostgreSQL instance / creation. Be it in a host or virtual machine or aws (rds)
afarrell
If you want setting up and maintaining servers to feel more like writing and refactoring a codebase, wrote a step-by-step tutorial that you are probably interested in. It is over at https://amfarrell.com/saltstack-from-scratch And the git repo with a branch and tests for the start & end of each step is at https://github.com/amfarrell/saltstack-from-scratch/

It walks you through setting up local VMs, writing configuration files, writing integration tests for those configuration files, and deploying to DigitalOcean. At the end you have a mental model of how configuration management works that you can apply generally. You also have a django site running behind nginx on top of a Postgres database.

floppydisk
I managed >1TB Postgres installs using not-fancy tools and we did just fine. The How:

Installing databases was pretty easy. We wrote DDLs for our databases and created shell scripts that would leverage pgsql via the command line to create the databases with all our desired extensions, plugins, functions, and schemas. Repeatable every time by even the least trained IT guy on the staff. Data loading was a little more finicky, but that was easily doable by using pgdump if we were coming from an existing database and it was executable by shell script.

Stats wise, we wrote stored functions in the database that would leverage the built in, and excellent, stats API. Our management system would execute a shell script that leveraged PGSQL to execute the stored procedure(s) and pass the resulting values to our graphing and system monitoring software. Standard setup on each DB box. Worked like a champ.

For managing configuration files, we preferred to be hands on and edit pgconf directly as each database box ended up being a little different in terms of needs and we would annotate the configuration file with notes to selves about why settings were the way they were.

How do other services do this via console? They interface with the database API directly (like you can do) and make an interface to trigger the commands they're executing.