Name: pgmasq
Owner: Citus Data
Description: Transparently forward transactions from a hot standby to a primary in PostgreSQL
Created: 2017-03-19 23:00:34.0
Updated: 2018-03-29 12:53:55.0
Pushed: 2017-03-24 12:28:48.0
Size: 100
Language: C
GitHub Committers
User | Most Recent Commit | # Commits |
---|
Other Committers
User | Most Recent Commit | # Commits |
---|
Pgmasq is a PostgreSQL extension that transparently forwards transactions from a hot standby to a primary to enable DDL and DML from any node, while providing a means of offloading some SELECTs to a standby.
Pqmasq is currently a prototype, with some important limitations.
Compile the extension from source:
clone https://github.com/citusdata/pgmasq.git
gmasq
&& sudo PATH=$PATH make install
Add the following to postgresql.conf:
ed_preload_libraries = 'pgmasq'
sq.execute_locally = immutable # one of: none, immutable, selects
_timeout = '2s'
The pgmasq.execute_locally
setting controls which queries are executed locally on the standby. The following settings are supported:
none
forwards all commands to the primary.immutable
executes selects outside of a transactino block that contain no functions that could modify the database on the standby.selects
executes all selects outside of a transaction block on the standby.Additionally, we recommend setting synchronous_commit = remote_apply
on the primary to provide read-your-writes consistency.
To enable pgmasq in a particular database, run CREATE EXTENSION pgmasq
in that database on the primary.
For pgmasq to work your replication user also needs to be superuser. You can make that the case by running:
R USER replicator SUPERUSER;
In addition, you may need to add a line to pg_hba.conf to ensure access to the database, in addition to replication access:
replication replicator 10.0.0.0/8 md5
all replicator 10.0.0.0/8 md5
CREATE TEMPORARY TABLE
is not supported as the parser on the hot standby cannot see the temporary table.
Multi-statement transactions of the form BEGIN; [DDL on table X]; [SELECT/DML on table X]; COMMIT;
may create an undetectable deadlock, since the DDL will take an exclusive lock when it gets replicated, which will cause the parser to block when processing the SELECT/DML.