starred/lldap-lldap

Fork 0

mirror of https://github.com/lldap/lldap.git synced 2026-04-26 00:36:01 +03:00

[GH-ISSUE #1194] [BUG] Metadata table error with Postgres Replica #422

New issue

Closed

opened 2026-02-27 08:17:13 +03:00 by kerem · 5 comments

kerem commented

2026-02-27 08:17:13 +03:00

Owner

Originally created by @azamaulanaaa on GitHub (Jun 28, 2025).
Original GitHub issue: https://github.com/lldap/lldap/issues/1194

Describe the bug
I want to connect lldap with postgresql with replica via wal_level = logical. lldap produce error on intial or successive connection to postgres

To Reproduce
Steps to reproduce the behavior:

Start PostgreSQL database with wal_level = logical
Set lldap with PostgreSQL
start lldap
See error

Expected behavior
Work just fine.

Logs

2025-06-28T09:40:47.782111568+00:00  INFO     set_up_server [ 23.7ms | 95.10% / 100.00% ]
2025-06-28T09:40:47.782159152+00:00  INFO     ┝━ ｉ [info]: Starting LLDAP version 0.6.1
2025-06-28T09:40:47.806475314+00:00  DEBUG    ┝━ get_schema_version [ 1.16ms | 4.90% ]
2025-06-28T09:40:47.810129947+00:00  DEBUG    │  ┕━ 🐛 [debug]:  | return: Some(SchemaVersion(1))
2025-06-28T09:40:47.810181073+00:00  INFO     ┝━ ｉ [info]: Upgrading DB schema from version 1
2025-06-28T09:40:47.810186823+00:00  INFO     ┕━ ｉ [info]: Upgrading DB schema to version 2   
Error: while creating base tables

Caused by:
    0: Execution Error: error returned from database: cannot update table "metadata" because it does not have a replica identity and publishes updates
    1: error returned from database: cannot update table "metadata" because it does not have a replica identity and publishes updates
    2: error returned from database: cannot update table "metadata" because it does not have a replica identity and publishes updates
    3: cannot update table "metadata" because it does not have a replica identity and publishes updates

Additional context
it is fixable through run this sql query then restart lldap

alter table metadata ADD COLUMN id SERIAL; alter table metadata add constraint metadata_pkey primary key (id);

Originally created by @azamaulanaaa on GitHub (Jun 28, 2025). Original GitHub issue: https://github.com/lldap/lldap/issues/1194 **Describe the bug** I want to connect lldap with postgresql with replica via `wal_level = logical`. lldap produce error on intial or successive connection to postgres **To Reproduce** Steps to reproduce the behavior: 1. Start PostgreSQL database with `wal_level = logical` 2. Set lldap with PostgreSQL 3. start lldap 4. See error **Expected behavior** Work just fine. **Logs** ``` 2025-06-28T09:40:47.782111568+00:00 INFO set_up_server [ 23.7ms | 95.10% / 100.00% ] 2025-06-28T09:40:47.782159152+00:00 INFO ┝━ ｉ [info]: Starting LLDAP version 0.6.1 2025-06-28T09:40:47.806475314+00:00 DEBUG ┝━ get_schema_version [ 1.16ms | 4.90% ] 2025-06-28T09:40:47.810129947+00:00 DEBUG │ ┕━ 🐛 [debug]: | return: Some(SchemaVersion(1)) 2025-06-28T09:40:47.810181073+00:00 INFO ┝━ ｉ [info]: Upgrading DB schema from version 1 2025-06-28T09:40:47.810186823+00:00 INFO ┕━ ｉ [info]: Upgrading DB schema to version 2 Error: while creating base tables Caused by: 0: Execution Error: error returned from database: cannot update table "metadata" because it does not have a replica identity and publishes updates 1: error returned from database: cannot update table "metadata" because it does not have a replica identity and publishes updates 2: error returned from database: cannot update table "metadata" because it does not have a replica identity and publishes updates 3: cannot update table "metadata" because it does not have a replica identity and publishes updates ``` **Additional context** it is fixable through run this sql query then restart lldap ``` alter table metadata ADD COLUMN id SERIAL; alter table metadata add constraint metadata_pkey primary key (id); ```

kerem

2026-02-27 08:17:13 +03:00

closed this issue
added the
bug
label

kerem commented

2026-02-27 08:17:14 +03:00

Author

Owner

@nitnelave commented on GitHub (Jun 28, 2025):

It doesn't really make sense for the metadata table to have a serial ID. Here, we're kinda abusing the SQL DB to store single scalar values (and currently just one, the migration version), in order to avoid having state anywhere else.

I'm not familiar with postgres replication, so I'm not sure I can help here

@nitnelave commented on GitHub (Jun 28, 2025): It doesn't really make sense for the metadata table to have a serial ID. Here, we're kinda abusing the SQL DB to store single scalar values (and currently just one, the migration version), in order to avoid having state anywhere else. I'm not familiar with postgres replication, so I'm not sure I can help here

kerem commented

2026-02-27 08:17:14 +03:00

Author

Owner

@azamaulanaaa commented on GitHub (Jun 28, 2025):

Sorry, wal_level = logical is not enough to produce error, it requires a publication. here is how i achieve publication

nerdctl exec -it postgres psql -U postgres -d postgres -c "CREATE PUBLICATION postgres_pub FOR ALL TABLES;"

note

nerdctl is replacement for docker
postgres is the container name
postgres is the postgres user
postgres is the database name

@azamaulanaaa commented on GitHub (Jun 28, 2025): Sorry, `wal_level = logical` is not enough to produce error, it requires a publication. here is how i achieve publication `nerdctl exec -it postgres psql -U postgres -d postgres -c "CREATE PUBLICATION postgres_pub FOR ALL TABLES;"` note - nerdctl is replacement for docker - postgres is the container name - postgres is the postgres user - postgres is the database name

kerem commented

2026-02-27 08:17:14 +03:00

Author

Owner

@azamaulanaaa commented on GitHub (Jun 28, 2025):

given the circumstances that metadata table has, i found the fastest way is to make version column as primary key.

alter table metadata add constraint metadata_pkey primary key (version);

i able to do the pr but i cannot guarantee 100% works as the ci hard to replicate in my side. i need to patch a lot of github ci yaml. as long as it able to compile and pass all the tests, it should be fine right?

@azamaulanaaa commented on GitHub (Jun 28, 2025): given the circumstances that metadata table has, i found the fastest way is to make version column as primary key. `alter table metadata add constraint metadata_pkey primary key (version);` i able to do the pr but i cannot guarantee 100% works as the ci hard to replicate in my side. i need to patch a lot of github ci yaml. as long as it able to compile and pass all the tests, it should be fine right?

kerem commented

2026-02-27 08:17:14 +03:00

Author

Owner

@nitnelave commented on GitHub (Jun 28, 2025):

I feel like the best solution would be to not create a publication for the metadata table. It only changes on DB migrations, which happen on startup when changing LLDAP versions

@nitnelave commented on GitHub (Jun 28, 2025): I feel like the best solution would be to not create a publication for the metadata table. It only changes on DB migrations, which happen on startup when changing LLDAP versions

kerem commented

2026-02-27 08:17:14 +03:00

Author

Owner

@azamaulanaaa commented on GitHub (Jun 28, 2025):

sorry, i miss that option.

after looking closely at pgsql docs for publication. pgsql's publication does not include alter table. so metadata table should not include at publication. i will close this issue and the pr. Thank you

@azamaulanaaa commented on GitHub (Jun 28, 2025): sorry, i miss that option. after looking closely at pgsql docs for publication. pgsql's publication does not include alter table. so `metadata` table should not include at publication. i will close this issue and the pr. Thank you

kerem referenced this issue

2026-02-27 09:09:32 +03:00

[PR #422] [MERGED] server, refactor: Add a conversion from bool for the filters #731