4

I am using pgloader to migrate DB data from MySQL to Postgres and I set up everything as per their documentation but still no data imported.

ubuntu@ip-123-31-13-119:/applications$ pgloader pgload.load

pgloader Response:

2020-02-22T06:28:27.676000Z LOG report summary reset
             table name       read   imported     errors      total time
-----------------------  ---------  ---------  ---------  --------------
            before load          3          3          0          0.015s
        fetch meta data          0          0          0          0.287s
         Create Schemas          0          0          0          0.006s
       Create SQL Types          0          0          0          0.008s
          Create tables          0          0          0          0.000s
         Set Table OIDs          0          0          0          0.000s
-----------------------  ---------  ---------  ---------  --------------
-----------------------  ---------  ---------  ---------  --------------
COPY Threads Completion          8          8          0          0.000s
 Index Build Completion          0          0          0          0.000s
        Reset Sequences          0          0          0          0.030s
           Primary Keys          0          0          0          0.000s
    Create Foreign Keys          0          0          0          0.000s
        Create Triggers          0          0          0          0.001s
       Install Comments          0          0          0          0.000s

When I checked into the database it's showing nothing

MD247_development=# select * from users;
ERROR:  relation "users" does not exist
LINE 1: select * from users;
                      ^
MD247_development=#

Here is the LOAD file:

LOAD DATABASE
     FROM mysql://ubuntu:secure@localhost/MD247_development
     INTO postgresql://ubuntu:secure2020@localhost/MD247_development

 WITH include drop, create tables, create indexes, reset sequences,
      workers = 8, concurrency = 1,
      multiple readers per thread, rows per range = 50000,
      prefetch rows = 10000

  SET PostgreSQL PARAMETERS
      maintenance_work_mem to '1024MB',
      work_mem to '1024',
      search_path to 'md247_development, public, "$user"'

  SET MySQL PARAMETERS
      net_read_timeout  = '220',
      net_write_timeout = '220'

  including only table names matching 'users,user_requests,search_queries,addresses'

  ALTER SCHEMA 'md247_development' RENAME TO 'pagila'

  BEFORE LOAD DO
   $$ create schema if not exists pagila; $$,
   $$ create schema if not exists mv;     $$,
   $$ alter database MD247_development set search_path to pagila, mv, public; $$;

I am not sure where is the issue.

Ravindra Yadav
  • 619
  • 2
  • 6
  • 15

1 Answers1

3

No doubt you're all sorted by now but I'll answer for anyone else who finds this.

I had the same issue but realised that pgloader would not actually load any data if the destination database was not completely empty and devoid of tables.

Even when the destination database had an empty table of name not existing in the source database I found that pgloader would appear to succeed but actually do nothing.

EDIT: Sorry, my bad. When importing into a database with existing schema the data is imported into a differing (not default) schema so a query without specifying that new schema will appear like the data is missing. Once I realised that my data was imported I then focused on moving it to the public schema (I was actually pushing two mysql databases in to one postgres database). I used the following which I found on another site .. https://dba.stackexchange.com/questions/237813/how-do-i-move-all-tables-from-one-postgres-schema-to-another

Infustion
  • 31
  • 4