This is a document for PGroonga 2.X and 3.X. See PGroonga 1.x document when you're using old PGroonga.
PGroonga supports PostgreSQL built-in WAL based streaming replication since 1.1.6.
If you're using PGroonga 3.2.1 or later and PostgreSQL 15 or later, streaming replication by WAL resource manager is recommended.
PGroonga's WAL send to standby server from primary server as below.
sequenceDiagram
box transparent Primary
participant Primary user
participant Primary PGroonga
participant WAL sender
end
box transparent Standby
participant WAL receiver
participant Standby user
participant Standby PGroonga
end
Primary user->>+Primary PGroonga:INSERT/UPDATE/DELETE
Note right of Primary PGroonga:Write WAL
Primary PGroonga->>+WAL sender:Notify write WAL
WAL sender->>+WAL receiver:Send WAL
Note right of WAL receiver:Save WAL
Standby user->>+Standby PGroonga:SELECT
Note right of Standby PGroonga:Apply saved WAL
Note that WAL support doesn't mean crash safe. It just supports WAL based streaming replication. If PostgreSQL is crashed while PGroonga index update, the PGroonga index may be broken. If the PGroonga index is broken, you need to recreate the PGroonga index by REINDEX.
See also: Crash safe
This document describes how to configure PostgreSQL built-in WAL based streaming replication for PGroonga. Most of steps are normal steps. There are some PGroonga specific steps.
Here are steps to configure PostgreSQL built-in WAL based streaming replication for PGroonga. "[normal]" tag means that the step is a normal step for streaming replication. "[special]" tag means that the step is a PGroonga specific step.
[normal] Configure PostgreSQL for streaming replication on primary
[normal] Insert data on primary
[special] Create a PGroonga index on primary
[special] Flush PGroonga related data on primary
[normal] Run pg_basebackup on standbys
[normal] Start PostgreSQL on standbys
This document uses the following environment:
Primary:
OS: Ubuntu 22.04
IP address: 192.168.0.30
Database name: blog
Replication user name: replicator
Replication user password: passw0rd
Standby1:
OS: Ubuntu 22.04
IP address: 192.168.0.31
Standby2:
OS: Ubuntu 22.04
IP address: 192.168.0.32
This document shows command lines for Ubuntu 22.04. If you're using other platforms, adjust command lines by yourself.
This is a normal step.
Install PostgreSQL 15 on both primary and standbys:
sudo apt install -y gpg lsb-release wget
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --no-default-keyring --keyring /usr/share/keyrings/pdgd-keyring.gpg --import -
echo "deb [signed-by=/usr/share/keyrings/pdgd-keyring.gpg] http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt install -y postgresql-15
This is a PGroonga specific step.
Install PGroonga on both primary and standbys.
sudo apt install -y software-properties-common
sudo add-apt-repository -y universe
sudo add-apt-repository -y ppa:groonga/ppa
sudo apt install -y wget lsb-release
wget https://packages.groonga.org/ubuntu/groonga-apt-source-latest-$(lsb_release --codename --short).deb
sudo apt install -y -V ./groonga-apt-source-latest-$(lsb_release --codename --short).deb
sudo apt update
sudo apt install -y -V postgresql-15-pgdg-pgroonga
This is a normal step.
Add the following streaming replication configurations to postgresql.conf on only primary:
listen_addresses = 'localhost,192.168.0.30'If you have many standbys, you need to specify max_wal_senders too. The default max_wal_senders is 10. 10 is enough value for 2 standbys.
See also PostgreSQL: Documentation: Replication.
/etc/postgresql/15/main/postgresql.conf:
Before:
#listen_addresses = 'localhost'
After:
listen_addresses = 'localhost,192.168.0.30'
Add the following streaming replication configurations to pg_hba.conf on only primary:
replicator from 192.168.0.0/24./etc/postgresql/15/main/pg_hba.conf:
Before:
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
After:
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
host replication all 192.168.0.0/24 scram-sha-256
Create the user for replication on only primary:
$ sudo -u postgres -H createuser --pwprompt --replication replicator
Enter password for new role: (passw0rd)
Enter it again: (passw0rd)
This is a PGroonga specific step.
You need to add PGroonga's WAL related configurations and crash safe related configurations.
For PGroonga's WAL, you need to add pgronga.enable_wal parameter and pgroonga.max_wal_size parameter configurations:
For crash safe, you need to add pgroonga_crash_safer module module to shared_preload_libraries parameter and add pgroonga.crash_safe = on.
NOTE: pgroonga_crash_safer module reduces write performance. There is a trade-off for easy to maintain and performance. If you need maximum write performance, you can't use this module. See also Crash safe for the trade-off.
/etc/postgresql/15/main/postgresql.conf:
Before:
#shared_preload_libraries = ''
After:
shared_preload_libraries = 'pgroonga_crash_safer'
/etc/postgresql/15/main/conf.d/pgroonga.conf:
pgroonga.enable_wal = on
pgroonga.max_wal_size = 100MB
pgroonga.enable_crash_safe = on
If your system has many write, you may need more size for pgroonga.max_wal_size.
If you don't use pgroonga_crash_safer module, you need to remove pgroonga_crash_safer from shared_preload_libraries and remove pgroonga.enable_crash_safe = on.
Restart PostgreSQL to apply the configuration:
sudo -H systemctl restart postgresql
This is a normal step.
Create a normal user on only primary:
sudo -u postgres -H createuser ${USER}
Create a database on only primary:
sudo -u postgres -H createdb --template template0 --locale C --encoding UTF-8 --owner ${USER} blog
Create a table in the created database on only primary.
Connect to the created blog database:
psql blog
Create entries table:
CREATE TABLE entries (
title text,
body text
);
Insert data to the created entries table:
INSERT INTO entries VALUES ('PGroonga', 'PGroonga is a PostgreSQL extension for fast full text search that supports all languages. It will help us.');
INSERT INTO entries VALUES ('Groonga', 'Groonga is a full text search engine used by PGroonga. We did not know about it.');
INSERT INTO entries VALUES ('PGroonga and replication', 'PGroonga 1.1.6 supports WAL based streaming replication. We should try it!');
This is a PGroonga specific step.
Install PGroonga to the database. It requires superuser privilege:
sudo -u postgres -H psql blog --command "CREATE EXTENSION pgroonga;"
sudo -u postgres -H psql blog --command "GRANT USAGE ON SCHEMA pgroonga TO ${USER};"
Connect to PostgreSQL by a normal user again:
psql blog
Create a PGroonga index on only primary:
CREATE INDEX entries_full_text_search ON entries USING pgroonga (title, body);
Confirm the index:
SET enable_seqscan TO off;
SELECT title FROM entries WHERE title &@~ 'replication';
-- title
-- --------------------------
-- PGroonga and replication
-- (1 row)
This is a PGroonga specific step.
Ensure writing PGroonga related data on memory to disk on only primary. You can choose one of them:
Run SELECT pgroonga_command('io_flush');
Disconnect all connections
Here is an example to use pgroonga_command('io_flush'):
SELECT pgroonga_command('io_flush') AS command;
-- command
-- -----------------------------------------------
-- [[0,1478446349.2241,0.1413860321044922],true]
-- (1 row)
You must not change tables that use PGroonga indexes on primary until the next pg_basebackup step is finished.
pg_basebackup on standbysThis is a normal step.
Run pg_basebackup on only standbys. It copies the current database from primary.
Standbys:
$ sudo -H systemctl stop postgresql
$ sudo -u postgres -H rm -rf /var/lib/postgresql/15/main
$ sudo -u postgres -H pg_basebackup --host 192.168.0.30 -D /var/lib/postgresql/15/main --progress -U replicator -R
Password: (passw0rd)
175007/175007 kB (100%), 1/1 tablespace
This is a PGroonga specific step.
Since 2.4.2.
Add the following modules to shared_preload_libraries parameter:
NOTE: pgroonga_crash_safer module reduces write performance. There is a trade-off for easy to maintain and performance. If you need maximum write performance, you can't use this module. See also Crash safe for the trade-off.
Standbys:
/etc/postgresql/15/main/postgresql.conf:
Before:
#shared_preload_libraries = ''
After:
shared_preload_libraries = 'pgroonga_standby_maintainer,pgroonga_crash_safer'
Standbys:
/etc/postgresql/15/main/conf.d/pgroonga.conf:
pgroonga.enable_wal = on
pgroonga.enable_crash_safe = on
If you don't use pgroonga_crash_safer module, you need to remove pgroonga_crash_safer from shared_preload_libraries and remove pgroonga.enable_crash_safe = on.
This is a normal step.
Start PostgreSQL on standbys:
sudo -H systemctl start postgresql
Now, you can search data inserted on primary by PGroonga index created on primary.
Standby1:
psql blog
SET enable_seqscan TO off;
SELECT title FROM entries WHERE title &@~ 'replication';
-- title
-- --------------------------
-- PGroonga and replication
-- (1 row)
You can also search data inserted on primary after pg_basebackup.
Primary:
INSERT INTO entries VALUES ('PostgreSQL 9.6 and replication', 'PostgreSQL supports generic WAL since 9.6. It is required for replication for PGroonga.');
Standby1:
SELECT title FROM entries WHERE title &@~ 'replication';
-- title
-- --------------------------------
-- PGroonga and replication
-- PostgreSQL 9.6 and replication
-- (2 rows)
Standby2:
psql blog
SET enable_seqscan TO off;
SELECT title FROM entries WHERE title &@~ 'replication';
-- title
-- --------------------------------
-- PGroonga and replication
-- PostgreSQL 9.6 and replication
-- (2 rows)