Skip site navigation (1) Skip section navigation (2)

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

From: Amador Alvarez <aalvarez(at)d2(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: Data split -- Creating a copy of database without outage
Date: 2012-05-30 17:12:17
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
Hi ,

I would start with a single high performance tuned database focusing 
mainly on dealing efficiently with concurrent activity and identifying 
the real hot spots.
If you check out that you really need to go forward on database power, 
consider on adding  new databases and relocate some users whenever you 
need it.

Take a look on the different options related to replication, tunning and 
database balancers, set up some testings and get a deeper understanding 
on your real needs and performance.


On 05/30/2012 08:14 AM, Igor Shmain wrote:
> 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:
> 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

In response to

pgsql-admin by date

Next:From: Igor ShmainDate: 2012-05-31 03:02:18
Subject: Re: Data split -- Creating a copy of database without outage
Previous:From: Craig JamesDate: 2012-05-30 15:38:13
Subject: Re: Data split -- Creating a copy of database without outage

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group