Обсуждение: Basic Question on Point In Time Recovery

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

On Wed, Mar 11, 2015 at 10:54:56AM +0000, Robert Inder wrote:
> 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.

Right.  It's the WAL, so everything in the relevant Postgres system is
involved.

> So before we go down that route, I'd like to check that we're not
> doing something dopey.

No, frequent pg_dumps are indeed hard on I/O.

> 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?

Slony-I, which is a PITA to administer, has a mode where you can ship
logs off and restore them in pieces.  The logs are not WAL, but Slony
logs (which are produced by triggers and some explicit event writing
for schema changes).  So they work per-database.  Schema changes are
really quite involved for Slony, and there's overhead resulting from
the triggrs, and as I said it's rather clunky to administer.  But it's
been around some time, it still is actively maintained, and it has
this functionality.  The PITR tools were, last I checked, pretty
primitive.  But the tool might work for your case.  I don't know
whether Bucardo or Londiste (two alternative systems that work on
roughly the same principle) have this functionality, but I kind of
doubt it since both were designed to get rid of several of the
complexities that Slony presented.  (Slony had all those complexities
because it was trying to offer all this functionality at once.)

Best regards,

A


--
Andrew Sullivan
ajs@crankycanuck.ca

Hello,

On 11/03/2015 11:54, Robert Inder wrote:
> 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.

One way you could choose is to have a server acting as WAL archiver.

pg_basebackup your slave every day, and store all WAL until new pg_basebackup
is taken.

Whenever you have to restore a single customer, you could recover the whole
instance up to the time *before* the worst happend and pg_dump the customer,
and pg_restore it.

Doing that, you won't have to pg_dump avery one hour or so all of your databases.



>
> 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.
>


--
Stéphane Schildknecht
Contact régional PostgreSQL pour l'Europe francophone
Loxodata - Conseil, expertise et formations
06.17.11.37.42

On Wed, Mar 11, 2015 at 09:40:09AM -0400, Joseph Kregloh wrote:
> Have you looked into Barman? http://www.pgbarman.org/ It does what you
> want.  You can take a full daily backup and it keeps track of the WAL files
> to allow for a PITR.

I just had a look at the documentation (and the rest of your mail),
and this doesn't actually seem to do what the OP wanted, which is to
get PITR _per database_ in the same cluster.  Upthread someone
suggested a way around this, which is to PITR a cluster to a
known-good point and then pg_dump the target database.  But if Barman
can do this automatically, that'd be cool (it's just not in the docs).

Barman does look like a nice convenience package for managing
WAL-shipping type backup installations instead of building one's own
scripts, so this note isn't intended as a criticism of the package.
I'm just not sure it does the thing requested in this case.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

Thanks for your comments (so far:-)

I guess I'm pleased that nobody has said that I'm doing something stupid!

I'll certainly look at Slony and Barman.

And Stephane's suggestion of doing regular basebackups and keeping the
WAL files seems neat.  If I under stand it, we'd use the/a standby
server to "replay" the entire installation up to the point when the
problem occurs, and then use pg_dump to dump just the database we
need.

I'm wondering just how the size of a day's worth of WAL files would
compare to a whole slew of hourly dumps.

The other issue would be how long the "replay" would take.  But, I
realise, that's not a major concern: the delay would only be seen by a
client that
had had a major problem.  Everyone else would see service as normal.

I think I'll be doing some experiments to find out:-)

Robert.

--
Robert Inder,
Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
                                           Interactions speak louder than words

Hi, Francisco,

On 11 March 2015 at 17:32, Francisco Olarte <folarte@peoplecall.com> wrote:

> This is, build an streaming replication slave, pg_dump from the slave. If
> needed, restore in the master.

I really like the idea of running pg_dump on the slave, but I don't understand
how I could do it.

Postgres on our live machine is regularly pushing WAL files to the standby.

Postgres on the standby machine is continually reading those files.
But that is all it will do. "pg_dump" just says "The database is starting up".

Could/should I have something configured differently?

Or Is this something that has changed with Postgres 9?
We're currently running Postgres 8.4.
Is this my specific reason to embark on an upgrade?

Robert.

--
Robert Inder,
Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
                                           Interactions speak louder than words

Robert Inder schrieb am 12.03.2015 um 12:52:
> Postgres on the standby machine is continually reading those files.
> But that is all it will do. "pg_dump" just says "The database is starting up".
>
> Could/should I have something configured differently?
>
> Or Is this something that has changed with Postgres 9?
> We're currently running Postgres 8.4.
> Is this my specific reason to embark on an upgrade?

8.4 cannot run queries on the standby, you need to upgrade to a supported/maintained version for this
(this feature was introduced in 9.0)

In 9.x you can start the slave as a "hot standby" to allow read only queries which is what pg_dump needs.

You should really upgrade to a current version 9.4 or 9.3

Thomas

> The thing is you can use desktop class machines for the slave. If you do
> not have spare machines I would suggest a desktop class machine with big
> RAM and whatever disks you need for the DB plus an extra disk to pg_dump to
> ( so pg_dump does not compete with DB for the db disks, this really kills
> performance ). Replication slaves do not need that much RAM ( as the only
> query it is going to run is the pg_dump ones, but desktop ram is cheap ).
> We did this with a not so powerful desktop with an extra sata disk to store
> the pg_dumps and it worked really well, and we are presently using two
> servers, using one of the extra gigabit interfaces with a crossover cable
> for the replication connection plus an extra sata disk to make hourly
> pg_dumps and it works quite well.

If load on the backup server becomes an issue you might be able to
make incremental pg_dump's onto tmpfs. Advantage there is that the
dump iteslf has effectively no write I/O overhead: you can dump to
tmpfs and then [bg]zip to stable storage w/o beating up the disks,
which becomes a real problem with comodity-grade hardware.

--
Steven Lembark                                             3646 Flora Pl
Workhorse Computing                                   St Louis, MO 63110
lembark@wrkhors.com                                      +1 888 359 3508

On 12 March 2015 at 12:31, Thomas Kellerer <spam_eater@gmx.net> wrote:

> 8.4 cannot run queries on the standby, you need to upgrade to a supported/maintained version for this
> (this feature was introduced in 9.0)
>
> In 9.x you can start the slave as a "hot standby" to allow read only queries which is what pg_dump needs.
>
> You should really upgrade to a current version 9.4 or 9.3

Thought you were going to say that:-(

Well, I guess we have to do it some time, and now there is a reason
for it to happen sooner rather than later...

But even if (OK, "though")  I'm doing that, Steven's suggestion of
making the dump to a ram file system, then filing it as a separate
step, looks simple enough to be worth trying as a stop-gap...

Robert.

--
Robert Inder,
Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
                                           Interactions speak louder than words

Hi Steven:

On Fri, Mar 13, 2015 at 9:03 PM, Steven Lembark <lembark@wrkhors.com> wrote:
> > The thing is you can use desktop class machines for the slave. If you do
.....

> If load on the backup server becomes an issue you might be able to
> make incremental pg_dump's onto tmpfs.

I'm curious, how do you make incremental backups using pg_dump? I do
not know it, haven't been able to find it in the docs and it would be
a great solutions for a couple of problems I have.

> Advantage there is that the
> dump iteslf has effectively no write I/O overhead: you can dump to
> tmpfs and then [bg]zip to stable storage w/o beating up the disks,
> which becomes a real problem with comodity-grade hardware.

I do not think this ramdisks are a good thing for that. Let me
explain, if you have tmpfs, you have devent pipe support in the shell.
If you can put the uncompressed dump in a ramdisk, you are going to be
able to fit the compressed one in a write-through disk cache. So you
just dump compressed ( either piping through a compressor or using
pg_dump's custom compressed format ) and let the write cache do its
magic ( also, it's going to beat the disk less ( or equal, if your RAM
is really big for the task ). If you use a ramdisk and compress you
have less ram to cache the compressed dump, write cache is gonna
perform worse, disk is gonna be beaten more ). Also, tmpfs means less
ram for disk cache and shared buffer for the database. On a machine
needing the RAM my money is in it making the things slower ( on an
scenario like 8Gb db on 64Gb machine whatever you do is going to be
fast enough, so just peek the simpler to code ).

And regarding compression, pg_dump's one does normally perform good
enough unless you have strange data, I've done several tests and found
in normal data it does not pay to either raise the level ( little
space gain for the cycles taken ) or try alternate ones ( I normally
always use custom format due to it's ability to easily do partial
restores. Compressing it outside means you need to decompress to use.
). Also, I did some compressor tests for intermediate files, and found
for many reading/writing using gzip/lzo was faster than uncompressed (
specially with modern multicore machines ), xz was the compression
king and I was severely disapointed by bzip2 ( for any option combo in
bzip2 I had another in xz which beat it in BOTH size AND time, YMMV ).

Regards.

   Francisco Olarte.

Hi Robert:

On Thu, Mar 12, 2015 at 12:52 PM, Robert Inder <robert@interactive.co.uk> wrote:
> On 11 March 2015 at 17:32, Francisco Olarte <folarte@peoplecall.com> wrote:
>> This is, build an streaming replication slave, pg_dump from the slave. If
>> needed, restore in the master.
...
> I really like the idea of running pg_dump on the slave, but I don't understand
> how I could do it.
....
> Or Is this something that has changed with Postgres 9?
> We're currently running Postgres 8.4.
> Is this my specific reason to embark on an upgrade?

It's been answered before, but yes, streaming replication is a (
someone may say THE ) 9.0 feature. My fault, I've been using it for so
long I forgot the past. If you are using log shipping I would
recommend some tests and considering an upgrade, I've found
replication is much simpler and easier with it.

Regards.
    Francisco Olarte.

On 3/11/15 6:46 AM, Andrew Sullivan wrote:
>> 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?
> Slony-I, which is a PITA to administer, has a mode where you can ship
> logs off and restore them in pieces.  The logs are not WAL, but Slony
> logs (which are produced by triggers and some explicit event writing
> for schema changes).  So they work per-database.  Schema changes are
> really quite involved for Slony, and there's overhead resulting from
> the triggrs, and as I said it's rather clunky to administer.  But it's
> been around some time, it still is actively maintained, and it has
> this functionality.  The PITR tools were, last I checked, pretty
> primitive.  But the tool might work for your case.  I don't know
> whether Bucardo or Londiste (two alternative systems that work on
> roughly the same principle) have this functionality, but I kind of
> doubt it since both were designed to get rid of several of the
> complexities that Slony presented.  (Slony had all those complexities
> because it was trying to offer all this functionality at once.)

You could do something very similar with londiste by setting up a second
queue and delaying when you move data to it from the primary queue,
based on event_time.

Or now that I think about it... you might be able to do that right in
the replay process itself.

The big reason I prefer londiste over Slony is that it's extremely
modular, so it's easy to do stuff like this.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com