Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
    duconja
    @duconja
    is there a way to load mysql dump to postgresql with pgloader?
    Wagner Caixeta
    @platbr
    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 
    $$
    BEGIN
      RETURN convert_from(convert_to($1, 'latin1'), 'utf8');
    EXCEPTION
        WHEN sqlstate '22P05' THEN
      RETURN convert_from(convert_to($1, 'windows-1252'), 'utf8');
    END;
    $$
    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 =)
    Chr3is
    @Chr3is
    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
    @tatzlwurm2:matrix.org
    [m]
    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
    @tatzlwurm2:matrix.org
    [m]
    Sorry trying to migrate mysql -> postgresql.
    Tatzlwurm Eads
    @tatzlwurm2:matrix.org
    [m]
    nm figured out:CAST type varchar when (= precision 1) to boolean drop typemod
    pgloader
    @pgloader
    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).

    (SB-INT:SIMPLE-EVAL-IN-LEXENV <!DOCTYPE #<NULL-LEXENV>)
    0]

    What could be wrong?
    avnxxi
    @avnxxi

    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:

    {10066D8CB3}>
    KABOOM!
    FATAL error: The value
    NIL
    is not of type
    PGLOADER.CATALOG:COLUMN
    An unhandled error condition has been signalled:
    The value
    NIL
    is not of type
    PGLOADER.CATALOG:COLUMN

    What I am doing here?

    The value
    NIL
    is not of type
    PGLOADER.CATALOG:COLUMN

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

    Sicheng Hao
    @sichenghao_gitlab
    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
    @tallpeak
    ..can I use pgloader to load a list of tables? ..
    my last config file for pgloader has a list of statements like this:
    INCLUDING ONLY TABLE NAMES LIKE 'tbl_dma_regions' IN SCHEMA 'dbo'
    INCLUDING ONLY TABLE NAMES LIKE 'tbl_Plan_Contacts' IN SCHEMA 'dbo'
    ..
    hainantao
    @hainantao
    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.
    alewmt
    @alewmt
    Hi! is it possible to just create migration script with schema only without applying it to destination database?
    Naftoli Gugenheim
    @nafg
    I'm also getting these incomprehensible parsing errors
    Carlos Ramos
    @carragom
    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
    @nafg
    Yeah I would want that too
    Vincent Taverna
    @vinnymac

    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.

    VenkiSagaMicroService
    @VenkiSagaMicroService
    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
    VenkiSagaMicroService
    @VenkiSagaMicroService
    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
    teutat3s
    @teutat3s:pub.solar
    [m]
    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
    QUERY: COMMIT
    > 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
    @damien-swarm

    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
    Cubixusin
    @cubixusin:matrix.org
    [m]
    pgloader can connect with remote mysql ex. google gcp? I have problem with my path to mysql, syntax error. Any idea?
    LOAD DATABASE
     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@127.0.0.1:5432/newpsql
    
     WITH include drop, create tables
    ALTER SCHEMA 'mydb' RENAME TO 'public';
    teutat3s
    @teutat3s:pub.solar
    [m]
    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:127.0.0.1:3306 some-bastion-host-in-private-network-with-db and use the localhost port to connect unencrypted to the mysql db
    pandrews-hdai
    @pandrews-hdai
    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.
    Slawa
    @slawa_d_twitter
    image.png
    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
    image.png
    bbigras
    @bbigras:matrix.org
    [m]

    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.

    pandrews-hdai
    @pandrews-hdai
    I'm having a hell of a time getting a stable pgloader run in an AWS environment. The process keeps exiting after several hours unattended and it just says "Killed" at the end even though I pass --debug and --verbose, it seems like it's being pretty tight-lipped and not giving me anything to debug the failures with. Are there any issues with the parameters or something? I've built pgloader 3.6.2c52da1 using CCL v1.12.1 because I was getting heap errors constantly using the latest RPM package and was hoping CCL would help with that. I just had a run take several days to transfer a 500GB table and then just dump "Segmentation Fault" and exit a few hours into the next large table but I don't really know what to do with that. Is there anything I'm better off running for guaranteeing stability if it's going to take a week to do anything?
    pandrews-hdai
    @pandrews-hdai
    Or at least are there options I can use that skip clearing tables and recopying rows already loaded so I can pick up where the command left off and eventually migrate the entire source DB?
    pandrews-hdai
    @pandrews-hdai
    I've also tried pulling the latest master code from git and building that, still just dumping to the console without any actual error information after a few hours. --debug doesn't seem to add anything.
    Michael Coll
    @Gigamick
    Don't suppose anyone is around?