1

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?

Andrew
  • 84
  • 2
  • 11
  • Please show the complete error message with the offending statement, details etc. – Laurenz Albe Oct 11 '21 at 14:30
  • Updated the question, the error message is the one shown: `ERROR: syntax error or at near "CREATE"`. – Andrew Oct 11 '21 at 14:36
  • https://stackoverflow.com/a/33600869/562459 – Mike Sherrill 'Cat Recall' Oct 11 '21 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. – Laurenz Albe Oct 11 '21 at 14:52
  • @LaurenzAlbe Added the lines before the function (which is the line where the first error occurs). – Andrew Oct 11 '21 at 15:01
  • 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? – Laurenz Albe Oct 11 '21 at 15:03
  • A version mismatch is not going to generate that stray "CREATE". This file has been corrupted, almost certainly by something external (e.g. a well-meaning admin with a text editor) – jjanes Oct 11 '21 at 16:20

0 Answers0