about 701 tables, the defaults should be good enough to let you get started, then you might want to review the parallelism and concurrency settings. Also run in VERBOSE mode so that you can follow along what's happening when trying it out.
please review https://github.com/dimitri/pgloader/issues?utf8=✓&q=label%3A%22Windows+support%22+ ; some of them have extended install steps
well version 3.5.2 is a little old now, so please try 3.6.1 which contains specific improvements for MS SQL support ; also review your FreeTDS configuration, which is often the problem at hand (in particular, the protocol version setup in there is important) (see http://pgloader.org for a quick intro on the topic)
@rvanrooy please hack https://github.com/dimitri/pgloader/blob/master/src/monkey/mssql.lisp#L98 and add
:syb-msdatetime2 in the list there, there's a slight chance it will just work; please then report back
if that's all it takes, you could then open a PR that I'd be happy to accept
@dimitri the process definitely gets further along, it correctly scans the target rows, but gives an error status: "invalid input syntax for type timestamp with time zone: "Aug 7 2014 12:00:00:0000000" I found a github issue #431 related to msdatetime4 and and will try to add those fixes in src/sources/mssql/mssql-schema.lisp
Ok, that seemed to work, dates looks correct at a quick glance, I'll do some more checks.
Is there a way to limit the amount of rows copied over and do incremental batches? I'm trying to copy over a huge (several hundred million rows) table, but from the debug logs, it seems that all rows are pre-fetched even though I have specified the batch rows to be fairly small. Is there a best practice to move over a large database?
see https://pgloader.readthedocs.io/en/latest/pgloader.html#with and the parameters batch rows, batch size, and prefetch rows; maybe the last one specifically here
oh and yeah the
convert()call to be used in the SELECT statement needs some attention too, in mssql-schema.lisp, good point! I guess with that you have a PR ready for improving pgloader?
@dimitri: Do we have a solution for https://pgdgbuild.dus.dg-i.net/view/Binaries/job/pgloader-binaries/lastFailedBuild/architecture=amd64,distribution=stretch/console that doesn't involve shipping sbcl?
Otherwise, yeah, we need a newer sbcl would be my default attempt
Upgrading sbcl doesn't help, with sbcl 1.4.16 on stretch, I still get The symbol "SYSTEM-DEFINITION-SEARCH-FUNCTIONS" is not external in the ASDF/FIND-SYSTEM package. http://paste.debian.net/1068003/
@dimitri: I'll stop here. Let me know what I can do to help
pgload file contents:
load database
from mssql://SA@172.17.0.2/ServerAutomation
into postgresql://postgres:'P@@ssword'@localhost/serverautomation
before load do $$ drop schema if exists dbo cascade; $$;pgloader command: pgloader myfile --verbose --debug
Can pgloader convert SQL Server .sql files to PostgreSQL sql files?
like table definitions etc
or is it only for running databases
2019-06-20T13:37:08.008000-07:00 LOG pgloader version "3.6.1"
KABOOM!
FATAL error: At
-- ********************
^ (Line 1, Column 0, Position 0)
In context KW-LOAD:
While parsing KW-LOAD. 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 "load"
clearly
-- is one of the expected strings
yet it explodes
I'm loading from csv file and importing to postgresql, how can I define default boolean value for column in target table?
will this work:
meta_type,state,is_imported boolean using TRUEthis is on target columns
You're using stable-slim, not a pinned version. However, I tried stretch-slim and jessie-slim and it fails for different reasons.