SQLines Data - Configure and Troubleshoot Connection to PostgreSQL

If you are going to connect to a localhost PostgreSQL database you do not need to setup any additional libraries to use SQLines Data tool as they are already supplied with PostgreSQL server.

If you need to connect to a remote PostgreSQL database, you need to have PostgreSQL C API libpq (PostgreSQL native client software) installed in your system.

If you use pgAdmin 4 tool the PostgreSQL client software is also already installed.

First check if you already have PostgreSQL libpq.so.5 library, for example:

• Linux:
  • /usr/lib/libpq.so.5
  • /usr/lib64/libpq.so.5
  • /usr/pgsql-9.4/lib/libpq.so.5
  • /opt/postgres/9.0.4/server/lib/libpq.so.5

Note that many Linux distributions are supplied with PostgreSQL library by default. Try to find its location by running:

find / -name "libpq.so.5"

• Windows: (Directory and version number can be different) i.e.
  • C:\Program Files\PostgreSQL\15\bin\libpq.dll - PostgreSQL server
  • C:\Program Files\PostgreSQL\10\bin\libpq.dll
  • C:\Program Files\pgAdmin 4\runtime\libpq.dll - pgAdmin 4 tool

If you do not have PostgreSQL libraries installed, go to https://www.postgresql.org/download and install appropriate version or install pgAdmin 4 tool at https://www.pgadmin.org/download/

To enable SSL connection to PostgreSQL database server (i.e. Google Cloud SQL for PostgreSQL), set the following environment variables before running ./sqldata:

  export PGSSLMODE=require
  export PGSSLROOTCERT=server-ca.pem
  export PGSSLCERT=client-cert.pem
  export PGSSLKEY=client-key.pem

When you click Test Connection you get the following error:

• Error Loading PostgreSQL libpq.dll. The specified module could not be found

Possible reasons:

• PostgreSQL client is not installed. Downlaod and install PostgreSQL client at https://www.postgresql.org/download or install pgAdmin 4 tool at https://www.pgadmin.org/download/

• PostgreSQL client is installed but SQLines Data tool cannot find libpq.dll for some reason. Add the directory where libpq.dll is located (for example, C:\Program Files\PostgreSQL\15\bin for PostgreSQL server, or C:\Program Files\pgAdmin 4\runtime for pgAdmin 4 tool) to the PATH environment variable.

To set the PATH variable on Windows, right-click the Computer icon, select Properties, click the Advanced System Settings. Then select Advanced tab and click the Environment Variables button. Go to System Variables, find and append Path.

Note. You have to restart SQLines Data tool after modifying the PATH environment variable.

You can also edit sqldata.cfg configuration file and set -pg_lib option.

Contact us at support@sqlines.com if you still have any connection problems.