Hi,
I am writing a Java app and currently am using postgres with it. I have
a unit test suite that runs frequently and inserts new data to test the
code. In order to be able to re-run this suite indefinitely, I have
written some SQL commands into my build script to DROP the database and
then CREATE the database again, repopulating the tables from known data
exported to a file, so that the tests always run with the database in a
known state.
I have two questions:
1. Are there any hazards in dropping a database and then creating it
again, over and over? This seemed like the cleanest way to remove all
of the data, including the sequences, but I am wondering if there is a
danger that the 'current OID' might be getting artificially pushed
toward its upper limit or something.
2. The [Ant] build script has no problem performing the necessary SQL
inserts when I use a certain file that I have handwritten that contains
nothing but CREATE TABLE and INSERT statements. Fine. However, if I
need to make some change to the database, this means I also have to
update the handwritten file (for instance, if I add new test data to the
database or change the structure of a table). This seems foolish, when
I could simply export the database schema and data to a file and then
use this file.
However, the format used by pg_dump consists of COPY statements, not
INSERT statements. The format also assumes that the commands are being
run from the shell, as there are certain escapes and references to
stdin. My build script does -not- run from a subshell, rather it is
executed via JDBC through the Ant <sql> task, so the SQL parser chokes
when it encounters some shell-specific escapes in the file. Is there a
way to run pg_dump so that the exported data resembles MySQL's mysqldump
data (entirely CREATE TABLE and INSERT statements) instead of the COPY
statements?
Thank you so much if you can advise me.
Erik