title: PostgreSQL: improving pg_dump, pg_restore performance
body: <p>When I began, I used <code>pg_dump</code> with the default plain format.  I was unenlightened.</p>
  
  <p>Research revealed to me time and file size improvements with <code>pg_dump -Fc | gzip -9 -c > dumpfile.gz</code>.  I was enlightened.</p>
  
  <p>When it came time to create the database anew,</p>
  
  <pre><code># create tablespace dbname location '/SAN/dbname';
  # create database dbname tablespace dbname;
  # alter database dbname set temp_tablespaces = dbname;
  
  % gunzip dumpfile.gz              # to evaluate restore time without a piped uncompression
  % pg_restore -d dbname dumpfile   # into a new, empty database defined above
  </code></pre>
  
  <p>I felt unenlightened:  the restore took 12 hours to create the database that's only a fraction of what it will become:</p>
  
  <pre><code># select pg_size_pretty(pg_database_size('dbname'));
  47 GB
  </code></pre>
  
  <p>Because there are predictions this database will be a few terabytes, I need to look at improving performance now.</p>
  
  <p>Please, enlighten me.</p>