0

I have a big dump file that is generated by pg_dump that comes with PostgreSQL v12 client, and it is in customized format instead of plain sql text. The version of the server is PostgreSQL v12 too.

Now I need to reload the file into a PostgreSQL v11 server, and I get a error message:

unrecognized configuration parameter "default_table_access_method"

I guess that this parameter is introduced by PG12, so PG11 can't recognize it, even though I use the 12 version of pg_restore.

Because the file is huge and in binary format, I can NOT edit it to remove the line manually.

I tested that pg_restore test_file -f plain.sql with a test dump file, and it looks like work, and I can remove the line from the plain SQL file. But the real dump file is about 30GB, the plain SQL file expanded from the binary might be bigger (the working database occupies 300GB disk space).

Is there a way in it, I can cleanly reload it into PG11, only without the line "SET default_table_access_method = heap;"?

In fact, the following all can be ignored, because they are all default values:

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
1

No, there is no such way, because downgrading PostgreSQL is not supported.

You'll have to manually edit the dump and remove lines such as that until the dump loads without error. There could be other, more complicated problems than new parameters, see this question for an example in an older version.

If the dump is large, you could split it in three parts:

pg_dump --format=plain  --section=pre-data  --file=dbname-pre.sql  dbname
pg_dump --format=custom --section=data      --file=dbname-data.dmp dbname
pg_dump --format=plain  --section=post-data --file=dbname-post.sql dbname

You will only have to edit the first and last file, which are always small. The "data" section can be dumped in custom format and will be compressed. After editing, load the files in the same order.

3
  • Thanks a lot !!! It's a Greate Idea! It not only saved me this time, but also give me a chance to revise the header of a dump. I think this method is better than mine "pg_restore dump_file.dmp -f - | sed '/default_table_access_method/d' | psql -d my_db". – Leon Jan 29 at 13:41
  • You can do even better by using the custom format for the data section, see my edit. – Laurenz Albe Jan 29 at 13:50
  • "pg_restore dump_file.dmp -f - | sed '/default_table_access_method/d' | psql -d my_db" can only remove one command, but your method can edit it as my like. – Leon Jan 29 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.