Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

How to import into public schema #645

Closed
ShurikAg opened this issue Oct 12, 2017 · 12 comments
Closed

How to import into public schema #645

ShurikAg opened this issue Oct 12, 2017 · 12 comments

Comments

@ShurikAg
Copy link

@ShurikAg ShurikAg commented Oct 12, 2017

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

This comment has been minimized.

Copy link
Owner

@dimitri dimitri commented Oct 13, 2017

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

This comment has been minimized.

Copy link
Author

@ShurikAg ShurikAg commented Oct 14, 2017

Thanks for the advice.

@samsondav

This comment has been minimized.

Copy link

@samsondav samsondav commented Oct 17, 2017

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

This comment has been minimized.

Copy link

@snusnu snusnu commented Oct 18, 2017

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

This comment has been minimized.

Copy link
Owner

@dimitri dimitri commented Oct 19, 2017

@snusnu please see #555 ; GETENV is now deprecated and replaced with a templating facility that knows how to access to the process environment.

@dimitri

This comment has been minimized.

Copy link
Owner

@dimitri dimitri commented Oct 19, 2017

@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

This comment has been minimized.

Copy link

@jfxp jfxp commented Oct 31, 2017

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

This comment has been minimized.

Copy link
Owner

@dimitri dimitri commented Oct 31, 2017

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

This comment has been minimized.

Copy link

@rossj-cargotel rossj-cargotel commented Nov 15, 2017

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 comment has been minimized.

Copy link
Owner

@dimitri dimitri commented Nov 15, 2017

This was fixed in 1f242cd ; 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

This comment has been minimized.

Copy link

@rossj-cargotel rossj-cargotel commented Nov 15, 2017

Thanks!

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

@benlieb

This comment has been minimized.

Copy link

@benlieb benlieb commented Jan 4, 2019

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';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
7 participants
You can’t perform that action at this time.