100
100
automatically generated user for `db-admin` relations is a PostgreSQL
103
## Database Permissions and Disaster Recovery
105
⚠ These two topics are entwined, because failing to follow best
106
practice with your database permissions will make your life difficult
107
when you need to recover after failure.
109
_Always_ set the 'roles' relationship setting when joining a
110
relationship. _Always_ grant permissions to database roles for _all_
111
database objects your charm creates. _Never_ rely on access permissions
112
given directly to a user, either explicitly or implicitly (such as being
113
the user who created a table). Consider the users you are provided by
114
the PostgreSQL charm as ephemeral. Any rights granted directly to them
115
will be lost if relations are recreated, as the generated usernames will
116
be different. _If you don't follow this advice, you will need to
117
manually repair permissions on all your database objects after any of
118
the available recovery mechanisms._
120
Learn about the SQL `GRANT` statement in the excellect [PostgreSQL
123
### block-storage-broker
125
If you are using external storage provided by the block storage broker,
126
recovery or a failed unit is simply a matter of ensuring the old unit
127
is fully shut down, and then bringing up a fresh unit with the old
128
external storage mounted. The charm will see the old database there
131
If you are unable or do not wish to to simply remount the same
132
filesystem, you can of course copy all the data from the old filesystem
133
to the new one before bringing up the new unit.
137
PostgreSQL dumps, such as those that can be scheduled in the charm, can
138
be recovered on a new unit by using 'juju ssh' to connect to the new unit
139
and using the standard PostgreSQL `pg_restore(1)` tool. This new unit must
140
be standalone, or the master unit. Any hot standbys will replicate the
141
recovered data from the master.
143
You will need to use `pg_restore(1)` with the `--no-owner` option, as
144
users that existed in the old service will not exist in the new
149
If you had configured WAL-E, you can recover a WAL-E backup and replay
150
to a point in time of your choosing using the `wal-e` tool. This
151
will recover the whole database cluster, so all databases will be
154
If there are any hot standby units, they will need to be destroyed
155
and recreated after the PITR recovery.
103
158
## During db-relation-joined
105
160
### the client service provides: