Обсуждение: Recovery/Restore and Roll Forward Question.
Hi All,
Is it at all possible to "roll forward" a database with archive logs
when it has been recovered using a dump?
Assuming I have the archive_command and archive_timeout parameters set
on our "live" system, then I follow these steps:
[1] pg_dump -d database > /backup/database.dump,
[2] initdb new instance on recovery machine,
[3] psql -f ./database.dump,
[4] shutdown new recovered db,
[5] create recovery.conf,
[6] copy WAL's from time of backup till time of recovery to temp dir
[7] start postgresql
In my mind I think I will have some problems somewhere along the way,
however I don't know enough about the internals of PostgreSQL to
actually see if there are additional steps I need to follow.
In our environment it takes approx 2 hours to perform a PIT backup of
our live system:
[1] select pg_start_backup('labe;')
[2] cpio & compress database directory (exclude wals)
[3] select pg_stop_backup()
However, if we perform a plain dump (pg_dump/pg_dumpall) we can dump the
whole lot in 15 minutes. For us this is more efficient.
The question is, how can we roll forward from our time of pg_dump, to
our most recent WAL (in case of failure - touch wood).
Any comments/suggestions are most welcome. if anyone knows of some docs
or reference info about the way we're trying to follow, please could you
let me know.
Thanks
Bruce
Bruce McAlister wrote:
> Hi All,
>
> Is it at all possible to "roll forward" a database with archive logs
> when it has been recovered using a dump?
>
> Assuming I have the archive_command and archive_timeout parameters set
> on our "live" system, then I follow these steps:
>
> [1] pg_dump -d database > /backup/database.dump,
> [2] initdb new instance on recovery machine,
> [3] psql -f ./database.dump,
> [4] shutdown new recovered db,
> [5] create recovery.conf,
> [6] copy WAL's from time of backup till time of recovery to temp dir
> [7] start postgresql
No. WALs track disk blocks not table-rows, so you need a file-level
backup of the original installation.
> In my mind I think I will have some problems somewhere along the way,
> however I don't know enough about the internals of PostgreSQL to
> actually see if there are additional steps I need to follow.
>
> In our environment it takes approx 2 hours to perform a PIT backup of
> our live system:
>
> [1] select pg_start_backup('labe;')
> [2] cpio & compress database directory (exclude wals)
> [3] select pg_stop_backup()
>
> However, if we perform a plain dump (pg_dump/pg_dumpall) we can dump the
> whole lot in 15 minutes. For us this is more efficient.
It sounds like there's something strange with your setup if it's quicker
for pg_dump to read your data than cp. Do you have *lots* of indexes, or
perhaps a lot of dead rows? What's the bottleneck with cpio+compress -
cpu/disk/network?
> The question is, how can we roll forward from our time of pg_dump, to
> our most recent WAL (in case of failure - touch wood).
Can't be done I'm afraid.
--
Richard Huxton
Archonet Ltd
Richard Huxton wrote:
> Bruce McAlister wrote:
>> Hi All,
>>
>> Is it at all possible to "roll forward" a database with archive logs
>> when it has been recovered using a dump?
>>
>> Assuming I have the archive_command and archive_timeout parameters set
>> on our "live" system, then I follow these steps:
>>
>> [1] pg_dump -d database > /backup/database.dump,
>> [2] initdb new instance on recovery machine,
>> [3] psql -f ./database.dump,
>> [4] shutdown new recovered db,
>> [5] create recovery.conf,
>> [6] copy WAL's from time of backup till time of recovery to temp dir
>> [7] start postgresql
>
> No. WALs track disk blocks not table-rows, so you need a file-level
> backup of the original installation.
Ahh okay, that makes a little more sense now. I thought they actually
contained the query and that was replayed to the database being recovered.
>
>> In my mind I think I will have some problems somewhere along the way,
>> however I don't know enough about the internals of PostgreSQL to
>> actually see if there are additional steps I need to follow.
>>
>> In our environment it takes approx 2 hours to perform a PIT backup of
>> our live system:
>>
>> [1] select pg_start_backup('labe;')
>> [2] cpio & compress database directory (exclude wals)
>> [3] select pg_stop_backup()
>>
>> However, if we perform a plain dump (pg_dump/pg_dumpall) we can dump the
>> whole lot in 15 minutes. For us this is more efficient.
>
> It sounds like there's something strange with your setup if it's quicker
> for pg_dump to read your data than cp. Do you have *lots* of indexes, or
> perhaps a lot of dead rows? What's the bottleneck with cpio+compress -
> cpu/disk/network?
Thats exactly what I think. There is something strange going on. At the
moment I think it is the disk I am writing the data to that is slow,
possibly due to the fact that it is mounted up as "forcedirectio", so as
not to interfere with the file system cache which we want to have mainly
pg datafiles in, and the RAID controller has this particular logical
driver configured as write-through, so there is no buffering in-between.
The cpu's and network are not the problem here (2 x Dual Core Opterons
and Quad Gigabit Ethernet, total cpu usage is around 10%, NIC's are
pushing around 3Mbit/s over each).
It's not all that big to be honest, the total database size is around
11GB and I'm currently recking my head to find out how to improve the
backup times, and not adversely affect our running instance. I just
recently tried to use UFS snapshots, but the backing store filled up
before i could complete a backup of the snapshot. I need to find a way
to improve the write speed of our destination disk. I have another
question in this pg group about autovacuum that is not running on one of
our database tables which adds an average of around 2.1GB of bloat to
the database each day. I've now (today) scheduled a cron job every 10
minutes to get around this in the meantime. Hopefully that should reduce
the amount of data backed up by 2GB when we get to the bottom of that
issue (autovacuum)
>
>> The question is, how can we roll forward from our time of pg_dump, to
>> our most recent WAL (in case of failure - touch wood).
>
> Can't be done I'm afraid.
Thanks, I'll have to stick with PIT backups and find a way to improve
the speed.
>
Bruce McAlister wrote: > Thats exactly what I think. There is something strange going on. At the > moment I think it is the disk I am writing the data to that is slow, > possibly due to the fact that it is mounted up as "forcedirectio", so as > not to interfere with the file system cache which we want to have mainly > pg datafiles in, and the RAID controller has this particular logical > driver configured as write-through, so there is no buffering in-between. > The cpu's and network are not the problem here (2 x Dual Core Opterons > and Quad Gigabit Ethernet, total cpu usage is around 10%, NIC's are > pushing around 3Mbit/s over each). > > It's not all that big to be honest, the total database size is around > 11GB and I'm currently recking my head to find out how to improve the > backup times, and not adversely affect our running instance. 11GB in 2 hours? Let's see, that's ~1.5MB/sec. Something is horribly wrong there - you could do better than that with a USB 1 drive. -- Richard Huxton Archonet Ltd
Richard Huxton wrote:
>> In our environment it takes approx 2 hours to perform a PIT backup of
>> our live system:
>>
>> [1] select pg_start_backup('labe;')
>> [2] cpio & compress database directory (exclude wals)
>> [3] select pg_stop_backup()
>>
>> However, if we perform a plain dump (pg_dump/pg_dumpall) we
>> can dump the whole lot in 15 minutes. For us this is more efficient.
>
> It sounds like there's something strange with your setup if it's
quicker
> for pg_dump to read your data than cp. Do you have *lots* of indexes,
or
> perhaps a lot of dead rows?
That sounds like a good bet.
Did you ever do a VACUUM FULL?
What is the size of the online backup?
What is the size of the pg_dumpall?
Yours,
Laurenz Albe
Richard Huxton wrote: > Bruce McAlister wrote: >> Thats exactly what I think. There is something strange going on. At the >> moment I think it is the disk I am writing the data to that is slow, >> possibly due to the fact that it is mounted up as "forcedirectio", so as >> not to interfere with the file system cache which we want to have mainly >> pg datafiles in, and the RAID controller has this particular logical >> driver configured as write-through, so there is no buffering in-between. >> The cpu's and network are not the problem here (2 x Dual Core Opterons >> and Quad Gigabit Ethernet, total cpu usage is around 10%, NIC's are >> pushing around 3Mbit/s over each). >> >> It's not all that big to be honest, the total database size is around >> 11GB and I'm currently recking my head to find out how to improve the >> backup times, and not adversely affect our running instance. > > 11GB in 2 hours? Let's see, that's ~1.5MB/sec. Something is horribly > wrong there - you could do better than that with a USB 1 drive. > LOL, now that you put it that way, I'll be sure to put some time in to get to the bottom of the issue. Jeez, not even USB 2 speeds, maybe i should hook my thumb drive in the back of the box and send backups there to prove a point :)
Albe Laurenz wrote:
> Richard Huxton wrote:
>>> In our environment it takes approx 2 hours to perform a PIT backup of
>>> our live system:
>>>
>>> [1] select pg_start_backup('labe;')
>>> [2] cpio & compress database directory (exclude wals)
>>> [3] select pg_stop_backup()
>>>
>>> However, if we perform a plain dump (pg_dump/pg_dumpall) we
>>> can dump the whole lot in 15 minutes. For us this is more efficient.
>> It sounds like there's something strange with your setup if it's
> quicker
>> for pg_dump to read your data than cp. Do you have *lots* of indexes,
> or
>> perhaps a lot of dead rows?
>
> That sounds like a good bet.
>
> Did you ever do a VACUUM FULL?
> What is the size of the online backup?
> What is the size of the pg_dumpall?
>
I have autovacuum configured on the system, but have only just recently
(yesterday) found out that the autovacuum daemon only appears to be
selecting 1 database to work on, i have another thread open on this
mailing list for that particular issue.
Online backup size compressed is around 3.4GB.
pg_dumpall size compressed is around 2GB.
We do have a couple indexes, however, the only one that has a large
amount of bloat is the one referenced in the autovacuum post on this
mailing list. Hopefully if i can get autovacuum working with that
database, then that would be one less worry :)
> Yours,
> Laurenz Albe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
On Jun 21, 2007, at 5:16 AM, Bruce McAlister wrote: > Thats exactly what I think. There is something strange going on. At > the > moment I think it is the disk I am writing the data to that is slow, > possibly due to the fact that it is mounted up as "forcedirectio", > so as > not to interfere with the file system cache which we want to have > mainly > pg datafiles in, and the RAID controller has this particular logical > driver configured as write-through, so there is no buffering in- > between. > The cpu's and network are not the problem here (2 x Dual Core Opterons > and Quad Gigabit Ethernet, total cpu usage is around 10%, NIC's are > pushing around 3Mbit/s over each). > > It's not all that big to be honest, the total database size is around > 11GB and I'm currently recking my head to find out how to improve the > backup times, and not adversely affect our running instance. I just > recently tried to use UFS snapshots, but the backing store filled up > before i could complete a backup of the snapshot. I need to find a way > to improve the write speed of our destination disk. I have another > question in this pg group about autovacuum that is not running on > one of > our database tables which adds an average of around 2.1GB of bloat to > the database each day. I've now (today) scheduled a cron job every 10 > minutes to get around this in the meantime. Hopefully that should > reduce > the amount of data backed up by 2GB when we get to the bottom of that > issue (autovacuum) > You said in your other thread that your on Solaris 10, right? We are as well and just discovered that having stats_block_level set to on increases write volume a lot and noticed a significant drop when we turned it off as well a significant drop in wal file traffic. The same goes for stats_row_level (wrt write volume at least), but you need that if you want query information to come through pg_stat_activity (we left that on). We just migrated off of a server wherein forcedirectio actually helped us a lot, but now we're wondering if that was due to us having forcedirectio on. We only at the beginning of a lot of systems migrations and restructuring so now that we have some new avenues and room to experiment, I'll try to post our results in a couple weeks. Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com