Pgloader: No errors, no results

[benvest]

I've been trying to move a mysql database to postgres using pgloader. Everything seems like it is working correctly, there are no errors / exceptions / warnings however, when I connect to the target postgres database it seems like nothing has happened. No tables get created, and no data is moved.

MacOSX 10.12.6

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.19, for macos10.12 (x86_64) using  EditLine wrapper

$ psql --version
psql (PostgreSQL) 10.0

$ pgpgloader --version (Installed via brew install --HEAD pgloader)
pgloader version "3.4.dd401c5"
compiled with SBCL 1.4.0

What I'm doing:

pgloader mysql://root@localhost/my_db postgresql://root@localhost/my_db

Which gives me good results:

2017-10-25T13:05:19.465000-05:00 LOG report summary reset
table name     errors       read   imported      bytes      total time
---------------------------------  ---------  ---------  --------- 
fetch meta data          0        337        337                     0.305s
Create Schemas        0          0          0                         0.000s
Create SQL Types       0          0          0                         0.004s
Create tables            0        334        334                    0.576s
Set Table OIDs          0        167        167                      0.004s
---------------------------------  ---------  ---------  ---------
.
tables
.
COPY Threads Completion 0          4          4                     2.795s
Create Indexes                     0        170        170                     0.715s
Index Build Completion        0        170        170                     0.238s
Reset Sequences                  0        165        165                     
Primary Keys                         0        166        166                     0.070s
Create Foreign Keys             0          0          0                     0.000s
Create Triggers                     0          0          0                     0.000s
Install Comments                   0          0          0                     0.000s
-----------------  ---------  ---------  ---------
Total import time          ✓     152880     152880    40.8 MB          3.875s
2017-10-25T13:05:19.494000-05:00 INFO Stopping monitor

Now when I open the postgres db there is no data in the new database

$ psql my_db

my_db=# \d+
Did not find any relations.

Anyone have any thoughts or suggestions as to what may be going wrong?

Thanks

[ckcollab]

I may have made a duplicate of this issue @ #655

[dimitri]

pgloader defaults to creating the tables into a PostgreSQL schema of the same name as the MySQL schema. It doesn't change the search_path in PostgreSQL for you tho. Try \dn and then \dt <schema name here>..

[iquad]

Were going to report same issue, but I was figured that it uses databasename as schema. Has no problem until PostgreSQL 10 upgrade, but now that.

[jonny64]

It is bad default to require renaming to default schema (public)
I need to get the job done and start the app.
I have 12 databases including prod, pre prod, dev, and local instanses for devs
I can obtain connection string, but each time I need to obtain not only conn string, but different schema name (oh f*ck) and change alter schema rename to (which does not support templating)
My pain.

[jonny64]

Another proglem is 4GB heap limit which was hardcoded somewhere which cause heap error sometime after migrating super large tables

[dimitri]

No PostgreSQL schema is renamed by pgloader. The command installs a mapping and renames the source MySQL schema into a different target PostgreSQL schema.

PostgreSQL has a notion of a search_path so that using public or something else is transparent to applications. Please read the documentation about PostgreSQL schemas. You can add the following SQL command to your pgloader command:

  -- that goes into the pgloader command
  -- the new schema created by pgloader is now in the search_path
  -- and behaves just like public does, only you are the owner of it
  after load do $$ alter database dbname set search_path to dbname, public; $$

Also, have you tried to have pgloader target the public schema anyway, as per the pgloader documentation (you've read it, right?)

  -- that goes into the pgloader command
  alter schema 'dbname' rename to 'public'

[iquad]

I've read 'em all, yes. But, problem is, to rename a schema, your user need to "own" database, which we do not allow it on developers. Other problem is, most of our developers are using JetBrains products -like PHPStorm, Gogland, Intellij- and its' database panel shows public schema as default. So, they need to change their configuration to use different schema.

I've no idea about Lisp, but if I could, I'd make a MR to give parameter for schema name like --target-schema="foo". If it null, could use public or vici versa.

You can think about it :)

[dimitri]

So, @iquad your idea of --target-schema is a good one, and is spelled alter schema ... rename to 'foo' in the pgloader command syntax.

I just added the feature in pgloader to add the created schema to the search_path in a MySQL migration. It should make the tooling your reference happy, and your developers to. Having the target schema in the search_path makes it behave just like public for most purposes.

The PostgreSQL command used to do that is the following and now automatically issued by pgloader, so that there should be no surprises left for you guys. Please confirm it makes thing “just work” again.

ALTER DATABASE plop SET search_path TO public, f1db;