Re: Duplicate HSQLDB - Export/Import from one instance to a new instance.
Fred Toussi via Hsqldb-user <[email protected]> Thu, 11 Jul 2024 21:16:55 +0100
| Newsgroups | gmane.comp.java.hsqldb.user |
|---|---|
| Message-ID | <[email protected]> |
--===============8288670187537230522== Content-Type: multipart/alternative; boundary=fb45f6c3b2da458e8be9f10b2a576a6e --fb45f6c3b2da458e8be9f10b2a576a6e Content-Type: text/plain;charset=utf-8 Content-Transfer-Encoding: quoted-printable 1) The database must be running when you export. No need to shut down. 2) You can sync both ways. 3) It's better to get the EXPORT and IMPORT working in the first place. = You can do the automation later. For your setup, you need to automate periodic export and import, so the = replica is not far behind the primary. After recovery from crash the=20 I don't know about 'all null on the receiving side'. What did you look a= t. The commands you included are for export and import of the full data. Yo= u will be using versioning variants of the commands after the initial se= tup.=20 Fred On Thu, Jul 11, 2024, at 16:01, Ken Lemoine wrote: > Thanks Fred. I've been trying the export/import as you mentioned and = when it imports, it's all 'null' on the receiving side. I'll keep fiddl= ing with it, I think I'm close.=20 > Below is my currently failing attempts at exporting the entire DB (bot= h schema and data), I've also tried bringing only the data over with the= same result. Should this work to just get a replica database on a secon= d machine? >=20 > PERFORM EXPORT SCRIPT FOR DATABASE WITH COLUMN NAMES TO 'export_s= cript_all.sql'; > PERFORM IMPORT SCRIPT DATA FROM 'export_script_all.sql' CONTINUE = ON ERROR; >=20 > Three questions I have now are - >=20 > 1) Does it matter if the database is running when I export, or ideally= would I shut it down first? > 2) When you mentioned "*sync only the changes in the primary database = with the secondary*", that seems to imply we can sync both ways? That w= ould be ideal. Is that supported also? >=20 > Brief description of what our use case is - We have a two machine setu= p, a primary and secondary. The secondary is idle, it will never receiv= e any traffic unless the primary if offline. So we need a way to get th= e data to the secondary machine, a great first step, but ideally, we wou= ld like to be able to fail back to primary if it comes back online. If = this is two completely seperate things in HSQLDB, I'd rather build the l= atter, but if we can get just the replica going now and roll out version= ed tables and two-way sync, that would be fantabulous. So with that: > 3) can I build the replica now and roll out a two-way sync later? >=20 > Thanks again, >=20 > Ken >=20 >=20 > *From:* Fred Toussi via Hsqldb-user <[email protected]> > *Sent:* Wednesday, July 10, 2024 12:36 PM > *To:* HSQLDB user list <[email protected]> > *Cc:* Fred Toussi <[email protected]> > *Subject:* Re: [Hsqldb-user] Duplicate HSQLDB - Export/Import from one= instance to a new instance. > =20 > There is no need for the Transfer Tool. >=20 > You can create a dump of the database using the PERFORM EXPORT stateme= nt. You can then use PERFORM IMPORT statement to load the dump into the = current database. >=20 > If you want to sync only the changes in the primary database with the = secondary, enable system versioning for the tables. See the guide at htt= p://hsqldb.org/doc/2.0/guide/management-chapt.html#mtc_system_versioned_= tables and the addtional description of the syntax in the Guide. >=20 > Fred Toussi >=20 > On Wed, Jul 10, 2024, at 15:37, Ken Lemoine wrote: >> Thanks, I'm still unable to get a simple export to work using this tr= ansfer tool. Can anyone give me the command syntax? If the usage is on= ly --dump, how can I pass it the connection details and authentication (= sa) ? Is there a transfer tool documentation that explains all this in = more detail? >>=20 >> Here's roughly how I'm trying it now >>=20 >> java -cp hsqldbutil.jar org.hsqldb.util.Transfer jdbc:hsqldb:hsql:/pa= th/to/prod/db/hsql;user=3Dsa;password=3D --dump >>=20 >> *-bash: --dump: command not found* >> ** >> Same error. I'm stuck, any help would be appreciated. Thanks! >>=20 >> Sincerely, >> Ken >>=20 >>=20 >>=20 >> *From:* Andrew Richardson <[email protected]> >> *Sent:* Tuesday, July 9, 2024 7:14 AM >> *To:* User discussions about HyperSQL Database Engine. <hsqldb-user@l= ists.sourceforge.net> >> *Subject:* Re: [Hsqldb-user] Duplicate HSQLDB - Export/Import from on= e instance to a new instance. >> =20 >> Per the help text, the tool only supports the switches specified. Th= us the " --database1 jdbc:hsqldb:hsql:/path/to/prod/db/hsql;user=3Dsa;pa= ssword=3D " text is a problem.=20 >> Suspect command line use is not supported presently...you might need = to add that support yourself, the source code is there. >> I was able to get the dump file generated using the GUI, but did not = try the restore function. >> I do not have any further input for you. =20 >> Good luck with your project! >>=20 >> On Mon, Jul 8, 2024 at 8:59=E2=80=AFPM Ken Lemoine <ken.lemoine@cyber= zek.com> wrote: >>> Thanks for the timely response, Andrew. >>>=20 >>> I've got a shiny new transfer tool (hsqldbutil.jar) compiled and tri= ed it every which way to no avail. >>>=20 >>> Here's my last attempt, with the response - I'm sure there's somethi= ng I'm missing still. The hsqldbutil.jar is in my local directory. >>>=20 >>>> java -cp hsqldbutil.jar org.hsqldb.util.Transfer --database1 jdbc:h= sqldb:hsql:/path/to/prod/db/hsql;user=3Dsa;password=3D --dump >>>=20 >>> Wondering if I need to change the jdbc connection to use 'file' vice= 'hsql' since we're using a .script file, but I can't seem to get past t= his message, as if it doesn't know what the --dump parameter is: >>>=20 >>> *Exception in thread "main" java.lang.IllegalArgumentException: Try:= java org.hsqldb.util.Transfer --help* >>> *=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82at org.hsqldb= .util.Transfer.main(Unknown Source)* >>> *-bash: --dump: command not found* >>>=20 >>>=20 >>> All the --help gives me is the usage (-help,-dump,-restore). It als= o seems to sometimes complain of the lack of X11 since it can also be ru= n graphically, but I do not want that, the goal here is to have a script= that can export data frequently, xfer it to another machine and have an= other script to import it. >>>=20 >>> Appreciate any help you can provide, and happy Monday! >>>=20 >>> Regards,=20 >>>=20 >>>=20 >>> *From:* Andrew Richardson <[email protected]> >>> *Sent:* Saturday, July 6, 2024 1:57 PM >>> *To:* User discussions about HyperSQL Database Engine. <hsqldb-user@= lists.sourceforge.net> >>> *Subject:* Re: [Hsqldb-user] Duplicate HSQLDB - Export/Import from o= ne instance to a new instance. >>> =20 >>> Hello again <sorry for the spam> >>> Out of curiosity, I looked into how to generate the Transfer Tool. = Here is what worked for me on Windows. >>>=20 >>> cd hsqldb-2.7.3\hsqldb\build >>> gradlew hsqldbutil >>> java -cp ..\lib\hsqldbutil.jar org.hsqldb.util.Transfer --dump >>>=20 >>> Maybe this saves you a little time. >>> Cheers! >>>=20 >>> On Sat, Jul 6, 2024 at 12:59=E2=80=AFPM Andrew Richardson <spun69@gm= ail.com> wrote: >>>> Hello Ken >>>> Not sure if this will cover all your requirements, but have a look = at the "Transfer Tool" utility. Might be a place to start. >>>> https://hsqldb.org/doc/2.0/util-guide/transfer-tool-chapt.html >>>>=20 >>>> Cheers, >>>> Andrew Richardson >>>>=20 >>>> On Fri, Jul 5, 2024 at 4:18=E2=80=AFPM Ken Lemoine <ken.lemoine@cyb= erzek.com> wrote: >>>>> Hello, >>>>>=20 >>>>> Apologies in advance if this has been asked before, I'm just not f= inding much information in the archives.=20 >>>>>=20 >>>>> We have a hsqldb instance that needs to be replicated to a seconda= ry. I've tried and failed 3 different methods, mostly using the sqltool= .jar and once by simply copying over the hsql.script file and crossing m= y fingers. The final attempt I just completed does seemingly create a v= alid 'exporteddata.sql' file, I can see it in there. Then when I import= it on the second machine, it also seemingly imports it - it outputs to = stdout so I know it did SOMEthing. But, the data is not showing up in t= he application. >>>>>=20 >>>>> This is the latest hsqldb, running on latest FreeBSD 14. =20 >>>>>=20 >>>>> I'll also be needing a way of fully automating this, to keep a sec= ond machine up to date, and have struggled also getting a script to do t= his, but at this point, I really just want to see if I can get this work= ing manually. >>>>>=20 >>>>> Any tips or hints, or a point in the right direction would be appr= eciated. =20 >>>>>=20 >>>>> Thanks! >>>>>=20 >>>>> Ken >>>>> *Ken Lemoine* | Deployment Manager >>>>>=20 >>>>> (614) 344-6500 >>>>>=20 >>>>> www.cyberzek.com >>>>>=20 >>>>>=20 >>>>> _______________________________________________ >>>>> Hsqldb-user mailing list >>>>> [email protected] >>>>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user >>> *Ken Lemoine* | Deployment Manager >>>=20 >>> (614) 344-6500 >>>=20 >>> www.cyberzek.com >>>=20 >>>=20 >>> _______________________________________________ >>> Hsqldb-user mailing list >>> [email protected] >>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user >> *Ken Lemoine* | Deployment Manager >>=20 >> (614) 344-6500 >>=20 >> www.cyberzek.com >>=20 >>=20 >>=20 >> _______________________________________________ >> Hsqldb-user mailing list >> [email protected] >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user >>=20 >=20 > *Ken Lemoine* | Deployment Manager >=20 > (614) 344-6500 >=20 > www.cyberzek.com >=20 >=20 >=20 > _______________________________________________ > Hsqldb-user mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/hsqldb-user >=20 --fb45f6c3b2da458e8be9f10b2a576a6e Content-Type: text/html;charset=utf-8 Content-Transfer-Encoding: quoted-printable <!DOCTYPE html><html><head><title></title><style type=3D"text/css">#qt P= {margin-top:0px;margin-bottom:0px;} #qt #qt-x_qt p{margin-top:0px;margin-bottom:0px;} p.MsoNormal,p.MsoNoSpacing{margin:0}</style></head><body><div style=3D"f= ont-family:Arial;">1) The database must be running when you export. No n= eed to shut down.<br></div><div style=3D"font-family:Arial;">2) You can = sync both ways.<br></div><div style=3D"font-family:Arial;">3) It's bette= r to get the EXPORT and IMPORT working in the first place. You can do th= e automation later.<br></div><div style=3D"font-family:Arial;"><br></div= ><div style=3D"font-family:Arial;">For your setup, you need to automate = periodic export and import, so the replica is not far behind the primary= . After recovery from crash the <br></div><div style=3D"font-family:Aria= l;"><br></div><div style=3D"font-family:Arial;">I don't know about 'all = null on the receiving side'. What did you look at.<br></div><div style=3D= "font-family:Arial;"><br></div><div style=3D"font-family:Arial;">The com= mands you included are for export and import of the full data. You will = be using versioning variants of the commands after the initial setup. <b= r></div><div style=3D"font-family:Arial;"><br></div><div style=3D"font-f= amily:Arial;">Fred<br></div><div style=3D"font-family:Arial;"><br></div>= <div style=3D"font-family:Arial;"><br></div><div style=3D"font-family:Ar= ial;"><br></div><div>On Thu, Jul 11, 2024, at 16:01, Ken Lemoine wrote:<= br></div><blockquote type=3D"cite" id=3D"qt" style=3D""><div class=3D"qt= -elementToProof" style=3D"font-family:Aptos, Aptos_EmbeddedFont, Aptos_M= SFontService, Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb(0,= 0, 0);">Thanks Fred. I've been trying the export/import as you me= ntioned and when it imports, it's all 'null' on the receiving side. &nbs= p;I'll keep fiddling with it, I think I'm close. <br></div><div sty= le=3D"font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibr= i, Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);">Below is my= currently failing attempts at exporting the entire DB (both schema and = data), I've also tried bringing only the data over with the same result.= Should this work to just get a replica database on a second machine?<br= ></div><div style=3D"font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFont= Service, Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0= );"><br></div><div style=3D"font-family:Aptos, Aptos_EmbeddedFont, Aptos= _MSFontService, Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb(= 0, 0, 0);"> PERFORM EXPORT SCRIPT FOR DATABASE WITH C= OLUMN NAMES TO 'export_script_all.sql';<br></div><div style=3D"font-fami= ly:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, s= ans-serif;font-size:12pt;color:rgb(0, 0, 0);"> PERFOR= M IMPORT SCRIPT DATA FROM 'export_script_all.sql' CONTINUE ON ERROR;<br>= </div><div style=3D"font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontS= ervice, Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0)= ;"><br></div><div style=3D"font-family:Aptos, Aptos_EmbeddedFont, Aptos_= MSFontService, Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb(0= , 0, 0);">Three questions I have now are -<br></div><div style=3D"font-f= amily:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica= , sans-serif;font-size:12pt;color:rgb(0, 0, 0);"><br></div><div class=3D= "qt-elementToProof" style=3D"font-family:Aptos, Aptos_EmbeddedFont, Apto= s_MSFontService, Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb= (0, 0, 0);">1) Does it matter if the database is running when I export, = or ideally would I shut it down first?<br></div><div class=3D"qt-element= ToProof" style=3D"font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontSer= vice, Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);"= >2) When you mentioned "<i>sync only the changes in the primary database= with the secondary</i>", that seems to imply we can sync both ways? &nb= sp;That would be ideal. Is that supported also?<br></div><div styl= e=3D"font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri= , Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);"><br></div><d= iv style=3D"font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, = Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);">Brief= description of what our use case is - We have a two machine setup, a pr= imary and secondary. The secondary is idle, it will never receive = any traffic unless the primary if offline. So we need a way to get= the data to the secondary machine, a great first step, but ideally, we would like to be able to fail back to primary if = it comes back online. If this is two completely seperate things in= HSQLDB, I'd rather build the latter, but if we can get just the replica= going now and roll out versioned tables and two-way sync, that would be fantabulous. So with that:<br></div><div styl= e=3D"font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri= , Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);">3) can I bui= ld the replica now and roll out a two-way sync later?<br></div><div styl= e=3D"font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri= , Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);"><br></div><d= iv style=3D"font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, = Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);">Thank= s again,<br></div><div style=3D"font-family:Aptos, Aptos_EmbeddedFont, A= ptos_MSFontService, Calibri, Helvetica, sans-serif;font-size:12pt;color:= rgb(0, 0, 0);"><br></div><div class=3D"qt-elementToProof" style=3D"font-= family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetic= a, sans-serif;font-size:12pt;color:rgb(0, 0, 0);">Ken<br></div><div id=3D= "qt-appendonsend"><br></div><div><hr style=3D"display:inline-block;width= :98%;"><br></div><div id=3D"qt-divRplyFwdMsg" dir=3D"ltr"><div><span cla= ss=3D"font" style=3D"font-family:Calibri, sans-serif;"><span class=3D"co= lor" style=3D"color:rgb(0, 0, 0);"><b>From:</b> Fred Toussi via Hsqldb-u= ser <[email protected]><br> <b>Sent:</b> Wednesday= , July 10, 2024 12:36 PM<br> <b>To:</b> HSQLDB user list <hsqldb-user= @lists.sourceforge.net><br> <b>Cc:</b> Fred Toussi <[email protected]= urceforge.net><br> <b>Subject:</b> Re: [Hsqldb-user] Duplicate HSQLDB= - Export/Import from one instance to a new instance.</span></span></div= ><div> <br></div></div><div><div style=3D"font-family:Arial;">There= is no need for the Transfer Tool.<br></div><div style=3D"font-family:Ar= ial;"><br></div><div style=3D"font-family:Arial;">You can create a dump = of the database using the PERFORM EXPORT statement. You can then use PER= FORM IMPORT statement to load the dump into the current database.<br></d= iv><div style=3D"font-family:Arial;"><br></div><div style=3D"font-family= :Arial;">If you want to sync only the changes in the primary database wi= th the secondary, enable system versioning for the tables. See the guide= at <a href=3D"http://hsqldb.org/doc/2.0/guide/management-chapt.html#mtc= _system_versioned_tables"> http://hsqldb.org/doc/2.0/guide/management-ch= apt.html#mtc_system_versioned_tables</a> and the addtional description o= f the syntax in the Guide.<br></div><div style=3D"font-family:Arial;"><b= r></div><div style=3D"font-family:Arial;">Fred Toussi<br></div><div styl= e=3D"font-family:Arial;"><br></div><div>On Wed, Jul 10, 2024, at 15:37, = Ken Lemoine wrote:<br></div><blockquote type=3D"cite" id=3D"qt-x_qt" sty= le=3D""><div class=3D"qt-x_qt-elementToProof" style=3D"font-family:Aptos= , Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-seri= f;font-size:12pt;color:rgb(0, 0, 0);">Thanks, I'm still unable to get a = simple export to work using this transfer tool. Can anyone give me= the command syntax? If the usage is only --dump, how can I pass i= t the connection details and authentication (sa) ? Is there a tran= sfer tool documentation that explains all this in more detail?<br></div><div class=3D"qt-x_qt-e= lementToProof" style=3D"font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSF= ontService, Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0= , 0);"><br></div><div class=3D"qt-x_qt-elementToProof" style=3D"font-fam= ily:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, = sans-serif;font-size:12pt;color:rgb(0, 0, 0);">Here's roughly how I'm tr= ying it now<br></div><div class=3D"qt-x_qt-elementToProof" style=3D"font= -family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helveti= ca, sans-serif;font-size:12pt;color:rgb(0, 0, 0);"><br></div><div class=3D= "qt-x_qt-elementToProof" style=3D"font-family:Aptos, Aptos_EmbeddedFont,= Aptos_MSFontService, Calibri, Helvetica, sans-serif;font-size:12pt;colo= r:rgb(0, 0, 0);">java -cp hsqldbutil.jar org.hsqldb.util.Transfer jdbc:h= sqldb:hsql:/path/to/prod/db/hsql;user=3Dsa;password=3D --dump<br><= /div><div class=3D"qt-x_qt-elementToProof" style=3D"font-family:Aptos, A= ptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif;f= ont-size:12pt;color:rgb(0, 0, 0);"><br></div><div class=3D"qt-x_qt-eleme= ntToProof" style=3D"font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontS= ervice, Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0)= ;"><b>-bash: --dump: command not found</b><br></div><div class=3D"qt-x_q= t-elementToProof" style=3D"font-family:Aptos, Aptos_EmbeddedFont, Aptos_= MSFontService, Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb(0= , 0, 0);"><b></b><br></div><div class=3D"qt-x_qt-elementToProof" style=3D= "font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, He= lvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);">Same error. I'm = stuck, any help would be appreciated. Thanks!<br></div><div c= lass=3D"qt-x_qt-elementToProof" style=3D"font-family:Aptos, Aptos_Embedd= edFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif;font-size:12= pt;color:rgb(0, 0, 0);"><br></div><div class=3D"qt-x_qt-elementToProof" = style=3D"font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Cal= ibri, Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);">Sincerel= y,<br></div><div class=3D"qt-x_qt-elementToProof" style=3D"font-family:A= ptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-= serif;font-size:12pt;color:rgb(0, 0, 0);">Ken<br></div><div id=3D"qt-x_q= t-appendonsend"><br></div><div style=3D"font-family:Aptos, Aptos_Embedde= dFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif;font-size:12p= t;color:rgb(0, 0, 0);"><br></div><div><hr style=3D"display:inline-block;= width:98%;"><br></div><div id=3D"qt-x_qt-divRplyFwdMsg" dir=3D"ltr"><div= ><span style=3D"color:rgb(0, 0, 0);"><span class=3D"font" style=3D"font-= family:Calibri, sans-serif;"><span class=3D"size" style=3D"font-size:11p= t;"><b>From:</b> Andrew Richardson <[email protected]><br> <b>= Sent:</b> Tuesday, July 9, 2024 7:14 AM<br> <b>To:</b> User di= scussions about HyperSQL Database Engine. <[email protected]= rge.net><br> <b>Subject:</b> Re: [Hsqldb-user] Duplicate HSQLDB = - Export/Import from one instance to a new instance.</span></span></span= ></div><div> <br></div></div><div style=3D"direction:ltr;">Per the = help text, the tool only supports the switches specified. Thus the= " --database1 jdbc:hsqldb:hsql:/path/to/prod/db/hsql;user=3Dsa;password= =3D " text is a problem. <br></div><div style=3D"direction:ltr;">Su= spect command line use is not supported presently...you might need to ad= d that support yourself, the source code is there.<br></div><div style=3D= "direction:ltr;">I was able to get the dump file generated using the GUI= , but did not try the restore function.<br></div><div style=3D"direction= :ltr;">I do not have any further input for you. <br></div><di= v style=3D"direction:ltr;">Good luck with your project!<br></div><div><b= r></div><div style=3D"direction:ltr;">On Mon, Jul 8, 2024 at 8:59=E2=80=AF= PM Ken Lemoine <<a href=3D"mailto:[email protected]" id=3D"qt-= x_qt-OWAe1070568-43cb-c428-8746-3214a587bb3f" class=3D"qt-x_qt-OWAAutoLi= nk">[email protected]</a>> wrote:<br></div><blockquote style=3D= "margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;pad= ding-left:1ex;border-left-color:rgb(204, 204, 204);border-left-style:sol= id;border-left-width:1px;"><div style=3D"direction:ltr;font-family:Aptos= , Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-seri= f;font-size:12pt;color:rgb(0, 0, 0);">Thanks for the timely response, An= drew.<br></div><div style=3D"direction:ltr;font-family:Aptos, Aptos_Embe= ddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif;font-size:= 12pt;color:rgb(0, 0, 0);"><br></div><div style=3D"direction:ltr;font-fam= ily:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, = sans-serif;font-size:12pt;color:rgb(0, 0, 0);">I've got a shiny new tran= sfer tool (hsqldbutil.jar) compiled and tried it every which way to no a= vail.<br></div><div style=3D"direction:ltr;font-family:Aptos, Aptos_Embe= ddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif;font-size:= 12pt;color:rgb(0, 0, 0);"><br></div><div style=3D"direction:ltr;font-fam= ily:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, = sans-serif;font-size:12pt;color:rgb(0, 0, 0);">Here's my last attempt, w= ith the response - I'm sure there's something I'm missing still. The hsq= ldbutil.jar is in my local directory.<br></div><div style=3D"direction:l= tr;font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, = Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);"><br></div><blo= ckquote style=3D"margin-left:0.8ex;padding-left:1ex;border-left-color:rg= b(200, 200, 200);border-left-style:solid;border-left-width:3px;"><div st= yle=3D"direction:ltr;font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFont= Service, Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0= );">java -cp hsqldbutil.jar org.hsqldb.util.Transfer --database1 jdbc:hs= qldb:hsql:/path/to/prod/db/hsql;user=3Dsa;password=3D --dump<br></= div></blockquote><div style=3D"direction:ltr;font-family:Aptos, Aptos_Em= beddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif;font-siz= e:12pt;color:rgb(0, 0, 0);"><br></div><div style=3D"direction:ltr;font-f= amily:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica= , sans-serif;font-size:12pt;color:rgb(0, 0, 0);">Wondering if I need to = change the jdbc connection to use 'file' vice 'hsql' since we're using a= .script file, but I can't seem to get past this message, as if it doesn= 't know what the --dump parameter is:<br></div><div style=3D"direction:l= tr;font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, = Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);"><br></div><div= style=3D"direction:ltr;font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSF= ontService, Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0= , 0);"><b>Exception in thread "main" java.lang.IllegalArgumentException:= Try: java org.hsqldb.util.Transfer --help</b><br></div><div style= =3D"direction:ltr;font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontSer= vice, Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);"= ><b>=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82at org.hsqldb.= util.Transfer.main(Unknown Source)</b><br></div><div style=3D"direction:= ltr;font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri,= Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);"><b>-bash: --d= ump: command not found</b><br></div><div id=3D"qt-x_qt-x_m_3172342521405= 161930appendonsend"><br></div><div style=3D"direction:ltr;font-family:Ap= tos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-s= erif;font-size:12pt;color:rgb(0, 0, 0);"><br></div><div style=3D"directi= on:ltr;font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calib= ri, Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);">All the --= help gives me is the usage (-help,-dump,-restore). It also seems t= o sometimes complain of the lack of X11 since it can also be run graphic= ally, but I do not want that, the goal here is to have a script that can= export data frequently, xfer it to another machine and have another script to import it.<br></div><div sty= le=3D"direction:ltr;font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontS= ervice, Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0)= ;"><br></div><div style=3D"direction:ltr;font-family:Aptos, Aptos_Embedd= edFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif;font-size:12= pt;color:rgb(0, 0, 0);">Appreciate any help you can provide, and ha= ppy Monday!<br></div><div style=3D"direction:ltr;font-family:Aptos, Apto= s_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif;font= -size:12pt;color:rgb(0, 0, 0);"><br></div><div style=3D"direction:ltr;fo= nt-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helve= tica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);">Regards, <br><= /div><div style=3D"direction:ltr;font-family:Aptos, Aptos_EmbeddedFont, = Aptos_MSFontService, Calibri, Helvetica, sans-serif;font-size:12pt;color= :rgb(0, 0, 0);"><br></div><div><hr style=3D"direction:ltr;display:inline= -block;width:98%;"><br></div><div id=3D"qt-x_qt-x_m_3172342521405161930d= ivRplyFwdMsg" dir=3D"ltr"><div><span style=3D"color:rgb(0, 0, 0);"><span= class=3D"font" style=3D"font-family:Calibri, sans-serif;"><span class=3D= "size" style=3D"font-size:11pt;"><b>From:</b> Andrew Richardson <= ;<a href=3D"mailto:[email protected]" id=3D"qt-x_qt-OWA92c47bb2-8963-9ea7= -0d2c-90f20b2e8e1e" class=3D"qt-x_qt-OWAAutoLink">[email protected]</a>&g= t;<br> <b>Sent:</b> Saturday, July 6, 2024 1:57 PM<br> <b>To:</b>&n= bsp;User discussions about HyperSQL Database Engine. <<a href=3D"mail= to:[email protected]" id=3D"qt-x_qt-OWA9df178c1-42b4-415= 2-9e35-013f26262346" class=3D"qt-x_qt-OWAAutoLink">[email protected]= rceforge.net</a>><br> <b>Subject:</b> Re: [Hsqldb-user] Duplicat= e HSQLDB - Export/Import from one instance to a new instance.</span></sp= an></span></div><div> <br></div></div><div style=3D"direction:ltr;"= >Hello again <sorry for the spam><br></div><div style=3D"direction= :ltr;">Out of curiosity, I looked into how to generate the Transfer Tool= . Here is what worked for me on Windows.<br></div><div style=3D"di= rection:ltr;margin-left:40px;"><br></div><div style=3D"direction:ltr;mar= gin-left:40px;"><div>cd hsqldb-2.7.3\hsqldb\build<br></div><div>gradlew = hsqldbutil<br></div><div>java -cp ..\lib\hsqldbutil.jar org.hsqldb.util.= Transfer --dump<br></div></div><div style=3D"direction:ltr;"><br></div><= div style=3D"direction:ltr;">Maybe this saves you a little time.<br></di= v><div style=3D"direction:ltr;">Cheers!<br></div><div style=3D"direction= :ltr;"><br></div><div style=3D"direction:ltr;">On Sat, Jul 6, 2024 at 12= :59=E2=80=AFPM Andrew Richardson <<a href=3D"mailto:[email protected]"= id=3D"qt-x_qt-OWA7fd106bd-4ade-54a6-18fc-083f18a5ee1a" class=3D"qt-x_qt= -OWAAutoLink">[email protected]</a>> wrote:<br></div><blockquote style= =3D"margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;= padding-left:1ex;border-left-color:rgb(204, 204, 204);border-left-style:= solid;border-left-width:1px;"><div style=3D"direction:ltr;">Hello Ken<br= ></div><div style=3D"direction:ltr;">Not sure if this will cover all you= r requirements, but have a look at the "Transfer Tool" utility. Mi= ght be a place to start.<br></div><div style=3D"direction:ltr;"><a href=3D= "https://hsqldb.org/doc/2.0/util-guide/transfer-tool-chapt.html" id=3D"q= t-x_qt-OWA9519d58d-a7ce-846b-1b7e-0431bf4c75f9" class=3D"qt-x_qt-OWAAuto= Link">https://hsqldb.org/doc/2.0/util-guide/transfer-tool-chapt.html</a>= <br></div><div style=3D"direction:ltr;"><br></div><div style=3D"directio= n:ltr;">Cheers,<br></div><div style=3D"direction:ltr;">Andrew Richardson= <br></div><div style=3D"direction:ltr;"><br></div><div style=3D"directio= n:ltr;">On Fri, Jul 5, 2024 at 4:18=E2=80=AFPM Ken Lemoine <<a href=3D= "mailto:[email protected]" id=3D"qt-x_qt-OWAec270083-2f07-a6f1-d8= 1f-cfba6bda62d9" class=3D"qt-x_qt-OWAAutoLink">[email protected]<= /a>> wrote:<br></div><blockquote style=3D"margin-top:0px;margin-right= :0px;margin-bottom:0px;margin-left:0.8ex;padding-left:1ex;border-left-co= lor:rgb(204, 204, 204);border-left-style:solid;border-left-width:1px;"><= div style=3D"direction:ltr;font-family:Aptos, Aptos_EmbeddedFont, Aptos_= MSFontService, Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb(0= , 0, 0);">Hello,<br></div><div style=3D"direction:ltr;font-family:Aptos,= Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif= ;font-size:12pt;color:rgb(0, 0, 0);"><br></div><div style=3D"direction:l= tr;font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, = Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);">Apologies in a= dvance if this has been asked before, I'm just not finding much inf= ormation in the archives. <br></div><div style=3D"direction:ltr;fon= t-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvet= ica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);"><br></div><div style= =3D"direction:ltr;font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontSer= vice, Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);"= >We have a hsqldb instance that needs to be replicated to a seconda= ry. I've tried and failed 3 different methods, mostly using the sq= ltool.jar and once by simply copying over the hsql.script file and cross= ing my fingers. The final attempt I just completed does seemingly create a valid 'exporteddata.sql' file, I can see it in = there. Then when I import it on the second machine, it also seemin= gly imports it - it outputs to stdout so I know it did SOMEthing. = But, the data is not showing up in the application.<br></div><div style=3D= "direction:ltr;font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontServic= e, Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);"><b= r></div><div style=3D"direction:ltr;font-family:Aptos, Aptos_EmbeddedFon= t, Aptos_MSFontService, Calibri, Helvetica, sans-serif;font-size:12pt;co= lor:rgb(0, 0, 0);">This is the latest hsqldb, running on latest FreeBSD = 14. <br></div><div style=3D"direction:ltr;font-family:Aptos, = Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif;= font-size:12pt;color:rgb(0, 0, 0);"><br></div><div style=3D"direction:lt= r;font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, H= elvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);">I'll also be ne= eding a way of fully automating this, to keep a second machine up to dat= e, and have struggled also getting a script to do this, but at this poin= t, I really just want to see if I can get this working manually.<br></di= v><div style=3D"direction:ltr;font-family:Aptos, Aptos_EmbeddedFont, Apt= os_MSFontService, Calibri, Helvetica, sans-serif;font-size:12pt;color:rg= b(0, 0, 0);"><br></div><div style=3D"direction:ltr;font-family:Aptos, Ap= tos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif;fo= nt-size:12pt;color:rgb(0, 0, 0);">Any tips or hints, or a point in the r= ight direction would be appreciated. <br></div><div styl= e=3D"direction:ltr;font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontSe= rvice, Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);= "><br></div><div style=3D"direction:ltr;font-family:Aptos, Aptos_Embedde= dFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif;font-size:12p= t;color:rgb(0, 0, 0);">Thanks!<br></div><div style=3D"direction:ltr;font= -family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helveti= ca, sans-serif;font-size:12pt;color:rgb(0, 0, 0);"><br></div><div style=3D= "direction:ltr;font-family:Aptos, Aptos_EmbeddedFont, Aptos_MSFontServic= e, Calibri, Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);">Ke= n<br></div><p style=3D"direction:ltr;"><b>Ken Lemoine</b> | Deploym= ent Manager<br></p><p style=3D"direction:ltr;">(614) 344-6500<br></p><p = style=3D"direction:ltr;"><a href=3D"http://www.cyberzek.com" id=3D"qt-x_= qt-OWA6dc2e0a9-459d-ed74-0e1d-1dedf72cbb12" class=3D"qt-x_qt-OWAAutoLink= " style=3D"margin-top:0px;margin-bottom:0px;">www.cyberzek.com</a><br></= p><div style=3D"direction:ltr;"><span><img style=3D"width:222px;height:5= 0px;" src=3D"https://static.wixstatic.com/media/7044be_7929e43cb3664f7f8= 7bd4b5d091e73f5~mv2.png/v1/fill/w_282,h_68,al_c,q_85,usm_0.66_1.00_0.01,= enc_auto/CYBERZEK%20Logo%20-%20Color%20-%20Horizontal.png" width=3D"222"= height=3D"50"></span><br></div><div style=3D"direction:ltr;"><div>_____= __________________________________________<br></div><div>Hsqldb-user mai= ling list<br></div><div><a href=3D"mailto:[email protected].= net" id=3D"qt-x_qt-OWA947c8451-bb93-726f-a832-6f1298dc2b97" class=3D"qt-= x_qt-OWAAutoLink">[email protected]</a><br></div><div><a= href=3D"https://lists.sourceforge.net/lists/listinfo/hsqldb-user" id=3D= "qt-x_qt-OWAc1fb859b-6e3b-e879-9aa2-fbdaaf21734b" class=3D"qt-x_qt-OWAAu= toLink">https://lists.sourceforge.net/lists/listinfo/hsqldb-user</a><br>= </div></div></blockquote></blockquote><p style=3D"direction:ltr;"><b>Ken= Lemoine</b> | Deployment Manager<br></p><p style=3D"direction:ltr;= ">(614) 344-6500<br></p><p style=3D"direction:ltr;"><a href=3D"http://ww= w.cyberzek.com" id=3D"qt-x_qt-OWA152b2acb-244d-8b69-c0ff-4c66e7e5a593" c= lass=3D"qt-x_qt-OWAAutoLink" style=3D"margin-top:0px;margin-bottom:0px;"= >www.cyberzek.com</a><br></p><div style=3D"direction:ltr;"><span><img st= yle=3D"width:222px;height:50px;" src=3D"https://static.wixstatic.com/med= ia/7044be_7929e43cb3664f7f87bd4b5d091e73f5~mv2.png/v1/fill/w_282,h_68,al= _c,q_85,usm_0.66_1.00_0.01,enc_auto/CYBERZEK%20Logo%20-%20Color%20-%20Ho= rizontal.png" width=3D"222" height=3D"50"></span><br></div><div>________= _______________________________________<br></div><div>Hsqldb-user mailin= g list<br></div><div><a href=3D"mailto:[email protected]= " id=3D"qt-x_qt-OWA875b3fd9-8a30-ab51-f4dd-f485413452c4" class=3D"qt-x_q= t-OWAAutoLink">[email protected]</a><br></div><div><a hr= ef=3D"https://lists.sourceforge.net/lists/listinfo/hsqldb-user" id=3D"qt= -x_qt-OWA16c87203-68c8-1b63-d8fe-8c7898a5e7cf" class=3D"qt-x_qt-OWAAutoL= ink">https://lists.sourceforge.net/lists/listinfo/hsqldb-user</a><br></d= iv></blockquote><p><b>Ken Lemoine</b> | Deployment Manager<br></p><p>(61= 4) 344-6500<br></p><p>www.cyberzek.com<br></p><div><img src=3D"https://s= tatic.wixstatic.com/media/7044be_7929e43cb3664f7f87bd4b5d091e73f5~mv2.pn= g/v1/fill/w_282,h_68,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/CYBERZEK%20Lo= go%20-%20Color%20-%20Horizontal.png" width=3D"222" height=3D"50"><br></d= iv><div><br></div><div>_______________________________________________<b= r></div><div>Hsqldb-user mailing list<br></div><div><a href=3D"mailto:Hs= [email protected]">[email protected]</a><b= r></div><div><a href=3D"https://lists.sourceforge.net/lists/listinfo/hsq= ldb-user">https://lists.sourceforge.net/lists/listinfo/hsqldb-user</a><b= r></div><div><br></div></blockquote><div style=3D"font-family:Arial;"><b= r></div></div><p><b>Ken Lemoine</b> | Deployment Manager<br></p><p>(614)= 344-6500<br></p><p>www.cyberzek.com<br></p><div><img src=3D"https://sta= tic.wixstatic.com/media/7044be_7929e43cb3664f7f87bd4b5d091e73f5~mv2.png/= v1/fill/w_282,h_68,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/CYBERZEK%20Logo= %20-%20Color%20-%20Horizontal.png" width=3D"222" height=3D"50"><br></div= ><div><br></div><div>_______________________________________________<br>= </div><div>Hsqldb-user mailing list<br></div><div><a href=3D"mailto:Hsql= [email protected]">[email protected]</a><br>= </div><div><a href=3D"https://lists.sourceforge.net/lists/listinfo/hsqld= b-user">https://lists.sourceforge.net/lists/listinfo/hsqldb-user</a><br>= </div><div><br></div></blockquote><div style=3D"font-family:Arial;"><br>= </div></body></html> --fb45f6c3b2da458e8be9f10b2a576a6e-- --===============8288670187537230522== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline --===============8288670187537230522== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline _______________________________________________ Hsqldb-user mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/hsqldb-user --===============8288670187537230522==--