unrecognized configuration parameter "default table access method" google cloud

Question

I try to import some files to a PostgreSQL database but I get this error:

Falha Importar: 
SET 
SET 
SET 
SET 
SET 
set_config ------------ 
(1 row) 
SET 
SET 
SET 
SET 
SET 
Import error: exit status 3 ERROR: unrecognized configuration parameter "default_table_access_method"

Maillot

Hi, can you please share with us how are you exporting and importing the database? – Bucephalus

Answer 1

You are importing a PostgreSQL v12 pg_dump into something that is obviously based on an earlier version of PostgreSQL.

This error is harmless (the parameter default_table_access_method was introduced in v12), but you may have more interesting problems with other new v12 features. If the rest of the import works without error, you are probably fine, but keep in mind that downgrading PostgreSQL is not supported.

You would be best off using the same version throughout. If you cannot have that locally because the hosted database is a closed source fork, that is a good point against using it.

Sarcomatosis

E.g. I have some initial data set and table setup that I know works across multiple PSQL versions -> sed -i 's/SET default_table_access_method = heap;/-- SET default_table_access_method = heap;/' <DUMP> is a viable solution. – Mira

@Mira , there is another elegant solution to this issue - just use PSQL meta-commands, so you can skip some parts of a script when importing it into an older version. For instance, to skip statements introduced in v12 one can wrap them in \if :VERSION_NUM >= 120000 ... \endif (it should be multiline). Refer to postgresql.org/docs/10/app-psql.html – Immunogenic

Answer 2

Here is my workaround:

1. Dump using pdAdmin, set as SQL, Plain, and "Only schema", and save to a local folder.
2. Edit the SQL file, (text format)
3. comment out "-- error from V 12 : SET default_table_access_method = heap;"
4. run the SQL scripts file using pgAdmin or other tools.
5. Dump the source database again, with "Only Data", "backup" options
6. Restore the data-only dump file.

It works for me.

Seadon