Is enable_wal needed in standby server?

[DeoLeung]

Hi,

I'm making a slave db and found this parameter in the document.

without it, the streaming seems to be ok, but strange that the slave db size is about 2x of the master(investigating)

my question is, is it required for slave db to maintain a usable pgroonga index?

[kou]

without it, the streaming seems to be ok

Does it mean that you can do full text search with PGroonga index on standby server with pgroonga.enable_wal = off?

If it's yes, could you show EXPLAIN SELECT ... on your standby server?

but strange that the slave db size is about 2x of the master(investigating)

How did you calculate the DB size on the master server and the standby server?

You can't calculate the DB size for PGroonga with the standard PostgreSQL way. See also: #15

is it required for slave db to maintain a usable pgroonga index?

Yes. pgroonga.enable_wal = on is required as documented.

[DeoLeung]

yes, explain on the standby server(pg12) is

show pgroonga.enable_wal; -- off, on both master/slave
explain select * from "data-xxx"
where extra &` 'string @ "121号楼"';

Index Scan using "data-xxx_extra_idx" on "data-xxx"  (cost=0.00..8196.64 rows=206 width=1687)
  Index Cond: (extra &` 'string @ "121号楼"'::text)

same as master

for the size , I did du -h on the path

# master, around 3.7k files under 16386
5.4G    /xxx/base/16386
18M     /xxx/base/26334
8.0M    /xxx/base/1
5.5G    /xxx/base

# slave, around 6k files under 16386
17G     /xxx/base/16386
17G     /xxx/base

I will do a file list diff on it later

[kou]

It's strange...

Do you use streaming replication https://pgroonga.github.io/reference/streaming-replication.html or logical replication https://pgroonga.github.io/reference/logical-replication.html ?

[DeoLeung]

I use repmgr, it shall be streaming replica

is it because the index exists before I did the first base backup?

let me double check with new created table.

[DeoLeung]

I tried the entries example, and got ERROR: pgroonga: object isn't found: <Sources61623> on slave now

if I add the enable_wal now, does master restart required? and do I need to re-do a new slave base backup?

---

without a restart of master, I added pgroonga.enable_wal = on , did a new base backup, creating new table on master still got the above error on slave

[kou]

I added pgroonga.enable_wal = on

Does it mean that you added pgroonga.enable_wal = on to postgresql.conf?
If it's yes, you need to restart your master server and standby servers.

If you used SET pgroonga.enable_wal = on, you don't need to restart them. But the configuration isn't persisted. You also need add pgroonga.enable_wal = on to postgresql.conf.

You don't need to re-run base backup after you enable pgroonga.enable_wal. You just need to REINDEX INDEX ${PGROONGA_INDEX}. It recreates the PGroonga index with full WAL and the recreated PGroonga index will work on standby servers well.

[DeoLeung]

Finally got a low traffic chance to restart the server, it seems not working, I still got the error.

from the log I can see each time recreate/ reindex it does switch the object

xxx                         | 2020-07-18 06:39:03.992 UTC [97] STATEMENT:  SELECT title FROM entries WHERE title %% 'replication';
xxx                         | 2020-07-18 06:39:05.405 UTC [97] ERROR:  pgroonga: object isn't found: <Sources69233>
xxx                         | 2020-07-18 06:39:05.405 UTC [97] STATEMENT:  SELECT title FROM entries WHERE title %% 'replication';
xxx                         | 2020-07-18 06:40:14.643 UTC [97] ERROR:  pgroonga: object isn't found: <Sources69234>
xxx                         | 2020-07-18 06:40:14.643 UTC [97] STATEMENT:  SELECT title FROM entries WHERE title %% 'replication';
xxx                         | 2020-07-18 06:40:17.660 UTC [97] ERROR:  pgroonga: object isn't found: <Sources69234>
xxx                         | 2020-07-18 06:40:17.660 UTC [97] STATEMENT:  SELECT title FROM entries WHERE title %% 'replication';
xxx                         | 2020-07-18 06:41:57.280 UTC [97] ERROR:  pgroonga: object isn't found: <Sources69235>
xxxx                        | 2020-07-18 06:41:57.280 UTC [97] STATEMENT:  SELECT title FROM entries WHERE title %% 'replication';
xxx                         | 2020-07-18 06:41:58.789 UTC [97] ERROR:  pgroonga: object isn't found: <Sources69235>
xxx                         | 2020-07-18 06:41:58.789 UTC [97] STATEMENT:  SELECT title FROM entries WHERE title %% 'replication';

[kou]

Um...
Could you provide an instruction to reproduce this case on my environment?

[DeoLeung]

I tried to reproduce it using the docker images with fresh database...and it works...

so I wonder would it be possible that my production master was created using 2.2.5 and later replaced with 2.2.6 image.

since the database is not that large, I would try later just dump and restore to a new database to see if it's ok

[zyp-rgb]

Um...
Could you provide an instruction to reproduce this case on my environment?

here is a repo reproducing the case
https://github.com/zyp-rgb/pgroonga-fail-reproduce

[kou]

@zyp-rgb Thanks. We found that postgresql-12-pgroonga package isn't build with WAL support.
We've uploaded postgresql-12-pgdg-pgroonga 2.2.6-2. (Note that package name is changed. -pgdg is added to show the package is for PostgreSQL provided by PGDG.)

Could you update your Docker image and try again?

[zyp-rgb]

@zyp-rgb Thanks. We found that postgresql-12-pgroonga package isn't build with WAL support.
We've uploaded postgresql-12-pgdg-pgroonga 2.2.6-2. (Note that package name is changed. -pgdg is added to show the package is for PostgreSQL provided by PGDG.)

Could you update your Docker image and try again?

That would work!!
Thank you very much

[kou]

Thanks for confirming this.
I close this.