Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Connect Knowledge Base

Definitive answers from Connect experts.

How to Deploy Alteryx Connect on PostgreSQL Database

KaterinaR
Alteryx
Alteryx
Created
The purpose of this document is to describe the manual steps to deploy Alteryx Connect on the PostgreSQL database. This document is valid for Alteryx Connect 2020.2 or newer.

Prerequisites
1. Database should include the UTF8 support.
2. Ensure the empty database “std_xforms” exists.
3. Ensure the schema “std_xforms” exists.
4. Ensure the user “std_xforms” with the default database “std_xforms” exists. The user must have assigned the role “db_owner”. 
5. Test the database connection with your SQL client to verify your database is accepting the TCP/IP connection. Make sure your username and password is working.

Installation Steps
1. Run the Alteryx Connect installer and ensure the Start the Alteryx Connect service now checkbox is unchecked. If startup occurred, stop the instance, remove the .done files in the ac_init folder and delete the default h2 database files.
idea Skyscrapers
2. Update the connection string in alteryx_connect.properties:
jdbc:postgresql://localhost:5432/std_xforms?currentSchema=std_xforms
NOTE: “localhost” will be correct only if you're running PostgreSQL locally. Otherwise you need to enter the hostname/IP address of your PostgreSQL server. Here is the full properties file:
[global]
semanta.xf3.db.jdbc=jdbc:postgresql://localhost:5432/std_xforms?currentSchema=std_xforms
semanta.xf3.db.user=std_xforms
semanta.xf3.db.schema=std_xforms
semanta.xf3.db.password=Conn3ct!!
enable.configuration=true
display.use.button=true
3. Start the Windows service Alteryx Connect and wait for the initialization of the database. The initialization can take up to 10 minutes. 
4. After the database is initialized, you should be able to connect to the UI (for example, http://localhost/). 
5. If you are not able to connect to the UI: 
a. Check the logs (../connect_catalina.log).
b. Check the tables are created in the selected schema.
idea Skyscrapers
c. Check your connection string is OK.

Appendix: Installing and Setting Up PostgreSQL Server Step by Step Guide
  1. Install PostgreSQL: To install PostgreSQL, see the Install PostgreSQL tutorial. 
  2. Run PGAdmin.
  3. Create db user:
    CREATE ROLE std_xforms WITH
    LOGIN
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    INHERIT
    NOREPLICATION
    CONNECTION LIMIT -1
    PASSWORD 'Conn3ct!!';
    COMMENT ON ROLE std_xforms IS 'Connect db user';
  4. Create database:
    CREATE DATABASE std_xforms
    WITH 
    OWNER = std_xforms
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;
    
    COMMENT ON DATABASE std_xforms
    IS 'Connect database';
  5. Connect to the newly created database and create a schema:
    CREATE SCHEMA std_xforms AUTHORIZATION std_xforms;

Troubleshooting for Known Errors 

If you observe the following errors in the log, we suggest to increase the connection pool in the Postgres database.
See How to increase the max connections in Postgres?.
Edit postgresql.conf in the installation directory (C:\Program Files\PostgreSQL\12\data) and increase the parameters max_connections, shared_buffers, and kernel.shmmax, as described in the link. 
13:36:43.756 [C3P0PooledConnectionPoolManager[identityToken->1hgekyma9c1iyyvy58u2w|7ff863f0]-HelperThread-#14] WARN  c.m.v.resourcepool.BasicResourcePool - com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@71621d64 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (10). Last acquisition attempt exception: 
org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
    at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:524) ~[postgresql-42.2.10.jar:42.2.10]
    at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:145) ~[postgresql-42.2.10.jar:42.2.10]
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:196) ~[postgresql-42.2.10.jar:42.2.10]
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49) ~[postgresql-42.2.10.jar:42.2.10]
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:211) ~[postgresql-42.2.10.jar:42.2.10]
    at org.postgresql.Driver.makeConnection(Driver.java:459) ~[postgresql-42.2.10.jar:42.2.10]
    at org.postgresql.Driver.connect(Driver.java:261) ~[postgresql-42.2.10.jar:42.2.10]
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175) ~[c3p0-0.9.5.2.jar:0.9.5.2]
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220) ~[c3p0-0.9.5.2.jar:0.9.5.2]
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206) ~[c3p0-0.9.5.2.jar:0.9.5.2]
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203) ~[c3p0-0.9.5.2.jar:0.9.5.2]
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138) ~[c3p0-0.9.5.2.jar:0.9.5.2]
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125) ~[c3p0-0.9.5.2.jar:0.9.5.2]
    at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44) ~[c3p0-0.9.5.2.jar:0.9.5.2]
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870) ~[c3p0-0.9.5.2.jar:0.9.5.2]
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696) ~[mchange-commons-java-0.2.11.jar:0.2.11]
13:36:43.757 [C3P0PooledConnectionPoolManager[identityToken->1hgekyma9c1iyyvy58u2w|7ff863f0]-HelperThread-#14] WARN  c.m.v.resourcepool.BasicResourcePool - Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@33244563 is interrupting al

 




 
 
Comments
andrewdatakim
12 - Quasar
12 - Quasar

Hi @KaterinaR ,

 

Is this going to be a required conversion at some point for customers who are using H2 currently? Also if we are looking to use Postgres instead of H2 is there conversion procedures you have available?

 

Thank you for your help.

KaterinaR
Alteryx
Alteryx

Hi @PetrH,

Would you please have a look at the question raised by @andrewdatakim

Thank you!

VojtechT
Alteryx
Alteryx

Hi @andrewdatakim ,

 

  • no, we definitely do not plan to abandon H2. This feature is to provide additional option for those who would like to leverage enhanced capabilities of PostgreSQL (or MS SQL, as that's also option) or just their internal policies do not allow for H2.
  • and no, there is no conversion procedure as the official recommendation is that the switch has to be done once Connect is installed for the first time. In theory, it should be possible to migrate from H2 to PgSQL and I would expect that a simple wf to copy all the H2 tables into PgSQL tables should do the job.