Re: pg_basebackup issues

On Fri, Apr 20, 2012 at 12:31 PM, Magnus Hagander <magnus@hagander.net> wrote:
> On Fri, Apr 20, 2012 at 19:51, Lonni J Friedman <netllama@gmail.com> wrote:
>> Anyway, lesson learned, I need to either invoke pg_basebackup as the
>> same user that runs the database (or is specified with the -U
>> parameter ?), or write the backup somewhere outside of the directory
>> structure that is being backed up.
>>
>> I eventually also found the following entries in the postgresql server log:
>> FATAL:  could not open directory "./backups": Permission denied
>> FATAL:  archive member "backups/base.tar.gz" too large for tar format
>>
>> What concerns me is the 2nd fatal error.  The tarball that
>> pg_basebackup created before erroring out is about 12GB:
>> 12393094165  base.tar.gz
>
> Are you actually storing your backup files *inside* the data
> directory? You really shouldn't do that, you're creating a cyclic
> dependency where each new backup will include the old one inside it...
> You should store the resulting backup file somewhere outside the data
> directory.

yea, in hindsight that was silly.  i just saw a convenient 'backups'
subdirectory and figured that'd be a nice logical place.

>
>> I wasn't aware of any 12GB file size limit for tar, so this is a bit
>> of a mystery to me.  Regardless, I'd be happy to try some other
>> archiving strategy, but the man page for pg_basebackup suggests that
>> there are only two formats, tar and basically just copying the
>> filesystem.  If I copied the filesystem, I'd still have to find some
>> way to archive them for easy management (copying elsewhere, etc).  Has
>> anyone come up with a good strategy on how to deal with it?
>
> The max file size of a single flie inside a standard tar file is 8Gb,
> see e.g. http://en.wikipedia.org/wiki/Tar_(file_format).
>
> I think there are extensions that let you store bigger files, but
> since PostgreSQL will never create files that  big it's not
> implemented in the basebackup system. Because again, the root of your
> problem seems to be that you are trying to store the resulting backup
> inside the data directory.

You're right, that was indeed the issue.  I've redone the process
using a location external to $PGDATA, and it completed successfully:
$ pg_basebackup -P -v -D /tmp/backup -x -Ft -z -U postgres
xlog start point: C6/64000020
135733616/135733616 kB (100%), 1/1 tablespace
xlog end point: C6/640000A0
pg_basebackup: base backup completed

So after running through this, I tried to use (restore) the backup
that was generated.  While everything appears to be working ok from a
functional perspective, in the server log I saw the following:
######
LOG:  creating missing WAL directory "pg_xlog/archive_status"
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at C6/66000078
LOG:  could not open file "pg_xlog/00000001000000C600000067" (log file
198, segment 103): No such file or directory
LOG:  redo done at C6/660000A0
FATAL:  the database system is starting up
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
#####

Just to be clear, here's what I did after pg_basebackup had completed
successfully:
0) shutdown postgresql gracefully, and verified that it was fully shutdown
1) moved $PGDATA to $PGDATA.old
2) created $PGDATA as postgres user
3) extracted the basebackup tarball as postgres user
cd $PGDATA && tar xzvpf /tmp/backup/base.tar.gz
4) started postgresql up

I would have expected that I wouldn't have gotten the 'not properly
shutdown' warning, or the 'could not open file' warning by following
this process.  Am I doing something wrong?

thanks