Pgloader: How to import into public schema

[ShurikAg]

I am trying to migrate one of our systems from mysql to postgresql.
Everything seem to be working as advertised and I would like to thank creators and supporters for that.

One question though:
When I import the database, it automatically creates an additional schema (named as the original db name in MySQL). Is there a way to import the database into public schema in target db?

[dimitri]

Hi, thanks!

It should be in the docs, you can use the pgloader clause ALTER SCHEMA dbname RENAME TO public in your pgloader command. This will change the name of the schema in the pgloader internal mapping of catalogs from MySQL to PostgreSQL. The impact is that all objects found in the dbname schema on MySQL are migrated over to the public schema in PostgreSQL.

By default tho, PostgreSQL public schema is owned by the superuser, so you won't have the necessary privileges to DROP SCHEMA public; which pgloader will try to do. So you have to tell pgloader not to create and drop schemas.

The following command should do it (untested, typed here directly, all from memory):

load database
   from mysql://user@host/dbname
   into pgsql://user@host/dbname
  with create no schema
  alter schema 'dbname' rename to 'public';

As a side note, I think it's preferable to keep things in the dbname schema in PostgreSQL and add that schema to the database default search_path : alter database dbname set search_path to dynamo, public; in PostgreSQL.

[ShurikAg]

Thanks for the advice.

[samsondav]

I tried this, it didn't work. I get this error:

KABOOM!
2017-10-17T15:23:14.011000+01:00 NOTICE Starting pgloader, log system is ready.
FATAL error: At

        into pgsql://nested:nested@localhost:54321/nextdayproperty
          with create no schema

                         ^ (Line 4, Column 23, Position 164)

2017-10-17T15:23:14.020000+01:00 LOG Data errors in '/private/tmp/pgloader/'
2017-10-17T15:23:14.023000+01:00 LOG Parsing commands from file #P"/Users/sam/code/work/nested/data-management-scripts/script/properties_pg_migration_command_file"
In context MYSQL-OPTION:

While parsing MYSQL-OPTION. Expected:

     the character Tab
  or the character Newline
  or the character Return
  or the character Space
  or the string "--"
  or the string "/*"
  or the string "indexes"
  or the string "tables"
An unhandled error condition has been signalled: At

        into pgsql://nested:nested@localhost:54321/nextdayproperty
          with create no schema

                         ^ (Line 4, Column 23, Position 164)

In context MYSQL-OPTION:

While parsing MYSQL-OPTION. Expected:

     the character Tab
  or the character Newline
  or the character Return
  or the character Space
  or the string "--"
  or the string "/*"
  or the string "indexes"
  or the string "tables"




What I am doing here?

At

        into pgsql://nested:nested@localhost:54321/nextdayproperty
          with create no schema

                         ^ (Line 4, Column 23, Position 164)

In context MYSQL-OPTION:

While parsing MYSQL-OPTION. Expected:

     the character Tab
  or the character Newline
  or the character Return
  or the character Space
  or the string "--"
  or the string "/*"
  or the string "indexes"
  or the string "tables"


Waiting for the monitor thread to complete.

[snusnu]

I've stumbled over a somewhat related issue.

I'm using pgloader to migrate several, differently named mysql DBs to postgres in a scripted environment, and I need the migrated tables to all live in a data schema inside every created postgres DB. To achieve this, I wanted to use the new (in 3.4.1) feature of accessing ENV vars using GETENV 'var_name' like shown below (previously, i performed manual sed replacements on a "template" config file prior to feeding it to pgloader).

While GETENV seems to work well within FROM and INTO, it doesn't work in the following statement:

ALTER SCHEMA GETENV 'DB_NAME' RENAMTE TO 'data'

This gives the error shown below, telling me that GETENV seems to be unsupported within ALTER SCHEMA. However, I think that this would make a perfect candidate scope for the ENV var interpolation? As it stands, this is the only reason why I still need to generate an intermediate config file for every DB, using sed or something like it, to be able to use the appropriate value.

KABOOM!
FATAL error: At

  ALTER SCHEMA
    GETENV 'DB_NAME'

    ^ (Line 24, Column 2, Position 457)

In context ALTER-SCHEMA-RENAME-TO:

While parsing QUOTED-NAMESTRING. Expected:

     the character ' (APOSTROPHE)

While parsing IGNORE-WHITESPACE. Expected:

     the character Tab
  or the character Newline
  or the character Return
  or the character Space
  or the string "--"
  or the string "/*"
An unhandled error condition has been signalled: At

  ALTER SCHEMA
    GETENV 'DB_NAME'

    ^ (Line 24, Column 2, Position 457)

In context ALTER-SCHEMA-RENAME-TO:

While parsing QUOTED-NAMESTRING. Expected:

     the character ' (APOSTROPHE)

While parsing IGNORE-WHITESPACE. Expected:

     the character Tab
  or the character Newline
  or the character Return
  or the character Space
  or the string "--"
  or the string "/*"



2017-10-18T12:10:03.039000Z NOTICE Starting pgloader, log system is ready.

What I am doing here?

At

  ALTER SCHEMA
    GETENV 'DB_NAME'

    ^ (Line 24, Column 2, Position 457)

In context ALTER-SCHEMA-RENAME-TO:

While parsing QUOTED-NAMESTRING. Expected:

     the character ' (APOSTROPHE)

While parsing IGNORE-WHITESPACE. Expected:

     the character Tab
  or the character Newline
  or the character Return
  or the character Space
  or the string "--"
  or the string "/*"


Waiting for the monitor thread to complete.

[dimitri]

@snusnu please see https://github.com/dimitri/pgloader/issues/555 ; GETENV is now deprecated and replaced with a templating facility that knows how to access to the process environment.

[dimitri]

@samphilipd Sorry I though I had implemented that already and now realise I didn't. I'm not even sure I should be implementing it. The public schema owner being the PostgreSQL instance's superuser is a problem...

[jfxp]

Hi Dimitri,

Firstly, I want to thank you for PGloader because it really is excellent!

I'd like to ask for some clarification over your advice about altering schemas, (suggested above), because since we've upgraded to 3.4.1 we've hit the issue where SET search_path TO _my_desired_schema_ no longer works as expected,

I see PGloader now creates a new schema, (based on db_name from MySQL), so my question is whether it's safe to use ALTER schema "_db_name_" RENAME TO "_desired_schema_" when "_desired_schema_" contains other tables/views that we don't want to lose?

Currently we're using AFTER LOAD queries to COPY/DROP the imported table from "_db_name_" to "_desired_schema_", (which is a functional, if somewhat ugly, workaround), so would you recommend we continue with this approach or can we safely use the suggested ALTER schema solution?

Apologies that this is actually more of a PGSQL question, and we're running on extremely limited resources so safely testing it ourselves would be a challenge, but hopefully this question, (and your advice), will also help guide others in future who may have the same issue/question.

All the best,
JFXP

[dimitri]

The ALTER SCHEMA clause in the pgloader command file only impacts the pgloader internal catalog, and doesn't introduce new commands. Tables are referenced with a schema-qualified name in the SQL commands sent to PostgreSQL, and this includes the new schema name when using ALTER SCHEMA or ALTER TABLE ... SET SCHEMA.

So you need to pay attention to your WITH clause options, that's what is taken into account for pgloader to decide if it should issue DROP commands to your PostgreSQL target. The best option is that you can try this with a small reproducible test-case.

[rossj-cargotel]

On 3.4.1 and related to the creation of a schema inside of the newly created database with the same name, comments on mysql tables fail to apply to postgres because pgloader is trying to add the comment to the table without prefacing it with the schema.

Here's a sample from :
pgloader -v -d mysql://root:r00t@localhost/twnfest_rotrydb pgsql://drupal@localhost/twnfest_rotrydb

This succeeds:
2017-11-15T12:05:45.762000-07:00 NOTICE ALTER TABLE twnfest_rotrydb.actions ADD PRIMARY KEY USING INDEX idx_18461839_primary;

This fails while trying to add the comment to the same table:
2017-11-15T12:05:45.771000-07:00 NOTICE comment on table actions is $LRGRF_PQCTL$Stores action information.$LRGRF_PQCTL$ 2017-11-15T12:05:45.772000-07:00 ERROR PostgreSQL Database error 42P01: relation "actions" does not exist QUERY: comment on table actions is $LRGRF_PQCTL$Stores action information.$LRGRF_PQCTL$

I was somewhat surprised by this creation of a schema with the same name inside the newly created database--I saw nothing in the on-line man page that suggests that this is the default behavior.

[dimitri]

This was fixed in 1f242cd29e9bcf6816583fae7647a90b3ba66858 ; can you try again from fresh sources?

About the documentation, the all in one file approach is showing its limits, and I'm now thinking to hook in with readthedocs.io and make a proper multi-pages manual. I didn't start on that yet.

[rossj-cargotel]

Thanks!

I opened a new issue--latest code won't compile for me.

[benlieb]

I was able to change the schema with:

load database
   from mysql://user@host/dbname
   into pgsql://user@host/dbname
   alter schema 'dbname' rename to 'public';