New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add support for PostgreSQL Driver #409

Open
DerVerruckteFuchs opened this Issue Aug 9, 2017 · 15 comments

Comments

Projects
None yet
5 participants
@DerVerruckteFuchs

DerVerruckteFuchs commented Aug 9, 2017

It would be really nice to be able to have PostgreSQL as an option for those of us that are trying to use a single postgres database.

@gkiko

This comment has been minimized.

Collaborator

gkiko commented Aug 9, 2017

@DerVerruckteFuchs

This comment has been minimized.

DerVerruckteFuchs commented Aug 9, 2017

I did, and I even made sure to install libpostgresql-jdbc-java on Debian to make sure the library was available. I kept getting errors that basically amounted to org.postgresql.Driver not found/available.

In my /data/streama/application.yml I had this:

environments:
    production:
        dataSource:
            driverClassName: 'org.postgresql.Driver'
            url: jdbc:postgresql://localhost:5432/streama_db
            username: streama
            password: <streama postgres db user password>

Some errors I'm getting:

ERROR org.apache.tomcat.jdbc.pool.ConnectionPool - Unable to create initial connections of pool.

java.sql.SQLException: Unable to load class: org.postgresql.Driver from ClassLoader:org.springframework.boot.loader.LaunchedURLClassLoader@681a9515;ClassLoader:org.springframework.boot.loader.LaunchedURLClassLoader@681a9515

Caused by: java.lang.ClassNotFoundException: Unable to load class: org.postgresql.Driver from ClassLoader:org.springframework.boot.loader.LaunchedURLClassLoader@681a9515;ClassLoader:org.springframework.boot.loader.LaunchedURLClassLoader@681a9515

Caused by: java.lang.ClassNotFoundException: org.postgresql.Driver

@Narrorek

This comment has been minimized.

Narrorek commented Aug 9, 2017

@DerVerruckteFuchs

in build.gradle add:
compile group: 'org.postgresql', name: 'postgresql', version: '42.1.1'

next application.yml

dbCreate: update url: jdbc:postgresql://$POSTGRES_HOST:$POSTGRESS_PORT/$DATABASE_NAME pooled: true jmxExport: true driverClassName: org.postgresql.Driver dialect: net.kaleidos.hibernate.PostgresqlExtensionsDialect username: sa password:

@DerVerruckteFuchs

This comment has been minimized.

DerVerruckteFuchs commented Aug 9, 2017

I'm getting an Unable to resolve name [net.kaleidos.hibernate.PostgresqlExtensionsDialect] as strategy [org.hibernate.dialect.Dialect] error now. I think a dependency might be missing.

@Narrorek

This comment has been minimized.

Narrorek commented Aug 9, 2017

@DerVerruckteFuchs

aaaaa sory:

compile group: 'org.postgresql', name: 'postgresql', version: '42.1.1'
compile 'org.grails.plugins:postgresql-extensions:5.0.0'

@DerVerruckteFuchs

This comment has been minimized.

DerVerruckteFuchs commented Aug 9, 2017

I'm now getting a java.lang.NoClassDefFoundError: org/hibernate/dialect/PostgreSQL94Dialect error.

@DerVerruckteFuchs

This comment has been minimized.

DerVerruckteFuchs commented Aug 10, 2017

So I tried using dialect: org.hibernate.dialect.PostgreSQLDialect in my application.yml, and it seems to work. However, I'm getting ERROR org.hibernate.tool.hbm2ddl.SchemaUpdate - HHH000388: Unsuccessful: create table user, which seems to be caused by the fact user is a reserved word for postgresql:

https://stackoverflow.com/questions/22256124/cannot-create-a-database-table-named-user-in-postgresql

According to the link, changing all references to the user table to "user" will work, but it's bad practice. Perhaps something like username could be used instead.

@gkiko

This comment has been minimized.

Collaborator

gkiko commented Aug 10, 2017

Maybe this is what you are looking for. You need to change User class, add mapping properties and then compile the code. I don't know if it's possible to define mapping from application.yml

@DerVerruckteFuchs

This comment has been minimized.

DerVerruckteFuchs commented Aug 10, 2017

Turns out there was an unaccepted pull request that does what I need it to:

#368

It's a breaking change, so it makes sense it hasn't been accepted yet. There will need to be a migration script that checks for a user table and runs mysql -u steama -p pass -e "RENAME TABLE 'user' TO 'users'" or similar. There would need to be something similar for the Java H2 database. I suppose a pull request with migration scripts could get merged with #368 getting merged after.

I've manually added what was in #368 to what I've git cloned and my steama-1.1.war runs just fine with the postgres database I made earlier.

@dularion

This comment has been minimized.

Member

dularion commented Aug 18, 2017

great!

@Jeronimo95

This comment has been minimized.

Contributor

Jeronimo95 commented Jul 29, 2018

I think it's a good idea to implement this. But I want to discuss how the migration should work.

Do we think its enough to put in the release notes of the update this is in that you need to migrate any databases before startup?

Is there a way we can auto migrate both h2 and mysql on application startup?

@DerVerruckteFuchs

This comment has been minimized.

DerVerruckteFuchs commented Aug 18, 2018

Since interacting with h2 seems to require Java anyway, a database check and migration could be included in the startup of streama. I feel like that it might be a bit "bloated" to keep one time use code around, so keeping it around for a few versions then removing it could be a way to go. If anyone stays too long on an old pre-migration version, then they can use one of the versions that still have the migration code before upgrading to the latest version. I think Java can be used to interact with mysql/mariadb, so we could use either the bash script I wrote, or do the same thing in Java. I think using Java may be more suitable cross platform, since the bash script should only be suitable for Linux and I think MacOS and BSD.

We could just stick with the script method and write a batch script for Windows too. During the version check, it can check for which database is being used and either download the script for the OS in use, or run the h2 migration java code. I'm thinking it's probably possible to make the h2 migration stuff a separate java file, but I'd need to look into that a bit further on how that works.

I like the idea of keeping the migration stuff separate from the main streama.war file as much as possible, and possibly download a script or a java file. We'd still need to include a means of checking the database on startup though if we want it to be more automatic. After the migration we could delete any leftover migration files since they aren't needed anymore.

@Jeronimo95

This comment has been minimized.

Contributor

Jeronimo95 commented Aug 19, 2018

I agree. Keeping the migration stuff separate from the main application is a good way to go. Maybe a separate jar distributed with the release when a migration needs to happen. In the main app we just need to check if migration has been completed before we start any database related stuff.

@dularion

This comment has been minimized.

Member

dularion commented Sep 10, 2018

In another application I am working on we are soon rolling out a migration system where upcoming migrations are added to a database table, and they are run through with a CRON job if they dont have to block startup, or at startup if they need to block. Then, when finished, they are marked as completed and never run again. That way, we dont bloat startup too much but still dont need to tell each user what migrations to run & when. What do you think?

@DerVerruckteFuchs

This comment has been minimized.

DerVerruckteFuchs commented Sep 20, 2018

That sounds good to me. I was toying with the idea of using systemd timers with that idea since some distros might not have a cron daemon installed by default, but have systemd. I think that would be a bit more tedious since creating the systemd timer and enabling it would require sudo, and as long as the streama user and permissions are set up correctly I don't think sudo would be necessary with a CRON job. We'll probably need to note somewhere in the streama setup guides and changelog that a cron daemon is a dependency if/when such a migration system is implemented.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment