We are developing a new software system which is now used by a number of independent clients for gathering and storing live data as part of their day to day work. We have a number of clients sharing a single server. It is running one Postgres service, and each client is a separate user with access to their own database. Each client's database will contain "hundreds of thousands" of records, and will be supporting occasional queries by a small number of users. So the system is currently running on "modest" hardware. To guard against the server failing, we have a standby server being updated by WAL files, so if the worst comes to the worst we'll only lose "a few minutes" work. No problems there. But, at least while the system is under rapid development, we also want to have a way to roll a particular client's database back to a (recent) "known good" state, but without affecting any other client. My understanding is that the WAL files mechanism is installation-wide -- it will affect all clients alike. So to allow us to restore data for an individual client, we're running "pg_dump" once an hour on each database in turn. In the event of a problem with one client's system, we can restore just that one database, without affecting any other client. The problem is that we're finding that as the number of clients grows, and with it the amount of data, pg_dump is becoming more intrusive. Our perception is that when pg_dump is running for any database, performance on all databases is reduced. I'm guessing this is because the dump is making heavy use of the disk. There is obviously scope for improving performance by getting using more, or more powerful, hardware. That's obviously going to be necessary at some point, but it is obviously an expense that our client would like to defer as long as possible. So before we go down that route, I'd like to check that we're not doing something dopey. Is our current "frequent pg_dump" approach a sensible way to go about things. Or are we missing something? Is there some other way to restore one database without affecting the others? Thanks in advance. Robert. -- Robert Inder, Interactive Information Ltd, 3, Lauriston Gardens, Edinburgh EH3 9HH Registered in Scotland, Company no. SC 150689 Interactions speak louder than words -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general