Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w2YDm-000OHI-1D for pgsql-general@arkaria.postgresql.org; Tue, 17 Mar 2026 17:28:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w2YDj-003XHI-36 for pgsql-general@arkaria.postgresql.org; Tue, 17 Mar 2026 17:28:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w2YDj-003XH9-1x for pgsql-general@lists.postgresql.org; Tue, 17 Mar 2026 17:28:43 +0000 Received: from mail-wr1-x436.google.com ([2a00:1450:4864:20::436]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1w2YDh-00000000drh-0a02 for pgsql-general@lists.postgresql.org; Tue, 17 Mar 2026 17:28:43 +0000 Received: by mail-wr1-x436.google.com with SMTP id ffacd0b85a97d-43a03cb1df9so5957637f8f.1 for ; Tue, 17 Mar 2026 10:28:41 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1773768520; cv=none; d=google.com; s=arc-20240605; b=h8rFP2Xk1z2K3VL7lKX/F9aV7R9FEdmLkx7F84E+ddEaPvYKjkb35UwZ1Bx2zGczlW Lok6BtJKg3ZxIl0UqGsC5U+LzuaT0fNier3xYjkKd6jnA9y9rYHi/AXmFOw5iIQiOU95 /tH8MaEgnrP2eCp2fpa6VU3UVk0GGC6hxvXPZ+uw4WMeeouYM55X75UlQNJQguZdIehC CoWilheZMU9h3PKoQu/NLT2iF/3urVwKUDbmPIXTNWYqTjagCSxRJ8uDktUEXWKrXmi4 kAJ2Z2sC/PmgSY7p55PgGbEsg71HQxGyvJ8KCTOdIkSlzq+1zmTF/52eCy0fLPKtK/f1 GSxg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=OHT75zKsOLqEmJfaLPJSuWX0A8hNqpC3szchgl+FHGk=; fh=u6i2FiVR94lw5x2fWg+p8ZwUFr7PYusEJrtj5v8JWHQ=; b=Q5vHmwyNWGM5aqYUN7HWI4tjVcMjw014hZFKZB8VWPXfdythSOLmDMTEF0s1qmLDzJ O7fz3sMYE95VhaXLWcWeI8wJ+Imc0EMindk6zqpzZpbd4UaxdVR16LjwVmo/XN90IvH9 4HOzjVNzwzBvg6pJWI7mIl2OZ2zM8qX45+gbV9zEBZRRVgKZjgKsYC0+2qF9J5jsTkin gi0AsuDI/TLzqL9eGyY9/iv/w754e/kD4FTVnLS+KWb9ri//OPY544Ojio6fB5SV6jSO MqasXeKxzC28DcVsGxyqzOX4Un9E3LSAT/Ns7ydpDeqHewwkdKkRFoqRx/WkR0ObQCj6 EdjQ==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1773768520; x=1774373320; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=OHT75zKsOLqEmJfaLPJSuWX0A8hNqpC3szchgl+FHGk=; b=Gnm5gxPkYvHv7wZMGLop09XZm+D/i9W7P8oV9dJuOpagEz7ewo+WRunsqSN0QZZT43 03EmCvmIvlH0DwUV+gReXln9hufwgFeopcWOHJcYsY4iSG/ebXOkvkk9PdmSPi/XsdE4 s5msfETfmPyKd3Eh+GX2SFydDf/hlLWemVu7mxtOnNylqr1Taon/Q7FpJdYyHXTXe29W K/SAj6D3fXNwfzUvabxt6hBYxujAgnCcNlNKSfOs3wG4XTd8jM8mcSPl1s9chGD0zP9C 190PZ1U+d2DK6B5G8qtbQ+HCH1FR1h1ZBIZCvPtiAtmmwJad/2FMMNct/38bUHbZJPYz neKg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1773768520; x=1774373320; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=OHT75zKsOLqEmJfaLPJSuWX0A8hNqpC3szchgl+FHGk=; b=RF0vT2pcfPDUCGRZ2r/N9DTVBe4smeXCM9/e+ivYuDqnOjnlOtlLwmPB+LnzdCSd9V FcITIY+s1dTHJIgZFuuZ3EKcS0vIYkj4yM2BQ/r8VpC3pvKb6sS9+gEk4XTh5jmMGboi Z5P9QpWGg5S64bEbYIHsOo/xT3XNl0XW5RQlUO0WS5TQ9kOm5cNerkiYzrf41a1PKvQz aDhbOMNjug6hHj03fl9bLgoiilHgnbylFwspQOT6PWRCqc5DO35IPCEg3UHH3JrG9Q5a z+Jh6ClfF/ugZLOyZ+cd+KEev2MJ9i/8AI81NCC0wwt4P59WbX5bVsUKl2dfmlUmeoY/ mb1A== X-Gm-Message-State: AOJu0YymFKv/8irOZkr78n9PaehnAHsyVIiGLKlDUrdNlTx7x+Qdp2VN tlqnwww/Sj7AoU/NNTYn+ixQseglPEZlY5sp/bY7PUPxKeRkwrnjuMplVkp5RYcZgZHPM4xMsTn IEP8/W2WpwWtAFS6w1Oyu+3ROI67/opRT+Q== X-Gm-Gg: ATEYQzwzwQfnQRQEEPuxD6nMqc1ijkvKaWE/mEvGd3c3DSL06JlBdamJhtHEdYPcTI6 djWBv7kkUA+AEEe8WMxvhiTjnR3yL24y0cu2EThFRgA+eInj/uToUTwu+NShxNkigBXyzGYFITI aIlO5ZeQdVsCMUwhNMPw9QF0scsulGvPGgYJ8GmFUbAkD8VFur/WpDCroN/ZCfTHKTXVX3y2yzj 0zb3IIc7xUo+Nssiz/MLr1aRhDwcoBKIVxjloKSHgWsGD79Ydg0g07bz1b1zpRrk7+gR/5qIQO1 fS3MILQ= X-Received: by 2002:a5d:4e0c:0:b0:43b:3cea:ddca with SMTP id ffacd0b85a97d-43b527cc391mr42421f8f.51.1773768520287; Tue, 17 Mar 2026 10:28:40 -0700 (PDT) MIME-Version: 1.0 References: <85f5b5b4-e530-4458-b562-5a49a4e2ff09@aklaver.com> <0c4e4d9c-bd93-4bec-91c5-13180eb71aa8@aklaver.com> In-Reply-To: <0c4e4d9c-bd93-4bec-91c5-13180eb71aa8@aklaver.com> From: Shaheed Haque Date: Tue, 17 Mar 2026 17:28:29 +0000 X-Gm-Features: AaiRm51SdAZ39top8CDk7-GvQrzUaFIbmdogfnHpkNbR6L-V0fjqxMr_WUwUTII Message-ID: Subject: Re: Can I use pg_dump to save a sequence for a table that is not also being saved? To: Adrian Klaver Cc: pgsql-general list Content-Type: multipart/alternative; boundary="00000000000090ca51064d3baa42" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000090ca51064d3baa42 Content-Type: text/plain; charset="UTF-8" Hi Adrian, Thanks for the kind replies, but it seems my email was not very clear... I was hoping, possibly foolishly, that specifying the wildcard in "--table=public.*id_seq" would dump the matched sequences, irrespective of whether the associated table data was being dumped. Is there a way to get just the sequences? It is very possible that I am barking up the wrong tree with pg_dump, and what I need is some queries using the information_schema. Thanks, Shaheed On Tue, 17 Mar 2026 at 14:55, Adrian Klaver wrote: > On 3/17/26 7:37 AM, Adrian Klaver wrote: > > On 3/17/26 6:58 AM, Shaheed Haque wrote: > >> Hi, > >> > >> I observe when using pg_dump like this: > >> > >> pg_dump -h localhost -p 5432 -U dbcoreuser -Ft -f abc.tar --no- > >> privileges --data-only \ > >> > >> --exclude-table="public.(jobs|queues|results) \ > >> --table=public.django_migrations \ > >> --table=public.paiyroll_input \ > >> > >> --table=public.*_id_seq \ > >> > >> --verbose foo > >> > >> > >> that the dumped data contains the content of the two tables, and the > >> two sequences. (FWIW, the above command is actually submitted via a > >> Python subprocess call, so quoting should not be an issue). The > >> verbose output confirms this: > >> > >> pg_dump: processing data for table "public.django_migrations" > >> pg_dump: processing data for table "public.paiyroll_input" > >> pg_dump: executing SEQUENCE SET django_migrations_id_seq > >> pg_dump: executing SEQUENCE SET paiyroll_input_id_seq > >> > >> > >> Note that the instance "foo" contains many other tables, whose > >> sequences I was expecting to be included. To confirm this, if I drop > >> the second "--table", the verbose log shows only: > >> > >> pg_dump: processing data for table "public.django_migrations" > >> pg_dump: executing SEQUENCE SET django_migrations_id_seq > >> > >> > >> My conclusion is that - despite what I understood from the pg_dump > >> docs - the use of "--table=public.*id_seq" does not include all the > >> sequences in fo, only those named by another --table. > >> > >> Did I misunderstand, or formulate the command incorrectly? > > > > My bet is this due to a dependency of paiyroll_input_id_seq on > > public.paiyroll_input. > > > > Provide the output, in psql, of: > > > > \d public.paiyroll_input > > To demonstrate: > > CREATE TABLE seq_test ( > line_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, > bool_fld boolean, > str_fld varchar > ); > > > > \d seq_test > Table "public.seq_test" > Column | Type | Collation | Nullable | > Default > > ----------+-------------------+-----------+----------+------------------------------ > line_id | integer | | not null | generated always > as identity > bool_fld | boolean | | | > str_fld | character varying | | | > Indexes: > "seq_test_pkey" PRIMARY KEY, btree (line_id) > > SELECT pg_get_serial_sequence('public.seq_test', 'line_id'); > pg_get_serial_sequence > ----------------------------- > public.seq_test_line_id_seq > > > pg_dump -d plant_grower_db -U db_admin -p 5482 -t seq_test --data-only > > -- > -- PostgreSQL database dump > -- > > [...] > > COPY public.seq_test (line_id, bool_fld, str_fld) FROM stdin; > \. > > > -- > -- Name: seq_test_line_id_seq; Type: SEQUENCE SET; Schema: public; > Owner: db_admin > -- > > SELECT pg_catalog.setval('public.seq_test_line_id_seq', 1, false); > > [...] > > -- > -- PostgreSQL database dump complete > -- > > > pg_dump -d plant_grower_db -U db_admin -p 5482 -t seq_test_line_id_seq > --data-only > > -- > -- PostgreSQL database dump > -- > > [...] > > -- No sequence data. > > [...] > > > -- > -- PostgreSQL database dump complete > -- > > > > > >> > >> Thanks, Shaheed > > > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > --00000000000090ca51064d3baa42 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Adrian,

Thanks for the ki= nd replies, but it seems my email was not very clear...

I was hoping, possibly foolishly, that specifying the=C2=A0wildcard i= n "--table=3Dpublic.*id_seq" would dump the=C2=A0 matched sequenc= es, irrespective of whether the associated table data was being dumped.=C2= =A0Is there a way to get just the sequences?

It is= very possible that I am barking up the wrong tree with pg_dump, and what I= need is some queries=C2=A0using the information_schema.

Thanks, Shaheed

On Tue, 17 Mar 2026 at 14= :55, Adrian Klaver <adrian.= klaver@aklaver.com> wrote:
On 3/17/26 7:37 AM, Adrian Klaver wrote:
> On 3/17/26 6:58 AM, Shaheed Haque wrote:
>> Hi,
>>
>> I observe when using pg_dump like this:
>>
>> =C2=A0=C2=A0=C2=A0 pg_dump -h localhost -p 5432 -U dbcoreuser -Ft = -f abc.tar --no-
>> =C2=A0=C2=A0=C2=A0 privileges --data-only \
>>
>> =C2=A0=C2=A0=C2=A0 --exclude-table=3D"public.(jobs|queues|res= ults) \
>> =C2=A0=C2=A0=C2=A0 --table=3Dpublic.django_migrations \
>> =C2=A0=C2=A0=C2=A0 --table=3Dpublic.paiyroll_input \
>>
>> =C2=A0=C2=A0=C2=A0 --table=3Dpublic.*_id_seq \
>>
>> =C2=A0=C2=A0=C2=A0 --verbose foo
>>
>>
>> that the dumped data contains the content of the two tables, and t= he
>> two sequences.=C2=A0 (FWIW, the above command is actually submitte= d via a
>> Python subprocess=C2=A0call, so quoting should not be an issue).= =C2=A0The
>> verbose output confirms this:
>>
>> =C2=A0=C2=A0=C2=A0 pg_dump: processing data for table "public= .django_migrations"
>> =C2=A0=C2=A0=C2=A0 pg_dump: processing data for table "public= .paiyroll_input"
>> =C2=A0=C2=A0=C2=A0 pg_dump: executing SEQUENCE SET django_migratio= ns_id_seq
>> =C2=A0=C2=A0=C2=A0 pg_dump: executing SEQUENCE SET paiyroll_input_= id_seq
>>
>>
>> Note that the instance "foo" contains many other tables,= whose
>> sequences I was expecting to be included. To confirm this, if I dr= op
>> the second "--table", the verbose log shows only:
>>
>> =C2=A0=C2=A0=C2=A0 pg_dump: processing data for table "public= .django_migrations"
>> =C2=A0=C2=A0=C2=A0 pg_dump: executing SEQUENCE SET django_migratio= ns_id_seq
>>
>>
>> My conclusion is that - despite what I understood=C2=A0from the pg= _dump
>> docs - the use of "--table=3Dpublic.*id_seq" does not in= clude all the
>> sequences in fo, only those named by another --table.
>>
>> Did I misunderstand, or formulate the command incorrectly?
>
> My bet is this due to a dependency of paiyroll_input_id_seq on
> public.paiyroll_input.
>
> Provide the output, in psql, of:
>
> \d public.paiyroll_input

To demonstrate:

CREATE TABLE seq_test (
=C2=A0 =C2=A0 =C2=A0line_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KE= Y,
=C2=A0 =C2=A0 =C2=A0bool_fld boolean,
=C2=A0 =C2=A0 =C2=A0str_fld varchar
);



\d seq_test
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Table "public.seq_test"
=C2=A0 =C2=A0Column=C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0Type=C2=A0 =C2=A0 =C2= =A0 =C2=A0 | Collation | Nullable |
Default
----------+-------------------+-----------+----------+---------------------= ---------
=C2=A0 line_id=C2=A0 | integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| not null | generated always
as identity
=C2=A0 bool_fld | boolean=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
=C2=A0 str_fld=C2=A0 | character varying |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
Indexes:
=C2=A0 =C2=A0 =C2=A0"seq_test_pkey" PRIMARY KEY, btree (line_id)<= br>
SELECT pg_get_serial_sequence('public.seq_test', 'line_id')= ;
=C2=A0 =C2=A0 pg_get_serial_sequence
-----------------------------
=C2=A0 public.seq_test_line_id_seq


pg_dump -d plant_grower_db -U db_admin -p 5482 -t seq_test --data-only

--
-- PostgreSQL database dump
--

[...]

COPY public.seq_test (line_id, bool_fld, str_fld) FROM stdin;
\.


--
-- Name: seq_test_line_id_seq; Type: SEQUENCE SET; Schema: public;
Owner: db_admin
--

SELECT pg_catalog.setval('public.seq_test_line_id_seq', 1, false);<= br>
[...]

--
-- PostgreSQL database dump complete
--


pg_dump -d plant_grower_db -U db_admin -p 5482 -t seq_test_line_id_seq
--data-only

--
-- PostgreSQL database dump
--

[...]

-- No sequence data.

[...]


--
-- PostgreSQL database dump complete
--


>
>>
>> Thanks, Shaheed
>
>


--
Adrian Klaver
adrian.klave= r@aklaver.com
--00000000000090ca51064d3baa42--