This is a document for PGroonga 2.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.
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] Install PostgreSQL on primary and standbys
[special] Install PGroonga on primary and standbys
[normal] Initialize PostgreSQL database on primary
[normal] Add some streaming replication configurations to postgresql.conf
and pg_hba.conf
on primary
[special] Add some PGroonga related configurations to postgresql.conf
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] Add some streaming replication configurations to postgresql.conf
on standbys
[special] Add some PGroonga's WAL configurations to postgresql.conf
on standbys
[normal] Start PostgreSQL on standbys
This document uses the following environment:
Primary:
OS: CentOS 7
IP address: 192.168.0.30
Database name: blog
Replication user name: replicator
Replication user password: passw0rd
Standby1:
OS: CentOS 7
IP address: 192.168.0.31
Standby2:
OS: CentOS 7
IP address: 192.168.0.31
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 9.6 on primary and standbys.
Primary:
% sudo -H yum install -y http://yum.postgresql.org/9.6/redhat/rhel-$(rpm -qf --queryformat="%{VERSION}" /etc/redhat-release)-$(rpm -qf --queryformat="%{ARCH}" /etc/redhat-release)/pgdg-centos96-9.6-3.noarch.rpm
% sudo -H yum install -y postgresql96-server
% sudo -H systemctl enable postgresql-9.6
Standbys:
% sudo -H yum install -y http://yum.postgresql.org/9.6/redhat/rhel-$(rpm -qf --queryformat="%{VERSION}" /etc/redhat-release)-$(rpm -qf --queryformat="%{ARCH}" /etc/redhat-release)/pgdg-centos96-9.6-3.noarch.rpm
% sudo -H yum install -y postgresql96-server
% sudo -H systemctl enable postgresql-9.6
See also PostgreSQL: Linux downloads (Red Hat family).
This is a PGroonga specific step.
Install PGroonga on primary and standbys.
Primary:
% sudo -H yum install -y https://packages.groonga.org/centos/groonga-release-.noarch.rpm
% sudo -H yum install -y postgresql96-pgroonga
Standbys:
% sudo -H yum install -y https://packages.groonga.org/centos/groonga-release-.noarch.rpm
% sudo -H yum install -y epel-release
% sudo -H yum install -y postgresql96-pgroonga
See also Install on CentOS.
This is a normal step.
Initialize PostgreSQL database on only primary. You don't need to initialize PostgreSQL database on standbys.
Primary:
% sudo -H env PGSETUP_INITDB_OPTIONS="--locale C --encoding UTF-8" /usr/pgsql-9.6/bin/postgresql96-setup initdb
postgresql.conf
and pg_hba.conf
on primaryThis is a normal step.
Add the following streaming replication configurations to postgresql.conf
on only primary:
listen_address = '*'
wal_level = replica
max_wal_senders = 4
(= 2 (The number of standbys) * 2
. * 2
is for unexpected connection close.)
/var/lib/pgsql/9.6/data/postgresql.conf
:
Before:
#listen_address = 'localhost'
#wal_level = minimal
#max_wal_senders = 0
After:
listen_address = '*'
wal_level = replica
max_wal_senders = 4
Add the following streaming replication configurations to pg_hba.conf
on only primary:
replicator
from 192.168.0.0/24
./var/lib/pgsql/9.6/data/pg_hba.conf
:
Before:
#local replication postgres peer
#host replication postgres 127.0.0.1/32 ident
#host replication postgres ::1/128 ident
After:
host replication replicator 192.168.0.0/24 md5
Create the user for replication on only primary:
% sudo -H systemctl start postgresql-9.6
% sudo -u postgres -H createuser --pwprompt --replication replicator
Enter password for new role: (passw0rd)
Enter it again: (passw0rd)
postgresql.conf
on primaryThis is a PGroonga specific step.
Add pgronga.enable_wal
parameter and pgroonga.max_wal_size
parameter configurations to postgresql.conf
on only primary:
/var/lib/pgsql/9.6/data/postgresql.conf
:
pgroonga.enable_wal = on
# You may need more large size
pgroonga.max_wal_size = 100MB
Restart PostgreSQL to apply the configuration:
% sudo -H systemctl restart postgresql-9.6
You can confirm whether you set the above parameters or not with the following SQL:
SELECT name,setting FROM pg_settings WHERE name LIKE '%pgroonga%';
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 --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 -u postgres -H pg_basebackup --host 192.168.0.30 --pgdata /var/lib/pgsql/9.6/data --xlog --progress --username replicator --password --write-recovery-conf
Password: (passw0rd)
149261/149261 kB (100%), 1/1 tablespace
postgresql.conf
on standbysThis is a normal step.
Add the following replica configurations to postgresql.conf
on only standbys:
hot_standby = on
Standbys:
/var/lib/pgsql/9.6/data/postgresql.conf
:
Before:
#hot_standby = off
After:
hot_standby = on
postgresql.conf
on standbysThis is a PGroonga specific step.
Since 2.3.3.
Add pgroonga_wal_applier
module to shared_preload_libraries
parameter:
Standbys:
/var/lib/pgsql/9.6/data/postgresql.conf
:
Before:
#shared_preload_libraries = ''
After:
shared_preload_libraries = 'pgroonga_wal_applier'
You can confirm whether you set shared_preload_libraries
parameter or not with the following SQL:
SELECT name,setting FROM pg_settings WHERE name = 'shared_preload_libraries';
This is a normal step.
Start PostgreSQL on standbys:
% sudo -H systemctl start postgresql-9.6
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
SELECT title FROM entries WHERE title %% 'replication';
-- title
-- --------------------------------
-- PGroonga and replication
-- PostgreSQL 9.6 and replication
-- (2 rows)