Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

#429 Dumping and restoring sequences

1.3 Dev Q
open
Koichi Suzuki
Utilities (9)
5
2013-12-04
2013-07-02
Abbas Butt
No

Consider this test case

Step 1: Create a sequence and create a table that uses the sequence.

CREATE SEQUENCE my_sequence increment by 10 start with 100;
create table test_sequence(a int default nextval('my_sequence'), b int) distribute by modulo(b);
insert into test_sequence values(DEFAULT, 1);
insert into test_sequence values(DEFAULT, 2);

Step 2: Dump the database
./pg_dump --file=/some/valid/path/ts.sql test

Step 3: Create a new database say ./createdb test2

Step 4: Restore the dump on the new database
./psql -d test2 -f /some/valid/path/ts.sql

Step 5: Insert a row in the same table in the new database and check the sequence value
./psql test2

test2=# insert into test_sequence values(DEFAULT, 3);
INSERT 0 1
test2=# select * from test_sequence;
a | b
-----+---
100 | 1
110 | 2
130 | 3 <=========== Notice that one sequence value is skipped
(3 rows)

Discussion

  • Koichi Suzuki
    Koichi Suzuki
    2013-12-02

    Should check PG behavior first.

     
  • Koichi Suzuki
    Koichi Suzuki
    2013-12-02

    • Group: 1.2 Dev Q --> 1.3 Dev Q
     
  • Koichi Suzuki
    Koichi Suzuki
    2013-12-04

    • assigned_to: Koichi Suzuki
     
  • Koichi Suzuki
    Koichi Suzuki
    2013-12-04

    Check GTM code if GTM restart point is backed up correctly.