The PostgreSQL charm, AWS and robustness

[Stuart Bishop]

On 28 May 2014 14:23, Tim Penhey <tim.penhey at canonical.com> wrote:


> To this effect, I want to make sure that the database that postgres is
> managing is persistent even if the machine it is running on dies.  I
> have taken a look at the config options for the postgres charm and I
> find them a little overwhelming.

Yes. I need to move the config.yaml details into the documentation
where I can give it some structure. PostgreSQL has a lot of features,
and a lot of knobs to control them... and there are plenty more that
haven't even been exposed yet. I've been wondering if the flat
namespace in config.yaml is the correct way of handling this over
abundance, or if instead I should tear out most of the options and
just include raw chunks of config provided by the user. Given some
users will want unit specific configuration (eg. tuning slave units
differently to the master), maybe they should just edit a file
directly on the filesystem that is included by the generated
postgresql.conf.


> I have to say I was very much encouraged to see that there are automatic
> backups taken of the database, but backups without a restore are little
> value.  I know that there is work on "actions" and that restoring the
> database is a wonderful use of an action with parameters, but I also
> know that we are not there yet and I want to start using things now :-)

To automatically restore a backup we really need actions. It would be
a nice use case to consider when designing the feature - can the
action stream in a 1TB backup stored locally, or will I have to pass a
path to some network resource that the unit running the action can
access? Does the action have a channel back to the user to report
status, since it could take days to complete?

The way of the future will be PITR, with filesystem dumps and WAL
information being shoved into an object store like Swift. An action
will allow winding back the clock (restoring a filesystem level dump
and replaying WAL). It would be nice if I could write this once for
all object stores, rather than having to write code specific to the
provider. The logical dumps (what we have now) still need to be
supported, but are less likely to be used for disaster recovery
purposes.


> What I want to make myself comfortable with, initially at least, is the
> robustness of my postgres database.
>
> Let's assume that I'm going to deploy on AWS, how do I go about manually
> configuring a machine so that the data directory for the postgres
> database is a persistent mount?

My understanding of the current state is that it is using the
'standard' volume management. You set volume-ephemeral-storage to
False, and volume-map to a yaml structure mapping unit names to volume
ids.

The charm will mount the volume. If there is no database found on the
volume, the existing one is moved from ephemeral storage to the new
mount. If there is already a database on the mount, the existing
database on ephemeral storage is swapped out and the mounted one used.

If you have a single unit in your PostgreSQL service, if it dies you
just need to startup a new unit with the same mount. Your data will be
there.

If you have multiple units, you can probably do the same thing but I'd
recommend using a new volume and letting replication rebuild your
database. There are issues with time travel making this non-trivial.
If your master unit failed, when you remove the unit the most up to
date slave is promoted to the master. If you bring a new unit online
using the ex-master's mount, then it may have had changes that had not
been replicated to the slaves. In PostgreSQL parlance, it will be in a
different timeline.

For the future, I believe there is work underway to replace most of
this with a common subordinate charm. I have even approved a merge
proposal for this, but merging it got blocked by getting the
subordinate charm landed (?). It seems much, much nicer. So far I've
left this code as I found it, since it is in production and written by
the people actually using it :)


> How do I restore the database from a backup?

To manually restore a backup, you 'juju ssh' into the unit, sudo to
the postgres user, and run pg_restore(1) to load the dump. This is
standard PostgreSQL, rather than anything specific to the charm. Given
the flexibility of restoring PostgreSQL dumps, the charm could provide
some simple actions (restore all, restore full db foo into bar), but
the more common tasks of restoring small pieces are going to remain
manual.

Automatic restore is quite scary, since restoration is a data loss
operation (from the perspective of any existing data being
overwritten). It is also something you should never have to do.
Replication and/or bringing up a new unit with the existing mount
covers most of the traditional disaster recovery scenarios. Backups
should really only be needed to recover from partial data loss
(deleting something you shouldn't), total data loss (corrupted file
system), or revisiting the past.


> If the machine dies, which I will manually do in testing, how do I go
> about bringing up a new machine, attaching the storage, deploying the
> charm and have it use the existing database that is there?  I'm guessing
> that there are orderings I need.

No orderings needed. Bring up a new unit and then set volume-map, or
set volume-map and then bring up the new unit. Whatever ;)

If the settings are screwed up, the hook aborts and you will need to
resolve the situation. juju could help here by allowing hooks more
flexibility than reporting just 'success/fail'. My life would be
easier with 'retry' (hook is waiting on hook on other units to run, or
a required operation is blocked) and 'invalid config' (retry this hook
after config-changed has successfully been run).


> Do I have to deploy a new database with a new persistent mount point and
> restore from a backup?  If I have to do that, what is the benefit of
> having the database itself in a persistent mount?
>
> Is there someone knowledgeable on the postgres charm and AWS that can
> help me with these points?
>
> BTW, I'm very happy to blog about this whole process once I have it all
> figured out :-)

Or give me a MP with an updated README :)

-- 
Stuart Bishop <stuart.bishop at canonical.com>