Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 0AF67184B5A9 for ; Wed, 30 May 2012 12:14:43 -0300 (ADT) Received: from mail-gh0-f174.google.com ([209.85.160.174]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SZkbg-0007KS-6H for pgsql-admin@postgresql.org; Wed, 30 May 2012 15:14:42 +0000 Received: by ghrr11 with SMTP id r11so3538524ghr.33 for ; Wed, 30 May 2012 08:14:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:to:cc:references:in-reply-to:subject:date:message-id :mime-version:content-type:x-mailer:content-language:thread-index; bh=HEbxGva4KNt4Ao4clY5JHDNEUd9767UlXOdEAP+esbY=; b=vpDQHa62BSjP9xkkBsU+M1Op4OyXeI1MFv4rH8c+tlVrNB2fJYTa4Xfk0eD6+cChsg y1CtsqEhWtv1e2WHzTkAetwXqKC/qSpKI9MoUcG0g/ejt8RhcYzYEs9nNIxag0KE23y8 N31vrtK5aPhy+osL7uU8+103o6Rwxv8IGXFenhNKgapoLzFB3XRcb9jwxGdxOlpPnfg8 ky5RpparRHO33yvQxWbCKObQYlNyms2wl+6pvB6IkVX7QOiUifxY3WLGka2oZTUlkhvM 3w2KSxUju/LuzIn2bIMuzFborZKJcxbXbcazF9O8I/8fKBwXPKBcSUiUk2tCnw/l5XL/ GLmw== Received: by 10.50.170.69 with SMTP id ak5mr7600813igc.47.1338390866381; Wed, 30 May 2012 08:14:26 -0700 (PDT) Received: from Tower (CPE001c1008f624-CM0011aec4f376.cpe.net.cable.rogers.com. [174.118.17.120]) by mx.google.com with ESMTPS id ut5sm18090382igc.13.2012.05.30.08.14.23 (version=TLSv1/SSLv3 cipher=OTHER); Wed, 30 May 2012 08:14:24 -0700 (PDT) From: "Igor Shmain" To: "'Haifeng Liu'" Cc: References: <000901cd3dd7$8654a690$92fdf3b0$@gmail.com> In-Reply-To: Subject: Re: Data split -- Creating a copy of database without outage Date: Wed, 30 May 2012 11:14:02 -0400 Message-ID: <00b101cd3e76$d9f04e40$8dd0eac0$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00B2_01CD3E55.52E209A0" X-Mailer: Microsoft Outlook 14.0 Content-Language: en-ca Thread-Index: AQKV3/IsTUoVocuh2ViALPGAauRL7gGNpQIClUS52wA= X-Pg-Spam-Score: -1.6 (-) X-Archive-Number: 201205/99 X-Sequence-Number: 39588 This is a multipart message in MIME format. ------=_NextPart_000_00B2_01CD3E55.52E209A0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Thank you, Liu, for your suggestion. I might be missing something (I am new to postgres), but it seems that your suggestion will not help much in my case. Since the number of db requests will grow with time (more users will come into the system), the current server will become incapable of serving all the requests quickly enough. The idea is to increase overall calculation capacity of the system by adding more servers to it. Throwing more hardware to a single main server is not an option in my case. Creating multiple replicas (slaves) is also not an good option - it would be way more efficient to have a group of db servers, each serving only some subset of users and hosting data for those users only. Buying new servers in advance is not an option too. What I am looking for is switching some of the users to another db server when the capacity of the existing server(s) is not enough. The point is to do it without interrupting the users' work (so they do not see that horrible "Sorry! This site is under maintenance."). If I missed something it would be very kind of you to point this out. Thank you once again, -igorS From: Haifeng Liu [mailto:liuhaifeng@live.com] Sent: May-29-12 9:13 PM To: Igor Shmain Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Data split -- Creating a copy of database without outage Why not use a partitioned table? You can write a trigger to control which partition the coming data should be inserted. Regards, ---- Liu Haifeng Home: http://liuhaifeng.com On May 30, 2012, at 4:13 AM, Igor Shmain wrote: Can you please help with advice? I need to design a solution for a database which will grow and will require a horizontal split at some moment. Here is how I am planning to do it: Every record in every table has a shard number. After the database becomes too large (gets too many requests), the tables need to be horizontally split. It means that for every table all records with some specific shard numbers need to be moved to a new database. My plan is to (1) create a copy of the database on a new server (backup/restore?), (2) synchronize the databases (how?), and then (3) forward all requests for the moved shards to the new database. (I will get rid of the unneeded shards later). An important requirement: there should be no outage for the users. What are the ways to achieve it? Thank you in advance, -igorS ------=_NextPart_000_00B2_01CD3E55.52E209A0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Thank you, Liu, for your suggestion.

 

I might be missing something (I am new to postgres), but it seems = that your suggestion will not help much in my case.  Since the = number of db requests will grow with time (more users will come into the = system), the current server will become incapable of serving all the = requests quickly enough.

 

The idea is to increase overall calculation capacity of the system by = adding more servers to it. Throwing more hardware to a single main = server is not an option in my case. Creating multiple replicas (slaves) = is also not an good option – it would be way more efficient to = have a group of db servers, each serving only some subset of users and = hosting data for those users only. Buying new servers in advance is not = an option too.

 

What I am looking for is switching some of the users to another db = server when the capacity of the existing server(s) is not enough. The = point is to do it without interrupting the users’ work (so they do = not see that horrible “Sorry! This site is under = maintenance…”).

 

If I missed something it would be very kind of you to point this = out.

 

Thank you once again,

-igorS

 

 

From:= Haifeng = Liu [mailto:liuhaifeng@live.com]
Sent: May-29-12 9:13 = PM
To: Igor Shmain
Cc: = pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Data split -- = Creating a copy of database without = outage

 

Why not use = a partitioned table? You can write a trigger to control which partition = the coming data should be inserted.

 

Regards,

----

Liu Haifeng

 



 

On = May 30, 2012, at 4:13 AM, Igor Shmain wrote:



Can you = please help with advice?

I need to = design a solution for a database which will grow and will require a = horizontal split at some moment.

Here is = how I am planning to do it: Every record in every table has a shard = number. After the database becomes too large (gets too many requests), = the tables need to be horizontally split. It means that for every table = all records with some specific shard numbers need to be moved to a new = database.

My plan is = to (1) create a copy of the database on a new server (backup/restore?), = (2) synchronize the databases (how?), and then (3) forward all requests = for the moved shards to the new database. (I will get rid of the = unneeded shards later). An important requirement: there should be no = outage for the users.

What are = the ways to achieve it?

 =

Thank you = in advance,
-igorS

 =

 

------=_NextPart_000_00B2_01CD3E55.52E209A0--