Mysql to postgresql with pgloader

gdamjan · February 3, 2022, 5:45pm

to whom it might concern,

I just successfully migrated a tt-rss installation from mysql 8.0 to postgresql 13.0 (both cloud instances on scaleway) with pgloader.

I skipped migrating the tables: ttrss_sessions and ttrss_prefs_sections (is this table even used?) and had to configure some custom type casts.

I used the docker image for pgloader, since the ubuntu package seemed broken.

I migrated 6 users and 25324 entries

Here’s the pgloader commands file for reference:

gist.github.com

https://gist.github.com/gdamjan/7a586e5fd809b4719e359683573d9d8f

commands.load

LOAD DATABASE
     FROM      mysql://ttrss:[email protected]:3306/ttrss
     INTO      postgresql://tt-rss:[email protected]:5432/tt-rss

WITH include no drop, create no tables, truncate

ALTER schema 'ttrss' rename to 'public'

CAST type datetime to timestamp,
     type timestamp to timestamp,

This file has been truncated. show original

report.txt

2022-02-03T03:21:28.195000Z LOG report summary reset
                     table name     errors       read   imported      bytes      total time       read      write
-------------------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
                fetch meta data          0         69         69                     0.837s    
              Drop Foreign Keys          0         62         62                     8.128s    
                       Truncate          0         31         31                     1.115s    
-------------------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
              public.ttrss_tags          0      53744      53744     1.5 MB         15.386s     8.430s     0.099s
      public.ttrss_user_entries          0      25324      25324     2.4 MB         17.575s     9.435s     0.140s
           public.ttrss_entries          0      25324      25324    95.3 MB       1m27.742s  1m27.500s    18.210s

This file has been truncated. show original

gdamjan · February 3, 2022, 5:47pm

ps.
I configured pgloader to only copy the data, I pre-created the postgresql schema from the tt-rss source. pgloader can try to migrate the schema too, but it didn’t create the same one that tt-rss expects.