Changing MySQL to PostgreSQL

[Jesus C]

Hi!

I know that the relational database used in Sentilo can be changed and I'd like to use PostgreSQL instead of MySQL.

What should I change exactly?

[io.se...@gmail.com]

Hi Jesus,

to change from MySQL to PostgreSQL you need modify the relational agent configuration:

1. Change the mysql dependency to postgreeSQL. You will find it in the pom.xml file

<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.jdbc.driver.version}</version>
</dependency>

2. Change the jdbc driver classname and connection url used by the dataSource. You will find it in the relational-client-config.properties file

#Properties to configure the datasource  sentiloDs
sentiloDs.jdbc.driverClassName=com.mysql.jdbc.Driver
sentiloDs.url=jdbc:mysql://127.0.0.1:3306/sentilo

Check also the sql scripts: you need to convert the MySQL script (agent_mysql.sql) to PostgreeSQL

Finally, you must build and redeploy the relational agent.

Please, feel free to feedback any comments or suggestions which you may wish to make. We always appreciate any feedback that helps us improve.

Regards,
  Sentilo Team

[Jesus C]

Do I have to change the <groupId> tag? I have no idea of what to put in each gap... Could you be more specific?

I guess I should download the jdbc for postgresql or something, right?

Regards

[io.se...@gmail.com]

Hi Jesus,

As highligthed in the previous message, to change from MySQL to PostgreSQL you should replace the mysql driver dependency

<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.jdbc.driver.version}</version>
</dependency>

with the appropriate  postgresql driver. But it depends on which version of the PostgreSQL server you are using.

The following links could be useful to you:
    http://mvnrepository.com/artifact/postgresql/postgresql
    http://mvnrepository.com/artifact/org.postgresql/postgresql

For example, if you are running PostgreSQL 9.4 an option would be:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>9.4-1200-jdbc41</version>
</dependency>

Regards,

  Sentilo Team

[manal_maro...@g.enp.edu.dz]

Hi, I tried this solution but the data are not transferred (With Mysql everything worked fine).

This is my configuration in the pom.xml file:

            <dependency>

                <groupId>org.postgresql</groupId>

                <artifactId>postgresql</artifactId>

                <version>9.4-1200-jdbc41</version>

            </dependency>

and the relational-client-config.properties  :

sentiloDs.jdbc.driverClassName=org.postgresql.Driver

sentiloDs.url=jdbc:postgresql://localhost:5432/sentilo

sentiloDs.username=sentilo_user

sentiloDs.password=sentilo_pwd

sentiloDs.validationQuery=Select 1

I am using this version of postgres: psql (PostgreSQL) 9.4.17

This is what I have with postgresql:

 2

When running the relational-agent this is what I have (sentilo.txt).

Thank you.

 

[io.se...@gmail.com]

Hi,

There isn't any errors in the log... are you sure the Sentilo instance is receiving data ?

If you've tested before with MySQL you've noticed it ... the data is inserted in batches so maybe there are not enough registries yet.

The truth is that we've never test it with postgres so let us know any results please ;)

Thanks !

Sentilo team.