This is a document for PGroonga 2.X and 3.X. See PGroonga 1.x document when you're using old PGroonga.

pgroonga_set_writable function

Summary

pgroonga_set_writable function set whether you can change PGroonga data or not. Normally, you don't need to change it.

You can implement online backup with this function. You need to keep the following conditions to use this use case:

Syntax

Here is the syntax of this function:

bool pgroonga_set_writable(new_writable)

new_writable is a bool type value. true means writable and false is read-only.

It returns whether writable or not before changing the current state.

Usage

Here are sample schema and data:

SET pgroonga.enable_wal = yes;

CREATE TABLE memos (
  content text
);

CREATE INDEX pgroonga_memos_index
          ON memos
       USING pgroonga (content);

INSERT INTO memos VALUES ('PGroonga (PostgreSQL+Groonga) is great!');

You can't change the PGroonga index after you pass false to pgroonga_set_writable function:

SELECT pgroonga_set_writable(false);
--  pgroonga_set_writable 
-- -----------------------
--  t
-- (1 row)
INSERT INTO memos VALUES ('Groonga is great!');
-- ERROR:  pgroonga: can't insert a record while pgroonga.writable is false

SELECT pgroonga_set_writable(true);
--  pgroonga_set_writable 
-- -----------------------
--  f
-- (1 row)
INSERT INTO memos VALUES ('Groonga is great!');
-- INSERT 0 1

Online backup

You can implement online backup with this function. You need to keep the following conditions to use this use case:

You must add the following configuration to your postgresql.conf:

pgroonga.enable_wal = yes

You must disable autovacuum:

CREATE TABLE memos (
  content text
) WITH (autovacuum_enabled = false);

CREATE INDEX pgroonga_memos_index
          ON memos
       USING pgroonga (content);

You must call SELECT pgroonga_command('io_flush', ARRAY['only_opened', 'yes']) in the same session after you change your data:

INSERT INTO memos VALUES ('PGroonga (PostgreSQL+Groonga) is great!');
SELECT pgroonga_command('io_flush', ARRAY['only_opened', 'yes']);

It's recommended that you run VACUUM manually before you create backup:

VACUUM ANALYZE memos;
SELECT pgroonga_command('io_flush', ARRAY['only_opened', 'yes']);

You must make your PGroonga data read-only before you create backup:

SELECT pgroonga_set_writable(false);
SELECT pgroonga_command('io_flush', ARRAY['only_opened', 'yes']);

Now, you can create backup without stopping PostgreSQL:

db_name="YOUR_DB_NAME"

# Detect database information
db_oid=$(psql \
  --dbname ${db_name} \
  --no-psqlrc \
  --no-align \
  --tuples-only \
  -c "SELECT datid FROM pg_stat_database WHERE datname = '${db_name}'")
data_dir=$(psql \
  --dbname ${db_name} \
  --no-psqlrc \
  --no-align \
  --tuples-only \
  -c "SHOW data_directory")

# Define directories
db_dir=${data_dir}/base/${db_oid}
backup_dir=${data_dir}/../../backup

# Create backup directory
mkdir -p ${backup_dir}

# Create backup
rsync -a --include '/pgrn*' --exclude '*' --delete ${db_dir}/ ${backup_dir}/

It's recommended that your PGroonga's WAL is truncated. You can run pgroonga_wal_truncate function in read-only mode:

SELECT pgroonga_wal_truncate();
SELECT pgroonga_command('io_flush', ARRAY['only_opened', 'yes']);

If you truncate PGroonga's WAL, you must create backup again with the above shell script. The process will be fast because rsync copies only different data.

You must make your PGroonga data writable again:

SELECT pgroonga_set_writable(true);
SELECT pgroonga_command('io_flush', ARRAY['only_opened', 'yes']);

See also