postgres.email
❮

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

  • Jump to comment-1
    Shaheed Haque<shaheedhaque@gmail.com>
    Mar 17, 2026, 1:58 PM UTC
    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.*idseq \
    --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:
    pgdump: processing data for table "public.djangomigrations"
    pgdump: processing data for table "public.paiyrollinput"
    pgdump: executing SEQUENCE SET djangomigrationsidseq
    pgdump: executing SEQUENCE SET paiyrollinputidseq
    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:
    pgdump: processing data for table "public.djangomigrations"
    pgdump: executing SEQUENCE SET djangomigrationsidseq
    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?
    Thanks, Shaheed
    View in PostgreSQL Archives →
    • Jump to comment-1
      Adrian Klaver<adrian.klaver@aklaver.com>
      Mar 17, 2026, 2:40 PM UTC
      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.djangomigrations \ > > --table=public.paiyrollinput \
      --table=public.*idseq \
      --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:
      pgdump: processing data for table "public.djangomigrations"
      pgdump: processing data for table "public.paiyrollinput"
      pgdump: executing SEQUENCE SET djangomigrationsidseq
      pgdump: executing SEQUENCE SET paiyrollinputidseq
      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:
      pgdump: processing data for table "public.djangomigrations"
      pgdump: executing SEQUENCE SET djangomigrationsidseq
      My conclusion is that - despite what I understood from the pgdump docs > - the use of "--table=public.*idseq" 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 paiyrollinputidseq on public.paiyrollinput.
      Provide the output, in psql, of:
      \d public.paiyroll_input
      Thanks, Shaheed
      --
      Adrian Klaver
      adrian.klaver@aklaver.com
      View in PostgreSQL Archives →
      • Jump to comment-1
        Adrian Klaver<adrian.klaver@aklaver.com>
        Mar 17, 2026, 2:55 PM UTC
        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.*idseq \

        --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:

        pgdump: processing data for table "public.djangomigrations"
        pgdump: processing data for table "public.paiyrollinput"
        pgdump: executing SEQUENCE SET djangomigrationsidseq
        pgdump: executing SEQUENCE SET paiyrollinputidseq

        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:

        pgdump: processing data for table "public.djangomigrations"
        pgdump: executing SEQUENCE SET djangomigrationsidseq

        My conclusion is that - despite what I understood from the pgdump >> docs - the use of "--table=public.*idseq" 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 paiyrollinputidseq on > public.paiyrollinput.
        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"
        ColumnTypeCollationNullableDefault
        line_idintegernot nullgenerated always as identity
        bool_fldboolean
        str_fldcharacter 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
        pgdump -d plantgrowerdb -U dbadmin -p 5482 -t seq_test --data-only
        --
        -- PostgreSQL database dump
        --
        [...]
        COPY public.seqtest (lineid, boolfld, strfld) FROM stdin;
        \.
        --
        -- Name: seqtestlineidseq; Type: SEQUENCE SET; Schema: public; Owner: db_admin
        --
        SELECT pg_catalog.setval('public.seq_test_line_id_seq', 1, false);
        [...]
        --
        -- PostgreSQL database dump complete
        --
        pgdump -d plantgrowerdb -U dbadmin -p 5482 -t seqtestlineidseq --data-only
        --
        -- PostgreSQL database dump
        --
        [...]
        -- No sequence data.
        [...]
        --
        -- PostgreSQL database dump complete
        --
        Thanks, Shaheed
        -- Adrian Klaver
        adrian.klaver@aklaver.com
        View in PostgreSQL Archives →
        • Jump to comment-1
          Shaheed Haque<shaheedhaque@gmail.com>
          Mar 17, 2026, 5:28 PM UTC
          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 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.*idseq \

          --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:

          pgdump: processing data for table "public.djangomigrations"
          pgdump: processing data for table "public.paiyrollinput"
          pgdump: executing SEQUENCE SET djangomigrationsidseq
          pgdump: executing SEQUENCE SET paiyrollinputidseq


          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:

          pgdump: processing data for table "public.djangomigrations"
          pgdump: executing SEQUENCE SET djangomigrationsidseq


          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 paiyrollinputid_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:
          "seqtestpkey" PRIMARY KEY, btree (line_id)

          SELECT pggetserialsequence('public.seqtest', 'line_id');
          pggetserial_sequence
          -----------------------------
          public.seqtestlineidseq


          pgdump -d plantgrowerdb -U dbadmin -p 5482 -t seq_test --data-only

          --
          -- PostgreSQL database dump
          --

          [...]

          COPY public.seqtest (lineid, boolfld, strfld) FROM stdin;
          \.


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

          SELECT pgcatalog.setval('public.seqtestlineid_seq', 1, false);

          [...]

          --
          -- PostgreSQL database dump complete
          --


          pgdump -d plantgrowerdb -U dbadmin -p 5482 -t seqtestlineidseq
          --data-only

          --
          -- PostgreSQL database dump
          --

          [...]

          -- No sequence data.

          [...]


          --
          -- PostgreSQL database dump complete
          --


          Thanks, Shaheed



          --
          Adrian Klaver
          adrian.klaver@aklaver.com
          View in PostgreSQL Archives →
          • Jump to comment-1
            Adrian Klaver<adrian.klaver@aklaver.com>
            Mar 17, 2026, 5:43 PM UTC
            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 pgdump, > and what I need is some queries using the informationschema.
            I'm going to say yes you will need to find another method. It would help to know exactly what it is you want.
            Thanks, Shaheed
            -- > Adrian Klaver
            adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
            View in PostgreSQL Archives →