Streama: Add support for PostgreSQL Driver

[DerVerruckteFuchs]

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]

Have you tried changing dataSource here https://github.com/dularion/streama/blob/master/grails-app/conf/application.yml#L15

[DerVerruckteFuchs]

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]

@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]

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]

@DerVerruckteFuchs

aaaaa sory:

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

[DerVerruckteFuchs]

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

[DerVerruckteFuchs]

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]

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]

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

https://github.com/dularion/streama/pull/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]

great!

[Jeronimo95]

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]

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]

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]

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]

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.

[DerVerruckteFuchs]

I've learned a bit more about systemd services, and you can configure them to be run by specific users and groups. So a streama user and/or a streaming group could run the service for the migration. This would be nice so streama database migrations are only run by a streama user and/or streaming group. This would be good since anything that happens can be isolated to what streama and/or streaming has permissions to access. We only want to handle streama related stuff, and containing everything permissions wise would be good in the event of something going wrong. I think it would be good security wise. Also having log output centralized and visible with dmesg or journalctl could be nice.

Since each migration is a one time thing, making things a bit more generic with systemd might be necessary. So having a streama-migration.service and streama-migration-at-startup.service for either migration case. The migration files could be put in /opt/, just to pick an arbitrary directory. Then the services could just be written to run their respective, generically named, migration files. The PathExists= option can be used to monitor where the migration script should show up, and automatically starts when it does show up. Signing the migration files with gpg and only running when they are signed correctly would be a good idea here.

I think a set of options regarding how migrations are handled would probably be a good thing to consider.

• Option 1: Notify user of needed migration and ask to be run (default)
• Option 2: Notify user of needed migration and run
• Option 3: Notify user of needed migration and ask to be run at a scheduled time*
• Option 4: Notify user of needed migration and run at a scheduled time*
• Option 5: Don't notify user of needed migration and just run (automatic mode)

• Option 6: Don't notify user of needed migration and just run at a scheduled time* (automatic scheduled mode)

  *Time could be set in configuration. If not set in config, time can be asked for and either be used one time or set in config. Boot time could be an option.

Also notifying the user of the result could be good too.

• Option 1: Notify user when migration succeeds or fails
• Option 2: Notify user when migration fails

Backups and rolling back in the event of failure would be nice, but that could be a separate feature/issue.

We can still have CRON jobs as an option, we'll just need a crontab for a streama user. Also a set of log files like /var/log/streama/error.log and /var/log/streama/streama.log, for example, would be nice if output is not going to be visible with dmesg and/or journalctl. Being able to choose between systemd or CRON in the streama settings page could be nice. Alternatively, it could be set in application.yml.

I think I could handle the systemd service bit myself. I'd imagine just fetching the migration filename/URL from the migration table, and downloading it from the streama repo could work nicely. That's assuming the migrations are added to the database table that way. I'd need to look at how it's being done with CRON, and go from there.

As far as getting the list of migrations, I guess there could be a migrations.latest file that could be merged with a users database, then checked if any migrations are needed. A periodic CRON job or systemd timer could check if migrations.latest has any differences from the users migrations table and merge them. Having streama automatically check for migrations.latetest on startup could be a good idea, or default behavior even.

After all is said and done, the migration files can be rmed after a successful migration. I think /tmp/ could be used, then we could avoid messing with deleting files manually. However, the migration files would need to be re-downloaded in the event of a migration failure and subsequent reboot. That might not be a big deal, but we'll need to consider that depending on the behavior we want with the migration files.