6799

Bulk loading into PostgreSQL from a remote client

Question:

I need to bulk load a large file into PostgreSQL. I would normally use the COPY command, but this file needs to be loaded from a remote client machine. With MSSQL, I can install the local tools and use bcp.exe on the client to connect to the server.

Is there an equivalent way for PostgreSQL? If not, what is the recommended way of loading a large file from a client machine if I cannot copy the file to the server first?

Thanks.

Answer1:

COPY command is supported in <a href="http://developer.postgresql.org/pgdocs/postgres/protocol-flow.html#PROTOCOL-COPY" rel="nofollow"><strong>PostgreSQL Protocol v3.0</strong></a> (Postgresql 7.4 or newer).

The only thing you need to use COPY from a remote client is a <strong>libpq</strong> enabled client such as psql command line utility.

From the remote client run:

$ psql -d dbname -h 192.168.1.1 -U uname < yourbigscript.sql

Answer2:

You can use the \copy command from psql tool like:

psql -h IP_REMOTE_POSTGRESQL -d DATABASE -U USER_WITH_RIGHTS -c "\copy TABLE(FIELD_LIST_SEPARATE_BY_COMMA) from 'FILE_IN_CLIENT_MACHINE(MAYBE IN THE SAME DIRECTORY)' with csv header"

Answer3:

Assuming you have some sort of client in order to run the query, you can use the COPY FROM STDIN form of the COPY command: <a href="http://www.postgresql.org/docs/current/static/sql-copy.html" rel="nofollow">http://www.postgresql.org/docs/current/static/sql-copy.html</a>

Answer4:

Use psql's \copy command to load data in sql:

$ psql -h <IP> -p <port> -U <username> -d <database> database# \copy schema.tablename from '/home/localdir/bulkdir/file.txt' delimiter as '|' database# \copy schema.tablename from '/home/localdir/bulkdir/file.txt' with csv header

Recommend

  • Angular 5 Universal (SSR) with dotnet core not working with i18n angular tools on Staging server
  • ConnectionAbortedError: [WinError 10053] An established connection was aborted by the software in yo
  • How does the compiler evaluate a condition in C
  • Finding length of contour in opencv
  • DateTime::createFromFormat - php format issue
  • `as.na` function
  • How to port a network extension NEPacketTunnelProvider class from Obj-C/Swift to Xamarin C#?
  • how to add images on gridview?
  • Memory Leak UITableView
  • Sorting list of dictionaries according to specific order
  • How to access images in UIATablecell
  • type “e” does not exist , Redshift through Postgresql connector in php codeigniter
  • Swing vertical Navigation Menu
  • How to do handle login-logoff
  • WEBrick fails to start default rails app: “uninitialized constant ActiveRecord::ConnectionAdapters::
  • Split string and numbers
  • three.js transparent png texture strange border webgl
  • Check every condition in Python if else even if one evaluates to true
  • Java Hibernate Check MySQL Replication is in sync
  • ARKit – Rendering a 3D object under an invisible plane
  • rownum issue in oracle query
  • get list of all triggers,functions and stored procedures
  • How to set title name of the pdf. While viewing the Document(New Tab)
  • Eclipse ADT Plugin crashed after updating to version 22.0
  • svnserve and SASL problem
  • Jasmine unit testing observable subscribe does not trigger
  • Null Space Binary Matrix : Java
  • Parse and remove special characters in java regex
  • get all files in git diff in intellij
  • Laravel 5 - Cache remember doesn't work
  • Refresh JSF component after custom javascript Ajax call
  • if you have a DLL creating a bitmap in memory, how to return it to the browser?
  • yii rewrite url with many sub categories
  • Draw 9 patch dotted /dashed line on Android
  • All Event listing on specified date in Google Calender api (V3) in java?
  • Can I read another applications memory?