Import postgres data from VM

yu.li · March 5, 2019, 7:31pm

Hi everyone, I'm trying to import a Postgres database from a VM, and use Neo4j to visualize the graph.
For a sample of such a database, I am able to load the data using ETL tools and run it on my local Windows machine using Neo4j desktop.

However, when I try to load the database from VM (Centos), I can not load it from ETL. It gives me this error:
Connection failed. SQL state: 08001, message: Connection to localhost:7687 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
close

And on my VM, my Neo4j status looks like this:

Am I doing correctly? Or I should not use ETL tool for loading external database in the beginning? What is the best approach in my case?
Please advise and thank you so much!

michael.hunger · March 6, 2019, 1:29am

It's the connection details to your postgres database that you need to enter there, not neo4j.

yu.li · March 6, 2019, 6:54pm

Hi Michael, thank you for helping me out. You are right, I should have imported the postgres database. My following question is that, How can I use this ETL tool to SSH my database that is hosted on VM? Thanks in advance!

michael.hunger · March 6, 2019, 11:24pm

You can't you have to expose the ports from the VM or use an sshtunnel to map the ports to localhost.

yu.li · March 7, 2019, 6:05am

Thanks Michael, I did forward the port using Putty. However, when I import the data, it just show as "mapping error", any ideas of what it is?

michael.hunger · March 7, 2019, 6:33am

What does your JDBC config look like?

yu.li · March 7, 2019, 6:55am

Thank you Michael.
I forward postgres database from port 5432 to local 6556, since my local I'm using 5432 as postgresql port.
Here is the connection:

Here is the log:

COMMAND: java -cp "C:\Users\.Neo4jDesktop\graphApps\neo4j-etl-ui/dist/neo4j-etl.jar" org.neo4j.etl.NeoIntegrationCli generate-metadata-mapping --rdbms:url "jdbc:postgresql://localhost:5432/?ssl=false" --rdbms:password "pass" --rdbms:user "postgres" --output-mapping-file "C:\Users\.Neo4jDesktop\neo4jDatabases\database-e8a7a811-ebca-4db7-aef7-bb3f0d43677a\installation-3.5.2/import/postgresql__mapping.json"
- Skipping reading import options from file because file [] doesn't exist.
- Creating RDBMS to CSV mappings...
- Connection error: 
org.postgresql.util.PSQLException: Connection to localhost:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:265)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:194)
	at org.postgresql.Driver.makeConnection(Driver.java:431)
	at org.postgresql.Driver.connect(Driver.java:247)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at org.neo4j.etl.sql.DatabaseClient.<init>(DatabaseClient.java:65)
	at org.neo4j.etl.commands.rdbms.GenerateMetadataMapping.call(GenerateMetadataMapping.java:90)
	at org.neo4j.etl.commands.rdbms.GenerateMetadataMapping.call(GenerateMetadataMapping.java:31)
	at org.neo4j.etl.cli.rdbms.GenerateMetadataMappingCli.run(GenerateMetadataMappingCli.java:118)
	at org.neo4j.etl.util.CliRunner.run(CliRunner.java:42)
	at org.neo4j.etl.util.CliRunner.run(CliRunner.java:35)
	at org.neo4j.etl.NeoIntegrationCli.main(NeoIntegrationCli.java:43)
Caused by: java.net.ConnectException: Connection refused: connect
	at java.net.DualStackPlainSocketImpl.waitForConnect(Native Method)
	at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:85)
	at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
	at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
	at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
	at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)
	at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
	at java.net.Socket.connect(Socket.java:589)
	at org.postgresql.core.PGStream.<init>(PGStream.java:62)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:144)
	... 13 more

- Could not connect to the host database. Please check your credentials 
org.postgresql.util.PSQLException: Connection to localhost:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:265)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:194)
	at org.postgresql.Driver.makeConnection(Driver.java:431)
	at org.postgresql.Driver.connect(Driver.java:247)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at org.neo4j.etl.sql.DatabaseClient.<init>(DatabaseClient.java:65)
	at org.neo4j.etl.commands.rdbms.GenerateMetadataMapping.call(GenerateMetadataMapping.java:90)
	at org.neo4j.etl.commands.rdbms.GenerateMetadataMapping.call(GenerateMetadataMapping.java:31)
	at org.neo4j.etl.cli.rdbms.GenerateMetadataMappingCli.run(GenerateMetadataMappingCli.java:118)
	at org.neo4j.etl.util.CliRunner.run(CliRunner.java:42)
	at org.neo4j.etl.util.CliRunner.run(CliRunner.java:35)
	at org.neo4j.etl.NeoIntegrationCli.main(NeoIntegrationCli.java:43)
Caused by: java.net.ConnectException: Connection refused: connect
	at java.net.DualStackPlainSocketImpl.waitForConnect(Native Method)
	at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:85)
	at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
	at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
	at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
	at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)
	at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
	at java.net.Socket.connect(Socket.java:589)
	at org.postgresql.core.PGStream.<init>(PGStream.java:62)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:144)
	... 13 more

- Command failed due to error (PSQLException: Connection to localhost:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.). Rerun with --debug flag for detailed diagnostic information.

michael.hunger · March 9, 2019, 2:02pm

But the test connection works?
And you're also selecting that one?
Seems as if the port doesn't make it into the URL which is odd.
Would you mind creating a GH issue for it?

yu.li · March 13, 2019, 6:22pm

Thank you so much Michael! Sure, I might do that. Meanwhile, I kind of figure out why I could not import my database. MY data is more than 200 GB and without a graphic card, so when I try to load it on my local computer, which has only 100 GB free space, I think that's why it failed.
Thank you for helping me!

Topic		Replies	Views
Neo4j ETL tool not able to import data of Online PostgreSQL database. Data imported with errors.NullPointerException ETL-Tool postgres , import , neo4j-desktop	3	671	February 4, 2021
ETL stucks at Mapping - No error ETL-Tool performance , neo4j-desktop	12	697	March 5, 2021
Import from POSTGRESQL fails with unhelpful error message ETL-Tool postgres , import	5	1267	October 19, 2021
Etl import of postgres db fails ETL-Tool	0	964	October 15, 2019
Can't map postgres to Neo4j graph using Neo4j ETL tool? Neo4j Graph Platform migrated	3	175	August 3, 2022

Import postgres data from VM

Related topics