Import from Postgresql
dgcombs
I'm attempting to import data from a Postgresql database to an H2
database to support my Blojsom Blog. Postgresql comes with a useful
program to export a backup of the database. Is there an easy way to
restore this into H2?
thanks,
Dan
Thomas Mueller
The most database independent way to transfer data is using a SQL
script. If the export of PostgreSQL is a SQL script file, you can use
the RunScript tool of H2 to import it. However, I am not sure in what
format the PostgreSQL export is?
Thanks,
Thomas
dgcombs
too. However, I also found that H2 and Postgresql have very different
ideas about what constitutes a "standard" sql script. So I am hacking
my way through (one line at a time) to see how much I can salvage and
how much is generally required.
The documentation doesn't make it obvious how to run RunScript. It is
clear the package itself is weighted to Java enthusiasts. After a bit
o' trial and error, I figured out how to start up RunScript and
eventually caught on to the syntax. My goal is to be able to remove
Postgresql (large footprint) from my server (Resin) and use H2 for all
my db requirements.
thanks,
Dan
On May 15, 5:23 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
> Hi,
>
> The most database independent way to transfer data is using a SQL
> script. If the export of PostgreSQL is a SQL script file, you can use
> the RunScript tool of H2 to import it. However, I am not sure in what
> format the PostgreSQL export is?
>
> Thanks,
> Thomas
>
Thomas Mueller
I like to make H2 compatible to PostgreSQL (and other databases as
well) if possible. The problem with the SQL 'standard' is that there
is no real standard. Somebody once said that SQL is not a standard, it
is a 'theme'. This has multiple reasons. Anyway, if you can tell me
what statements H2 does not understand I will try to fix this. (I know
about CHARACTER VARYING already).
> The documentation doesn't make it obvious how to run RunScript.
Would it make sense if I write a H2 Console extension so that you can
run the tools like this (using a simple GUI)? The GUI could also
display the command line that is used, so that you can include that in
a batch file if required.
Thomas
Jeffrey Krzysztow
Would it make sense if I write a H2 Console extension so that you can
run the tools like this (using a simple GUI)? The GUI could also
display the command line that is used, so that you can include that in
a batch file if required.
YES! PLEASE!
Jeffrey
Thomas Kellerer
wrote:
> The most database independent way to transfer data is using a SQL
> script.
file.
And there are a lot of good tools out there to export and import flat
files in various formats.
> Would it make sense if I write a H2 Console extension so that you can
> run the tools like this (using a simple GUI)? The GUI could also
> display the command line that is used, so that you can include that in
> a batch file if required.
As much as I appreciate your dedication I think concentrating on the
DB engine makes a lot more sense,
considering the vast number of GUI tools out there.
Thomas
Thomas Mueller
> Actually, I think the the most database independent way is a flat
> file.
>
> And there are a lot of good tools out there to export and import flat
> files in various formats.
I think you are right. Probably CSV files are the best documented 'low
level' formats. The only problem is that there are no data types for
CSV files. And each table needs a file.
> As much as I appreciate your dedication I think concentrating on the
> DB engine makes a lot more sense,
> considering the vast number of GUI tools out there.
I agree. The command line tools have been kept simple so far, not very
user friendly. I am thinking about using the BNF based autocomplete
feature of the H2 Console to build command line (it just needs a BNF
for the command line options).
Thomas
demetrios...@googlemail.com
http://db.apache.org/ddlutils/
I was using the example tasks(from the site) and was able to migrate
db (schema and data) between various database types: e.g. between
PostgreSQL and Hypersonic or Derby worked without problems for me. I
suppose that it should work with H2 too (or if it doesn't than adding
support should be very simple since it already does for Hypersonic).
Demetrios.
dgcombs
Good find!
On May 17, 9:24 am, "demetrios.kyria...@googlemail.com" >
> What about DDLUtils?http://db.apache.org/ddlutils/
dgcombs
a little time tonight.
thanks!
On May 15, 6:48 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
dgcombs
line stuff. Once I figured out where the command line was documented,
it was a snap to run the script, over and over and over. But I suspect
it'll take some doing to put together the equivalent of PGAdmin.
Thanks for making the basics work and work well!
Dan
On May 16, 6:30 pm, Thomas Kellerer <CAJWEDXOJ...@spammotel.com>
wrote:
dgcombs
found to be incompatible. I'm not sure how many of them are necessary
for Postgresql compatibility though. I do want to use Perl (under
Resin) to access H2 databases. I'd like to port my GEDAFE applications
and Blojsom. So yes, there are a few applications I've got my eye on.
-- SET client_encoding = 'UTF8';
-- SET check_function_bodies = false;
-- SET client_min_messages = warning;
-- CREATE PROCEDURAL LANGUAGE plperl;
-- CREATE PROCEDURAL LANGUAGE plpgsql;
--SET search_path = public, pg_catalog;
--SET default_tablespace = '';
--SET default_with_oids = false;
--id serial NOT NULL,
On May 15, 6:48 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote: