Using Syncope with Postgres-XL

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Using Syncope with Postgres-XL

Alessio Maccioni

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

 

Reply | Threaded
Open this post in threaded view
|

Re: Using Syncope with Postgres-XL

ilgrosso
Administrator
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/
Reply | Threaded
Open this post in threaded view
|

Re: Using Syncope with Postgres-XL

ilgrosso
Administrator
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/
Reply | Threaded
Open this post in threaded view
|

R: Using Syncope with Postgres-XL

Alessio Maccioni

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à
[hidden email]

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

 

Da: Francesco Chicchiriccò [mailto:[hidden email]]
Inviato: martedì 8 novembre 2016 09:22
A: [hidden email]
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/
Reply | Threaded
Open this post in threaded view
|

Re: Using Syncope with Postgres-XL

ilgrosso
Administrator
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à
[hidden email]

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

 

Da: Francesco Chicchiriccò [[hidden email]]
Inviato: martedì 8 novembre 2016 09:22
A: [hidden email]
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/