Bulk loading into PostgreSQL from a remote client

postgresql bulk-load
Jon picture Jon · Aug 30, 2010 · Viewed 7.1k times · Source

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.

Answer

Pablo Santa Cruz picture Pablo Santa Cruz · Aug 30, 2010

COPY command is supported in PostgreSQL Protocol v3.0 (Postgresql 7.4 or newer).

The only thing you need to use COPY from a remote client is a libpq 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

Related questions

In PostgreSQL, how to insert data with COPY command?

I have problem when run 1 project NodeJs with PostgreSQL database. I have error when trying to insert data in pgAdmin using the COPY command. COPY beer (name, tags, alcohol, brewery, id, brewery_id, image) FROM stdin; Bons Voeux blonde 9.5 Brasserie …

Read More
How to convert date strings to timestamp without knowing the date format

I am trying to write a query to insert a value into a timestamp with no timezone data type field. The value is coming from CSV file. The version I am working with is PostgreSQL 8.1.21. The CSV file upload is …

Read More
How to import tables with missing values?

I use basketball data tables to get some understanding of Postgres 9.2 & phppgadmin. Therefore I would like to import csv tables into that database. However, I get: ERROR: missing data for column "year" CONTEXT: COPY coaches, line 1: ""coachid";"year";"yr_…

Read More