Hi Francesco,
The first way you provided it’s the one i used to. Here is the method explained: 1) Run project into a PostgreSQL instance and let tables populate; 2) Create & edit audit_pgxl.sql (audit folder) & tables_pgxl.sql (quartz folder) using PostgreSQL defaults (audit.sql & tables_postgres.sql), then add a “DISTRIBUTE BY REPLICATION;” statement for each CREATE TABLE instruction; (see http://files.postgres-xl.org/documentation/sql-createtable.html); 3) Edit core/src/main/resources/domains.xml by adding: <entry key="openjpa.ConnectionFactoryMode" value="managed"/> After <entry key="openjpa.NontransactionalWrite" value="false"/> 4) Edit provisioning.properties by this way: quartz.jobstore=org.quartz.impl.jdbcjobstore.PostgreSQLDelegate quartz.sql=tables_pgxl.sql 5) Edit Master.properties by this way (accordingly to https://sourceforge.net/p/postgres-xc/xc-wiki/JDBC, you must set binaryTransfer=false when using JDBC) : Master.driverClassName=org.postgresql.Driver Master.url=jdbc:postgresql://pgxl-server-ip:port/syncope?binaryTransfer=false?tcpKeepAlive=true Master.schema= Master.username=syncope Master.password=syncope Master.databasePlatform=org.apache.openjpa.jdbc.sql.PostgresDictionary Master.orm=META-INF/spring-orm.xml # note: other connection pool settings can also be configured here, see DataSource definition Master.pool.validationQuery=SELECT 1 Master.audit.sql=audit_pgxl.sql 6) Make sure you reflect JDBC url on tomcat/conf/context.conf too, i.e.: <!-- Configure Syncope JDBC DataSource --> <Resource name="jdbc/syncopeDataSource" auth="Container" type="javax.sql.DataSource" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://pgxl-server-ip:port/syncope?binaryTransfer=false?tcpKeepAlive=true" username="syncope" password="syncope" maxTotal="20" maxIdle="10" maxWaitMillis="-1"/> 7) Rebuild .war files using mvn clean verify -Dconf.directory=/opt/syncope/conf -Dbundles.directory=/opt/syncope/bundles -Dlog.directory=/opt/syncope/log from Syncope’s root project folder (make sure you specify your custom directories); 8) Make a pg_dump from PostgreSQL instance using this command: pg_dump -U syncope --column-inserts syncope > export.sql 9) Add “DISTRIBUTE BY REPLICATION;” statement for each CREATE TABLE on export.sql file too; 10) Import modified export.sql on Postgres-XL instance using this command: psql -U syncope -d syncope -f backup.sql Make sure you got user syncope (with password syncope) & db created, i.e: createuser -P -s --replication syncope createdb -O syncope syncope And you set right connection permissions on pg_hba.conf file (Postgres-XL will trust local connections by default) 11) Finally, Deploy .war files into tomcat/webapps and started Tomcat instance. Hope this will be a good starting point for those that want to try Postgres-XL as DB. Have a nice day. :) Alessio Maccioni Area Infrastrutture Tecnologiche Divisione Pubblica Amministrazione e Sanità <mailto:[email protected]> [email protected] Engineering Ingegneria Informatica S.p.A. Via Santa Gilla - P.zza L'Unione Sarda - Edificio F, 2° piano Tel.: +39-0700997013 - VRUC: 606013 <http://www.eng.it/> www.eng.it Da: Francesco Chicchiriccò [mailto:[email protected]] Inviato: martedì 8 novembre 2016 09:22 A: [email protected] Oggetto: Re: Using Syncope with Postgres-XL Hi Alessio, I was wondering if you made any progress on this, it seems like as interesting use case. Regards. On 25/10/2016 13:28, Francesco Chicchiriccò wrote: On 25/10/2016 10:32, Alessio Maccioni wrote: Hello, We are trying to deploy Syncope v2.0.1 into a Tomcat 8 AS. Package is built using latest version of Maven. We are using latest Postgres-XL as DB container via JDBC connector. All settings are done as reference guide. Since XL’s “CREATE TABLE” logica is different from classic Postgres, we need to specify how tables will be distributed into datanodes, otherwise we get an error during constraints initialization (Postgres-XL’s distribution table is by HASH default). Inspecting .WAR files I just found and edited audit & quartz SQL instructions, but i can’t find main tables ones. Can you point me to the right direction? Hi Alessio, even if I am personally a bug fun of PostgreSQL, I wasn't aware of Postgres-XL: it looks definitely cool [1]. If you need to tweak the whole SQL schema creation, you will have to: 1. provide your own SQL script based on [2], save it as core/src/main/resources/quartz/tables_pgxl.sql, change provisioning.properties accordingly 2. provide your own SQL script based on [3], place it under core/src/main/resources/audit/audit_pgxl.sql, add Master.audit.sql=audit_pgxl.sql to Master.properties 3. pre-populate your DB instance with the full, OpenJPA-generated SQL schema You can achieve the last item in two different ways: 1. (simpler) run your project on a plain PostgreSQL instance: this will create the SQL schema and fill the table with some values; at that point, dump the DB in a SQL script file, manually tweak it to work with Postgres-XL, and load it there; finally, run again your project against Postgres-XL 2. (more involved): configure the OpenJPA Maven plugin in your core/pom.xml (take [4] as reference) to generate the SQL schema file for PostgresQL, then manually tweak it to work with Postgres-XL, and load it there; finally, run your project against Postgres-XL HTH Regards. [1] http://www.postgres-xl.org/ [2] https://github.com/apache/syncope/blob/syncope-2.0.1/core/provisioning-java/src/main/resources/quartz/tables_postgres.sql [3] https://github.com/apache/syncope/blob/syncope-2.0.1/core/persistence-jpa/src/main/resources/audit/audit.sql [4] https://github.com/apache/syncope/blob/syncope-2.0.1/core/persistence-jpa/pom.xml#L217-L230 -- Francesco Chicchiriccò Tirasa - Open Source Excellence http://www.tirasa.net/ Member at The Apache Software Foundation Syncope, Cocoon, Olingo, CXF, OpenJPA, PonyMail http://home.apache.org/~ilgrosso/
