Re: how to adjust auto increment id offset?

From: John R Pierce <pierce(at)hogranch(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: how to adjust auto increment id offset?
Date: 2011-11-15 09:12:11
Message-ID: 4EC22CEB.1070604@hogranch.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 11/15/11 12:56 AM, Bèrto ëd Sèra wrote:
> Hi
>
> On 15 November 2011 11:44, Yan Chunlu <springrider(at)gmail(dot)com
> <mailto:springrider(at)gmail(dot)com>> wrote:
>
> I would like to implement two master db with even-odd id sharding.
> in mysql it is fairly easy by using the configuration:
>
> auto_increment_offset = 1
> auto_increment_increment = 2
>
>
> but I have searched a lot didn't find anything related to this,
> some users doing this via trigger like "rubyrep".
>
> is there an easy way to do this? thanks!
>
>
> http://www.postgresql.org/docs/8.1/static/sql-createsequence.html
>
>

also see ALTER SEQUENCE.

basically, you'll need to fix up every sequence (these are created
automatically if you have fields of type SERIAL).... on your 2nd server,

ALTER SEQUENCE somesequencename INCREMENT BY 2 RESTART WITH 2;

and on your 1st server,

ALTER SEQUENCE somesequencename INCREMENT BY 2;

do this before inserting any data.

thats a fairly unusual sharding technique, how do you plan on doing
queries across both sets of data?

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrew Sullivan 2011-11-15 10:30:07 Re: how to adjust auto increment id offset?
Previous Message Tarlika Elisabeth Schmitz 2011-11-15 08:59:28 all non-PK columns from information schema