Обсуждение: how to adjust auto increment id offset?
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!
Hi
http://www.postgresql.org/docs/8.1/static/sql-createsequence.html
--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
On 15 November 2011 11:44, Yan Chunlu <springrider@gmail.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 = 1auto_increment_increment = 2but 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
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
On 11/15/11 12:56 AM, Bèrto ëd Sèra wrote: > Hi > > On 15 November 2011 11:44, Yan Chunlu <springrider@gmail.com > <mailto:springrider@gmail.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
On Tue, Nov 15, 2011 at 04:44:23PM +0800, Yan Chunlu 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! Why not adjust the underlying sequences to have different start values and to advance by 2? A -- Andrew Sullivan ajs@crankycanuck.ca
thanks a lot for the tip!
sorry for used the wrong word, it is just multi-master but not sharding, I would like to setup two master server across two datacenter. one's id increased by 1, and the other by 2.
so I could have a queue sync the record in the background by myself. kind of a dumb way but it seems they only choice for me, the delay within several minutes is acceptable.
On Tue, Nov 15, 2011 at 5:12 PM, John R Pierce <pierce@hogranch.com> wrote:
On 11/15/11 12:56 AM, Bèrto ëd Sèra wrote:Hi
On 15 November 2011 11:44, Yan Chunlu <springrider@gmail.com <mailto:springrider@gmail.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Tue, Nov 15, 2011 at 9:03 PM, Yan Chunlu <springrider@gmail.com> wrote: > thanks a lot for the tip! > sorry for used the wrong word, it is just multi-master but not sharding, I > would like to setup two master server across two datacenter. one's id > increased by 1, and the other by 2. > so I could have a queue sync the record in the background by myself. kind > of a dumb way but it seems they only choice for me, the delay within > several minutes is acceptable. you might want to look at either using a larger skip, like 10 or 20, so you can add more servers at a later date if you need to. The other way is to start each sequence at some huge offset like 2Billion and be sure to use a bigint not a regular int.