Re: Backup and failover process

Поиск
Список
Период
Сортировка
От Campbell, Lance
Тема Re: Backup and failover process
Дата
Msg-id B10E6810AC2A2F4EA7550D072CDE8760CDDE84@SAB-FENWICK.sab.uiuc.edu
обсуждение исходный текст
Ответ на Backup and failover process  ("Campbell, Lance" <lance@illinois.edu>)
Список pgsql-admin
Got it.  Thanks a bunch.  Your last email put it all together.

Thanks,

-----Original Message-----
From: Evan Rempel [mailto:erempel@uvic.ca]
Sent: Wednesday, July 16, 2008 10:22 AM
To: Campbell, Lance
Subject: Re: [ADMIN] Backup and failover process

postgres does not use "time" to determine if a WAL file contains
transactions
that come before or after a pg-dump. It uses transaction numbers.

pg_dump does NOT dump the transaction numbers. When reloading from a
pg-dump
file, the instance of postgresql that you are loading into will generate
all of the transaction numbers, and they will NOT be appropriate for
using
the WAL files. You will not be able to copy WALL files into the xlog
directory
and roll them into a different instance of postgresql.

To make use of the WAL files, you MUST have the filesystem level restore
of the entire
postgresql cluster/instance, not just the pg_dump of a single database.

In my opinion, to get reliable point in time failover, you need one of

1. The system administrators provide failover as part of thier postgres
offering
2. You need assistance from the system administrators to set up failover
3. You need to administer the machines yourself.

Just my $0.02

Evan.

Campbell, Lance wrote:
> Evan,
> During failover my idea was to load my database from a nightly dump
> created from pg_dump.  Then apply all of the WAL files from when the
> full backup was created by pg_dump.
>
> Are you saying that the database would not be able to load the WAL
> files?
>
> I thought I might have to start PostgreSQL in standard mode.  Restore
> from the pg_dump.  Then start it using archiving.  Then restore using
> WAL files.  Does this not work?
>
> Thanks,
>
> -----Original Message-----
> From: Evan Rempel [mailto:erempel@uvic.ca]
> Sent: Tuesday, July 15, 2008 9:46 PM
> To: Campbell, Lance
> Subject: Re: [ADMIN] Backup and failover process
>
> You can not mix WAL recovery/restore and pg_dump restores. To restore
a
> pg_dump, you
> require a fully functioning postgresql server, which makes its own WAL
> files. After the
> restore of the pg_dump, you can not interject the WAL archive files.
>
> The WAL archive files can only be used to roll-forward from known
> checkpoints (known by
> the "recovery mode" internal to postgresql.
>
> You can use pg_dump to get specific snapshots of databases, but you
can
> not roll transactions
> forward from the pg_dump using WAL files.
>
> We use pg_dump to get daily snapshots of databases so that if a user
> accidentally
> breaks their database, we can upon request, restore to the previous
> nights backup.
>
> We also use filesystem backups and WAL files to allow us to recover to
> any point in
> time given a disaster.
>
> It would be VERY nice to use filesystem backups and WAL files to
recover
> a SINGLE database,
> but that is currently on my wish list.
>
> Evan Rempel.
>
>
> Campbell, Lance wrote:
>> Kevin,
>> I have read this documentation.  I still does not answer my basic
>> question.  What happens if you take an SQL snapshot of a database
> while
>> creating WAL archives then later restore from that SQL snapshot and
>> apply those WAL files?  Will there be a problem if the transactions
>> within the newest WAL file after the SQL snapshot was taken cause
>> problems when they are applied?  I would assume yes but I wanted to
>> check if there was some type of timestamp that would prevent an issue
>> from occurring?
>>
>> Thanks,
>>
>> -----Original Message-----
>> From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
>> Sent: Tuesday, July 15, 2008 12:24 PM
>> To: Campbell, Lance; pgsql-admin@postgresql.org
>> Subject: Re: [ADMIN] Backup and failover process
>>
>>>>> "Campbell, Lance" <lance@illinois.edu> wrote:
>>> PostgreSQL: 8.2
>>> I am about to change my backup and failover procedure from dumping a
>> full
>>> file SQL dump of our data every so many minutes
>>
>> You're currently running pg_dump every so many minutes?
>>
>>> to using WAL files.
>>
>> Be sure you have read (and understand) this section of the docs:
>>
>> http://www.postgresql.org/docs/8.2/interactive/backup.html
>>
>> -Kevin
>>
>
>


--
Evan Rempel                erempel@uvic.ca
Senior Programmer Analyst        250.721.7691
Computing Services
University of Victoria

В списке pgsql-admin по дате отправления:

Предыдущее
От: "Campbell, Lance"
Дата:
Сообщение: Re: Backup and failover process
Следующее
От: "System/IJS - Joko"
Дата:
Сообщение: log_statement at postgres.conf