CMD: Andrew Nierman's Blog http://www.commandprompt.com/blogs/andrew_nierman/ Command Prompt Blog Feed: Andrew Nierman's Blog Fri, 18 Oct 2013 09:24:18 -0700 www.commandprompt.com http://www.commandprompt.com/images/small_mammoth.gif Command Prompt Mammoth http://www.commandprompt.com/blogs/andrew_nierman/ 46 46 http://www.commandprompt.com/blogs/andrew_nierman/2013/10/a_pg_basebackup_wish_list/ Fri, 18 Oct 2013 09:24:18 -0700 A pg_basebackup Wish List http://www.commandprompt.com/blogs/andrew_nierman/2013/10/a_pg_basebackup_wish_list/ <p><a target="_blank" href="http://www.postgresql.org/docs/current/static/app-pgbasebackup.html">pg_basebackup</a> was introduced in Postgres 9.1 as a simple way to copy the data directory of a running database cluster. This was a great addition to a small group of <a target="_blank" href="http://www.postgresql.org/docs/current/static/reference-client.html">PostgreSQL Client Applications</a>.</p> <p>The pg_basebackup approach differs from the standard pg_start_backup(), rsync (or other file system copy), pg_stop_backup() approach in that it uses the replication protocol over a standard Postgres connection to make the base backup. A few highlights: <ul> <li>A completely standalone backup can be created easily by using the --xlog-method argument (the stream option here is particularly nice so that you don&rsquo;t have to muck with the <a target="_blank" href="http://www.postgresql.org/docs/current/static/runtime-config-replication.html#GUC-WAL-KEEP-SEGMENTS">wal_keep_segments</a> Postgres setting).</li> <li>Thanks to the cascading replication machinery in 9.2 pg_basebackup can take a backup from an active hot standby server (where the basic pg_start_backup() approach fails).</li> <li>As of 9.3 pg_basebackup will even create a recovery.conf file for you (with the --write-recovery-conf switch), so it&rsquo;s basically a one-liner to get a standby with replication set up!</li> </ul> </p> <p>There&rsquo;s a lot to like, but I&rsquo;m greedy....</p> <h2>Wish #1: Parallelization</h2> <p>If &ldquo;idle hands are the devil&rsquo;s playthings&rdquo; I can only imagine what the devil would do with idle CPUs and network bandwidth. It would be great if pg_basebackup were able to parallelize creation of the base backup and make use of more available resources. In the past I&rsquo;ve written a script to parallelize rsync by first processing its --itemize-changes output and then spawning multiple rsync processes in parallel to perform the copies; this parallelization can really cut down on the time to get a base backup copied. There are times when getting something done <i>as fast as possible</i> trumps other concerns.</p> <h2>Wish #2: Compression Options</h2> Sometimes network bandwidth is in short supply. pg_basebackup has a --gzip switch to enable compression, but this compression is actually done on the client machine rather than the server. Certainly there is the option to run pg_basebackup from the originating server and use netcat (or scp, etc.) to get the data copied to the destination <em>if</em> we are using pg_basebackup&rsquo;s --format=tar (-Ft) option. For example, on the originating server: <pre><samp><span class="prompt">nierman@source:~$</span><kbd> pg_basebackup -D - -Ft -x | pbzip2 -p8 | nc -l 1201</kbd></samp></pre> (in an actual use case I'd also insert <a target="_blank" href="http://www.ivarch.com/programs/pv.shtml">pv</a> in the pipeline to get nice status/progress updates at various stages), and on the destination server: <pre><samp><span class="prompt">nierman@destination:~$</span><kbd> nc source 1201 > newbasedir/base.tar.bz2</kbd></samp></pre> This process is a bit more complicated than a single command from the client/destination server, but more importantly we lose out on things like streaming WAL files when using the tar format with pg_basebackup. Note that I&rsquo;ve also used pbzip2 above (a parallelized version of bzip2). It would be great to have the option to use external programs like pbzip2, pigz, etc. with pg_basebackup compression. Let&rsquo;s call that Wish #2b. <h2>Wish #3: Don&rsquo;t Die on Me Now!</h2> Here&rsquo;s some output from a recent pg_basebackup session: <pre>pg_basebackup: starting background WAL receiver 2052690927/2052718105 kB (100%), 1/1 tablespace pg_basebackup: could not get transaction log end position from server: FATAL: could not open file "./#postgresql.conf#": Permission denied</pre> pb_basebackup chunked through almost 2TB of data and then it died just before finishing. Ack. Why? At some point someone had edited the postgresql.conf file as root and their editor saved the backup copy seen above (in the postgres data directory of course!); the Postgres backend couldn&rsquo;t read that file (owned by root) when creating the base backup. I would have loved to see a simple error message and have pg_basebackup continue on its merry way. Certainly an initial ls -l in the data directory is a good quick check that can be done manually, although that wouldn&rsquo;t save me from people that hide their cat photos deep inside the Postgres base directory. http://www.commandprompt.com/blogs/andrew_nierman/2013/10/a_pg_basebackup_wish_list/ OpenSource PostgreSQL