BUG #19927: PostgreSQL 16: SQL dump adds experimental/incompatible options like \restrict
The following bug has been logged on the website:
Bug reference: 19927
Logged by: Maciej Nux
Email address: egil@wp.pl
PostgreSQL version: 16.15
Operating system: Ubuntu
Description:
Hi. I encountered a compatibility issue while restoring a plain SQL dump
using PostgreSQL 16.
Created sql dump on one machine with 16.15 and wanted to restore on test
machine that happens to be on 16.3 at the moment.
**Expected:**
A plain SQL dump generated using PostgreSQL 16 tools should be restorable
using compatible PostgreSQL 16 client tools without encountering unsupported
psql meta-commands.
**Actual:**
The restore process encounters `\restrict`, which PostgreSQL 16.3 `psql`
does not recognise, and terminates with a non-zero exit code.
**Workarounds?**
If there is a workaround I would be interested. For procedural reasons we
cannot upgrade all servers at once, but also might need to move clients
around.
1. Is there any supported option to prevent `\restrict` and `\unrestrict`
from appearing in plain SQL dumps?
2. What is the recommended way to ensure compatibility when generating and
restoring plain SQL dumps?
Thanks in advance for any guidance.
PG Bug reporting form <noreply@postgresql.org> writes:
A plain SQL dump generated using PostgreSQL 16 tools should be restorable
using compatible PostgreSQL 16 client tools without encountering unsupported
psql meta-commands.
This was installed as part of a security fix. We will not be
removing it.
(It is generally true that we try not to break backwards compatibility
of dump files. But there are exceptions, and this was one.)
regards, tom lane
On Friday, November 20, 2026, PG Bug reporting form <noreply@postgresql.org>
wrote:
The following bug has been logged on the website:
Bug reference: 19927
Logged by: Maciej Nux
Email address: egil@wp.pl
PostgreSQL version: 16.15
Operating system: Ubuntu
Description:Hi. I encountered a compatibility issue while restoring a plain SQL dump
using PostgreSQL 16.Created sql dump on one machine with 16.15 and wanted to restore on test
machine that happens to be on 16.3 at the moment.
.1. Is there any supported option to prevent `\restrict` and `\unrestrict`
from appearing in plain SQL dumps?
2. What is the recommended way to ensure compatibility when generating and
restoring plain SQL dumps?
This failure mode seems like one we’d be willing to live with. You can
edit the sql file manually if you really need to restore it on an older
release (I’m assuming this to be true). The supported thing is to never
expect a newer version to produce output an older version can read. Even
for a minor release though we do usually avoid this for
non-security-related changes.
David J.
On Fri, 2026-11-20 at 13:21 +0000, PG Bug reporting form wrote:
Hi. I encountered a compatibility issue while restoring a plain SQL dump
using PostgreSQL 16.Created sql dump on one machine with 16.15 and wanted to restore on test
machine that happens to be on 16.3 at the moment.**Expected:**
A plain SQL dump generated using PostgreSQL 16 tools should be restorable
using compatible PostgreSQL 16 client tools without encountering unsupported
psql meta-commands.**Actual:**
The restore process encounters `\restrict`, which PostgreSQL 16.3 `psql`
does not recognise, and terminates with a non-zero exit code.**Workarounds?**
If there is a workaround I would be interested. For procedural reasons we
cannot upgrade all servers at once, but also might need to move clients
around.1. Is there any supported option to prevent `\restrict` and `\unrestrict`
from appearing in plain SQL dumps?
2. What is the recommended way to ensure compatibility when generating and
restoring plain SQL dumps?
What you are trying to do (downgrade) is not supported.
This is not a bug.
Update both servers to 16.16.
Don't try to tell me that you couldn't schedule the few minutes down time
for a restart of the server since August 2024, when 16.4 was released.
If you insist on staying on 16.3, you'd have to edit the dump file and
remove those lines. They serve a security purpose - but if security were
a consideration for you, you wouldn't be on 16.3...
Yours,
Laurenz Albe
What I'm asking for is a way to generate reasonably portable SQL dumps
that aren't tied to a specific minor version.
My understanding is that minor releases should not introduce breaking
changes. In practice, if an upgrade introduces such changes across
multiple environments, it is simply impossible to upgrade quickly. We
have gigabytes of data on each server and more than 20 servers. Some of
them are test servers with different upgrade schedules and processes.
Also in the past we already seen breaking changes so since then we tend
to test each version for days or weeks.
Does PostgreSQL follow semantic versioning principles? If so, shouldn't
compatibility between minor releases be maintained?
Is there a recommended way to generate SQL dumps that remain compatible
across minor releases of the same major version?
Regards,
Maciej "Nux" Jaros
On Nov 20, 2026, at 10:41, Maciej Jaros <egil@wp.pl> wrote:
Does PostgreSQL follow semantic versioning principles? If so, shouldn't compatibility between minor releases be maintained?
Tom Lane answered the question previously on this thread:
On Nov 20, 2026, at 07:25, Tom Lane <tgl@sss.pgh.pa.us> wrote:
This was installed as part of a security fix. We will not be
removing it.(It is generally true that we try not to break backwards compatibility
of dump files. But there are exceptions, and this was one.)
This is a relatively unusual situation. You can automate removal of the \restrict using sed or equivalent if you need a hands-free solution.
There are also quite a few ways of upgrading PostgreSQL that don't involve dump files; you might investigate if any of those will meet your needs so that you aren't tied to dump file formats.
Christophe Pettus (20.11.2026 20:35):
This is a relatively unusual situation. You can automate removal of the \restrict using sed or equivalent if you need a hands-free solution.
There are also quite a few ways of upgrading PostgreSQL that don't involve dump files; you might investigate if any of those will meet your needs so that you aren't tied to dump file formats.
Well if the minor versions have breaking changes then how am I supposed
to upgrade without making a backup? You seem to saying contradicting things.
Regards,
Eccenux
Christophe Pettus <xof@thebuild.com> writes:
This is a relatively unusual situation. You can automate removal of the \restrict using sed or equivalent if you need a hands-free solution.
You can also simply ignore these error messages, though I grant that
feels dangerous. There have been many cases in the past where
cross-version dump restoration would result in harmless error messages.
regards, tom lane
On Nov 20, 2026, at 11:56, Maciej Jaros <egil@wp.pl> wrote:
Well if the minor versions have breaking changes then how am I supposed to upgrade without making a backup? You seem to saying contradicting things.
I'm not quite sure what you mean here.
You don't need to do a dump/restore to upgrade from one minor version to another within a major version; you just restart on the newer binaries. I don't believe there's ever been a PostgreSQL minor version upgrade that required anything else (except possible post-upgrade maintenance activities like rebuilding certain indexes).
The situation that you are encountering is not an upgrade: it's a downgrade from a higher minor version to a lower one. PostgreSQL has never guaranteed that a dump created on a later version, minor or major, will restore without any errors on an earlier version.
If you need to do this routinely (restore a dump taken on a later minor version on a server running an earlier minor version), you have some options:
1. Process the dump file to removes newer features that won't restore without error on the earlier version.
2. Just ignore the error, as Tom suggests.
3. Don't use pg_dump at all, but just move the underlying files around (assuming compatible architectures, etc.).
Import Notes
Reply to msg id not found: eefe1947-4278-4f7c-a26c-8be6ccbd4b2b@wp.plReference msg id not found: eefe1947-4278-4f7c-a26c-8be6ccbd4b2b@wp.pl
On Fri, Nov 20, 2026 at 12:23 PM Maciej Jaros <egil@wp.pl> wrote:
Does PostgreSQL follow semantic versioning principles?
Not strictly.
If so, shouldn't
compatibility between minor releases be maintained?
This is why my answer was "not strictly"...
Is there a recommended way to generate SQL dumps that remain compatible
across minor releases of the same major version?
The data directory format is the invariant PostgreSQL worries about,
avoiding requiring pg_upgrade for a minor release. Everything else is
pretty much "unsupported" the moment a new minor release comes out.
We do in practice try to avoid annoying users but there is definitely a
line that doesn't get crossed. xml namespaces and pg_dump search path are
historical examples of willingly inflicted boundaries in this area. In
particular if the feature came about in response to a CVE like search_path
and this one.
David J.
Christophe Pettus (20.11.2026 21:17):
There are also quite a few ways of upgrading PostgreSQL that don't involve dump files; you might investigate if any of those will meet your needs so that you aren't tied to dump file formats
On Nov 20, 2026, at 11:56, Maciej Jaros <egil@wp.pl> wrote:
Well if the minor versions have breaking changes then how am I supposed to upgrade without making a backup? You seem to saying contradicting things.
I'm not quite sure what you mean here.
You don't need to do a dump/restore to upgrade from one minor version to another within a major version; you just restart on the newer binaries. I don't believe there's ever been a PostgreSQL minor version upgrade that required anything else (except possible post-upgrade maintenance activities like rebuilding certain indexes).
I'm not saying that a minor upgrade requires a dump/restore in theory.
I'm saying backup is required in practice, especially if PostgreSQL
doesn't follow semantic versioning best practices. And so I need a
backup and a rollback plan before upgrading a critical system with
thousands to millions of users. This takes hours, not minutes, and might
be put on hold for various reasons.
If minor versions can introduce breaking changes that aren't immediately
obvious, I also need to test the specific PostgreSQL version before
deploying it. This can take days or weeks or even more when waiting for
approval. Effectively, this makes the planning and testing effort for a
minor upgrade almost the same as for a major upgrade.
Regards,
Nux
On Nov 20, 2026, at 15:31, Maciej Jaros <egil@wp.pl> wrote:
If minor versions can introduce breaking changes that aren't immediately obvious, I also need to test the specific PostgreSQL version before deploying it.
You are over-generalizing here. There is one particular contract that you expected to exist that doesn't, which is that a pg_dump taken from a later version can be restored to an earlier version with no errors. (Note that in this particular case, the restore actually does work, you just get an alarming-looking error message.) Taking that one change to mean all bets are off and every new minor version of PostgreSQL can introduce arbitrary changes that might break anything isn't supported by the real history of the project.
And it's good practice to test even minor releases of PostgreSQL before deploying them in production, because even "non-breaking" changes can result in things like different query plans that can affect system performance. It's most definitely never been the case that the project has guaranteed that new minor versions will have no observable differences in behavior from prior versions.
Import Notes
Reply to msg id not found: 79cd25f9-0150-45bb-92da-eb1afce38d65@wp.plReference msg id not found: 79cd25f9-0150-45bb-92da-eb1afce38d65@wp.pl
On Fri, 2026-11-20 at 20:56 +0100, Maciej Jaros wrote:
Well if the minor versions have breaking changes then how am I supposed to upgrade
without making a backup? You seem to saying contradicting things.
I think you got that wrong. Upgrading wouldn't be a problem, as the dump from
16.15 would work with 19.0 without any problems. It is *down*grading that is the
problem. Moreover, as has been mentioned, moving from 16.3 to 16.16 wouldn't
need a dump (and it would solve your problem).
I keep being amazed at people who expend more effort resisting the update to the
latest minor version than the effort of the actual update would be.
Yours,
Laurenz Albe
Import Notes
Reply to msg id not found: eefe1947-4278-4f7c-a26c-8be6ccbd4b2b@wp.plReference msg id not found: eefe1947-4278-4f7c-a26c-8be6ccbd4b2b@wp.pl