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?
Advertisement
Answer
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