Can I use pg_dump to save a sequence for a table that is not also being saved?

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

Thanks, Shaheed

--
Adrian Klaver
adrian.klaver@aklaver.com

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

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 <adrian.klaver@aklaver.com>
wrote:

On 3/17/26 10:28 AM, Shaheed Haque wrote:

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?

1) You are using --data-only which means you won't get the sequence
definition, only the statement to set the sequence value:

SELECT pg_catalog.setval('public.seq_test_line_id_seq', 1, false);

If the sequence is not already in the database instance the above will fail.

2) Sequences are an object that can be created as a stand alone which
you then use as needed or more generally these days as a dependent
object to a serial 'type' or a GENERATED ALWAYS AS IDENTITY attribute to
a column. In the latter cases the table needs to exist for the sequence
to have any relevance. In your case I'm thinking that the sequences you
want are tied to tables and in the case where you don't dump the
associated table the sequence data(pg_catalog.setval(...)) is not dumped
as there is no point.

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.

I'm going to say yes you will need to find another method. It would help
to know exactly what it is you want.