
Ask HN: How do you automate PostgreSQL? - dcgoss
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.<p>How does one automate the creation and management of a Postgres deployment? Does anyone use Ansible or Docker?<p>Services like RDS and databaselabs.io offer Postgres with automatic backups, replication, etc via a console - how do they manage it?<p>How do you manage yours?
======
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".

------
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](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/](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.

------
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)

