Thread: pg_dump & restore question regarding creating with seri al

pg_dump & restore question regarding creating with seri al

From
Pam Wampler
Date:

Josh
thanks for replying but please let me clarify:

my original pg_dump has records with the id from 1 to 644714794.
my "replica" database started at 4064868  (where did that number come from?
--
there is not an id of 1 in the database)
I am rerunning it now -- to see if it happens again
when I run select * from inv_trans_seq_id
i see this:
mcs=# select * from inv_trans_id_seq;
  sequence_name   | last_value | increment_by |      max_value      |
min_value | cache_value | log_cnt | is_cycled | is_called
------------------+------------+--------------+---------------------+-------
----+-------------+---------+-----------+-----------
 inv_trans_id_seq |          1 |            1 | 9223372036854775807 |
1 |           1 |       1 | f         | f
(1 row)

which leads me to believe that the first record will have 1 as it's id

what I saw in the first try was 4064868????


I understand what is going on in sequences -- but I don't understand what I
am seeing.

thanks

Pam
-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Friday, October 04, 2002 3:14 PM
To: Pam Wampler; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] pg_dump & restore question regarding creating with
serial



Pam,

> I am trying to make a copy of one database to another.  I have done the
> pg_dump and then psql -f filename into new database-- but when I look at a
> table that has been created with (id serial primary key) -- -- on the new
> database the id does not start at 1 but the number greater than the last
id
> of the original table.  How can I make the id start at 1  -- I would have
> thought that the initial load into the new database would be an exact
> replica of the original database?

It *is* an exact replica ... including having the sequence for the table be
at
whatever number the original database is at.  This is intentional and
desired
behaviour.

>  id           | integer                     | not null default
> nextval('"inv_trans_id_seq"'::text)

After rebuilding the database, run:
SELECT SETVAL('inv_trans_id_seq', 1);

HOWEVER, I think you need some comprehension of Postgres sequences.
Fortunately, Justin has created a excellent animated flash tutorial on
sequences; see:
http://techdocs.postgresql.org/college/001_sequences/index.php

--
Josh Berkus
josh@agliodbs.com
Aglio Database Solutions
San Francisco

Re: pg_dump & restore question regarding creating with seri al

From
Josh Berkus
Date:
Pam,

> my original pg_dump has records with the id from 1 to 644714794.
> my "replica" database started at 4064868  (where did that number come from?

I'm not sure either.   Questions:

1. Are you dumping just the database schema, or the schema and the data?

2. How were records inserted into the table originally?  That is, is it
possible that many/all of the records were inserted without using the
sequence as their id, but getting their ID numbers from some external source?

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco