cancel
Showing results for 
Search instead for 
Did you mean: 

Import postgres data from VM

yu_li
Node

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!

8 REPLIES 8

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

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!

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

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?

What does your JDBC config look like?

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.
 

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?

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!

Nodes 2022
Nodes
NODES 2022, Neo4j Online Education Summit

On November 16 and 17 for 24 hours across all timezones, you’ll learn about best practices for beginners and experts alike.