Migrating from sqlite to pgsql

[roughnecks]

Hello,
If I wanted to migrate db from sqlite to postgres, what should I change in chyrp-lite config to reflect this change? Assuming that's feasible.

Thanks

[xenocrat]

The actual database migration is not something Chyrp Lite can help with. Assuming you've already done the database migration and you've now got a PostgreSQL database with a copy of the SQLite data...

You need to manually edit your includes/config.json.php file, replacing the values in the "sql" object with your new values for host, port, username, database, and prefix. Change the "adapter" key to "adapter": "pgsql". That should be all.

[roughnecks]

yes, thanks I only needed to know where to configure the new db in chyrp-lite haven't migrated yet but it's not the first time for me doing that, so there's hope I can succeed Il 9 giugno 2024 23:38:33 CEST, Daniel Pimley ***@***.***> ha scritto:

…

The actual database migration is not something Chyrp Lite can help with. Assuming you've already done the database migration and you've now got a PostgreSQL database with a copy of the SQLite data... You need to manually edit your _includes/config.json.php_ file, replacing the values in the `"sql"` object with your new values for host, port, username, database, and prefix. Change the `"adapter"` key to `"adapter": "pgsql"`. That should be all. -- Reply to this email directly or view it on GitHub: #261 (comment) You are receiving this because you authored the thread. Message ID: ***@***.***>

[roughnecks]

Good morning.
I think this will have to wait unless you have some suggestions:

using "pgloader" to do the conversion

2024-06-10T05:17:03.028000Z LOG pgloader version "3.6.7~devel"
2024-06-10T05:17:03.036000Z LOG Data errors in '/tmp/pgloader/'
2024-06-10T05:17:03.388002Z ERROR Database error 42704: type "longtext" does not exist
QUERY: CREATE TABLE post_attributes
(
  post_id bigint,
  name    text default '',
  value   longtext
);
2024-06-10T05:17:03.392002Z FATAL Failed to create the schema, see above.
2024-06-10T05:17:03.396002Z LOG report summary reset
       table name     errors       rows      bytes      total time
-----------------  ---------  ---------  ---------  --------------
            fetch          0          0                     0.000s
  fetch meta data          0         22                     0.076s
   Create Schemas          0          0                     0.000s
 Create SQL Types          0          0                     0.008s
    Create tables          0          0                     0.000s
-----------------  ---------  ---------  ---------  --------------

dimitri/pgloader#1231

[xenocrat]

I don't know anything about pgloader and how it works, but LONGTEXT is not a valid column type for PostgreSQL.

You can see in QueryBuilder.php that we alter the CREATE query depending on the database adapter. Among the changes for pgsql is to replace LONGTEXT column definitions with TEXT, and DATETIME with TIMESTAMP. The other column definitions look a bit suspect to me as well - post_id should have a NOT NULL constraint and there is no key defined, which could degrade performance. You can see the original column definition in install.php. If pgloader can't recreate all of that, you might want to do a manual check over the new tables after conversion and recreate any missing keys etc.

[roughnecks]

Okay but I'm not that confident in doing such operations. Everything will stay as it is currently.