Re: Consistent file-level backup of pg data directory

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: Consistent file-level backup of pg data directory
Дата
Msg-id CAMkU=1ybhEUNXFX+BsV16=1Z0BfUzXTHpsR=EP22atsevc2vKA@mail.gmail.com
обсуждение исходный текст
Ответ на Consistent file-level backup of pg data directory  (gator_ml@yahoo.de)
Ответы Re: Consistent file-level backup of pg data directory
Список pgsql-general
Дерево обсуждения
On Tue, Jan 7, 2014 at 10:58 AM, <gator_ml@yahoo.de> wrote:
Hi,

On my site, we run rsync-based backups of all servers to a central backup server every night. Unfortunately, reading the postgres documentation I could not find any direct way how to ensure that the state of the postgres data directory stored on the backup server is in a consistent state.

It seems to me, that using (abusing;-) the "Continuous Archiving" it
should be possible to get what i need. Specifically, I came up with
the following strategy:
- set wal_level=archive
- call pg_start_backup()
- keep a copy "backup_label" under a different name

Why under a different name?  That sounds dangerous to me.
 
- call pg_stop_backup()
- the run the regular backup procedure

You have this order backwards.  You can only tell postgres that you are done doing the backup once you are done doing the backup.
 
 
After restoring the backup somewhere,
I only need to create a "recovery.conf" file (containing
restore_command='/bin/true' because restore_command is
mandatory) and everything should be fine.

Does this mean you weren't actually archiving the xlog when you turned archiving on?  

What do you do with the backup_label?  If you don't restore it to its original name, then postgres will just think it is recovering from a power failure or something like that, not a media failure, so it will not even try to access the archived logs.
 
In practice, this procedure also seems to work as expected.

If no checkpoints occurred during the backup (or no more than one of them did), then your method might accidentally work.  Or it could silently corrupt your database in hard to detect ways.

 
Is there any hidden pitfall I overlooked? Or is there maybe
a better way not mentioned in the postgres documentation how to
get the data into a consistent state for a file level backup?

Any Feedback would be appreciated!

It is pretty much a disaster.  Do you really need rsync?  Otherwise, use pg_basebackup, it is easier to get right.

Cheers,

Jeff

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

Предыдущее
От: ChoonSoo Park
Дата:
Сообщение: Re: Is there a way to return "true"/"false" string for boolean type?
Следующее
От: Rich Shepard
Дата:
Сообщение: Re: Server Crash: Issues Re-starting Postgres [RESOLVED]