topic Re: Unable to start task using PostGres as target endpoint in Qlik Replicate

Unable to start task using PostGres as target endpoint

desmondchew — Thu, 06 Jun 2024 07:29:22 GMT

Hitting errors while trying to start a new task using Postgres SQL RDS as target endpoint. The source endpoint is RDS SQL Server.

Failed to send 'START_REGULAR' command for transaction '00', start time '2024-06-06 08:22:13.410', commit lsn '000001EB00005CA70001'

I have granted "can login" and "create database" privileges to user in Qlik Replicate connection setup.

https://help.qlik.com/en-US/replicate/May2024/Content/Replicate/Main/PostgreSQL/security_req_postgresql_target.htm#ar_postgresds_802412600_1397925

Stream component 'st_0_Semarchy_DEV' terminated
Stream component failed at subtask 0, component st_0_Semarchy_DEV
Error executing command
Cannot create Exception table
Cannot create Special table
execute create table statement failed, statement CREATE TABLE "attrep_apply_exceptions" ( "TASK_NAME" VARCHAR(128) NOT NULL, "TABLE_OWNER" VARCHAR(128) NOT NULL, "TABLE_NAME" VARCHAR(128) NOT NULL, "ERROR_TIME" TIMESTAMP NOT NULL, "STATEMENT" text NOT NULL, "ERROR" text NOT NULL )
RetCode: SQL_ERROR SqlState: 42501 NativeError: 1 Message: ERROR: permission denied for schema public;
Error while executing the query
Failed (retcode -1) to execute statement: CREATE TABLE "attrep_apply_exceptions" ( "TASK_NAME" VARCHAR(128) NOT NULL, "TABLE_OWNER" VARCHAR(128) NOT NULL, "TABLE_NAME" VARCHAR(128) NOT NULL, "ERROR_TIME" TIMESTAMP NOT NULL, "STATEMENT" text NOT NULL, "ERROR" text NOT NULL )

Re: Unable to start task using PostGres as target endpoint

OritA — Thu, 06 Jun 2024 07:35:54 GMT

Hi,

The table attrep_apply_exception is a table created by Replicate in the target database and includes details about data errors received in the task (when they occurs) .

As we see from the message the error that you get is due to lack of permission to the user used by Replicate to access the target database. We recommend to doublecheck the permission granted to the user. In case problem persists, please open a case in SaleForce and attach to the case the task diagnostic package so we can review the details of the setting and help solving the problem.

Regards,

Orit

Re: Unable to start task using PostGres as target endpoint

john_wang — Thu, 06 Jun 2024 07:46:54 GMT

Hello @desmondchew ,

I greatly agree with @OritA , PostgreSQL will error out and tell you that you don't have permissions to create something inside the PUBLIC schema without explicitly specifying who is allowed to do that beforehand. You need to grant the privileges to Qlik Replicate endpoint user with:

GRANT ALL ON SCHEMA public TO <QR-CDCUser>;

Hope this helps.

John.

Re: Unable to start task using PostGres as target endpoint

desmondchew — Thu, 06 Jun 2024 07:48:46 GMT

I have resolved this after granted the permission on public schema for Qlik user to write.

Re: Unable to start task using PostGres as target endpoint

john_wang — Thu, 06 Jun 2024 07:50:27 GMT

Thank you so much for your great support @desmondchew !