Export Postgres sequences only

All we need is an easy explanation of the problem, so here it is.

Is there a way using PG_DUMP or any other commands to export only sequences from an existing database?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I’m not sure what you mean by "export sequences".

I suppose that you mean to extract all statements as:

CREATE SEQUENCE [sequence name] ....;

ALTER SEQENCE [sequence name] OWNED BY 

If that’s so than I would do pg_dump of db and then parse extract all sequence related DDL-statements.

E.g.

# CREATE SEQUENCE
$  pg_restore db.dump --section=pre-data  -f - | awk  '/CREATE SEQUENCE/ {print}' FS="\n" RS=""
CREATE SEQUENCE public.address_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
...

# ALTER SEQUENCE
$ pg_restore db.dump --section=pre-data  -f - | awk  '/ALTER SEQUENCE/ {print}' FS="\n"       
ALTER SEQUENCE public.address_id_seq OWNED BY public.address.id;
....

Note: gawk 5.1.0 was used.

Possible alterations:

  • db dump file is not required – redirect output of pg_dump directly to awk
  • no need to dump data. pg_dump --schema-only will be enough

Hope this helps

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply