def test_connect(self, psycopg2_connect, port, rels, local_unit):
psycopg2_connect.return_value = sentinel.connection
port.return_value = sentinel.local_port
self.assertEqual(postgresql.connect(), sentinel.connection)
psycopg2_connect.assert_called_once_with(
user="******", database="postgres", host=None, port=sentinel.local_port
)
psycopg2_connect.reset_mock()
local_unit.return_value = sentinel.local_unit
rels().peer = {
sentinel.local_unit: {"host": sentinel.local_host, "port": sentinel.local_port},
sentinel.remote_unit: {"host": sentinel.remote_host, "port": sentinel.remote_port},
}
self.assertEqual(postgresql.connect(sentinel.user, sentinel.db, sentinel.local_unit), sentinel.connection)
psycopg2_connect.assert_called_once_with(
user=sentinel.user, database=sentinel.db, host=None, port=sentinel.local_port
)
psycopg2_connect.reset_mock()
self.assertEqual(postgresql.connect(sentinel.user, sentinel.db, sentinel.remote_unit), sentinel.connection)
psycopg2_connect.assert_called_once_with(
user=sentinel.user, database=sentinel.db, host=sentinel.remote_host, port=sentinel.remote_port
)
def drain_master_and_promote_anointed():
# Wait until this anointed unit is fully in-sync with the
# master, and then promote it to master. But first we
# need to ensure that the master is following us, and that we
# have no outstanding requests on the restart lock to avoid deadlocking
# the cluster.
peer_rel = helpers.get_peer_relation()
master = get_master()
if peer_rel is None or master is None:
return # Peers all gone? Other handlers will promote.
if peer_rel[master].get("following") != hookenv.local_unit():
status_set("waiting",
"Waiting for master to follow me, its anointed successor")
return # Try again next hook
# Drain the master
while True:
local_offset = postgresql.wal_received_offset(postgresql.connect())
if local_offset is None:
# Huh? Should not happen unless the server was unexpectedly
# restarted.
break
try:
remote_con = postgresql.connect(user=replication_username(),
unit=master)
remote_offset = postgresql.wal_received_offset(remote_con)
if remote_offset is None:
# Huh? Should not happen either, since the master published
# that it is following us.
break
except (psycopg2.Error, postgresql.InvalidConnection) as x:
status_set(
"waiting",
"Waiting to query replication state of {}: {}"
"".format(master, x),
)
time.sleep(1)
continue
if local_offset >= remote_offset:
break # In sync. Proceed to promotion.
status_set(
"waiting",
"{} bytes to sync before promotion"
"".format(remote_offset - local_offset),
)
time.sleep(1)
# Promote the anointed to master
promote_to_master()
switchover_status()
def migrate_user(old_username, new_username, password, superuser=False):
if postgresql.is_primary():
# We do this on any primary, as the master is
# appointed later. It also works if we have
# a weird setup with manual_replication and
# multiple primaries.
con = postgresql.connect()
postgresql.ensure_user(con,
new_username,
password,
superuser=superuser)
cur = con.cursor()
hookenv.log("Granting old role {} to new role {}"
"".format(old_username, new_username))
cur.execute(
"GRANT %s TO %s",
(
postgresql.pgidentifier(old_username),
postgresql.pgidentifier(new_username),
),
)
con.commit()
else:
hookenv.log("Primary must map role {!r} to {!r}"
"".format(old_username, new_username))
def postgresql_conf_changed():
'''
After postgresql.conf has been changed, check it to see if
any changed options require a restart.
Sets the postgresql.cluster.needs_restart state.
Sets the postgresql.cluster.needs_reload state.
'''
store = unitdata.kv()
live = store.getrange('postgresql.cluster.pgconf.live.', strip=True)
current = store.getrange('postgresql.cluster.pgconf.current.', strip=True)
if not live or not current:
hookenv.log('PostgreSQL started without current config being saved. '
'Was the server rebooted unexpectedly?', WARNING)
reactive.set_state('postgresql.cluster.needs_restart')
return
con = postgresql.connect()
cur = con.cursor()
cur.execute("SELECT name FROM pg_settings WHERE context='postmaster'")
needs_restart = False
for row in cur.fetchall():
key = row[0]
old = live.get(key)
new = current.get(key)
if old != new:
hookenv.log('{} changed from {!r} to {!r}. '
'Restart required.'.format(key, old, new))
needs_restart = True
if needs_restart:
reactive.set_state('postgresql.cluster.needs_restart')
else:
reactive.set_state('postgresql.cluster.needs_reload')
def replication_resume(params):
if not postgresql.is_secondary():
hookenv.action_fail("Not a hot standby")
return
con = postgresql.connect()
con.autocommit = True
offset = postgresql.wal_received_offset(con)
hookenv.action_set(dict(offset=offset))
cur = con.cursor()
if postgresql.has_version("10"):
cur.execute("SELECT pg_is_wal_replay_paused()")
else:
cur.execute("SELECT pg_is_xlog_replay_paused()")
if cur.fetchone()[0] is False:
# Not a failure, per lp:1670613
hookenv.action_set(dict(result="Already resumed"))
return
if postgresql.has_version("10"):
cur.execute("SELECT pg_wal_replay_resume()")
else:
cur.execute("SELECT pg_xlog_replay_resume()")
hookenv.action_set(dict(result="Resumed"))
def elect_master():
"""Elect a new master after the old one has departed.
The new master is the secondary that has replayed the most
WAL data. There must be no hot standbys still replicating
data from the previous master, or we may end up with diverged
timelines.
Note we check replayed wal instead of received wal, because the
servers have just been restarted with no master and information
about received wal lost.
"""
rel = helpers.get_peer_relation()
local_unit = hookenv.local_unit()
# The unit with the most advanced WAL offset should be the new master.
if postgresql.is_running():
local_offset = postgresql.wal_replay_offset(postgresql.connect())
offsets = [(local_offset, local_unit)]
else:
offsets = []
for unit, relinfo in rel.items():
try:
con = postgresql.connect(user=replication_username(), unit=unit)
offsets.append((postgresql.wal_replay_offset(con), unit))
except (psycopg2.Error, postgresql.InvalidConnection) as x:
hookenv.log(
"Unable to query replication state of {}: {}"
"".format(unit, x),
WARNING,
)
# TODO: Signal re-cloning required. Or autodetect
# based on timeline switch. Or PG9.3+ could use pg_rewind.
offsets.sort()
if not offsets:
# This should only happen if we failover before replication has
# been setup, like a test suite destroying units without waiting
# for the initial deployment to complete.
status_set("blocked", "No candidates for master found!")
raise SystemExit(0)
elected_master = offsets[0][1]
return elected_master
def create_replication_user():
username = replication_username()
hookenv.log("Creating replication user {}".format(username))
con = postgresql.connect()
postgresql.ensure_user(con,
username,
leader_get("replication_password"),
replication=True)
con.commit()
reactive.set_state("postgresql.replication.replication_user_created")
def ensure_db_relation_resources(rel):
"""Create the database resources needed for the relation."""
master = rel.local
if "password" not in master:
return
hookenv.log("Ensuring database {!r} and user {!r} exist for {}" "".format(master["database"], master["user"], rel))
# First create the database, if it isn't already.
postgresql.ensure_database(master["database"])
# Next, connect to the database to create the rest in a transaction.
con = postgresql.connect(database=master["database"])
superuser, replication = _credential_types(rel)
postgresql.ensure_user(
con,
master["user"],
master["password"],
superuser=superuser,
replication=replication,
)
if not superuser:
postgresql.ensure_user(con, master["schema_user"], master["schema_password"])
# Grant specified privileges on the database to the user. This comes
# from the PostgreSQL service configuration, as allowing the
# relation to specify how much access it gets is insecure.
config = hookenv.config()
privs = set(filter(None, config["relation_database_privileges"].split(",")))
postgresql.grant_database_privileges(con, master["user"], master["database"], privs)
if not superuser:
postgresql.grant_database_privileges(con, master["schema_user"], master["database"], privs)
# Reset the roles granted to the user as requested.
if "roles" in master:
roles = filter(None, master.get("roles", "").split(","))
postgresql.grant_user_roles(con, master["user"], roles)
# Create requested extensions. We never drop extensions, as there
# may be dependent objects.
if "extensions" in master:
extensions = list(filter(None, master.get("extensions", "").split(",")))
# Convert to the (extension, schema) tuple expected by
# postgresql.ensure_extensions
for i in range(0, len(extensions)):
m = re.search(r"^\s*([^(\s]+)\s*(?:\((\w+)\))?", extensions[i])
if m is None:
raise RuntimeError("Invalid extension {}".format(extensions[i]))
extensions[i] = (m.group(1), m.group(2) or "public")
postgresql.ensure_extensions(con, extensions)
con.commit() # Don't throw away our changes.
def test_connect(self, psycopg2_connect, port, peer_rel, local_unit):
psycopg2_connect.return_value = sentinel.connection
port.return_value = sentinel.local_port
self.assertEqual(postgresql.connect(), sentinel.connection)
psycopg2_connect.assert_called_once_with(user='******',
database='postgres',
host=None,
port=sentinel.local_port)
psycopg2_connect.reset_mock()
local_unit.return_value = sentinel.local_unit
peer_rel.return_value = {
sentinel.local_unit: {
'host': sentinel.local_host,
'port': sentinel.local_port
},
sentinel.remote_unit: {
'host': sentinel.remote_host,
'port': sentinel.remote_port
}
}
self.assertEqual(
postgresql.connect(sentinel.user, sentinel.db,
sentinel.local_unit), sentinel.connection)
psycopg2_connect.assert_called_once_with(user=sentinel.user,
database=sentinel.db,
host=None,
port=sentinel.local_port)
psycopg2_connect.reset_mock()
self.assertEqual(
postgresql.connect(sentinel.user, sentinel.db,
sentinel.remote_unit), sentinel.connection)
psycopg2_connect.assert_called_once_with(user=sentinel.user,
database=sentinel.db,
host=sentinel.remote_host,
port=sentinel.remote_port)
def migrate_user(old_username, new_username, password, superuser=False):
if postgresql.is_primary():
# We do this on any primary, as the master is
# appointed later. It also works if we have
# a weird setup with manual_replication and
# multiple primaries.
con = postgresql.connect()
postgresql.ensure_user(con, new_username, password,
superuser=superuser)
cur = con.cursor()
hookenv.log('Granting old role {} to new role {}'
''.format(old_username, new_username))
cur.execute('GRANT %s TO %s',
(postgresql.pgidentifier(old_username),
postgresql.pgidentifier(new_username)))
con.commit()
else:
hookenv.log('Primary must map role {!r} to {!r}'
''.format(old_username, new_username))
def postgresql_conf_changed():
"""
After postgresql.conf has been changed, check it to see if
any changed options require a restart.
Sets the postgresql.cluster.needs_restart state.
Sets the postgresql.cluster.needs_reload state.
"""
store = unitdata.kv()
live = store.getrange("postgresql.cluster.pgconf.live.", strip=True)
current = store.getrange("postgresql.cluster.pgconf.current.", strip=True)
if live == current:
hookenv.log("postgresql.conf settings unchanged", DEBUG)
return
if not live or not current:
hookenv.log(
"PostgreSQL started without current config being saved. " "Was the server rebooted unexpectedly?",
WARNING,
)
reactive.set_state("postgresql.cluster.needs_restart")
return
con = postgresql.connect()
cur = con.cursor()
cur.execute("SELECT name FROM pg_settings WHERE context='postmaster'")
needs_restart = False
for row in cur.fetchall():
key = row[0]
old = live.get(key)
new = current.get(key)
if old != new:
hookenv.log("{} changed from {!r} to {!r}. " "Restart required.".format(key, old, new))
needs_restart = True
if needs_restart:
reactive.set_state("postgresql.cluster.needs_restart")
else:
reactive.set_state("postgresql.cluster.needs_reload")
def ensure_db_relation_resources(rel):
"""Create the database resources needed for the relation."""
master = rel.local
hookenv.log("Ensuring database {!r} and user {!r} exist for {}" "".format(master["database"], master["user"], rel))
# First create the database, if it isn't already.
postgresql.ensure_database(master["database"])
# Next, connect to the database to create the rest in a transaction.
con = postgresql.connect(database=master["database"])
superuser, replication = _credential_types(rel)
postgresql.ensure_user(con, master["user"], master["password"], superuser=superuser, replication=replication)
if not superuser:
postgresql.ensure_user(con, master["schema_user"], master["schema_password"])
# Grant specified privileges on the database to the user. This comes
# from the PostgreSQL service configuration, as allowing the
# relation to specify how much access it gets is insecure.
config = hookenv.config()
privs = set(filter(None, config["relation_database_privileges"].split(",")))
postgresql.grant_database_privileges(con, master["user"], master["database"], privs)
if not superuser:
postgresql.grant_database_privileges(con, master["schema_user"], master["database"], privs)
# Reset the roles granted to the user as requested.
if "roles" in master:
roles = filter(None, master.get("roles", "").split(","))
postgresql.grant_user_roles(con, master["user"], roles)
# Create requested extensions. We never drop extensions, as there
# may be dependent objects.
if "extensions" in master:
extensions = filter(None, master.get("extensions", "").split(","))
postgresql.ensure_extensions(con, extensions)
con.commit() # Don't throw away our changes.
def wal_e_restore():
reactive.remove_state("action.wal-e-restore")
params = hookenv.action_get()
backup = params["backup-name"].strip().replace("-", "_")
storage_uri = params["storage-uri"].strip()
ship_uri = hookenv.config().get("wal_e_storage_uri")
if storage_uri == ship_uri:
hookenv.action_fail(
"The storage-uri parameter is identical to "
"the wal_e_storage_uri config setting. Your "
"restoration source cannot be the same as the "
"folder you are archiving too to avoid corrupting "
"the backups."
)
return
if not params["confirm"]:
m = "Recovery from {}.".format(storage_uri)
if ship_uri:
m += "\nContents of {} will be destroyed.".format(ship_uri)
m += "\nExisting local database will be destroyed."
m += "\nRerun action with 'confirm=true' to proceed."
hookenv.action_set({"info": m})
return
with tempfile.TemporaryDirectory(prefix="wal-e", suffix="envdir") as envdir:
update_wal_e_env_dir(envdir, storage_uri)
# Confirm there is a backup to restore
backups = wal_e_list_backups(envdir)
if not backups:
hookenv.action_fail("No backups found at {}".format(storage_uri))
return
if backup != "LATEST" and backup not in (b["name"] for b in backups):
hookenv.action_fail("Backup {} not found".format(backup))
return
# Shutdown PostgreSQL. Note we want this action to run synchronously,
# so there is no opportunity to ask permission from the leader. If
# there are other units cloning this database, those clone operations
# will fail. Which seems preferable to blocking a recovery operation
# in any case, because if we are doing disaster recovery we generally
# want to do it right now.
status_set("maintenance", "Stopping PostgreSQL for backup restoration")
postgresql.stop()
# Trash the existing database. Its dangerous to do this first, but
# we probably need the space.
data_dir = postgresql.data_dir() # May be a symlink
for content in os.listdir(data_dir):
cpath = os.path.join(data_dir, content)
if os.path.isdir(cpath) and not os.path.islink(cpath):
shutil.rmtree(cpath)
else:
os.remove(cpath)
# WAL-E recover
status_set("maintenance", "Restoring backup {}".format(backup))
wal_e_run(["backup-fetch", data_dir, backup], envdir=envdir)
# Create recovery.conf to complete recovery
is_master = reactive.is_state("postgresql.replication.is_master")
standby_mode = "off" if is_master else "on"
if params.get("target-time"):
target_time = "recovery_target_time='{}'" "".format(params["target-time"])
else:
target_time = ""
target_action = "promote" if is_master else "shutdown"
immediate = "" if is_master else "recovery_target='immediate'"
helpers.write(
postgresql.recovery_conf_path(),
dedent(
"""\
# Managed by Juju. PITR in progress.
standby_mode = {}
restore_command='{}'
recovery_target_timeline = {}
recovery_target_action = {}
{}
{}
"""
).format(
standby_mode,
wal_e_restore_command(envdir=envdir),
params["target-timeline"],
target_action,
target_time,
immediate,
),
mode=0o600,
user="******",
group="postgres",
)
# Avoid circular import. We could also avoid the import entirely
# with a sufficiently complex set of handlers in the replication
# module, but that seems to be a worse solution. Better to break
# out this action into a separate module.
from reactive.postgresql import replication
if is_master:
if ship_uri:
# If master, trash the configured wal-e storage. This may
# contain WAL and backups from the old cluster which will
# conflict with the new cluster. Hopefully it does not
# contain anything important, because we have no way to
# prompt the user for confirmation.
wal_e_run(["delete", "--confirm", "everything"])
# Then, wait for recovery and promotion.
postgresql.start()
con = postgresql.connect()
cur = con.cursor()
while True:
if postgresql.has_version("10"):
cur.execute(
"""SELECT pg_is_in_recovery(),
pg_last_wal_replay_lsn()"""
)
else:
cur.execute(
"""SELECT pg_is_in_recovery(),
pg_last_xlog_replay_location()"""
)
in_rec, loc = cur.fetchone()
if not in_rec:
break
status_set("maintenance", "Recovery at {}".format(loc))
time.sleep(10)
else:
# If standby, startup and wait for recovery to complete and
# shutdown.
status_set("maintenance", "Recovery")
# Startup might shutdown immediately and look like a failure.
postgresql.start(ignore_failure=True)
# No recovery point status yet for standbys, as we would need
# to handle connection failures when the DB shuts down. We
# should do this.
while postgresql.is_running():
time.sleep(5)
replication.update_recovery_conf(follow=replication.get_master())
# Reactive handlers will deal with the rest of the cleanup.
# eg. ensuring required users and roles exist
replication.update_replication_states()
reactive.remove_state("postgresql.cluster.configured")
reactive.toggle_state("postgresql.cluster.is_running", postgresql.is_running())
reactive.remove_state("postgresql.nagios.user_ensured")
reactive.remove_state("postgresql.replication.replication_user_created")
reactive.remove_state("postgresql.client.published")
def ensure_nagios_user():
con = postgresql.connect()
postgresql.ensure_user(con, nagios_username(), leadership.leader_get("nagios_password"))
con.commit()
reactive.set_state("postgresql.nagios.user_ensured")
def ensure_nagios_user():
con = postgresql.connect()
postgresql.ensure_user(con, nagios_username(), leadership.leader_get("nagios_password"))
con.commit()
reactive.set_state("postgresql.nagios.user_ensured")
