FAQ
Hi All,

I am using a sequence in a table in my database. There are large nos of
insertion and deletion operations are happening on the table.
When the sequence overflows I need to recreate the database.
While restoring the database I want this sequence column to start from
again from 0 and fill all holes created by deletion on db. But all other
columns should remain unchanged.
I am backing up my database using pg_dump and recreating it using
pg_restore.
Is there any way I can tell pg_dump not to backup values for this sequence
column and create them afresh while restoring?

Thanks
Abhinav
--
View this message in context: http://www.nabble.com/how-to-ignore-a-column-from-pg_dump-tp24573351p24573351.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Search Discussions

  • Michael Wood at Jul 20, 2009 at 7:43 pm
    2009/7/20 Abhi_m <talk2abhinav@gmail.com>:
    Hi All,

    I am using a sequence in a table in my database. There are large nos of
    insertion and deletion operations are happening on the table.
    When the sequence overflows I need to recreate the database.
    While restoring the database I want this sequence column to start  from
    again from 0 and fill all holes created by deletion on db. But all other
    columns should remain unchanged.
    I am backing up my database using pg_dump and recreating it using
    pg_restore.
    Is there any way I can tell pg_dump not to backup values for this sequence
    column and create them afresh while restoring?
    Another way to do this would be to create another table that is
    identical to the first and then select the data out of the first one
    into the second one. Then drop the first one and rename the second to
    the first.

    e.g.:

    x=> create table one (id serial primary key, name text);
    NOTICE: CREATE TABLE will create implicit sequence "one_id_seq" for
    serial column "one.id"
    NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
    "one_pkey" for table "one"
    CREATE TABLE
    x=> insert into one (name) values ('fred');
    INSERT 0 1
    x=> insert into one (name) values ('bob');
    INSERT 0 1
    x=> insert into one (name) values ('mary');
    INSERT 0 1
    x=> insert into one (name) values ('joe');
    INSERT 0 1
    x=> insert into one (name) values ('sue');
    INSERT 0 1
    x=> delete from one where id = 3 or id = 4;
    DELETE 2
    x=> insert into one (name) values ('gill');
    INSERT 0 1
    x=> select * from one;
    id | name
    ----+------
    1 | fred
    2 | bob
    5 | sue
    6 | gill
    (4 rows)

    x=> create table two (id serial primary key, name text);
    NOTICE: CREATE TABLE will create implicit sequence "two_id_seq" for
    serial column "two.id"
    NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
    "two_pkey" for table "two"
    CREATE TABLE
    x=> insert into two (name) select name from one;
    INSERT 0 4
    x=> select * from two;
    id | name
    ----+------
    1 | fred
    2 | bob
    3 | sue
    4 | gill
    (4 rows)

    x=> drop table one;
    DROP TABLE
    x=> alter table two rename to one;
    ALTER TABLE
    x=> \dt
    List of relations
    Schema | Name | Type | Owner
    --------+------+-------+---------
    public | one | table | x
    (1 row)

    Of course your indexes etc. will now not be named after your table,
    but you can rename them too if want to:

    x=> \d one
    Table "public.one"
    Column | Type | Modifiers
    --------+---------+--------------------------------------------------
    id | integer | not null default nextval('two_id_seq'::regclass)
    name | text |
    Indexes:
    "two_pkey" PRIMARY KEY, btree (id)

    x=> \ds
    List of relations
    Schema | Name | Type | Owner
    --------+------------+----------+---------
    public | two_id_seq | sequence | x
    (1 row)

    x=> \di
    List of relations
    Schema | Name | Type | Owner | Table
    --------+----------+-------+---------+-------
    public | two_pkey | index | x | one
    (1 row)

    x=>

    --
    Michael Wood <esiotrot@gmail.com>

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJul 20, '09 at 4:45p
activeJul 20, '09 at 7:43p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Michael Wood: 1 post Abhi_m: 1 post

People

Translate

site design / logo © 2022 Grokbase