Bug#1053757: migration from sqlite to postgresql 15 failed
Hi Thomas, sorry for the late response. Somehow this went out of focus. I have backported your 2.5.0 package too bookworm. After running the new xca the sqlite db was updated, next I exported it using sqlite3, but on importing it psql complained about the first line in the dump file again: ERROR: syntax error at or near "PRAGMA" LINE 1: PRAGMA foreign_keys=OFF; ^ BEGIN CREATE TABLE INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 : Finally (after about 400 lines) it failed with a syntax error: : INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 ERROR: syntax error at or near ")" LINE 1: ...0190307103908Z',replace('obsolete\n','\n',char(10)),2507,1); ^ ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block : I am not sure if xca is to blame here. The complete line looks like this: INSERT INTO items VALUES(399,'old_strongSwan_IPsec_client',5,5,'20190307103908Z',replace('obsolete\n','\n',char(10)),2507,1); There are tons of lines with replace('sometext\n','\n',char(10)) I wonder how these newlines came into the database at all? Regards Harri
Bug#1053757: migration from sqlite to postgresql 15 failed
Package: xca Version: 2.4.0-2+b1 Migrating from sqlite to postgresql 15 (as described on https://hohnstaedt.de/xca/index.php/documentation/remote-databases) fails. The first line in the sqlite3 dump "PRAGMA foreign_keys=OFF;" is not accepted on import. After removing the line (similar to the mysql procedure) the import fails with : INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 ERROR: syntax error at or near ")" LINE 1: ...0190307103908Z',replace('obsolete\n','\n',char(10)),2507,1); ^ ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block : This is fatal. My sqlite database got pretty huge. I'd love to move to postgresql 15 to improve performance. BTW, xca 2.5.0 was released a few days ago. Regards Harri