schemacrawler

SchemaCrawler getting Sequences from PostgreSQL database

Im using Schema 14.16.01 and running it on a PostgreSQL 9.6 database with the java api. I can't seem to get the api to get the sequence data from my PostgreSQL. I saw from a previous question that you'll need to set the info level to maximum, but that didn't seem to help. I'm able to get all other information the database but the sequences. 

    SchemaCrawlerOptions options = new SchemaCrawlerOptions();
    options.setSchemaInfoLevel(SchemaInfoLevelBuilder.maximum());
    options.setColumnInclusionRule(new IncludeAll());
    options.setTableInclusionRule(new IncludeAll());
    options.setSchemaInclusionRule(new IncludeAll());
    options.setRoutineInclusionRule(new ExcludeAll());
    options.setSequenceInclusionRule(new IncludeAll());

    Connection con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/database", "postgres", "postgres" );
    SchemaCrawler crawler = new SchemaCrawler(con, new DatabaseSpecificOverrideOptions());

Here is the console output from my testing. 

Java HotSpot(TM) 64-Bit Server VM warning: ignoring option 
MaxPermSize=1g; support was removed in 8.0
Connected to the target VM, address: '127.0.0.1:64896', transport: 'socket'
Apr 28, 2017 5:25:42 PM schemacrawler.crawl.SchemaCrawler crawlSchemas
INFO: Crawling schemas
Apr 28, 2017 5:25:42 PM schemacrawler.crawl.SchemaRetriever retrieveAllSchemas
INFO: Retrieving all schemas
Apr 28, 2017 5:25:42 PM schemacrawler.crawl.SchemaRetriever retrieveAllCatalogs
INFO: Retrieving all catalogs
Apr 28, 2017 5:25:42 PM schemacrawler.crawl.MetadataResultSet close
INFO: Processed 3 rows for <retrieveAllSchemas>
Apr 28, 2017 5:25:42 PM schemacrawler.crawl.SchemaRetriever retrieveSchemas
INFO: Retrieved 3 schemas
Apr 28, 2017 5:25:42 PM schemacrawler.crawl.SchemaCrawler crawlSchemas
INFO: Total time taken for <crawlSchemas> - 00:00:00.028 hours
- 92.9% - 00:00:00.026 - <retrieveSchemas>
-  7.1% - 00:00:00.002 - <sortAndFilterSchemas>

Apr 28, 2017 5:25:42 PM schemacrawler.crawl.SchemaCrawler crawlDatabaseInfo
INFO: Retrieving database information
Apr 28, 2017 5:25:43 PM schemacrawler.crawl.SchemaCrawler crawlDatabaseInfo
INFO: Retrieving JDBC driver information
Apr 28, 2017 5:25:43 PM schemacrawler.crawl.SchemaCrawler crawlDatabaseInfo
INFO: Retrieving SchemaCrawler crawl information
Apr 28, 2017 5:25:43 PM schemacrawler.crawl.SchemaCrawler crawlDatabaseInfo
INFO: Total time taken for <crawlDatabaseInfo> - 00:00:00.678 hours
-  0.0% - 00:00:00.000 - <retrieveDatabaseInfo>
- 81.4% - 00:00:00.552 - <retrieveAdditionalDatabaseInfo>
-  0.0% - 00:00:00.000 - <retrieveJdbcDriverInfo>
-  1.2% - 00:00:00.008 - <retrieveAdditionalJdbcDriverInfo>
- 17.4% - 00:00:00.118 - <retrieveCrawlHeaderInfo>

Apr 28, 2017 5:25:43 PM schemacrawler.crawl.SchemaCrawler crawlColumnDataTypes
INFO: Crawling column data types
Apr 28, 2017 5:25:43 PM schemacrawler.crawl.SchemaCrawler$1 call
INFO: Retrieving system column data types
Apr 28, 2017 5:25:43 PM schemacrawler.crawl.SchemaCrawler$2 call
INFO: Retrieving user column data types
Apr 28, 2017 5:25:43 PM schemacrawler.crawl.DatabaseInfoRetriever retrieveUserDefinedColumnDataTypes
INFO: Retrieving data types for schema <information_schema>
Apr 28, 2017 5:25:44 PM schemacrawler.crawl.DatabaseInfoRetriever retrieveUserDefinedColumnDataTypes
INFO: Retrieving data types for schema <pg_catalog>
Apr 28, 2017 5:25:44 PM schemacrawler.crawl.DatabaseInfoRetriever retrieveUserDefinedColumnDataTypes
INFO: Retrieving data types for schema <public>
Apr 28, 2017 5:25:44 PM schemacrawler.crawl.SchemaCrawler crawlColumnDataTypes
INFO: Total time taken for <crawlColumnDataTypes> - 00:00:01.039 hours
- 34.7% - 00:00:00.361 - <retrieveSystemColumnDataTypes>
- 65.3% - 00:00:00.678 - <retrieveUserDefinedColumnDataTypes>

Apr 28, 2017 5:25:44 PM schemacrawler.crawl.SchemaCrawler crawlTables
INFO: Crawling tables
Apr 28, 2017 5:25:44 PM schemacrawler.crawl.TableRetriever retrieveTables
INFO: Retrieving tables
Apr 28, 2017 5:25:44 PM schemacrawler.crawl.TableRetriever retrieveTablesFromMetadata
INFO: Retrieving tables for schema <information_schema>
Apr 28, 2017 5:25:44 PM schemacrawler.crawl.MetadataResultSet close
INFO: Processed 0 rows for <retrieveTables>
Apr 28, 2017 5:25:44 PM schemacrawler.crawl.TableRetriever retrieveTablesFromMetadata
INFO: Retrieving tables for schema <pg_catalog>
Apr 28, 2017 5:25:44 PM schemacrawler.crawl.MetadataResultSet close
INFO: Processed 0 rows for <retrieveTables>
Apr 28, 2017 5:25:44 PM schemacrawler.crawl.TableRetriever retrieveTablesFromMetadata
INFO: Retrieving tables for schema <public>
Apr 28, 2017 5:25:44 PM schemacrawler.crawl.MetadataResultSet close
INFO: Processed 202 rows for <retrieveTables>
Apr 28, 2017 5:25:44 PM schemacrawler.crawl.SchemaCrawler crawlTables
INFO: Retrieved 202 tables
Apr 28, 2017 5:25:44 PM schemacrawler.crawl.TableColumnRetriever retrieveColumns
INFO: Retrieving table columns
Apr 28, 2017 5:25:46 PM schemacrawler.crawl.TableColumnRetriever retrieveHiddenColumns
INFO: No hidden table columns SQL provided
Apr 28, 2017 5:25:46 PM schemacrawler.crawl.ForeignKeyRetriever retrieveForeignKeys
INFO: Retrieving foreign keys
Apr 28, 2017 5:25:57 PM schemacrawler.crawl.SchemaCrawler$22 call
INFO: Retrieving primary keys and indexes
Apr 28, 2017 5:25:57 PM schemacrawler.crawl.IndexRetriever retrieveIndexes
INFO: Retrieving indexes
Apr 28, 2017 5:26:02 PM schemacrawler.crawl.IndexRetriever retrievePrimaryKeys
INFO: Retrieving primary keys
Apr 28, 2017 5:26:03 PM schemacrawler.crawl.TableExtRetriever retrieveTriggerInformation
INFO: Not retrieving trigger definitions, since this was not requested
Apr 28, 2017 5:26:03 PM schemacrawler.crawl.TableExtRetriever retrieveViewInformation
INFO: Not retrieving additional view information, since this was not requested
Apr 28, 2017 5:26:03 PM schemacrawler.crawl.TableExtRetriever retrieveTableDefinitions
INFO: Not retrieving table definitions, since this was not requested
Apr 28, 2017 5:26:04 PM schemacrawler.crawl.TableExtRetriever retrieveIndexInformation
INFO: Not retrieving additional index information, since this was not requested
Apr 28, 2017 5:26:04 PM schemacrawler.crawl.TableExtRetriever retrieveIndexColumnInformation
INFO: Not retrieving additional index column information, since this was not requested
Apr 28, 2017 5:26:04 PM schemacrawler.crawl.TableExtRetriever retrieveAdditionalTableAttributes
INFO: Not retrieving additional table attributes, since this was not requested
Apr 28, 2017 5:26:04 PM schemacrawler.crawl.TableExtRetriever retrieveAdditionalColumnAttributes
INFO: Not retrieving additional column attributes, since this was not requested
Apr 28, 2017 5:26:04 PM schemacrawler.crawl.SchemaCrawler crawlTables
INFO: Total time taken for <crawlTables> - 00:00:19.387 hours
-  0.2% - 00:00:00.047 - <retrieveTables>
-  9.2% - 00:00:01.789 - <retrieveColumns>
- 54.8% - 00:00:10.615 - <retrieveForeignKeys>
-  0.4% - 00:00:00.079 - <filterAndSortTables>
- 35.2% - 00:00:06.821 - <retrieveIndexes>
-  0.0% - 00:00:00.000 - <retrieveTableConstraintInformation>
-  0.0% - 00:00:00.000 - <isRetrieveTableConstraintDefinitions>
-  0.0% - 00:00:00.000 - <retrieveTriggerInformation>
-  0.0% - 00:00:00.000 - <retrieveViewInformation>
-  0.0% - 00:00:00.000 - <retrieveTableDefinitions>
-  0.0% - 00:00:00.001 - <retrieveIndexInformation>
-  0.0% - 00:00:00.001 - <retrieveAdditionalTableAttributes>
-  0.2% - 00:00:00.031 - <retrieveTablePrivileges>
-  0.0% - 00:00:00.001 - <retrieveAdditionalColumnAttributes>
-  0.0% - 00:00:00.002 - <retrieveTableColumnPrivileges>

Apr 28, 2017 5:26:04 PM schemacrawler.crawl.SchemaCrawler crawlRoutines
INFO: Crawling routines
Apr 28, 2017 5:26:04 PM schemacrawler.crawl.RoutineRetriever retrieveProcedures
INFO: Not retrieving procedures, since this was not requested
Apr 28, 2017 5:26:04 PM schemacrawler.crawl.RoutineRetriever retrieveFunctions
INFO: Not retrieving functions, since this was not requested
Apr 28, 2017 5:26:04 PM schemacrawler.crawl.RoutineRetriever retrieveProcedures
INFO: Not retrieving procedures, since this was not requested
Apr 28, 2017 5:26:04 PM schemacrawler.crawl.RoutineRetriever retrieveFunctions
INFO: Not retrieving functions, since this was not requested
Apr 28, 2017 5:26:04 PM schemacrawler.crawl.RoutineRetriever retrieveProcedures
INFO: Not retrieving procedures, since this was not requested
Apr 28, 2017 5:26:04 PM schemacrawler.crawl.RoutineRetriever retrieveFunctions
INFO: Not retrieving functions, since this was not requested
Apr 28, 2017 5:26:04 PM schemacrawler.crawl.SchemaCrawler crawlRoutines
INFO: Retrieved 0 routines
Apr 28, 2017 5:26:04 PM schemacrawler.crawl.SchemaCrawler crawlSynonyms
INFO: Crawling synonyms
Apr 28, 2017 5:26:04 PM schemacrawler.crawl.SynonymRetriever retrieveSynonymInformation
INFO: Not retrieving synonyms, since this was not requested
Apr 28, 2017 5:26:04 PM schemacrawler.crawl.SchemaCrawler crawlSynonyms
INFO: Total time taken for <crawlSynonyms> - 00:00:00.001 hours
-  0.0% - 00:00:00.000 - <retrieveSynonymInformation>
-100.0% - 00:00:00.001 - <sortAndFilterSynonms>

Apr 28, 2017 5:26:04 PM schemacrawler.crawl.SchemaCrawler crawlSequences
INFO: Crawling sequences
Apr 28, 2017 5:26:04 PM schemacrawler.crawl.SequenceRetriever retrieveSequenceInformation
INFO: Retrieving sequences
Apr 28, 2017 5:26:04 PM schemacrawler.crawl.SchemaCrawler crawlSequences
INFO: Total time taken for <crawlSequences> - 00:00:00.002 hours
-  0.0% - 00:00:00.000 - <retrieveSequenceInformation>
-100.0% - 00:00:00.002 - <sortAndFilterSequences>

EDITS

Small Segment from Sequence Query 

    sequence_catalog | sequence_schema |              sequence_name              | increment | minimum_value |    maximum_value    | cycle_option
------------------+-----------------+-----------------------------------------+-----------+---------------+---------------------+--------------
                  | public          | analysis_license_pk_seq                 | 1         | 1             | 9223372036854775807 | NO
                  | public          | analysis_summaries_instance_pk_seq      | 1         | 1             | 9223372036854775807 | NO
Solution

  • You need to make sure that you use the SchemaCrawler - PostgreSQL plug-in. In order to do this programtically, you need to:

    1. Include the us.fatehi:schemacrawler-postgresql jar on your classpath.
    2. When you instantiate a SchemaCrawler Java object, make sure that you use the correct DatabaseSpecificOverrideOptions instance, that will load the PostgreSQL plug-in. Please see code from SchemaCrawlerUtility for an example.

    Sualeh Fatehi, SchemaCrawler