Subject: bug: pg_dumpall with --data-only and --clean options is giving an error after some dump

From: Mahendra Singh Thalor <[email-masked]>
Date: Sun, 15 Mar 2026 00:48:39 +0530

Hi all,

I was doing some tests with pg_dump, pg_dumpall and pg_restore tools. With
"pg_dumpall --data-only --clean", we are reporting an error after dumping
some data.

Please see the example below.

./pg_dumpall --data-only --clean
> --
> -- PostgreSQL database cluster dump
> --
>
> \restrict ZQDDv56JBW8CVfkLsRDeyRpBvDGYUeqhZbJkDccKbXG8q6PI4RB69Dd8KaqcWMY
>
> SET default_transaction_read_only = off;
>
> SET client_encoding = 'UTF8';
> SET standard_conforming_strings = on;
>
> \unrestrict ZQDDv56JBW8CVfkLsRDeyRpBvDGYUeqhZbJkDccKbXG8q6PI4RB69Dd8KaqcWMY
>
> --
> -- Databases
> --
>
> --
> -- Database "template1" dump
> --
>
> pg_dump: error: options -c/--clean and -a/--data-only cannot be used
> together
> pg_dumpall: error: pg_dump failed on database "template1", exiting
>
>
Error is coming from pg_dump but it should come from pg_dumpall without
any dump.

Here, I am attaching a patch to fix this problem. Please review this.

-- 
Thanks and Regards
Mahendra Singh Thalor
EnterpriseDB: http://www.enterprisedb.com
From bbc014ab3e6d956df53aa7965d3a6b6d5d869baf Mon Sep 17 00:00:00 2001
From: Mahendra Singh Thalor <[email-masked]>
Date: Sun, 15 Mar 2026 00:38:16 +0530
Subject: [PATCH] pg_dumpall: -clean and --data-only are incompatible

Report error when -clean and --data-only are given with pg_dumpall.

As of now, we are dumping some data and later we are exiting with
error from pg_dump.
---
 src/bin/pg_dump/pg_dumpall.c   | 4 ++++
 src/bin/pg_dump/t/001_basic.pl | 5 +++++
 2 files changed, 9 insertions(+)

diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 3d2a1d27aef..47a062d0160 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -455,6 +455,10 @@ main(int argc, char *argv[])
 						schema_only, "-s/--schema-only",
 						tablespaces_only, "-t/--tablespaces-only");
 
+	/* --clean and --data-only are incompatible */
+	check_mut_excl_opts(output_clean, "-c/--clean",
+			data_only, "-a/--data-only");
+
 	if (if_exists && !output_clean)
 		pg_fatal("option %s requires option %s",
 				 "--if-exists", "-c/--clean");
diff --git a/src/bin/pg_dump/t/001_basic.pl b/src/bin/pg_dump/t/001_basic.pl
index 2f5eb48e7b8..8aab1187282 100644
--- a/src/bin/pg_dump/t/001_basic.pl
+++ b/src/bin/pg_dump/t/001_basic.pl
@@ -50,6 +50,11 @@ command_fails_like(
 	'pg_dump: options -a/--data-only and -s/--schema-only cannot be used together'
 );
 
+command_fails_like(
+        [ 'pg_dumpall', '-c', '-a' ],
+        qr/\Qpg_dumpall: error: options -c\/--clean and -a\/--data-only cannot be used together\E/,
+        'pg_dumpall: options -c/--clean and -a/--data-only cannot be used together');
+
 command_fails_like(
 	[ 'pg_dump', '-s', '--statistics-only' ],
 	qr/\Qpg_dump: error: options -s\/--schema-only and --statistics-only cannot be used together\E/,
-- 
2.52.0

---

From: Srinath Reddy Sadipiralla <[email-masked]>
Date: Sun, 15 Mar 2026 11:44:50 +0530

Hello,

On Sun, Mar 15, 2026 at 12:49 AM Mahendra Singh Thalor <[email-masked]>
wrote:

>> \unrestrict
>> ZQDDv56JBW8CVfkLsRDeyRpBvDGYUeqhZbJkDccKbXG8q6PI4RB69Dd8KaqcWMY
>>
>> --
>> -- Databases
>> --
>>
>> --

+1 for the fix , as it immediately throws an error in pg_dumpall itself,
instead of going through all the way until pg_dump and then finding
out it's not a correct combination of options.

I have reviewed,tested the patch , except these it LGTM.
1) maybe match the pg_dumpall comment with the pg_dump comment.
2) please fix the indentation using pgindent

diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 47a062d0160..b47e683e1d4 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -455,9 +455,9 @@ main(int argc, char *argv[])
                                                schema_only,
"-s/--schema-only",
                                                tablespaces_only,
"-t/--tablespaces-only");

-       /* --clean and --data-only are incompatible */
+       /* --clean is incompatible with --data-only */
        check_mut_excl_opts(output_clean, "-c/--clean",
-                       data_only, "-a/--data-only");
+                                               data_only,
"-a/--data-only");

-- 
Thanks,
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/

---

From: Andrew Dunstan <[email-masked]>
Date: Sun, 15 Mar 2026 07:58:31 -0400

On 2026-03-14 Sa 3:18 PM, Mahendra Singh Thalor wrote:
> Hi all,
>
> I was doing some tests with pg_dump, pg_dumpall and pg_restore tools. 
> With "pg_dumpall --data-only --clean", we are reporting an error after 
> dumping some data.

Hmm, this looks like behaviour that goes a long way back. Should we 
backpatch this fix?

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

---

From: Mahendra Singh Thalor <[email-masked]>
Date: Sun, 15 Mar 2026 19:36:14 +0530

Thanks Andrew, Álvaro and Srinath for the review and feedback.

> 1) maybe match the pg_dumpall comment with the pg_dump comment.

I took this comment from pg_restore so i am keeping this same as pg_restore.

> 2) please fix the indentation using pgindent

Fixed.

On Sun, 15 Mar 2026 at 17:41, Álvaro Herrera <[email-masked]> wrote:
>
> Hi,
>
> On 2026-Mar-15, Andrew Dunstan wrote:
>
> IMO it would make sense to do so.
>
> --
> Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/

As per suggestions, I made patches till PG14.

Here, I am attaching updated patches.

-- 
Thanks and Regards
Mahendra Singh Thalor
EnterpriseDB: http://www.enterprisedb.com
From 9c206b78915663fef9387ba623bfb15350f280c8 Mon Sep 17 00:00:00 2001
From: Mahendra Singh Thalor <[email-masked]>
Date: Sun, 15 Mar 2026 18:40:02 +0530
Subject: [PATCH] pg_dumpall: -clean and --data-only are incompatible

index 3d2a1d27aef..20cdd2d92f0 100644

+command_fails_like(

---

From: Nathan Bossart <[email-masked]>
Date: Mon, 16 Mar 2026 09:52:21 -0500

On Sun, Mar 15, 2026 at 12:20:41PM -0400, Tom Lane wrote:
> Andrew Dunstan <[email-masked]> writes:
> I wouldn't bother.  At the end of the day you get the same failure
> report anyway.

Agreed, I don't think back-patching is worth the (probably low) risk.  The
latest patch for v19 looks good to me.  I recently committed some stuff in
this area, so I can take care of committing this one, too.

-- 
nathan

---

From: Nathan Bossart <[email-masked]>
Date: Mon, 16 Mar 2026 11:05:39 -0500

> this area, so I can take care of committing this one, too.

Committed.

-- 
nathan