- streaming_lag 0.0.1
- A custom background worker process to measure lag in units of time
streaming_lag is an experimental extension to measure the lag of a streaming slave in units of time instead of bytes.
The idea is to create a table with exactly one row and one column which is a timestamp. A background process writes this timestamp on a regular basis, like every 1sec. Now, given the master and the slave are time-synchronized via NTP or similar, the difference of clock_timestamp() minus that value on the slave returns a good measure how far it lags behind the master in units of time.
As it is my first extension, this module is also a learning exercise. I have tested it only on Linux.
Compile and install it
To build it, just do this:
make && sudo make install
You need GNU
pg_config which comes with Postgres. If
you are using a Postgres package provided by the OS vendor, you
probably want to install a development package. If
not found in your
$PATH, point the
variable to it:
PG_CONFIG=/path/to/pg_config make -e &&
sudo PG_CONFIG=/path/to/pg_config make -e install
Create and configure the extension
The next step is to create the extension and to configure it. Since
streaming replication works on the whole cluster, the extension is
installed in exactly one database of the cluster. By default this
postgres. So, log in to the database as superuser:
psql postgres postgres
and create the extension:
CREATE EXTENSION streaming_lag;
This command creates 2 objects, a table called
streaming_lag_data and a
streaming_lag. By default they are created in the
public schema. However, you can create them in a different schema:
CREATE EXTENSION streaming_lag SCHEMA lag;
You can also move the objects later to a different schema using:
ALTER EXTENSION streaming_lag SET SCHEMA my_lag;
Once the extension is created, you need to add a few values to your
shared_preload_libraries = 'streaming_lag'
streaming_lag.database = 'postgres'
streaming_lag.schema = 'public'
streaming_lag.precision = 5000
If you already have set
to it separated by a comma, e.g.
shared_preload_libraries = 'pg_stat_statements,streaming_lag'
Next, restart the database. You should see the following lines in the log file:
LOG: registering background worker "streaming_lag"
LOG: loaded library "streaming_lag"
LOG: starting background worker process "streaming_lag"
LOG: streaming_lag: initialized, database objects validated
streaming_lag.databasespecifies the database where the extension was created. To change the value postgres has to be restarted.
streaming_lag.schemathe schema To change the value postgres has to be restarted.
streaming_lag.precisionspecifies the frequency at which the timestamp is written in milliseconds. By default a timestamp is written every 5000ms. This value can be changed in SIGHUP context. That means a full restart is not required. Just change the value in the
postgresql.confand reload the database:
sudo -u postgres pg_ctl reload -D /path/to/data/directory
streaming_lag.precision is set to
0, the table is not
updated anymore. You can use that to temporary disable the
feature. In that case the lag reported on the slave will be
growing with time.
Provided both, master and slave, have synchronized clocks, you can measure the lag on the slave by:
``` postgres=# select * from lag.streaming_lag;
00:00:04.647359 (1 row) ```
A quick test
For a quick test, I configured streaming replication over WIFI to my notebook and installed the extension. Then I created a table with 10000000 rows and 1 column.
select clock_timestamp(), * from streaming_lag;
psql I watched the lag on the notebook while
UPDATE tbl SET i=i;
Then I used the output produced to generate this image: