Re: Changes to backup.sgml

On 05/15/2015 10:03 AM, Robert Haas wrote:
>
> On Thu, May 14, 2015 at 12:53 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>> 1. File System Level Backup
>>
>> The section should be a note within the larger document. It is largely a
>> legacy section from before 8.3.
>
> I agree.  I think this section is just plain weird at this point.
> Most people would assume that you can copy or move the database files
> when the database server is shut down, but few people would consider
> that a usable backup strategy.  I'm not sure exactly how this should
> be refactored, but I think something should be done.  Maybe the title
> should be something like "Moving or Copying Database Files" instead of
> "File System Level Backup", and the content could be adjusted to fit
> that theme.
>

In looking at this further, we really need to think about this in a 
different light.

The first page needs to immediately mention we don't need to take the 
database offline for a backup.

24.1. SQL Dump
I think we should remove any references to redirection and use only 
appropriate flags instead. Any admin worth their salt will realize you 
can use redirection and those who aren't worth their salt are just going 
to be confused. For example:

Instead of

pg_dump dbname > file

use

pg_dump -d dbname -f dbname.sql
I also think we really need to encourage the use of -Fd or -Fc

24.1.1. Restoring the Dump
In conjunction with my suggestions for SQL Dump, I think we need to 
focus on using pg_restore. In short, -Fd and -Fc get precedence 
(although we will discuss the sql dump) and thus pg_restore will also 
get precedence.

24.1.2. Using pg_dumpall
We need to give larger precedence to pg_dumpall for the sake of 
globals. I also recommend that we eliminate referencing pg_dumpall as 
the way to backup the cluster, (except for small databases) and instead 
focus on the required -g option.

24.1.3. Handling Large Databases
The idea of a "large" database is completely different now from then. 
This reads almost identical to what it said in 8.0.

24.2. File System Level Backup
We already discussed this

24.3. Continuous Archiving and Point-in-Time Recovery (PITR)
List robocopy (available since server 2003)

24.3.1. Setting Up WAL Archiving
24.3.2. Making a Base Backup
Better discussion of pg_basebackup needs to be had.

24.3.3. Making a Base Backup Using the Low Level API
Already discussed this

24.3.4. Recovering Using a Continuous Archive Backup
"Remove any files present in pg_xlog/; these came from the file system 
backup and are therefore probably obsolete rather than current. If you 
didn't archive pg_xlog/ at all, then recreate it with proper 
permissions, being careful to ensure that you re-establish it as a 
symbolic link if you had it set up that way before."

Is that actually needed? Won't PostgreSQL just ignore them or spit them out?

24.3.5. Timelines

I need to read through this again

24.3.6. Tips and Examples

I will probably add some to this.

24.3.7. Caveats

-- 
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.