Feedback-ID: i76984098:Fastmail
Message-ID: <0c4e4d9c-bd93-4bec-91c5-13180eb71aa8@aklaver.com>
Date: Tue, 17 Mar 2026 07:55:36 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Can I use pg_dump to save a sequence for a table that is not also
 being saved?
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Shaheed Haque <shaheedhaque@gmail.com>,
 pgsql-general list <pgsql-general@lists.postgresql.org>
References: <CAHAc2jdyYM4U3Ts6Hed8GVYWMgNPo=kaBc3QpC5XoHEZ_5YAag@mail.gmail.com>
 <85f5b5b4-e530-4458-b562-5a49a4e2ff09@aklaver.com>
Content-Language: en-US
In-Reply-To: <85f5b5b4-e530-4458-b562-5a49a4e2ff09@aklaver.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/0c4e4d9c-bd93-4bec-91c5-13180eb71aa8%40aklaver.com>
Precedence: bulk

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