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.
x
pgloader mysql://user:password@localhost/mydb postgresql://user:password@localhost/mydbThis 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 postgresPassword 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_migrationsshould return 17 rows – imported with pgloader