Re: Data split -- Creating a copy of database without outage

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(at)live(dot)com]
Sent: May-29-12 9:13 PM
To: Igor Shmain
Cc: pgsql-admin(at)postgresql(dot)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