mysqlsqlpostgresqldatabase-migrationpgloader

pgloader not importing data from MySQL to Postgres


I tried the following command and it returns no errors but the data is not imported in my postgres database.

Database is already created in Postgres.

pgloader mysql://user:password@localhost/mydb postgresql://user:password@localhost/mydb

This is the result:

                                        table name       read   imported     errors      total time
-------------------------------------------------  ---------  ---------  ---------  --------------
                                  fetch meta data         38         38          0          1.032s 
                                   Create Schemas          0          0          0          0.253s 
                                 Create SQL Types          0          0          0          0.008s 
                                    Create tables         20         20          0          0.417s 
                                   Set Table OIDs         10         10          0          0.020s 
-------------------------------------------------  ---------  ---------  ---------  --------------
                        mydb.active_admin_comments          0          0          0          0.007s 
                         mydb.ar_internal_metadata          1          1          0          0.139s 
                                  mydb.departments          2          2          0          0.090s 
                                        mydb.roles          2          2          0          0.174s 
                                   mydb.sentiments          3          3          0          0.223s 
                                mydb.twitter_users          6          6          0          0.276s 
                                 mydb.designations          3          3          0          0.087s 
                            mydb.schema_migrations         17         17          0          0.085s 
                                       mydb.tweets         47         47          0          0.238s 
                                        mydb.users          2          2          0          0.184s 
-------------------------------------------------  ---------  ---------  ---------  --------------
                          COPY Threads Completion          4          4          0          0.333s 
                                   Create Indexes         22         22          0          2.770s 
                           Index Build Completion         22         22          0          0.626s 
                                  Reset Sequences          8          8          0          0.208s 
                                     Primary Keys         10         10          0          0.069s 
                              Create Foreign Keys          6          6          0          0.053s 
                                  Create Triggers          0          0          0          0.000s 
                                 Install Comments          0          0          0          0.000s 
-------------------------------------------------  ---------  ---------  ---------  --------------
                                Total import time         83         83          0          4.051s 

When I login to psql to look for the data, its not there. For example, for the table users 2 records were supposed to be imported as mentioned above by pgloader, but this is the result:

user1=> \c mydb postgres
Password for user postgres: 
psql (10.0, server 9.6.5)
You are now connected to database "mydb" as user "postgres".
mydb=# SELECT count(*) FROM users;

 count 
-------
     0
(1 row)

mydb=# \dn
            List of schemas
            Name             |  Owner   
-----------------------------+----------
 public                      | postgres
 mydb                        | postgres
(2 rows)

What is going wrong?


Solution

  • I assume due to the difference in terminology (mysql schema is what postgres takes as database) you have your tables loaded to mydb schema, not public. the list with "prefix" in table name gave this idea. so in order to find your data loaded - specify schema name before table name , eg

    select count(*) from mydb.schema_migrations
    

    should return 17 rows - imported with pgloader