pg_dump2hibernate

Extracts PSQL statements from pg_dump file to import them with hibernate.

scenario

You want to initialize a PostgreSQL database with existing sql file generated by pg_dump programmatically by means of hibernate, e.g. for testing with junit / arquillian.

I found that existing hibernate mapper org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor didn't get along with psql too well. This library improves/supports:

• handling of multiple line commands
• translation from 'COPY from stdin' construct to INSERT commands (very poor performance but jdbc/hibernate compatible)
• escaping " characters in imported column values

prerequisites

This library is intended for the following environments

• PostgreSQL database
• existing hibernate layer (standalone, Spring or JEE/JPA 2.1, wildlfy)
• test environment (JUnit, Arquillian)

I don't recommend this scenario for production environments. In this case think about liquibase or similar tools instead...

usage

1. Maven dependency to link to this project's artifact:

    <dependency>
        <groupId>pro.trautmann</groupId>
        <artifactId>pg_dump2hibernate</artifactId>
        <version>0.0.8</version>
    </dependency>

2. Add property to persistence.xml (or analogous to hibernate.cnf)

    <property
        name="hibernate.hbm2ddl.import_files_sql_extractor"
	    value="pro.trautmann.pg.hibernate.PSQLCommandExtractor" />

3. Have pg_dump file available or create with pg_dump -U <user> <database> > PGDUMP.sql

4. Configure hibernate to your needs so that pdump.sql file gets imported during startup. Here is an example configuration:

	<properties>
			<property name="hibernate.dialect"
				value="org.hibernate.dialect.PostgreSQLDialect" />
			<property name="hibernate.show_sql" value="true" />
			<property name="hibernate.connection.charSet" value="UTF-8" />
			<!-- Have a script to DROP existing public schema -->
			<property
				name="javax.persistence.schema-generation.drop-source"
				value="script" />
			<property
				name="javax.persistence.schema-generation.drop-script-source"
				value="META-INF/DROP.sql" />
			<!-- Have a script to CREATE new public schema -->
			<property
				name="javax.persistence.schema-generation.database.action"
				value="create" />
			<property
				name="javax.persistence.schema-generation.create-source"
				value="script" />
			<property
				name="javax.persistence.schema-generation.create-script-source"
				value="META-INF/CREATE.sql" />
			<!-- Set the extractor -->
			<property
				name="hibernate.hbm2ddl.import_files_sql_extractor"
				value="pro.trautmann.pg.hibernate.PSQLCommandExtractor" />
			<!-- Here goes your dump file -->
			<property name="javax.persistence.sql-load-script-source"
				value="META-INF/PGDUMP.sql" />
	</properties>

a note about JFlex

This extractor uses JFlex for scanning. To enhance the extractor

• find and adapt jflex script `src/main/jflex/pg_dump.flex'
• rebuild with maven
• drop me a nore ;)

github

https://github.com/t-man01/pg_dump2hibernate/

Andreas Trautmann ([email protected])