search

dimitri / pgloader

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

Fail to connect postgresql from remote #390

Closed fdsauioprweq closed 8 years ago

fdsauioprweq commented 8 years ago

this my command from command prompt >> 

pgloader.95961a4.exe --type csv --field "fish_id, fish_descr" --with "truncate" --with "fields terminated by '|'" C:\temp\pgloader\more_fish.txt postgresql://postgres:pssword@servername:5432/mydb?tablename=fish

and error return >>

C:\temp\pgloader>pgloader.95961a4.exe --type csv --field "fish_id, fish_descr" -
-with "truncate" --with "fields terminated by '|'" C:\temp\pgloader\more_fish.tx
t postgresql://postgres:pssword@servername:5432/mydb?tablename=fish
2016-04-21T09:20:57.075000Z LOG Main logs in 'C:/cygwin/tmp/pgloader/pgloader.lo
g'
2016-04-21T09:20:57.082000Z LOG Data errors in 'C:/cygwin/tmp/pgloader/'
An unhandled error condition has been signalled:
   Failed to connect to pgsql at "servername" (port 5432) as user "postgres": Post
gres warning: CommitTransaction

i test connect from psql from remote computer it success. what's wrong. thank you.

fdsauioprweq commented 8 years ago

my os on remote computer is windows 7 ent 64 bit

dimitri commented 8 years ago

I need more information to be able to help you. Please share PostgreSQL server logs about that connection attempt, and retry with --debug option.

fdsauioprweq commented 8 years ago

thank dimitri for your response

  1. detail in pg_hba.conf>>
    TYPE  DATABASE   USER    ADDRESS         METHOD
    host     all     all     10.0.0.0/8  trust
  1. detail in postgresql.conf>>
    listen_addresses = '*'
  1. postgresql log
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | DEBUG:  00000: postgres child[3720]: starting with (
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | LOCATION:  BackendRun, postmaster.c:4226
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | DEBUG:  00000:    postgres
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | LOCATION:  BackendRun, postmaster.c:4229
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | DEBUG:  00000: )
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | LOCATION:  BackendRun, postmaster.c:4231
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | DEBUG:  00000: InitPostgres
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | LOCATION:  InitPostgres, postinit.c:553
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | DEBUG:  00000: my backend ID is 7
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | LOCATION:  SharedInvalBackendInit, sinvaladt.c:324
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | DEBUG:  00000: StartTransaction
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | LOCATION:  ShowTransactionState, xact.c:4929
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | DEBUG:  00000: name: unnamed; blockState:       DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | LOCATION:  ShowTransactionStateRec, xact.c:4967
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | DEBUG:  00000: CommitTransaction
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | LOCATION:  ShowTransactionState, xact.c:4929
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | DEBUG:  00000: name: unnamed; blockState:       STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | LOCATION:  ShowTransactionStateRec, xact.c:4967
    2016-04-22 11:25:37 ICT | XX000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | LOG:  XX000: could not receive data from client: No connection could be made because the target machine actively refused it.

    2016-04-22 11:25:37 ICT | XX000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | LOCATION:  pq_recvbuf, pqcomm.c:898
    2016-04-22 11:25:37 ICT | 08003 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | DEBUG:  08003: unexpected EOF on client connection
    2016-04-22 11:25:37 ICT | 08003 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | LOCATION:  SocketBackend, postgres.c:363
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | DEBUG:  00000: shmem_exit(0): 1 before_shmem_exit callbacks to make
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | LOCATION:  shmem_exit, ipc.c:226
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | DEBUG:  00000: shmem_exit(0): 6 on_shmem_exit callbacks to make
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | LOCATION:  shmem_exit, ipc.c:259
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | DEBUG:  00000: proc_exit(0): 3 callbacks to make
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | LOCATION:  proc_exit_prepare, ipc.c:188
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | DEBUG:  00000: exit(0)
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | LOCATION:  proc_exit, ipc.c:141
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | DEBUG:  00000: shmem_exit(-1): 0 before_shmem_exit callbacks to make
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | LOCATION:  shmem_exit, ipc.c:226
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | DEBUG:  00000: shmem_exit(-1): 0 on_shmem_exit callbacks to make
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | LOCATION:  shmem_exit, ipc.c:259
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | DEBUG:  00000: proc_exit(-1): 0 callbacks to make
    2016-04-22 11:25:37 ICT | 00000 | [unknown] | postgres | mydb | 10.35.36.85(58521) | 10.35.36.85 | 3720 | LOCATION:  proc_exit_prepare, ipc.c:188
  1. windows firewall on postgresql server and remote computer is OFF
  2. debug detail
c:\temp\pgloader>pgloader.95961a4.exe --type csv --field "fish_id, fish_descr" --with "truncate" --with "fields terminat
ed by '|'" --debug C:\temp\pgloader\more_fish.txt postgresql://postgres:pssword@servername:5432/mydb?tablename=fish
sb-impl::*default-external-format* :CP874
tmpdir: #P"C:/cygwin/tmp/pgloader/"
2016-04-22T04:43:53.043000Z NOTICE Starting pgloader, log system is ready.
2016-04-22T04:43:53.076000Z INFO Starting monitor
2016-04-22T04:43:53.083000Z LOG Main logs in 'C:/cygwin/tmp/pgloader/pgloader.log'
2016-04-22T04:43:53.083000Z LOG Data errors in 'C:/cygwin/tmp/pgloader/'
2016-04-22T04:43:53.086000Z INFO SOURCE: #<PGLOADER.CSV:CSV-CONNECTION csv://FILENAME:C:/temp/pgloader/more_fish.txt {26
DCEE31}>
2016-04-22T04:43:53.087000Z INFO TARGET: #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://postgres@servername:5432/mydb {26E777F9
}>
2016-04-22T04:43:53.088000Z DEBUG LOAD DATA FROM #<PGLOADER.CSV:CSV-CONNECTION csv://FILENAME:C:/temp/pgloader/more_fish
.txt {26DCEE31}>
An unhandled error condition has been signalled:
   Failed to connect to pgsql at "servername" (port 5432) as user "postgres": Postgres warning: CommitTransaction

Date/time: 2016-04-22-11:43An unhandled error condition has been signalled:
                              Failed to connect to pgsql at "servername" (port 5432) as user "postgres": Postgres warning:
 CommitTransaction

Backtrace for: #<SB-THREAD:THREAD "main thread" RUNNING {26C887D9}>
0: ((LAMBDA NIL :IN SB-DEBUG::FUNCALL-WITH-DEBUG-IO-SYNTAX))
1: (SB-IMPL::CALL-WITH-SANE-IO-SYNTAX #<CLOSURE (LAMBDA NIL :IN SB-DEBUG::FUNCALL-WITH-DEBUG-IO-SYNTAX) {273AA6DD}>)
2: (SB-IMPL::%WITH-STANDARD-IO-SYNTAX #<CLOSURE (LAMBDA NIL :IN SB-DEBUG::FUNCALL-WITH-DEBUG-IO-SYNTAX) {273AA6C5}>)
3: (PRINT-BACKTRACE :STREAM #<SYNONYM-STREAM :SYMBOL SB-SYS:*STDOUT* {224B3D61}> :START 0 :FROM :DEBUGGER-FRAME :COUNT 5
36870911 :PRINT-THREAD T :PRINT-FRAME-SOURCE NIL :METHOD-FRAME-STYLE NIL)
4: (TRIVIAL-BACKTRACE:PRINT-BACKTRACE-TO-STREAM #<SYNONYM-STREAM :SYMBOL SB-SYS:*STDOUT* {224B3D61}>)
5: (TRIVIAL-BACKTRACE:PRINT-BACKTRACE #<PGLOADER.CONNECTION:DB-CONNECTION-ERROR {273A6001}> :OUTPUT #<SYNONYM-STREAM :SY
MBOL SB-SYS:*STDOUT* {224B3D61}> :IF-EXISTS :APPEND :VERBOSE T)
6: (SIGNAL #<PGLOADER.CONNECTION:DB-CONNECTION-ERROR {273A6001}>)
7: (ERROR PGLOADER.CONNECTION:DB-CONNECTION-ERROR :MESG "Postgres warning: CommitTransaction" :TYPE "pgsql" :HOST "dlsva
d51" :PORT 5432 :USER "postgres")
8: (PGLOADER.PGSQL:LIST-RESERVED-KEYWORDS #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://postgres@servername:5432/mydb {26E777F
9}>)
9: ((LAMBDA ()))
10: (PGLOADER:RUN-COMMANDS (LAMBDA NIL (LET* ((PGLOADER.PARSER::STATE-BEFORE (PGLOADER.UTILS:MAKE-PGSTATE)) (PGLOADER.PA
RSER::SUMMARY (NULL PGLOADER.PARAMS:*STATE*)) (PGLOADER.PARAMS:*STATE* (OR PGLOADER.PARAMS:*STATE* (PGLOADER.UTILS:MAKE-
PGSTATE))) (PGLOADER.PARSER::STATE-AFTER NIL) (PGLOADER.PARAMS:*PG-SETTINGS* (QUOTE NIL)) (PGLOADER.PGSQL::*PGSQL-RESERV
ED-KEYWORDS* (PGLOADER.PGSQL:LIST-RESERVED-KEYWORDS #1=#<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://postgres@servername:5432/
mydb {26E777F9}>)) (PGLOADER.PARAMS:*COPY-BATCH-ROWS* (OR NIL PGLOADER.PARAMS:*COPY-BATCH-ROWS*)) (PGLOADER.PARAMS:*COPY
-BATCH-SIZE* (OR NIL PGLOADER.PARAMS:*COPY-BATCH-SIZE*)) (PGLOADER.PARAMS:*CONCURRENT-BATCHES* (OR NIL PGLOADER.PARAMS:*
CONCURRENT-BATCHES*)) (PGLOADER.PARSER::SOURCE-DB (PGLOADER.UTILS:WITH-STATS-COLLECTION ("fetch" :STATE PGLOADER.PARSER:
:STATE-BEFORE) (PGLOADER.CONNECTION:EXPAND (PGLOADER.CONNECTION:FETCH-FILE #<PGLOADER.CSV:CSV-CONNECTION csv://FILENAME:
C:/temp/pgloader/more_fish.txt {26DCEE31}>))))) (PROGN NIL (LET ((TRUNCATE (GETF (QUOTE (:TRUNCATE T :SEPARATOR #\|)) :T
RUNCATE)) (PGLOADER.PARSER::SOURCE (MAKE-INSTANCE (QUOTE PGLOADER.CSV:COPY-CSV) :TARGET-DB #1# :SOURCE PGLOADER.PARSER::
SOURCE-DB :TARGET "fish" :ENCODING :UTF-8 :FIELDS (QUOTE (("fish_id") ("fish_descr"))) :COLUMNS (QUOTE NIL) :SEPARATOR #
\|))) (PGLOADER.SOURCES:COPY-FROM PGLOADER.PARSER::SOURCE :TRUNCATE TRUNCATE)) NIL (WHEN PGLOADER.PARSER::SUMMARY (PGLOA
DER.UTILS:REPORT-FULL-SUMMARY "Total import time" PGLOADER.PARAMS:*STATE* :BEFORE PGLOADER.PARSER::STATE-BEFORE :FINALLY
 PGLOADER.PARSER::STATE-AFTER))))) :START-LOGGER NIL :SUMMARY NIL :LOG-FILENAME NIL :LOG-MIN-MESSAGES NIL :CLIENT-MIN-ME
SSAGES NIL)
11: ((FLET #:CLEANUP-FUN-281 :IN PGLOADER::MAIN)) [cleanup]
12: (PGLOADER::MAIN ("pgloader.95961a4.exe" "--type" "csv" "--field" "fish_id, fish_descr" "--with" "truncate" "--with"
"fields terminated by '|'" "--debug" "C:\\temp\\pgloader\\more_fish.txt" "postgresql://postgres:pssword@servername:5432/my
db?tablename=fish"))
13: ((LAMBDA NIL :IN "//vboxsrv/vagrant/dumper-2SKVI5f7.lisp"))
14: ((FLET #:WITHOUT-INTERRUPTS-BODY-75 :IN SAVE-LISP-AND-DIE))
15: ((LABELS SB-IMPL::RESTART-LISP :IN SAVE-LISP-AND-DIE))
16: ("foreign function: #x42AE90")
17: ("foreign function: #x416676")

debugger invoked on a PGLOADER.CONNECTION:DB-CONNECTION-ERROR in thread
#<THREAD "main thread" RUNNING {26C887D9}>:
  Failed to connect to pgsql at "servername" (port 5432) as user "postgres": Postgres warning: CommitTransaction

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

restarts (invokable by number or by possibly-abbreviated name):
  0: [ABORT] Exit application

debugger invoked on a TYPE-ERROR in thread
#<THREAD "main thread" RUNNING {26C887D9}>:
  The value INVOKE-DEBUGGER is not of type SB-DI:FRAME.

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

restarts (invokable by number or by possibly-abbreviated name):
  0: [ABORT] Exit application

(SB-DEBUG::FRAME-CALL INVOKE-DEBUGGER :METHOD-FRAME-STYLE :NORMAL :REPLACE-DYNAMIC-EXTENT-OBJECTS NIL)
0[2] 2016-04-22T04:43:53.289000Z FATAL We have a situation here.
dimitri commented 8 years ago

Thanks for the detailed answer. The problem is that you're using an old version of pgloader that doesn't know how to handle PostgreSQL notification messages (such as the warning: CommitTransaction here) in the connection packet. So either change your log level in PostgreSQL (either on the server or just in pgloader with a SET client_min_messages TO notice; in the load file), or use a more recent version of pgloader.