Failed (retcode -1) to execute statement: 'SELECT lsn FROM pg_create_logical_replication_slot('cp_zabbix_00338063_3a70ce11_fb42_0d42_a881_04506a7df6e8

[CarlosCavalcanti]

Hello there,

I'm trying to create a replication task from a postgres database, but when I start the task, I'm facing the following errors:

00001436: 2024-07-04T13:33:28 [SOURCE_CAPTURE ]E: Failed (retcode -1) to execute statement: 'SELECT lsn FROM pg_create_logical_replication_slot( 'cp_zabbix_00338063_3a70ce11_fb42_0d42_a881_04506a7df6e8', 'test_decoding' )' [1022502] (ar_odbc_stmt.c:2810)

00001436: 2024-07-04T13:33:28 [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 58P01 NativeError: 1 Message: ERROR: could not access file "test_decoding": No such file or directory;

Error while executing the query [1022502] (ar_odbc_stmt.c:2816)

00001436: 2024-07-04T13:33:28 [SOURCE_CAPTURE ]E: wal_slot_create(...) - Unable to create slot 'cp_zabbix_00338063_3a70ce11_fb42_0d42_a881_04506a7df6e8' (on execute(...) phase) [1022502] (postgres_endpoint_wal_utils.c:3708)

00001436: 2024-07-04T13:33:28 [SOURCE_CAPTURE ]E: Creating replication slot 'cp_zabbix_00338063_3a70ce11_fb42_0d42_a881_04506a7df6e8' failed. [1022502] (postgres_endpoint_capture.c:415)

00013132: 2024-07-04T13:33:28 [TASK_MANAGER ]I: Task error notification received from subtask 0, thread 0, status 1022502 (replicationtask.c:3517)

00001436: 2024-07-04T13:33:28 [SOURCE_CAPTURE ]E: Error executing source loop [1022502] (streamcomponent.c:1940)

00001436: 2024-07-04T13:33:28 [TASK_MANAGER ]E: Stream component failed at subtask 0, component st_0_Source_Zabbix [1022502] (subtask.c:1396)

00001436: 2024-07-04T13:33:28 [SOURCE_CAPTURE ]E: Stream component 'st_0_Source_Zabbix' terminated [1022502] (subtask.c:1565)

What can I do to resolve this?
My wal-level parameter already is ">= logical" and the user has superuser.

[john_wang]

Hello @CarlosCavalcanti ,

Welcome to Qlik Community forum and thanks for reaching out here!

This is because the test_decoding plugin was not installed in your PostgreSQL instance. You may install it manually.

BTW, as I remember this plugin is part of the newer versions of PostgreSQL installation by default. What's the PostgreSQL Version you are running now?

thanks,

John.

[john_wang · Accepted solution]

Hello @CarlosCavalcanti ,

Plugin "test_decoding" is SQL-based test/example module for WAL logical decoding, it is part of the additional supplied modules in postgresql-contrib, you may download the proper version installation file from PostgreSQL site eg PostgreSQL version 15.4 on RHEL 8 platform 

postgresql15-contrib-15.4-1PGDG.rhel8.x86_64.rpm 

 Hope this helps.

John.

[CarlosCavalcanti]

Hello @john_wang,

Thank you for helping. I'm using postgres 14.

[CarlosCavalcanti]

@john_wang, how may I install the "test_decoding" plugin on Postgres 14?

[john_wang]

Hello @CarlosCavalcanti ,

You may download the appropriate version/platform contrib from PostgreSQL download link, for example version 14.x. It's better you work with your DBA to install it and restart the database.

Hope this helps.

John.