You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@syncope.apache.org by Alessio Maccioni <al...@eng.it> on 2016/10/25 08:32:44 UTC
Using Syncope with Postgres-XL
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?
Thank you for your support.
Alessio
Re: Using Syncope with Postgres-XL
Posted by Francesco Chicchiriccò <il...@apache.org>.
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\u2019s 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 \u201cDISTRIBUTE BY REPLICATION;\u201d
> 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\u2019s 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 \u201cDISTRIBUTE BY REPLICATION;\u201d 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�
> alessio.maccioni@eng.it <ma...@eng.it>
>
> *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:ilgrosso@apache.org]
> *Inviato:* marted� 8 novembre 2016 09:22
> *A:* user@syncope.apache.org
> *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\u2019s \u201cCREATE TABLE\u201d 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\u2019s distribution table is by HASH
> default).
>
> Inspecting .WAR files I just found and edited audit & quartz
> SQL instructions, but i can\u2019t 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/
R: Using Syncope with Postgres-XL
Posted by Alessio Maccioni <al...@eng.it>.
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à
<ma...@eng.it> alessio.maccioni@eng.it
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:ilgrosso@apache.org]
Inviato: martedì 8 novembre 2016 09:22
A: user@syncope.apache.org
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/
Re: Using Syncope with Postgres-XL
Posted by Francesco Chicchiriccò <il...@apache.org>.
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\u2019s \u201cCREATE TABLE\u201d 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\u2019s distribution table is by HASH default).
>>
>> Inspecting .WAR files I just found and edited audit & quartz SQL
>> instructions, but i can\u2019t 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/
Re: Using Syncope with Postgres-XL
Posted by Francesco Chicchiriccò <il...@apache.org>.
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/