Where communities thrive

  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
Repo info
    Benjamin David
    It seems that the primary keys are not migrated
    hence causing the issues
    or the Unique constraint of the primary key is not passed
    is there any wya to force that?
    I looked over the whole doc and cant find it
    Benjamin David
    here's my empty DB
    just migrating the schema fails
    whereas its pretty straightforward
    Hi I am trying to load data from MS SQL server to Postgres, both password have special characters, for postgres I am using PGPASSWORD environment variable , what is the environment variable for MSSQL server password it tried PWD, PASSWORD it does not work
    Manoj Jadhav

    HI All,

    I am facing this issue?

    31: ("foreign function: call_into_lisp")
    can any one guide me here?
    ariel yosef
    hello everyone
    so i'v tried use pgloader and i have problem that mysql wont let me pass the password Visible
    any suggestion on how to pass the password without be Visible?
    is there a way to load mysql dump to postgresql with pgloader?
    Wagner Caixeta
    Hi, I have a messed table, some rows were inserted using latin1 and others were windows-1252. How can i apply my pgsql function for each varchar/text columns on import?
    CREATE OR REPLACE FUNCTION fix_text_encoding(text) returns text AS 
      RETURN convert_from(convert_to($1, 'latin1'), 'utf8');
        WHEN sqlstate '22P05' THEN
      RETURN convert_from(convert_to($1, 'windows-1252'), 'utf8');
    language plpgsql immutable;
    Is possible to use this fix_text_encoding? Or do I need to convert it to LISP? I know nothing about LISP =)
    Hey, when trying to execute pgloader to migrate a mariadb to posgres I receive following error: "ERROR Database error 42703: column "adsrc" does not exist" I'm using pgloader v3.6.1 - PostgreSQL 13.2
    Tatzlwurm Eads
    Hi, I have lots of columns that are like this "is_nonconcurrent character varying(1),", they are boolean but can't seem to get pgloader syntax right. I can workaround by altering columns post migration like : alter table acq.qrtz2_job_details alter is_durable type boolean using is_durable::boolean;
    Tatzlwurm Eads
    Sorry trying to migrate mysql -> postgresql.
    Tatzlwurm Eads
    nm figured out:CAST type varchar when (= precision 1) to boolean drop typemod
    I was trying to build pgloader-3.6.2 on RHEL 7 and got following errors

    mkdir -p build
    curl -o build/quicklisp.lisp http://beta.quicklisp.org/quicklisp.lisp
    % Total % Received % Xferd Average Speed Time Time Time Current
    Dload Upload Total Spent Left Speed
    100 3628 100 3628 0 0 521k 0 --:--:-- --:--:-- --:--:-- 708k
    sbcl --noinform --no-sysinit --no-userinit --load build/quicklisp.lisp \
    --load src/getenv.lisp \
    --eval '(quicklisp-quickstart:install :path "build/quicklisp" :proxy (getenv "http_proxy"))' \
    --eval '(quit)'
    While evaluating the form starting at line 1, column 0
    of #P"/var/opt/mwang/pgloader/pgloader-3.6.2/build/quicklisp.lisp":

    debugger invoked on a UNBOUND-VARIABLE in thread

    <THREAD "main thread" RUNNING {9461061}>:

    The variable <!DOCTYPE is unbound.

    Type HELP for debugger help, or (SB-EXT:EXIT) to exit from SBCL.

    restarts (invokable by number or by possibly-abbreviated name):
    0: [RETRY ] Retry EVAL of current toplevel form.
    1: [CONTINUE] Ignore error and continue loading file "/var/opt/mwang/pgloader/pgloader-3.6.2/build/quicklisp.lisp".
    2: [ABORT ] Abort loading file "/var/opt/mwang/pgloader/pgloader-3.6.2/build/quicklisp.lisp".
    3: Ignore runtime option --load "build/quicklisp.lisp".
    4: Skip rest of --eval and --load options.
    5: Skip to toplevel READ/EVAL/PRINT loop.
    6: [EXIT ] Exit SBCL (calling #'EXIT, killing the process).


    What could be wrong?

    Hi! I'm trying to migrate from one PostgreSQL DB to another with simple script:

    LOAD database
    FROM postgresql://user:password@psql/db1
    INTO postgresql://user:password@psql/db2
    CAST type mchar to char,
    type mvarchar to text drop typemod
    ALTER SCHEMA 'public' RENAME TO 'db1'

    Error dropped:

    FATAL error: The value
    is not of type
    An unhandled error condition has been signalled:
    The value
    is not of type

    What I am doing here?

    The value
    is not of type

    Linux OpenSuse pgloader 3.6.2
    PostgreSQL 10.6
    6322 tables were created as I see.

    Sicheng Hao
    Does anyone know how can I use pgloader to load a list of tables? I was thinking of writing a loop but not sure how to do that with pgloader. Thanks!
    Aaron William West
    ..can I use pgloader to load a list of tables? ..
    my last config file for pgloader has a list of statements like this:
    not finding that in the documentation, can anyone tell me if an after load EXECUTE sql script can contain SQL block such as DO begin...end; begin ... end;? or only straight SQL only?
    the script script containing the SQL block works by itself at the psql command line.
    Hi! is it possible to just create migration script with schema only without applying it to destination database?
    Naftoli Gugenheim
    I'm also getting these incomprehensible parsing errors
    Carlos Ramos
    hi, quick question, is it possible to tell pgloader to write the sql to a file instead of running it on the destination server ?
    Naftoli Gugenheim
    Yeah I would want that too
    Vincent Taverna

    I recently started using pgloader to transition a MySQL DB to PostgreSQL. Everything is working well, but I do see a single unique index error. I have an AFTER LOAD step that alters the database and adds a proper unique index. Is it possible to skip this index to avoid the error without modifying the MySQL database? I'd love to be able to tell pgloader to just not try to create that one index.

    ERROR PostgreSQL Database error 23505: could not create unique index "idx_106456_my_column"
    DETAIL: Key ("myColumn")=(48301) is duplicated.

    It seems I am not the first person to ask this question either, a couple of people on stackoverflow asked it but never received a reply. Curious if anyone here has tackled this before.

    Hello Folks,
    I need an assistance to rollback the data if the total no of rejected row count exceeds 50
    Could you please advise on the above
    Hello Folks,
    I am loading the CSV data to postgres database, need an assistance to rollback the data if the total no of rejected row count exceeds 50
    Could you please advise on this
    Hello everyone,
    thank you for this useful tool!
    Do you have a tip for me how to configure the correct batch / prefetch rows to avoid such errors when importing a drone sqlite db into a cockroach cluster?
    2022-04-24T21:28:55.911543Z ERROR Database error 40001: restart transaction: TransactionRetryWithProtoRefreshError: cannot publish new versions for descriptors: [{repos 282 4}], old versions still in use
    HINT: See: https://www.cockroachlabs.com/docs/v21.2/transaction-retry-error-reference.html
    > Debug: Database error 40001: restart transaction: TransactionRetryWithProtoRefreshError: cannot publish new versions for descriptors: [{repos 282 4}], old versions still in use
    >        HINT: See: https://www.cockroachlabs.com/docs/v21.2/transaction-retry-error-reference.html
    >        QUERY: COMMIT
    > While executing: LPARALLEL.KERNEL::CALL-WITH-TRACKED-ERROR, in process lparallel(10).
    ;;; #<PROCESS lparallel(10) [Active] #x302001CE7CFD> requires access to Shared Terminal Input
    ;;; Type (:y 10) to yield control to this thread.
    Damien Jiang

    Hello! pgloader seems like the easiest way to migrate MySQL to Postgres, so thanks for creating it! :)

    I'm running into a problem: I have a MariaDB column obj.type char(1) not null default 'X', but when I run pgloader, it complains that there's a syntax error on the column

      type     char(1) not null default ''X'',

    I found a previous issue dimitri/pgloader#533 and stackoverflow question https://stackoverflow.com/questions/54616661/pgloader-quote-handling-problems-when-create-table-for-default-values, but they don't really help (the stackoverflow question doesn't really have an answer.)

    I don't think the table was originally created by triple-quoting the default value, as in the github issue, though I guess I have no way to know anymore... SHOW CREATE TABLE and select * from information_schema.columns both indicate the default value is 'X' though.

    Worst-case I'll just drop the default value and add it back later, but is there a better workaround than migrating my original database first?

    hmm, reading the past chats, I'm not sure if @dimitri uses this site anymore :(
    I'll just bump the github issue
    pgloader can connect with remote mysql ex. google gcp? I have problem with my path to mysql, syntax error. Any idea?
     FROM     mysql://dupa:pass@12.345.678.910:3306/mydb?useSSL=true&clientCertificateKeyStoreUrl=file:/home/xxx/keys/clientkeystore.jks&clientCertificateKeyStorePassword=mypass&trustCertificateKeyStoreUrl=file:/home/xxx/keys/serverTrust.jks&trustCertificateKeyStorePassword=mypass
     INTO     pgsql://new:pass@
     WITH include drop, create tables
    ALTER SCHEMA 'mydb' RENAME TO 'public';
    Cubixusin: In don’t know if TLS options are implemented in pgloader, also couldn’t get custom certs to work with a postgres target connection string. If you can, you could try an SSH tunnel with ssh -L 3306: some-bastion-host-in-private-network-with-db and use the localhost port to connect unencrypted to the mysql db
    Is there some way to make sure the process hasn't hung? I have the 3.6.2 RPM package for CentOS7 running on Amazon Linux and it was very chatty early on but hasn't produced log output in about 45 minutes. I had a lot of trouble getting it to run at all, and a lot of false starts due to heap issues so I want to make sure it's still working and didn't break again.
    Hi, what is the easiest way to map a table from mysql to postgres?
    I did it like this:
    But know i getting a random error, i did not change anything and its not working anymore

    Is pgloader the right tool to import a file to PostgreSQL, multiples times a day?

    My use case is to import a file, update the old rows and delete rows that are not in the file anymore.

    Currently, I'm using a temp table, but I'm wondering if it's efficient.