Glad to hear that it worked, Alessio!
I will add your instructions to the reference guide.
Regards.
On 10/11/2016 10:59, Alessio Maccioni wrote:
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. J
*Alessio Maccioni*
Area Infrastrutture Tecnologiche
Divisione Pubblica Amministrazione e Sanità
[email protected] <mailto:[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
www.eng.it <http://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/
