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 logical replication since 1.2.4.
This document describes how to configure PostgreSQL built-in logical replication for PGroonga. Most of steps are normal steps. There are some PGroonga specific steps.
It doesn't have to be have same schema for source of replication and destionation of replication for logical replication.
Therefore, setting an index on only a destination of replication in here.
Here are steps to configure PostgreSQL built-in logical replication for PGroonga. "[normal]" tag means that the step is a normal step for logical replication. "[special]" tag means that the step is a PGroonga specific step.
[normal] Install PostgreSQL on Publisher and Subscriber
[special] Install PGroonga on Subscriber
[normal] Initialize PostgreSQL database on Publisher and Subscriber
[normal] Add some logical replication configurations to postgresql.conf and pg_hba.conf on Publisher
[normal] Create table on Publisher and Subscriber
[normal] Create publication on Publisher
[normal] Create subscription on Subscriber
[special] Create a PGroonga index on Subscriber
[normal] Insert data on Publisher
This document uses the following environment:
Publisher:
OS: CentOS 7
IP Address: 172.16.0.1
Database name: blog
Replication user name: replicator
Replication user password: passw0rd
Subscriber:
OS: CentOS 7
IP Address: 172.16.0.2
Database name: blog
This document shows command lines for CentOS 7. If you're using other platforms, adjust command lines by yourself.
This is a normal step.
Install PostgreSQL 17 on Publisher and Subscriber.
Publisher and Subscriber:
% sudo -H yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -qf --queryformat="%{VERSION}" /etc/redhat-release)-$(rpm -qf --queryformat="%{ARCH}" /etc/redhat-release)/pgdg-redhat-repo-latest.noarch.rpm
% sudo yum install postgresql17-server
See also PostgreSQL: Linux downloads (CentOS).
This is a PGroonga specific step.
Install PGroonga on Subscriber.
Subscriber:
% sudo -H yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -qf --queryformat="%{VERSION}" /etc/redhat-release)-$(rpm -qf --queryformat="%{ARCH}" /etc/redhat-release)/pgdg-redhat-repo-latest.noarch.rpm
% sudo -H yum install -y https://packages.groonga.org/centos/groonga-release-latest.noarch.rpm
% sudo -H yum install -y postgresql17-pgroonga
This is a normal step.
Initialize PostgreSQL database on Publisher and Subscriber.
Publisher and Subscriber:
% sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
% sudo systemctl enable --now postgresql-17
postgresql.conf on PublisherThis is a normal step.
Add the following logical replication configurations to postgresql.conf on only Publisher:
wal_level = logical
max_wal_senders = 2 (= 1 (The number of Subscribers) * 2. * 2 is for unexpected connection close.)
max_replication_slots = 1 (= 1 (The number of Subscribers)).
/var/lib/pgsql/17/data/postgresql.conf:
Before:
#listen_address = 'localhost'
#wal_level = minimal
#max_wal_senders = 0
#max_replication_slots = 0
After:
listen_address = '*'
wal_level = logical
max_wal_senders = 2
max_replication_slots = 1
Add the following logical replication configurations to pg_hba.conf on only Publisher:
replicator from 172.16.0.2/32./var/lib/pgsql/17/data/pg_hba.conf:
Before:
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
After:
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
# IPv4 remote connections:
host all replicator 172.16.0.2/32 md5
Restart PostgreSQL to apply the configuration:
% sudo -H systemctl restart postgresql-17
Create the user for replication on only Publisher:
% /usr/pgsql-17/bin/createuser --pwprompt replicator -U postgres
Enter password for new role: (passw0rd)
Enter it again: (passw0rd)
This is a normal step.
Create a normal user on Subscriber:
Subscriber:
% /usr/pgsql-17/bin/createuser ${USER} -U postgres
Create a database on Publisher and Subscriber:
Publisher and Subscriber:
% /usr/pgsql-17/bin/createdb --owner ${USER} blog -U postgres
Create a table in the created database.
Connect to the created blog database:
% /usr/pgsql-17/bin/psql blog -U ${USER}
Create entries table:
CREATE TABLE entries (
title text,
body text
);
This is a PGroonga specific step.
Install PGroonga to the database. It requires superuser privilege:
Subscriber:
CREATE EXTENSION pgroonga;
Create a PGroonga index on Subscriber:
Subscriber:
CREATE INDEX entries_full_text_search ON entries USING pgroonga (title, body);
Create a PUBLICATION on Publisher only:
CREATE PUBLICATION pub_srv1_blog FOR TABLE entries;
Create a SUBSCRIPTION on Subscriber only:
CREATE SUBSCRIPTION sub_srv2_blog CONNECTION 'dbname=blog hostaddr=172.16.0.2 port=5432 user=replicator password=passw0rd' PUBLICATION pub_srv1_blog;
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!');
Confirm replication:
Publisher:
SELECT * FROM entries;
title | body
---------+---------------------------------------------------------------------------
PGroonga | PGroonga is a PostgreSQL extension for fast full text search that supports all languages. It will help us.
Groonga | Groonga is a full text search engine used by PGroonga. We did not know about it.
PGroonga and replication | PGroonga 1.1.6 supports WAL based streaming replication. We should try it!
(3 rows)
Subscriber:
SELECT * FROM entries;
title | body
---------+---------------------------------------------------------------------------
PGroonga | PGroonga is a PostgreSQL extension for fast full text search that supports all languages. It will help us.
Groonga | Groonga is a full text search engine used by PGroonga. We did not know about it.
PGroonga and replication | PGroonga 1.1.6 supports WAL based streaming replication. We should try it!
(3 rows)
Now, we can search data replicated on subscriber by PGroonga index created on subscriber.
SET enable_seqscan TO off;
SELECT * FROM entries WHERE body &@ 'Groonga';
title | body
---------+----------------------------------------------------------------------------------
Groonga | Groonga is a full text search engine used by PGroonga. We did not know about it.
(1 row)