ReportServer Forum

richardlrz

Member

Registered: 2020-12-20

ReportServer with Postgres

Hello As I am new in this field and I wonder if any one can give me a hand post a tutorial for best practice setup in windows by using postgres?

I am following the current best practice on windows but it is based on MariaDB, I am not sure if there is any restriction on using Postgres 13 and I did very similar thing as best practice posted create a database called reportserver andrun the postgres sql from ddl directory, I do see all of the table created by the sql in the postgres reportsercer database, and I did created three folder as
C:\Program Files\reportserver
C:\Program Files\reportserver\config
C:\Program Files\reportserver\lib
and copy those three
persistence.properties.example
reportserver.properties
logging-rs.properties from WEB-INF/classes directory and  change the persistence.properties.example to persistence.properties and modified them as:

#
# file: persistence.properties
# description: This file contains the database settings for ReportServer

# Credentials
#

hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
hibernate.connection.driver_class=org.postgresql.Driver
hibernate.connection.url=jdbc:postgresql://localhost/reportserver

hibernate.connection.username=postgres
hibernate.connection.password=postgres
hibernate.connection.autocommit=false

# Connection pool settings.

# Hibernate uses the C3P0 connection pool. The following properties allow to

# configure C3P0 as used by Hibernate. Note that this does not have any effect

# on the connection pool used by ReportServer for handling reporting.

#
# If no changes are made here, then the default settings are active

hibernate.c3p0.acquire_increment=5

hibernate.c3p0.idle_test_period=60
hibernate.c3p0.timeout=3600

hibernate.c3p0.max_size=30
hibernate.c3p0.max_statements=0

hibernate.c3p0.min_size=5

also copy all the files in the lib folder under WEB-INF/classes directory, tried to change the folder name under C:\Program Files\Apache Software Foundation\Tomcat 8.5\webapps to ROOT or reportserver,

I have no issue to use pgAdmin4 or psql to connect with reportserver DB, but after I start tomcat, I always got:

ReportServer encountered an error while validating its environment. Please check the server log files for additional information.

  _____                       _    _____                         
|  __ \                     | |  / ____|                         
| |__) |___ _ __   ___  _ __| |_| (___   ___ _ ____   _____ _ __
|  _  // _ \ '_ \ / _ \| '__| __|\___ \ / _ \ '__\ \ / / _ \ '__|
| | \ \  __/ |_) | (_) | |  | |_ ____) |  __/ |   \ V /  __/ |   
|_|  \_\___| .__/ \___/|_|   \__|_____/ \___|_|    \_/ \___|_|   
            | |                                                   
            |_|                                                   

Version: RS3.3.0-6030 2020-09-25-11-16-41
Code Version: 2020-09-25-10-02-31
Java Version: Oracle Corporation Java HotSpot(TM) 64-Bit Server VM 25.271-b09 (1.8)
VM Args: -Dcatalina.home=C:\Program Files\Apache Software Foundation\Tomcat 8.5 -Dcatalina.base=C:\Program Files\Apache Software Foundation\Tomcat 8.5 -Djava.io.tmpdir=C:\Program Files\Apache Software Foundation\Tomcat 8.5\temp -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Djava.util.logging.config.file=C:\Program Files\Apache Software Foundation\Tomcat 8.5\conf\logging.properties -Drs.configdir=C:\Program Files\reportserver -Dfile.encoding=UTF8 exit abort -Xms512m -Xmx2048m
rs.configdir: C:\Program Files\reportserver (OK)

### DB Config ###
hibernate.dialect: org.hibernate.dialect.PostgreSQLDialect (OK)
hibernate.connection.driver_class: org.postgresql.Driver (OK)
hibernate.connection.url: jdbc:postgresql://localhost/reportserver  (OK)
hibernate.connection.username: postgres
hibernate.connection.password: **********
hibernate.default_schema:

Connection Test: Failed (FATAL: database "reportserver " does not exist)

Please help me out as I have no idea what cause this issue.

Thanks in advance!

Richard

eduardo

Administrator

Registered: 2016-11-01

Website

Re: ReportServer with Postgres

Hi Richard,

"also copy all the files in the lib folder under WEB-INF/classes directory"
-> this is not necessary. In the external lib directory you only have to place the specific libs needed by your installation, e.g. external drivers not included by reportserver, external libs, etc. So in your case the external lib directory should be kept empty.

FATAL: database "reportserver " does not exist"
-> the error comes from your postgres driver, not from reportserver. Pls make sure the database "reportserver" exists, and that the user trying to connect has permissions for reading this database.

Also, try to restart your tomcat, maybe the tables were created after tomcat was running already.

Regards,
Eduardo

richardlrz

Member

Registered: 2020-12-20

Re: ReportServer with Postgres

Hi Eduardo,

Thanks for your reply. I do have report server and I did re setup from very beginning and got following change for my configuration and then my reportserver i sup now.
Basically I did change on following:

# Credentials
hibernate.connection.username=rs
hibernate.connection.password=rs

# PostgreSQL
#hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
hibernate.dialect=net.datenwerke.rs.utils.hibernate.PostgreSQLDialect
hibernate.connection.driver_class=org.postgresql.Driver
hibernate.connection.url=jdbc:postgresql://localhost:5432/reportserver
hibernate.connection.autocommit=false

The documentation for manual configuration does not work for me if I use hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect   instead of hibernate.dialect=net.datenwerke.rs.utils.hibernate.PostgreSQLDialect; I was initally using hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect then I got hibernate.sequence error, after I change it back to as example unzip from the source file as hibernate.dialect=net.datenwerke.rs.utils.hibernate.PostgreSQLDialect and explicit point out my reportserver as :
hibernate.connection.url=jdbc:postgresql://localhost:5432/reportserver
I got report server running running okay and also I have to copy the configuration files in the C:\Program Files\reportserver\config back to tomcat webapp folder otherwise i also got error could not verify the schema which i think this does not make sense for me. but anyway it works for me now.

Thanks anyway

Richard

eduardo

Administrator

Registered: 2016-11-01

Website

Re: ReportServer with Postgres

Hi Richard,

The documentation for manual configuration does not work for me if I use hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect   instead of hibernate.dialect=net.datenwerke.rs.utils.hibernate.PostgreSQLDialect; I was initally using hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect then I got hibernate.sequence error

The manual configuration (https://reportserver.net/en/tutorials/i … /#postgres) has this postgres dialect configuration:
hibernate.dialect=net.datenwerke.rs.utils.hibernate.PostgreSQLDialect

which is working on your case. I found the other dialect you mention here: https://reportserver.net/en/guides/conf … roperties/ which is in fact incorrect, I will correct documentation here on next release cycle. Thanks for pointing this out.

Regards,
Eduardo