Re: Can I use pg_dump to save a sequence for a table that is not also being saved? - Mailing list pgsql-general

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