ERROR Database error 3F000: no schema has been selected to create in

[pathikrit]

This is my config:

load database
  from      mysql://mysqluser:mypassword@localhost/main
  into      postgresql://localhost/main

WITH include drop, create tables, no truncate, create indexes, reset sequences, foreign keys

SET maintenance_work_mem to '128MB', work_mem to '12MB', search_path to 'sakila'

CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,
     type date drop not null drop default using zero-dates-to-null

BEFORE LOAD DO
$$ create schema if not exists main;$$;

I get this error:

2016-02-03T16:18:25.014000-08:00 NOTICE Starting pgloader, log system is ready.
2016-02-03T16:18:25.099000-08:00 LOG Main logs in '/private/tmp/pgloader/pgloader.log'
2016-02-03T16:18:25.103000-08:00 LOG Data errors in '/private/tmp/pgloader/'
2016-02-03T16:18:25.103000-08:00 LOG Parsing commands from file #P"/Volumes/User Data/Users/pbhowmick/Projects/gandalf/scripts/postgres/pgloader.conf"
2016-02-03T16:18:26.527000-08:00 NOTICE MySQL metadata fetched: found 139 tables with 137 indexes total.
2016-02-03T16:18:26.527000-08:00 NOTICE DROP then CREATE TABLES
2016-02-03T16:18:31.209000-08:00 WARNING PostgreSQL warning: table "access_logs" does not exist, skipping
2016-02-03T16:18:31.210000-08:00 ERROR Database error 3F000: no schema has been selected to create in
QUERY: CREATE TABLE access_logs
(
  id                     bigserial not null,
  request_time           timestamptz(6),
  user_agent             text,
  client_version         text,
  ip                     text not null,
  "user"                 text,
  action                 text not null,
  uri                    text not null,
  request_context_json   text,
  body                   text,
  response_time          bigint,
  response_code          bigint,
  errors                 text,
  server_metadata        text,
  role                   text,
  impersonating_user     text,
  impersonating_user_role   text
);
2016-02-03T16:18:31.210000-08:00 FATAL Failed to create the schema, see above.

What am I doing wrong? If I copy paste the create table into psql, it creates the table just fine!

[dimitri]

The search_path setting in your SET clause looks wrong. Change it to main?

[pathikrit]

Still the same error:

This is my new conf:

LOAD database
  FROM  mysql://dev:dev@localhost/main
  INTO  postgresql://dev@localhost/main
  WITH  include no drop, create tables, no truncate, create indexes, reset sequences, foreign keys
  SET   maintenance_work_mem to '128MB', work_mem to '12MB', search_path to 'main'
  CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,
       type date drop not null drop default using zero-dates-to-null
  BEFORE LOAD DO $$ CREATE SCHEMA IF NOT EXISTS main; $$
;

[pathikrit]

Actually it worked if I removed the search_path completely. What is it meant to be? The mysql db name??

This is the config that worked:

LOAD database
  FROM  mysql://dev:dev@localhost/main
  INTO  postgresql://dev@localhost/main
  WITH  include no drop, create tables, no truncate, create indexes, reset sequences, foreign keys
  SET   maintenance_work_mem to '128MB', work_mem to '12MB'
  CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,
       type date drop not null drop default using zero-dates-to-null
  BEFORE LOAD DO $$ CREATE SCHEMA IF NOT EXISTS main; $$
;

[dimitri]

The SET clause, as documented, is a list of PostgreSQL specific GUCs that are going to be set by pgloader in its PostgreSQL connections. See PostgreSQL documentation about the search_path to understand its behavior and relationship with the schema. It seems that in your case you would be good to go with just:

pgloader mysql://dev:dev@localhost/main postgresql://dev@localhost/main

Maybe you would still need the --before create-schema.sql option.