PostgreSQL pg_dump creates sql script, but it is not a sql script: is there a way to get pg

I'm running pg_dump to create a script to automate the creation of a system like this:

pg_dump --dbname=postgresql://postgres:[email protected]:5432/OHDSI -t webapi.* > webapi.sql

This creates a sql script, but it is not really a sql script as it has code in it like what is shown below.

When this script is run as a sql script, it fails giving the error shown below.

Is there a way to get pg_dump to create a sql script that is standard sql and can be executed as a sql script?

Code sample from sql generated by pg_dump:

COPY webapi.cohort_version (asset_id, comment, description, version, asset_json, archived, created_by_id, created_date) FROM stdin;
\.


--
-- Data for Name: concept_of_interest; Type: TABLE DATA; Schema: webapi; Owner: ohdsi_admin_user
--

COPY webapi.concept_of_interest (id, concept_id, concept_of_interest_id) FROM stdin;
1   4329847 4185932
2   4329847 77670
3   192671  4247120
4   192671  201340

Error seen when running the script generated by pg_dump:

--
-- Name: penelope_laertes_uni_pivot id; Type: DEFAULT; Schema: webapi; Owner: ohdsi_admin_user
--
ALTER TABLE ONLY webapi.penelope_laertes_uni_pivot ALTER COLUMN id SET DEFAULT nextval('webapi.penelope_laertes_uni_pivot_id_seq'::regclass)

--
-- Data for Name: achilles_cache; Type: TABLE DATA; Schema: webapi; Owner: ohdsi_admin_user
--
COPY webapi.achilles_cache (id, source_id, cache_name, cache) FROM stdin

Error executing: COPY webapi.achilles_cache (id, source_id, cache_name, cache) FROM stdin
.  Cause: org.postgresql.util.PSQLException: ERROR: COPY from stdin failed: COPY commands are only supported using the CopyManager API.
  Where: COPY achilles_cache, line 1
Exception in thread "main" java.lang.RuntimeException: org.apache.ibatis.jdbc.RuntimeSqlException: Error executing: COPY webapi.achilles_cache (id, source_id, cache_name, cache) FROM stdin
.  Cause: org.postgresql.util.PSQLException: ERROR: COPY from stdin failed: COPY commands are only supported using the CopyManager API.
  Where: COPY achilles_cache, line 1
    at org.yaorma.database.Database.executeSqlScript(Database.java:344)
    at org.yaorma.database.Database.executeSqlScript(Database.java:332)
    at org.nachc.tools.fhirtoomop.tools.build.postgres.build.A04_CreateAtlasWebApiTables.exec(A04_CreateAtlasWebApiTables.java:29)
    at org.nachc.tools.fhirtoomop.tools.build.postgres.build.A04_CreateAtlasWebApiTables.main(A04_CreateAtlasWebApiTables.java:19)
Caused by: org.apache.ibatis.jdbc.RuntimeSqlException: Error executing: COPY webapi.achilles_cache (id, source_id, cache_name, cache) FROM stdin
.  Cause: org.postgresql.util.PSQLException: ERROR: COPY from stdin failed: COPY commands are only supported using the CopyManager API.
  Where: COPY achilles_cache, line 1
    at org.apache.ibatis.jdbc.ScriptRunner.executeLineByLine(ScriptRunner.java:109)
    at org.apache.ibatis.jdbc.ScriptRunner.runScript(ScriptRunner.java:71)
    at org.yaorma.database.Database.executeSqlScript(Database.java:342)
    ... 3 more
Caused by: org.postgresql.util.PSQLException: ERROR: COPY from stdin failed: COPY commands are only supported using the CopyManager API.
  Where: COPY achilles_cache, line 1
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:286)
    at org.apache.ibatis.jdbc.ScriptRunner.executeStatement(ScriptRunner.java:190)
    at org.apache.ibatis.jdbc.ScriptRunner.handleLine(ScriptRunner.java:165)
    at org.apache.ibatis.jdbc.ScriptRunner.executeLineByLine(ScriptRunner.java:102)
    ... 5 more

--- EDIT ------------------------------------

The --inserts method in the accepted answer gave me exactly what I needed.

I ended up doing this:

pg_dump --inserts --dbname=postgresql://postgres:[email protected]:5432/OHDSI -t webapi.* > webapi.sql

CodePudding user response：

The client tool you are using to restore the dump cannot deal with the data from the (nonstandard) COPY command being mixed into the script. You need psql to restore such a dump.

You can use the --inserts option of pg_dump to create a dump that contains INSERT statements rather than COPY. That will be slower to restore, but will work with more client tools.

However, your wish to get a standard SQL script is hopeless. PostgreSQL extends the standard in many ways, so a database cannot be dumped with a standard SQL script. Note, for example, that indexes are not defined by the SQL standard. If you are looking to transfer a PostgreSQL dump to a different RDBMS, you will be disappointed. That is more difficult.