0

For my rails application, I'm converting a local sqlite3 file into a postgres database with pgloader.

To do this, I created a postgres database

 createdb my_database_development

and I create a pgload command file

load database  
     from 'db/production.sqlite3'  
     into postgresql:///my_database_development  

 with include drop, create tables, create indexes, reset sequences  

  set work_mem to '16MB', maintenance_work_mem to '512 MB';

Now loading the sqlite file into the postgres db works, and I don't think I have any errors. All tables and records are copied, but when I look in my rails application, none of the records have an index (or id). Here is the output of loading the sqlite file into the database

simon (master *) Blog-database $ pgloader db/development.sqlite3 postgresql:///my_database_development
2016-08-06T13:57:41.201000+03:00 LOG Main logs in '/tmp/pgloader/pgloader.log'
2016-08-06T13:57:41.222000+03:00 LOG Data errors in '/tmp/pgloader/'
2016-08-06T13:57:41.425000+03:00 WARNING Postgres warning: table "schema_migrations" does not exist, skipping
2016-08-06T13:57:41.626000+03:00 WARNING Postgres warning: table "comments" does not exist, skipping
2016-08-06T13:57:41.627000+03:00 WARNING Postgres warning: table "users" does not exist, skipping
2016-08-06T13:57:41.627000+03:00 WARNING Postgres warning: table "pictures" does not exist, skipping
2016-08-06T13:57:41.828000+03:00 WARNING Postgres warning: table "posts" does not exist, skipping
            table name       read   imported     errors            time
----------------------  ---------  ---------  ---------  --------------
                 fetch          0          0          0          0.001s
       fetch meta data          8          8          0          0.050s
      create, truncate          0          0          0          0.343s
----------------------  ---------  ---------  ---------  --------------
     schema_migrations         12         12          0          0.068s
              comments         23         23          0          0.014s
                 users          3          3          0          0.064s
              pictures         24         24          0          0.069s
                 posts          4          4          0          0.055s
index build completion          0          0          0          0.014s
----------------------  ---------  ---------  ---------  --------------
        Create Indexes          4          4          0          0.312s
       Reset Sequences          0          0          0          0.064s
----------------------  ---------  ---------  ---------  --------------
  Total streaming time         66         66          0          0.742s

The pgloader logs look like this

simon pgloader $ cat pgloader.log
2016-08-06T14:23:53.146000+03:00 NOTICE Starting pgloader, log system is ready.
2016-08-06T14:23:53.223000+03:00 LOG Main logs in 'NIL'
2016-08-06T14:23:53.241000+03:00 LOG Data errors in '/tmp/pgloader/'
2016-08-06T14:23:53.241000+03:00 LOG Parsing commands from file #P"/home/simon/Blog-database/sqlite.loader"
2016-08-06T14:23:53.442000+03:00 NOTICE DROP then CREATE TABLES
2016-08-06T14:23:53.443000+03:00 WARNING Postgres warning: table "schema_migrations" does not exist, skipping
2016-08-06T14:23:53.644000+03:00 WARNING Postgres warning: table "comments" does not exist, skipping
2016-08-06T14:23:53.644000+03:00 WARNING Postgres warning: table "users" does not exist, skipping
2016-08-06T14:23:53.645000+03:00 WARNING Postgres warning: table "pictures" does not exist, skipping
2016-08-06T14:23:53.645000+03:00 WARNING Postgres warning: table "posts" does not exist, skipping
2016-08-06T14:23:53.845000+03:00 NOTICE COPY schema_migrations
2016-08-06T14:23:53.846000+03:00 NOTICE CREATE UNIQUE INDEX "unique_schema_migrations" ON "schema_migrations" ("version")
2016-08-06T14:23:53.846000+03:00 NOTICE COPY comments
2016-08-06T14:23:53.847000+03:00 NOTICE CREATE INDEX "index_comments_on_post_id" ON "comments" ("post_id")
2016-08-06T14:23:53.847000+03:00 NOTICE COPY users
2016-08-06T14:23:53.847000+03:00 NOTICE CREATE UNIQUE INDEX "index_users_on_email" ON "users" ("email")
2016-08-06T14:23:53.847000+03:00 NOTICE CREATE UNIQUE INDEX "index_users_on_reset_password_token" ON "users" ("reset_password_token")
2016-08-06T14:23:53.848000+03:00 NOTICE COPY pictures
2016-08-06T14:23:53.848000+03:00 NOTICE COPY posts
2016-08-06T14:23:54.049000+03:00 NOTICE Reset sequences

Is this a rails issue? Could it be that you can't just copy an id because it behaves as a protected attribute?

sjbuysse
  • 3,872
  • 7
  • 25
  • 37
  • `... none of the records have an index in the postgres database` and `Why are record indexes not copied?` what exactly are record indexes? – wildplasser Aug 06 '16 at 13:07
  • Sorry wildplasser, I just edited my question as I realized it wasn't clear - I'm talking about id's in ActiveRecord – sjbuysse Aug 06 '16 at 13:08
  • It looks like the indexes in the logs are only supporting indexes for FKs and additional UNIQUE contraints. if you run `pg_dump --schema-only your_db_name` you get the entire DDL for the DB, so you can inspect it. (I assume the PKs all use serials for their id columns, the last line in the log mentions resetting the sequences) – wildplasser Aug 06 '16 at 13:16
  • I'm not completely sure what you're explaining. I should have said that I'm a quite new when it comes to databases. Did you want me to post the output of that pg_dump --schema-only? – sjbuysse Aug 06 '16 at 13:26
  • No absolutely not. You can just read this output to see what the current structure of the database is (table definitions, constraints and indexes ,etc) – wildplasser Aug 06 '16 at 13:28
  • Actually when I inspect pg_dump with the data included, I can see that the id's are in there! But then at the end I see lines that say for example `-- Name: index_comments_on_post_id; Type: INDEX; Schema: public; Owner: sjbuysse; Tablespace: -- CREATE INDEX index_comments_on_post_id ON comments USING btree (post_id); ' I'm not completely sure what's happening here, would you mind explaining this? – sjbuysse Aug 06 '16 at 13:34
  • No. Better read a book, or the online documentation. The pg_dump output is basically what would be needed to reconstruct (restore) the db. So it is a handy way to check what is actually in there. In the case of these last lines: some indexes are created (which looks good). – wildplasser Aug 06 '16 at 13:51
  • When I inspect the database with some SELECT queries, it seems that all the id's are there. So it must be an ActiveRecord issue. I'll look into it, thanks for your help so far wildplasser – sjbuysse Aug 06 '16 at 13:54

0 Answers0