CODEWITHSUNDEEP
postgresqlpsqlpg-dump
odigity
odigity

Reputation: 8216

Why does pg_dump include "transaction_timeout" if psql doesn't understand it?

I'm running PostgreSQL 15.7 (in Docker) and connecting with psql and pg_dump 17.0 on Ubuntu 22.04.

I have two databases:

I run pg_dump to dump db1's schema:

pg_dump --schema-only 'postgresql://.../db1' > schema.sql

I then run psql to run the result on db2 so it will end up with the same schema:

psql 'postgresql://.../db2' < schema.sql

And get this error: ERROR: unrecognized configuration parameter "transaction_timeout"

Resulting from line 11:

SET transaction_timeout = 0;

Why? Why is pg_dump dumping a statement that psql or postgres won't recognize? And how can I disable all these "SET" statements from being included in my SQL file in the first place?

(All I want are the tables, indices, constraints, and views.)

Upvotes: 14

Views: 12709

Answers (1)

odigity
odigity

Reputation: 8216

"Because pg_dump is used to transfer data to newer versions of PostgreSQL, the output of pg_dump can be expected to load into PostgreSQL server versions newer than pg_dump's version. pg_dump can also dump from PostgreSQL servers older than its own version. (Currently, servers back to version 9.2 are supported.) However, pg_dump cannot dump from PostgreSQL servers newer than its own major version; it will refuse to even try, rather than risk making an invalid dump. Also, it is not guaranteed that pg_dump's output can be loaded into a server of an older major version — not even if the dump was taken from a server of that version. Loading a dump file into an older server may require manual editing of the dump file to remove syntax not understood by the older server."

From the Notes section of the pg_dump docs. (Thanks to @Adrian Klaver for pointing me to it.)

PS - Emphasis mine.

PPS - This seems like bad design to me, which is why I found it so surprising and needed explicit confirmation.

Upvotes: 8

Related Questions