Re: pg_basebackup issues

On Fri, Apr 20, 2012 at 19:51, Lonni J Friedman <netllama(at)gmail(dot)com> wrote:
> Greetings,
> I'm running postgresql-9.1.3 on a Linux-x86_64 (Fedora16, if it
> matters) system.  I noticed the existence of pg_basebackup starting in
> 9.1, and figured I'd try it out and see if it would simplify our
> backup & management processes.  I setup a test system (same OS &
> postgresql version as production) with a fairly recent snapshot of our
> production database, invoked it, and saw the following output:
> ######
> # pg_basebackup -P -v -D backups -Ft -z -U postgres
> 135717206/135717230 kB (100%), 1/1 tablespace
> pg_basebackup: could not get WAL end position from server
> ######
>
> I wasn't sure what that error meant, so after googling a bit, turns
> out that it really means that there were one or more files not owned
> by the postgres user (see
> http://serverfault.com/questions/312205/pg-basebackup-could-not-get-wal-end-position-from-server
> ).  Sure enough, the file that wasn't owned by the postgres user was
> the backup tarball that pg_basebackup was creating, since I had been
> running it as root.  That error is rather cryptic, and it would be
> helpful if it was improved to suggest the real cause of the failure.

Yeah, the error message comes from the fact that the backend gives up,
and the real message is in the backend log. We should try to do
something about that.

> 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.

> 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.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/