def get_replication_slot(self, slot_name):
"""
Retrieve from the PostgreSQL server a physical replication slot
with a specific slot_name.
This method returns a dictionary containing the following data:
* slot_name
* active
* restart_lsn
:param str slot_name: the replication slot name
:rtype: psycopg2.extras.DictRow
"""
if self.server_version < 90400:
# Raise exception if replication slot are not supported
# by PostgreSQL version
raise PostgresUnsupportedFeature('9.4')
else:
cur = self._cursor(cursor_factory=NamedTupleCursor)
try:
cur.execute("SELECT slot_name, "
"active, "
"restart_lsn "
"FROM pg_replication_slots "
"WHERE slot_type = 'physical' "
"AND slot_name = '%s'" % slot_name)
# Retrieve the replication slot information
return cur.fetchone()
except (PostgresConnectionError, psycopg2.Error) as e:
_logger.debug("Error retrieving replication_slots: %s",
str(e).strip())
raise
def get_synchronous_standby_names(self):
"""
Retrieve the list of named synchronous standby servers from PostgreSQL
This method returns a list of names
:return list: synchronous standby names
"""
if self.server_version < 90100:
# Raise exception if synchronous replication is not supported
raise PostgresUnsupportedFeature('9.1')
else:
synchronous_standby_names = (
self.get_setting('synchronous_standby_names'))
# Normalise the list of sync standby names
# On PostgreSQL 9.6 it is possible to specify the number of
# required synchronous standby using this format:
# n (name1, name2, ... nameN).
# We only need the name list, so we discard everything else.
# The name list starts after the first parenthesis or at pos 0
names_start = synchronous_standby_names.find('(') + 1
names_end = synchronous_standby_names.rfind(')')
if names_end < 0:
names_end = len(synchronous_standby_names)
names_list = synchronous_standby_names[names_start:names_end]
return [x.strip() for x in names_list.split(',')]
def test_replication_status(self, capsys):
"""
Test management of pg_stat_archiver view output
:param MagicMock connect_mock: mock the database connection
:param capsys: retrieve output from consolle
"""
# Build a fake get_replication_stats record
replication_stats_data = dict(
pid=93275,
usesysid=10,
usename='postgres',
application_name='replica',
client_addr=None,
client_hostname=None,
client_port=-1,
slot_name=None,
backend_start=datetime.datetime(
2016, 5, 6, 9, 29, 20, 98534,
tzinfo=FixedOffsetTimezone(offset=120)),
backend_xmin='940',
state='streaming',
sent_location='0/3005FF0',
write_location='0/3005FF0',
flush_location='0/3005FF0',
replay_location='0/3005FF0',
current_location='0/3005FF0',
sync_priority=0,
sync_state='async'
)
replication_stats_class = namedtuple("Record",
replication_stats_data.keys())
replication_stats_record = replication_stats_class(
**replication_stats_data)
# Prepare the server
server = build_real_server(main_conf={'archiver': 'on'})
server.postgres = MagicMock()
server.postgres.get_replication_stats.return_value = [
replication_stats_record]
server.postgres.current_xlog_location = "AB/CDEF1234"
# Execute the test (ALL)
server.postgres.reset_mock()
server.replication_status('all')
(out, err) = capsys.readouterr()
assert err == ''
server.postgres.get_replication_stats.assert_called_once_with(
PostgreSQLConnection.ANY_STREAMING_CLIENT)
# Execute the test (WALSTREAMER)
server.postgres.reset_mock()
server.replication_status('wal-streamer')
(out, err) = capsys.readouterr()
assert err == ''
server.postgres.get_replication_stats.assert_called_once_with(
PostgreSQLConnection.WALSTREAMER)
# Execute the test (failure: PostgreSQL too old)
server.postgres.reset_mock()
server.postgres.get_replication_stats.side_effect = \
PostgresUnsupportedFeature('9.1')
server.replication_status('all')
(out, err) = capsys.readouterr()
assert 'Requires PostgreSQL 9.1 or higher' in out
assert err == ''
server.postgres.get_replication_stats.assert_called_once_with(
PostgreSQLConnection.ANY_STREAMING_CLIENT)
# Execute the test (failure: superuser required)
server.postgres.reset_mock()
server.postgres.get_replication_stats.side_effect = \
PostgresSuperuserRequired
server.replication_status('all')
(out, err) = capsys.readouterr()
assert 'Requires superuser rights' in out
assert err == ''
server.postgres.get_replication_stats.assert_called_once_with(
PostgreSQLConnection.ANY_STREAMING_CLIENT)
def get_replication_stats(self, client_type=STANDBY):
"""
Returns streaming replication information
"""
try:
cur = self._cursor(cursor_factory=NamedTupleCursor)
# Without superuser rights, this function is useless
# TODO: provide a simplified version for non-superusers
if not self.is_superuser:
raise PostgresSuperuserRequired()
# pg_stat_replication is a system view that contains one
# row per WAL sender process with information about the
# replication status of a standby server. It has been
# introduced in PostgreSQL 9.1. Current fields are:
#
# - pid (procpid in 9.1)
# - usesysid
# - usename
# - application_name
# - client_addr
# - client_hostname
# - client_port
# - backend_start
# - backend_xmin (9.4+)
# - state
# - sent_location
# - write_location
# - flush_location
# - replay_location
# - sync_priority
# - sync_state
#
if self.server_version < 90100:
raise PostgresUnsupportedFeature('9.1')
from_repslot = ""
if self.server_version >= 90500:
# Current implementation (9.5+)
what = "r.*, rs.slot_name"
# Look for replication slot name
from_repslot = "LEFT JOIN pg_replication_slots rs " \
"ON (r.pid = rs.active_pid) "
elif self.server_version >= 90400:
# PostgreSQL 9.4
what = "*"
elif self.server_version >= 90200:
# PostgreSQL 9.2/9.3
what = "pid," \
"usesysid," \
"usename," \
"application_name," \
"client_addr," \
"client_hostname," \
"client_port," \
"backend_start," \
"CAST (NULL AS xid) AS backend_xmin," \
"state," \
"sent_location," \
"write_location," \
"flush_location," \
"replay_location," \
"sync_priority," \
"sync_state "
else:
# PostgreSQL 9.1
what = "procpid AS pid," \
"usesysid," \
"usename," \
"application_name," \
"client_addr," \
"client_hostname," \
"client_port," \
"backend_start," \
"CAST (NULL AS xid) AS backend_xmin," \
"state," \
"sent_location," \
"write_location," \
"flush_location," \
"replay_location," \
"sync_priority," \
"sync_state "
# Streaming client
if client_type == self.STANDBY:
# Standby server
where = 'WHERE replay_location IS NOT NULL '
elif client_type == self.WALSTREAMER:
# WAL streamer
where = 'WHERE replay_location IS NULL '
else:
where = ''
# Execute the query
cur.execute("SELECT %s, "
"pg_is_in_recovery() AS is_in_recovery,"
"CASE WHEN pg_is_in_recovery() "
" THEN {pg_last_wal_receive_lsn}() "
" ELSE {pg_current_wal_lsn}() "
"END AS current_location "
"FROM pg_stat_replication r "
"%s"
"%s"
"ORDER BY sync_state DESC, sync_priority".format(
**self.name_map) % (what, from_repslot, where))
# Generate a list of standby objects
return cur.fetchall()
except (PostgresConnectionError, psycopg2.Error) as e:
_logger.debug("Error retrieving status of standby servers: %s",
str(e).strip())
return None
