On Wed, Mar 13, 2013 at 4:01 AM, <krishnakumar.eeee@gmail.com> wrote:
> Hi,
> I'm using PostgreSQL 9.0.1 and driver PostgreSQL 9.0 JDBC4 and my OS
> is Ubuntu.I'm using serial type for auto-incrementing column id in my table
> everything works fine .when i stop my application and restart the
> application its fine but when i backup DB and then restore it back manually
> i'm inserting id column by myself during restoring...after restore If i
> start the application, the id column doesn't start incrementing from maximum
> value of id instead starts from begining (i.e., 1) and getting duplicate key
> value violates unique constraint "smsserver_out_pkey" Detail: Key (id)=(1)
> already exists till upto restored number of entries. ...what is the solution
> to over come this issue ...
[snip]
> My Insert Query for restoring the database
> INSERT INTO
>
smsserver_out(id,type,recipient,text,wap_url,wap_expiry_date,wap_signal,create_date,originator,encoding,status_report,flash_sms,src_port,dst_port,sent_date,ref_no,priority,status,errors,gateway_id)
Well, you didn't specify how exactly you are backing up and restoring
the table, although from the INSERT snippet you showed it looks like
you're not using pg_dump. If you were using pg_dump, it would handle
resetting the sequence behind smsserver_out.id to its value at the
time of the dump. If you are using some other method to backup and
reload the table, you will have to include a call to setval() to set a
sane value for the sequence. For the record, reimplementing pg_dump is
usually a bad idea.
Josh