2

I have a Postgres database dump of around 20GB size. I am trying to restore it using the command:

$ psql dbname < path-to-dump

which yields this final error:

enter image description here

After finding these questions (2 and 3), I rerun the restoration process like so:

$ psql -v ON_ERROR_STOP=1 dbname < path-to-dump

and got the first (out of many) error that occurs during the restoration:

enter image description here

This is the function where the specific error occurs:

--
-- Name: documents_search_trigger(); Type: FUNCTION; Schema: public; Owner: app
--

CREATE 
--

CREATE FUNCTION public.documents_search_trigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
          begin
            new.tsv := setweight(to_tsvector(coalesce(new.name,'')), 'A');
            return new;
          end
      $$;

After researching, I found these answers ([5], [6]). I tried to encode the dump in UTF-8 using the command iconv -f UTF-8 dump.sql > utf8dump.sql, but again, nothing worked, as I am getting the same errors.

The postgres version of my ubuntu 20.04 machine is:

postgres=# select version();
                                                             version                                                              
----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.0 (Ubuntu 14.0-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
(1 row)

while the version of postgres where the dump was taken can be found in the dump itself:

$ head -15 dump.sql
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.5
-- Dumped by pg_dump version 13.1 (Ubuntu 13.1-1.pgdg20.04+1)

I suspect there is a version mismatch problem, as I am getting more errors/warning before the invalid command \N starts taking over. Any ideas on how to proceed from here?

7
  • Please show the complete error message with the offending statement, details etc. Commented Oct 11, 2021 at 14:30
  • Updated the question, the error message is the one shown: ERROR: syntax error or at near "CREATE". Commented Oct 11, 2021 at 14:36
  • stackoverflow.com/a/33600869/562459 Commented Oct 11, 2021 at 14:48
  • I see. It would be interesting to see that line in the dump file, plus the stuff that comes immediately before it. Commented Oct 11, 2021 at 14:52
  • 2
    That single CREATE clearly is a syntax error. But pg_dump will never write a line like that. Investigate what was done to the file after it was created with pg_dump. Some misguided search/replace? Commented Oct 11, 2021 at 15:03

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.