OpenNode2 PostgreSQL Schema

This is the OpenNode2 Transaction, Logging and Administration database used by the Java version of OpenNode2 version 1.x. The advice here is based on my notes and you may need to alter things such as what user runs the database software and so forth.

OpenNode2 Administration Schema

It was generating using pgAdmin backup as a plain text file.

pg_dump --host localhost --port 5432 --username "postgres" --format plain --schema-only --verbose --file "/Users/ian/GoMOOS/DatabaseBackups/ODPX_Node/opennode2_schema.backup" node2_prod_test

It "should" restore to a database you create, I used the default UTF8 format.

You may need to FIRST replace the database owner and the user and permissions to fit your environment before restoring this. Remember the user must exist before restoring.

The database owner is set to pgsql.

The user for the OpenNode2 software is node2_web. That's set in jdbc.properties.

webapps/wnos/WEB-INF/CLASSES/jdbc.properties

# PostgreSQL

jdbc.driverClassName=org.postgresql.Driver

jdbc.url=jdbc:postgresql://localhost:5432/node2_prod_test

jdbc.username=node2_web

jdbc.password=xxxx

postgres database driver

This is the driver we have been using.

PostgreSQL 9.x

The bytea type supports two external formats for input and output: PostgreSQL's historical "escape" format, and "hex" format. Both of these are always accepted on input. The output format depends on the configuration parameter bytea_output; the default is hex. (Note that the hex format was introduced in PostgreSQL 9.0; earlier versions and some tools don't understand it.)

You'll need to set

bytea_output = escape

to use this with OpenNode2.

On my computer this is located here and I need to su to root to get at the file.

/usr/local/pgsql/data/posrgresql.conf

To reload the configuration (unix user pgsql is what runs the database.)

su to user pgsql

$  pg_ctl -D /usr/local/pgsql/data reload

Epilogue

 The above should work but again this is from my notes and installations vary. If it gives you trouble let us know.

Ian