0

I am trying to use SchemaSpy with PostgreSQL 8.0.2, but it is throwing an error. I'm seeking guidance on what steps to take to resolve this issue since the documentation is limited. The error message I receive mentions "unsupported server version" and "type does not exist" in the SQL query.

Command:

java -jar schemaspy-6.2.2.jar \
-s public -db <databasename> -u <username> -p <password> \
-host <host> -o /tmp \
-dp /usr/share/java/postgresql-42.6.0.jar \
-debug

error:

INFO  - Starting Main v6.2.2 on fs-dev-02 with PID 13334 (/usr/share/java/schemaspy-6.2.2.jar started by frank in /usr/share/java)
INFO  - The following profiles are active: default
INFO  - Started Main in 1.352 seconds (JVM running for 1.798)
DEBUG - Debug enabled
DEBUG - Resolving dbType: pgsql ->
    schemaspy-6.2.2.jar!/BOOT-INF/classes!/org/schemaspy/types/pgsql.properties
INFO  - Starting schema analysis
...
WARN  - Unsupported Server Version: 8.0.2
DEBUG - supportsSchemasInTableDefinitions: true
DEBUG - supportsCatalogsInTableDefinitions: false
DEBUG - Catalog not provided, queried jdbc driver and got '<redacted database name>'
INFO  - Connected to PostgreSQL - 8.0.2
INFO  - Gathering schema details
Gathering schema details...ERROR - SqlException
org.postgresql.util.PSQLException: ERROR: type "e" does not exist
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
    at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:246)
    at org.postgresql.jdbc.PgDatabaseMetaData.getTables(PgDatabaseMetaData.java:1347)
    at org.schemaspy.input.dbms.service.DatabaseService.getBasicTableMetaFromDatabaseMetaData(DatabaseService.java:510)
    at org.schemaspy.input.dbms.service.DatabaseService.getBasicTableMeta(DatabaseService.java:471)
    at org.schemaspy.input.dbms.service.DatabaseService.initTables(DatabaseService.java:182)
    at org.schemaspy.input.dbms.service.DatabaseService.gatherSchemaDetails(DatabaseService.java:117)
    at org.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:269)
    at org.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:134)
    at org.schemaspy.cli.SchemaSpyRunner.runAnalyzer(SchemaSpyRunner.java:109)
    at org.schemaspy.cli.SchemaSpyRunner.run(SchemaSpyRunner.java:98)
    at org.schemaspy.Main.main(Main.java:55)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:48)
    at org.springframework.boot.loader.Launcher.launch(Launcher.java:87)
    at org.springframework.boot.loader.Launcher.launch(Launcher.java:51)
    at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:52)

I got the above error with postgresql-42.6.0.jar

With postgresql-8.0-325.jdbc3.jar I got a different error:

WARN  - Connection Failure
org.schemaspy.input.dbms.exceptions.ConnectionFailure: Failed to connect to database [redacted] Connection rejected: FATAL: no pg_hba.conf entry for [redacted], SSL off.
    at org.schemaspy.input.dbms.DbDriverLoader.getConnection(DbDriverLoader.java:136)
    at org.schemaspy.input.dbms.DbDriverLoader.getConnection(DbDriverLoader.java:110)
    at org.schemaspy.input.dbms.service.SqlService.connect(SqlService.java:69)
    at org.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:243)
    at org.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:134)
    at org.schemaspy.cli.SchemaSpyRunner.runAnalyzer(SchemaSpyRunner.java:109)
    at org.schemaspy.cli.SchemaSpyRunner.run(SchemaSpyRunner.java:98)
    at org.schemaspy.Main.main(Main.java:55)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:48)
    at org.springframework.boot.loader.Launcher.launch(Launcher.java:87)
    at org.springframework.boot.loader.Launcher.launch(Launcher.java:51)
    at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:52)
Caused by: org.postgresql.util.PSQLException: Connection rejected: FATAL: no pg_hba.conf entry for host "::ffff:10.131.11.212", user "SRVAMR-STDAWF", database "sdc_analytics_prod", SSL off.
    at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:275)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:94)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:65)
    at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:117)
    at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
    at org.postgresql.jdbc3.Jdbc3Connection.<init>(Jdbc3Connection.java:24)
    at org.postgresql.Driver.connect(Driver.java:235)
    at org.schemaspy.input.dbms.DbDriverLoader.getConnection(DbDriverLoader.java:129)
    ... 15 common frames omitted
Frank
  • 952
  • 1
  • 9
  • 23
  • 2
    Postgres is 13 years past EOL per [Versions](https://www.postgresql.org/support/versioning/). This then leads to `WARN - Unsupported Server Version: 8.0.2` in the error message. You need to use a supported version of Postgres, currently 11-15. – Adrian Klaver Apr 25 '23 at 18:19
  • Is there no way to make a custom properties file? I was reading something about that in the docs but I couldn't figure it out – Frank Apr 25 '23 at 18:41
  • 1
    1) Why are you using an ancient version of Postgres? 2) What is you want to do with the Schemaspy output? 3) You would have to go here [Pg 8 system catalogs](https://www.postgresql.org/docs/8.0/catalogs.html) and change the queries in the properties to match what is available in that version. The question is whether that is worth it or given the answer to 2) might there be an easier way to get the info e.g `pg_dump ... --schema-only`. – Adrian Klaver Apr 25 '23 at 18:58
  • I would make a back up and restore this in a newer version. And then try again, SchemaSpy should now work – Frank Heikens Apr 25 '23 at 19:12
  • I'm not the manager of the database so I can't change the database, There are about 70 tables and I am trying to figure out the database schema as no one knows the schema. I could possibly get the last month of data and spin up a docker container with a newer version of postgresql to use schemaspy. I'm really looking for a way to automatically determine a schema map. The database does not have foreign keys and inconsistent id-names across the several hundred column names. And the tables have a lot of relationships... Do you think schemaspy would even help in this case? – Frank Apr 27 '23 at 01:16
  • If there are no FK's I don't see how tables could be related in a diagram. I still think doing `pg_dump -d -U --schema-only` would be a start. It would provide a text version of the table and other object definitions. You could also load that into a newer version of Postgres if you wanted without pulling over the data. – Adrian Klaver Apr 27 '23 at 15:06
  • If I do what you said, would schemaspy be able to find anything useful from the empty database tables? My dream solution would be something that can programmatically look at every column of every table and figure out with minimum processing what might be foreign keys and guess/estimate the table relationships. – Frank Apr 27 '23 at 17:04
  • 1
    I am not that familiar with SchemaSpy, though from what I seen it gathers the information from the system catalogs. These will be populated when you restore the table/object definitions from the dump file. If there are no explicit FK references in the table definitions then I will say with a fair degree of confidence that SchemaSpy **will not** figure out implied relationships. Do the `pg_dump` and then you can search in the file for `REFERENCES`, that will show you if there are any explicit FK's defined. I'm going to guess that you are looking at manual inspection to figure things out. – Adrian Klaver Apr 27 '23 at 18:13

0 Answers0