search

dimitri / pgloader

Migrate to PostgreSQL in a single command!
http://pgloader.io
Other
5.3k stars 537 forks source link

Control stack guard page temporarily disabled: proceed with caution #995

Open ThomasEnzingerWH opened 5 years ago

ThomasEnzingerWH commented 5 years ago

I knowing that was an Issue a time ago, but for me I can not fix it, including compiling last source code from git. The size of the sqlite file is roughly 100GB and one table holds nearly 90% of data. Edit: Postgresql Version 12

Need help to find a solution.

My last steps are

Get a copy of git

git clone https://github.com/dimitri/pgloader.git

Build it at docker

docker build -t local_pgloader_build .

Run it

docker run --rm --name pgloader --security-opt seccomp=unconfined --network host local_pgloader_build pgloader --debug /path/to/Sqlite.db postgres://... > log

Log-File

pgloader version 3.6.2~devel compiled with SBCL 1.3.14.debian sb-impl::default-external-format :ANSI_X3.4-1968 tmpdir: #P"/tmp/pgloader/" 2019-07-03T10:13:48.024000Z NOTICE Starting pgloader, log system is ready. 2019-07-03T10:13:48.033000Z INFO Starting monitor 2019-07-03T10:13:48.037000Z LOG pgloader version "3.6.2-devel" 2019-07-03T10:13:48.037000Z INFO SOURCE: "/external/Rohdaten.db" 2019-07-03T10:13:48.037000Z INFO SOURCE URI: #<PGLOADER.SOURCE.SQLITE:SQLITE-CONNECTION sqlite:///external/xyz.db {1006452BE3}> 2019-07-03T10:13:48.037000Z INFO TARGET: "postgres://skiped_from_reading/test" 2019-07-03T10:13:48.037000Z INFO TARGET URI: #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://..../test {100669B983}> 2019-07-03T10:13:48.037000Z DEBUG LOAD DATA FROM #<PGLOADER.SOURCE.SQLITE:SQLITE-CONNECTION sqlite:///external/xyz.db {1006452BE3}> 2019-07-03T10:13:48.111000Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://..../test {100669B983}> 2019-07-03T10:13:48.111000Z DEBUG SET client_encoding TO 'utf8' 2019-07-03T10:13:48.112000Z DEBUG SET application_name TO 'pgloader' 2019-07-03T10:13:48.126000Z LOG Migrating from #<SQLITE-CONNECTION sqlite:///external/xyz.db {1006452BE3}> 2019-07-03T10:13:48.126000Z LOG Migrating into #<PGSQL-CONNECTION pgsql://..../test {100669B983}> 2019-07-03T10:13:48.142000Z DEBUG CONNECTED TO /external/xyz.db 2019-07-03T10:13:48.142000Z SQL SQLite: SELECT tbl_name FROM sqlite_master WHERE tbl_name = 'sqlite_sequence' 2019-07-03T10:13:48.155000Z SQL SELECT tbl_name FROM sqlite_master WHERE type='table' AND tbl_name <> 'sqlite_sequence' 2019-07-03T10:13:48.163000Z NOTICE SQLite column a.ID uses a sequence 2019-07-03T10:13:48.164000Z NOTICE SQLite column b.ID uses a sequence 2019-07-03T10:13:48.166000Z NOTICE SQLite column c.ID uses a sequence 2019-07-03T10:13:48.166000Z NOTICE SQLite column d.ID uses a sequence 2019-07-03T10:13:48.167000Z NOTICE SQLite column e.Id uses a sequence 2019-07-03T10:13:48.167000Z NOTICE SQLite column f.Id uses a sequence 2019-07-03T10:13:48.167000Z NOTICE SQLite column g.Id uses a sequence 2019-07-03T10:13:48.168000Z NOTICE SQLite column h.Id uses a sequence 2019-07-03T10:13:48.168000Z NOTICE SQLite column i.ID uses a sequence 2019-07-03T10:13:48.210000Z INFO Processing source catalogs 2019-07-03T10:13:48.221000Z NOTICE Prepare PostgreSQL database. 2019-07-03T10:13:48.227000Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://..../test {100669B983}> 2019-07-03T10:13:48.227000Z DEBUG SET client_encoding TO 'utf8' 2019-07-03T10:13:48.228000Z DEBUG SET application_name TO 'pgloader' 2019-07-03T10:13:48.233000Z DEBUG BEGIN 2019-07-03T10:13:48.579000Z INFO Stopping monitor

ThomasEnzingerWH commented 5 years ago

Test with PostgreSQL 11 works fine. So, it's may be an issue with PostgreSQL 12.

ThomasEnzingerWH commented 5 years ago

With PostgreSQL 11 a new issue. Heap exhausted.

2019-07-03T11:10:09.192000Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://..../test2 {10084769D3}> 2019-07-03T11:10:09.192000Z DEBUG SET client_encoding TO 'utf8' 2019-07-03T11:10:09.241000Z DEBUG SET application_name TO 'pgloader' 2019-07-03T11:10:09.243000Z NOTICE CREATE UNIQUE INDEX idx_16585_sqlb_temp_table_1_pkey ON sqlb_temp_table_1 (ID); Heap exhausted during garbage collection: 0 bytes available, 48 requested. Gen StaPg UbSta LaSta LUbSt Boxed Unboxed LB LUB !move Alloc Waste Trig WP GCs Mem-age 0: 0 0 0 0 0 0 0 0 0 0 0 10737418 0 0 0.0000 1: 0 0 0 0 0 0 0 0 0 0 0 10737418 0 0 0.0000 2: 9776 15925 0 0 1002 5624 0 0 10 216648032 472736 133247690 0 1 1.3839 3: 17217 32767 0 0 2304 13198 0 0 23 507481504 488032 10737418 196 0 1.0231 4: 15259 22777 0 0 1665 5663 200 0 85 245706832 970672 2000000 552 0 0.2782 5: 0 0 0 0 0 0 0 0 0 0 0 2000000 0 0 0.0000 6: 0 0 0 0 1890 1222 0 0 0 101974016 0 2000000 1798 0 0.0000 Total bytes allocated = 1071810384 Dynamic-space-size bytes = 1073741824 GC control variables: GC-INHIBIT = true GC-PENDING = true STOP-FOR-GC-PENDING = false fatal error encountered in SBCL pid 1(tid 140737292465920): Heap exhausted, game over. Welcome to LDB, a low-level debugger for the Lisp runtime environment. ldb> Error opening /dev/tty: No such device or address

paveldvorak5 commented 5 years ago

got the same error, so I tried master version it run for a while then crash

 2019-07-10T22:35:20.299000Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://devel@windows:5432/global {1016396213}>
2019-07-10T22:35:20.299000Z DEBUG SET client_encoding TO 'utf8'
2019-07-10T22:35:20.300000Z DEBUG SET application_name TO 'pgloader'
2019-07-10T22:35:20.302000Z NOTICE CREATE INDEX idx_8544858_sort_order ON global.table_column_group (sort_order);
2019-07-10T22:35:20.396000Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://devel@windows:5432/global {101638F673}>
2019-07-10T22:35:20.396000Z DEBUG SET client_encoding TO 'utf8'
2019-07-10T22:35:20.397000Z DEBUG SET application_name TO 'pgloader'
2019-07-10T22:35:20.402000Z NOTICE CREATE UNIQUE INDEX idx_8544858_primary ON global.table_column_group (id);
2019-07-10T22:35:20.439000Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://devel@windows:5432/global {101633F213}>
2019-07-10T22:35:20.440000Z DEBUG SET client_encoding TO 'utf8'
2019-07-10T22:35:20.442000Z DEBUG SET application_name TO 'pgloader'
2019-07-10T22:35:20.445000Z SQL SET search_path TO global;
2019-07-10T22:35:20.446000Z INFO pgsql:copy-rows-from-queue[2]: global.table_column_history (date_added id
                                                            entity_id
                                                            entity_data
                                                            backtrace user_id)
2019-07-10T22:35:20.490000Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://devel@windows:5432/global {1015A0F823}>
2019-07-10T22:35:20.490000Z DEBUG SET client_encoding TO 'utf8'
2019-07-10T22:35:20.491000Z DEBUG SET application_name TO 'pgloader'
2019-07-10T22:35:20.493000Z NOTICE CREATE INDEX idx_8544858_code ON global.table_column_group (code);
Heap exhausted during garbage collection: 7664 bytes available, 10208 requested.
Gen  Boxed Unboxed   LgBox LgUnbox  Pin       Alloc     Waste        Trig      WP GCs Mem-age
 0    7292    6011       0       0    5   374122304  61790400   202248168   13303   1  0,0000
 1     114   28681       0       0    8   794002928 149551632    42949672   28795   0  1,0879
 2     358   67902       0       0   61  2075873664 160870016   116773352   68260   1  0,7186
 3    1055    3789    1500   11568 1055   581754064   5186352   624703736   17912   1  0,0000
 4       0       0       0       0    0           0         0     2000000       0   0  0,0000
 5       0       0       0       0    0           0         0     2000000       0   0  0,0000
 6    1330    1094     202     176    0    88695488   3120448     2000000    2802   0  0,0000
 7       0       0       0       0    0           0         0     2000000       0   0  0,0000
           Total bytes allocated    =    3914448448
           Dynamic-space-size bytes =    4294967296
GC control variables:
   *GC-INHIBIT* = true
   *GC-PENDING* = true
   *STOP-FOR-GC-PENDING* = false
fatal error encountered in SBCL pid 18056(tid 0x7f3ace96f700):
Heap exhausted, game over.
Welcome to LDB, a low-level debugger for the Lisp runtime environment.
ldb> trace   
unknown command: ``trace''
ldb>  
ldb> help
help    Display this help information.
?   (an alias for help)
backtrace   Backtrace up to N frames.
catchers    Print a list of all the active catchers.
context Print interrupt context number I.
dump    Dump memory starting at ADDRESS for COUNT words.
d   (an alias for dump)
exit    Exit this instance of the monitor.
flush   Flush all temp variables.
grab-signals    Set the signal handlers to call LDB.
kill    Kill ourself with signal number N (useful if running under gdb)
purify  Purify. (Caveat purifier!)
print   Print object at ADDRESS.
p   (an alias for print)
pte Page table entry for address
quit    Quit.
regs    Display current Lisp registers.
ldb> backtrace
Backtrace:
   0: SB-VM::ALLOCATE-VECTOR-WITH-WIDETAG, pc = 0x52858bce, fp = 0x7f3ace96dd20
   1: SB-KERNEL::%MAKE-ARRAY, (LAMBDA (SB-VM::A SB-VM::B) :IN SB-KERNEL::%MAKE-ARRAY), (LAMBDA (SB-VM::VALUE) :IN SB-VM::%SAVE-DISPLACED-ARRAY-BACKPOINTER), pc = 0x527853ac, fp = 0x7f3ace96ddd8
   2: QMYND-IMPL::READ-WHOLE-CHUNK, pc = 0x525faf7b, fp = 0x7f3ace96de08
   3: QMYND-IMPL::PREPARE-NEXT-CHUNK, pc = 0x52205889, fp = 0x7f3ace96de48
   4: (SB-PCL::FAST-METHOD QMYND-IMPL::MYSQL-CONNECTION-READ-PACKET (QMYND::MYSQL-BASE-CONNECTION)), pc = 0x52372234, fp = 0x7f3ace96de70
   5: QMYND-IMPL::PARSE-RESULTSET-ROW-AS-TEXT-VECTOR, pc = 0x523734eb, fp = 0x7f3ace96dec0
   6: QMYND-IMPL::MAP-RESULTSET-ROWS, pc = 0x5260530d, fp = 0x7f3ace96df30
   7: QMYND-IMPL::SEND-COMMAND-QUERY, pc = 0x52374aef, fp = 0x7f3ace96e000
   8: QMYND::MYSQL-QUERY, pc = 0x52609a34, fp = 0x7f3ace96e080
   9: PGLOADER.SOURCE.MYSQL::CALL-WITH-ENCODING-HANDLER, (FLET "H2" :IN PGLOADER.SOURCE.MYSQL::CALL-WITH-ENCODING-HANDLER), (FLET "H1" :IN PGLOADER.SOURCE.MYSQL::CALL-WITH-ENCODING-HANDLER), (FLET "H0" :IN PGLOADER.SOURCE.MYSQL::CALL-WITH-ENCODING-HANDLER), pc = 0x52750010, fp = 0x7f3ace96e1b8
  10: (SB-PCL::FAST-METHOD PGLOADER.SOURCES::MAP-ROWS (PGLOADER.SOURCE.MYSQL::COPY-MYSQL)), (FLET "H0" :IN PGLOADER.SOURCES::MAP-ROWS), (FLET "H0" :IN PGLOADER.SOURCES::MAP-ROWS), (LAMBDA () :IN PGLOADER.SOURCES::MAP-ROWS), (LAMBDA () :IN PGLOADER.SOURCES::MAP-ROWS), pc = 0x522c5085, fp = 0x7f3ace96e2c0
  11: (SB-PCL::FAST-METHOD PGLOADER.LOAD::QUEUE-RAW-DATA (PGLOADER.SOURCES::COPY T T)), (LAMBDA (PGLOADER.LOAD::ROW) :IN PGLOADER.LOAD::QUEUE-RAW-DATA), (LAMBDA (PGLOADER.LOAD::ROW) :IN PGLOADER.LOAD::QUEUE-RAW-DATA), pc = 0x52754aca, fp = 0x7f3ace96e318
  12: LPARALLEL.KERNEL::MAKE-CHANNELED-TASK, (LAMBDA () :IN LPARALLEL.KERNEL::MAKE-CHANNELED-TASK), (FLET "BODY-FN0" :IN LPARALLEL.KERNEL::MAKE-CHANNELED-TASK), pc = 0x5263efa2, fp = 0x7f3ace96e3b8
  13: LPARALLEL.KERNEL::MAKE-CHANNELED-TASK, (LAMBDA () :IN LPARALLEL.KERNEL::MAKE-CHANNELED-TASK), (FLET "BODY-FN0" :IN LPARALLEL.KERNEL::MAKE-CHANNELED-TASK), pc = 0x5263ee5e, fp = 0x7f3ace96e458
  14: LPARALLEL.KERNEL::EXEC-TASK/WORKER, pc = 0x52387c25, fp = 0x7f3ace96e4b8
  15: LPARALLEL.KERNEL::WORKER-LOOP, pc = 0x5263e106, fp = 0x7f3ace96e520
  16: LPARALLEL.KERNEL::%CALL-WITH-TASK-HANDLER, (LAMBDA (COND) :IN "/home/devel/Downloads/pgloader-master/build/quicklisp/dists/quicklisp/software/lparallel-20160825-git/src/kernel/handling.lisp"), pc = 0x5228bee4, fp = 0x7f3ace96e5b0
  17: LPARALLEL.KERNEL::CALL-WITH-WORKER-CONTEXT, (LAMBDA () :IN LPARALLEL.KERNEL::CALL-WITH-WORKER-CONTEXT), (LAMBDA (COND) :IN "/home/devel/Downloads/pgloader-master/build/quicklisp/dists/quicklisp/software/lparallel-20160825-git/src/kernel/core.lisp"), pc = 0x5238847f, fp = 0x7f3ace96e6a8
  18: LPARALLEL.KERNEL::CALL-WITH-WORKER-CONTEXT, (LAMBDA () :IN LPARALLEL.KERNEL::CALL-WITH-WORKER-CONTEXT), (LAMBDA (COND) :IN "/home/devel/Downloads/pgloader-master/build/quicklisp/dists/quicklisp/software/lparallel-20160825-git/src/kernel/core.lisp"), pc = 0x523880fc, fp = 0x7f3ace96e738
  19: LPARALLEL.KERNEL::MAKE-WORKER-THREAD, (LAMBDA () :IN LPARALLEL.KERNEL::MAKE-WORKER-THREAD), pc = 0x5263e7a0, fp = 0x7f3ace96e7a8
  20: BORDEAUX-THREADS::BINDING-DEFAULT-SPECIALS, (LAMBDA () :IN BORDEAUX-THREADS::BINDING-DEFAULT-SPECIALS), pc = 0x52490ffe, fp = 0x7f3ace96e820
  21: SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE, (FLET SB-THREAD::WITH-MUTEX-THUNK :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), (LAMBDA (COND) :IN "SYS:SRC;CODE;TARGET-THREAD.LISP"), (LAMBDA (STREAM) :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), (LAMBDA () :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), (FLET SB-UNIX::BODY :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), (FLET SB-THREAD::WITH-SYSTEM-MUTEX-THUNK :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), (FLET SB-THREAD::WITH-SYSTEM-MUTEX-THUNK :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), pc = 0x5293f007, fp = 0x7f3ace96e930
  22: SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE, (FLET SB-THREAD::WITH-MUTEX-THUNK :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), (LAMBDA (COND) :IN "SYS:SRC;CODE;TARGET-THREAD.LISP"), (LAMBDA (STREAM) :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), (LAMBDA () :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), (FLET SB-UNIX::BODY :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), (FLET SB-THREAD::WITH-SYSTEM-MUTEX-THUNK :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), (FLET SB-THREAD::WITH-SYSTEM-MUTEX-THUNK :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), pc = 0x5293f4cf, fp = 0x7f3ace96ea70
  23: SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE, (FLET SB-THREAD::WITH-MUTEX-THUNK :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), (LAMBDA (COND) :IN "SYS:SRC;CODE;TARGET-THREAD.LISP"), (LAMBDA (STREAM) :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), (LAMBDA () :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), (FLET SB-UNIX::BODY :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), (FLET SB-THREAD::WITH-SYSTEM-MUTEX-THUNK :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), (FLET SB-THREAD::WITH-SYSTEM-MUTEX-THUNK :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), pc = 0x5293eb07, fp = 0x7f3ace96ebe0
  24: SB-THREAD::CALL-WITH-MUTEX, (FLET SB-UNIX::BODY :IN SB-THREAD::CALL-WITH-MUTEX), pc = 0x5279c6fd, fp = 0x7f3ace96eca8
  25: SB-THREAD::CALL-WITH-MUTEX, (FLET SB-UNIX::BODY :IN SB-THREAD::CALL-WITH-MUTEX), pc = 0x5279c3a8, fp = 0x7f3ace96ed50
  26: SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE, (FLET SB-THREAD::WITH-MUTEX-THUNK :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), (LAMBDA (COND) :IN "SYS:SRC;CODE;TARGET-THREAD.LISP"), (LAMBDA (STREAM) :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), (LAMBDA () :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), (FLET SB-UNIX::BODY :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), (FLET SB-THREAD::WITH-SYSTEM-MUTEX-THUNK :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), (FLET SB-THREAD::WITH-SYSTEM-MUTEX-THUNK :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE), pc = 0x5293e584, fp = 0x7f3ace96ee98
  27: Foreign function call_into_lisp, pc = 0x43ac5f, fp = 0x7f3ace96eed0
  28: Foreign function new_thread_trampoline, pc = 0x424067, fp = 0x7f3ace96eef0
paveldvorak5 commented 5 years ago

regs CSP = 0x7f3ace96dd20
ALLOC = 0x1100000000

dimitri commented 5 years ago

@paveldvorak5 did you try changing the batch size parameters? In particular lowering prefetch rows is a good idea here, as per documentation at https://pgloader.readthedocs.io/en/latest/pgloader.html#with.

paveldvorak5 commented 5 years ago

Hi, i used command line so I need to write a script first. Before I start, can you transform a polygon? Thanks