topic Re: Postgres endpoint Error in Qlik Replicate

Postgres endpoint Error

sush_1 — Thu, 01 Jun 2023 06:44:00 GMT

Hi Team,

I am trying to connect postgres database to qlik replicate. getting postgre odbc driver error that is libpd.dll not found. besides I have followed all the perquisites for connecting this endpoint. attaching screenshot of error msg.

Re: Postgres endpoint Error

john_wang — Thu, 01 Jun 2023 07:05:49 GMT

Hello @sush_1 ,

Thanks for reaching out!

It's hard to tell from the picture only this time. let's trace it by steps:

1- Make sure you are using x64-bit installation kit rather than 32-bit as Replicate support 64-bit driver only

2- What's the driver version please?

3- Would you please add the installation folder eg "C:\Program Files\psqlODBC\<versionNumber>\bin" to system environment PATH and restart Replicate services ?

4- How about if config an ODBC DSN manually to see if same error popup ? if yes then sesms the installation is some problematic.

Hope this helps.

Regards,

John.

Re: Postgres endpoint Error

sush_1 — Thu, 01 Jun 2023 08:57:39 GMT

Hi John

1. yes I am using 64 bit installation kit.

2. psqlodbc_12_02_0000-x64 this is the version of the driver.

3. I have also added the path to system variables.

still the the issue is there.

4. how to config ODBC dsn manuall?

Regards,

Sushant

Re: Postgres endpoint Error

Arun_Arasu — Thu, 01 Jun 2023 09:13:38 GMT

Hi @sush_1 , can you please follow the below steps and verify if the test connection works

To manually configure an ODBC DSN (Data Source Name), you can follow these general steps:

Open the ODBC Data Source Administrator:
In Windows, you can typically find it in the Control Panel under Administrative Tools or by searching for "ODBC" in the Start menu.

Navigate to the System DSN or User DSN tab: The System DSN tab configures a DSN that is available to all users on the computer, while the User DSN tab configures a DSN specific to the current user.

Click the "Add" button: This will open a dialog to create a new DSN.

Select the appropriate driver: Choose the driver that matches the database or data source you want to connect to. The available drivers depend on the software and database drivers installed on your system. For example, if you want to connect to a Microsoft SQL Server database, you would select the appropriate SQL Server driver.

Provide the necessary information: The exact information required may vary depending on the selected driver, but typically includes the DSN name, a description (optional), the server or database name, authentication details (username and password), and any additional connection parameters or options.

Test the connection: After providing the required information, you can use the "Test" or "Test Connection" button to verify that the connection to the data source is successful. This step helps ensure that the configuration is correct and the necessary drivers are installed.

Save the DSN: Once you have tested the connection and verified its success, save the DSN configuration.

After configuring the DSN, you can use it in applications that support ODBC to connect to the specified data source using the configured settings.

Re: Postgres endpoint Error

sush_1 — Thu, 01 Jun 2023 09:28:15 GMT

Hi Arun,

I have tested OBDC connection by adding DSN . It has shown that Connection Successful.

but in qlik replicate same error msg is pops up when I test the connection.

Regards,

Sushant

Re: Postgres endpoint Error

john_wang — Thu, 01 Jun 2023 10:08:06 GMT

Hello @sush_1 ,

Please confirm you restarted Replicate services after adding to PATH.

Regards,

John.

Re: Postgres endpoint Error

sush_1 — Thu, 01 Jun 2023 10:11:11 GMT

Hi John,

Yes, I have restarted the replicate services after doing this step.

Regards,

Sushant

Re: Postgres endpoint Error

john_wang — Thu, 01 Jun 2023 10:16:55 GMT

Hello @sush_1 ,

Thanks for the clarification.

Do you have multiple versions of PSQL ODBC driver installed on the same machine ? how many "libpq.dll" files you can find? if more than once then please remove the unnecessary ones.

BTW, try to reboot the server to see if it is helpful?

Regards,

John.

Re: Postgres endpoint Error

sush_1 — Thu, 01 Jun 2023 10:43:08 GMT

Hi John,

As I checked there is only one libpq.dll file in psql ODBC driver and also done with the rebooting the server. but still issue is there.

Regards,

Sushant

Re: Postgres endpoint Error

john_wang — Thu, 01 Jun 2023 13:04:42 GMT

Hello @sush_1 ,

Thank you for the update.

Let's continue the trace until we figure out the root cause.

Would you please copy all files from the PSQL ODBC installation folder (for example "C:\Program Files\psqlODBC\1302\bin") to Replicate bin folder (for example "C:\Program Files\Attunity\Replicate\bin") then see what happens?

thanks,

John.

Re: Postgres endpoint Error

sush_1 — Thu, 01 Jun 2023 14:46:00 GMT

HI John ,

1. Now I am able to connect with the database also connection is successful. I tried to replicate the table by creating task and got the error of failed to create replication slot.

2. I also checked for wal_level in psql configuration file and it is already set to logical.

3. Attaching the error msg screenshot

Regards,

Sushant

Re: Postgres endpoint Error

john_wang — Thu, 01 Jun 2023 15:10:31 GMT

Hello Sushant @sush_1 ,

Good news. The connectivity issue looks like a configuration issue eg PATH settings, or restart service etc.

Regarding the new error, please follow the Server Side setup steps, take note to restart PostgreSQL services after save the settings changes.

BTW, you may try to create a slot manually to troubleshoot the issue by using the same account connect to PostgreSQL, for example:

SELECT lsn FROM pg_create_logical_replication_slot('testslot', 'test_decoding');

Hope this helps.

Regards,

John.

Re: Postgres endpoint Error

sush_1 — Fri, 02 Jun 2023 04:18:15 GMT

Hi John ,

I am done with the server side setup steps and also restarted the server. Although I have set the wal_level to logical, still the above query you send results to that logical decoding requires wal_levels = logical.

Attaching screenshots for your reference.

Regards ,

Sushant

Re: Postgres endpoint Error

john_wang — Fri, 02 Jun 2023 04:44:25 GMT

Hello Sushant @sush_1 ,

From the picture you did not set wal_level correctly:

#wal_level = logical

This line is commented out. You need remove the prefix "#". It's the same for other parameters.

BTW, if you need further assistance please choose this article closed by "Accept as Solution" as the original issue fixed. Open a new one to make articles clear.

Thank you,

John.

Re: Postgres endpoint Error

sush_1 — Fri, 02 Jun 2023 04:49:36 GMT

Hi John,

Thank you so much for your continuous support.

Regards,

Sushant