Pg_dump restore local dev box bug?

Hi all,

Problem 1

I am trying to restore the pg_dump file via in pom.xml. And below error is thrown when running prepare-database step. After looking into this step, it seems the bug is related to incorrect parsing of the pg_dump file. Also setting <importMethod>psql</importMethod> doesn’t seem to do anything. As stated in the logs below it is still using sql to import.

[INFO] Executing commands
[INFO] 1 of 1 SQL statements executed successfully
[INFO] Importing dump file: /Users/saurabh.gupta/workspace/sample/database-dumps/confluencedb-8.0.1.sql into postgres72 using sql
[INFO] Executing file: /var/folders/d8/tfg36_rj2zd17r0xdxjpdkc00000gp/T/confluencedb-8.0.1.858484586sql
[ERROR] Failed to execute:

 CREATE FUNCTION public.content_function_for_denormalised_permissions() RETURNS trigger
 LANGUAGE plpgsql
 AS $$
 BEGIN
 RETURN NEW
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  4.380 s
[INFO] Finished at: 2022-12-20T02:18:07-05:00
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal com.atlassian.maven.plugins:confluence-maven-plugin:8.9.0:prepare-database (default) on project Sample: Unterminated dollar quote started at position 114 in SQL
[ERROR]
[ERROR]  CREATE FUNCTION public.content_function_for_denormalised_permissions() RETURNS trigger
[ERROR]  LANGUAGE plpgsql
[ERROR]  AS $$
[ERROR]  BEGIN
[ERROR]  RETURN NEW. Expected terminating $$
[ERROR] -> [Help 1]
[ERROR]
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR]
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException

I can use the same sql file and restore a database directly though psql command. Can anyone confirm if anyone has faced this issue before, or have a solution to this or possibly report this?

Solution 1

I resolved the above error by manually replacing $$ by single quote (').

Problem 2

Then comes the next problem which is

[INFO] Executing commands
[INFO] 1 of 1 SQL statements executed successfully
[INFO] Importing dump file: /Users/saurabh.gupta/workspace/Sample/database-dumps/confluencedb-8.0.1.psql into postgres72 using sql
[INFO] Executing file: /var/folders/d8/tfg36_rj2zd17r0xdxjpdkc00000gp/T/confluencedb-8.0.1.1825460729psql
[ERROR] Failed to execute:


 COPY public."AO_187CCC_SIDEBAR_LINK" ("CATEGORY", "CUSTOM_ICON_CLASS", "CUSTOM_TITLE", "DEST_PAGE_ID", "HARDCODED_URL", "HIDDEN", "ID", "POSITION", "SPACE_KEY", "TYPE", "WEB_ITEM_KEY") FROM stdin
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  7.069 s
[INFO] Finished at: 2022-12-20T02:51:07-05:00
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal com.atlassian.maven.plugins:confluence-maven-plugin:8.9.0:prepare-database (default) on project Sample: ERROR: COPY from stdin failed: COPY commands are only supported using the CopyManager API.
[ERROR]   Where: COPY AO_187CCC_SIDEBAR_LINK, line 1
[ERROR] -> [Help 1]
[ERROR]
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR]
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException

I don’t even know where to begin with this error.

Solution 2
So I came up with my own solution of using pg_restore and not using <dumpFilePath> tag with help of exec-maven-plugin. Hopefully Atlassian fixes this in future.

@Wendy @Kusal
Could you please look into this.

Thanks in Advance.