codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>26.1. SQL Dump</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="backup.html" title="Chapter 26. Backup and Restore" /><link rel="next" href="backup-file.html" title="26.2. File System Level Backup" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">26.1. <acronym class="acronym">SQL</acronym> Dump</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="backup.html" title="Chapter 26. Backup and Restore">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="backup.html" title="Chapter 26. Backup and Restore">Up</a></td><th width="60%" align="center">Chapter 26. Backup and Restore</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="backup-file.html" title="26.2. File System Level Backup">Next</a></td></tr></table><hr /></div><div class="sect1" id="BACKUP-DUMP"><div class="titlepage"><div><div><h2 class="title" style="clear: both">26.1. <acronym class="acronym">SQL</acronym> Dump <a href="#BACKUP-DUMP" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="backup-dump.html#BACKUP-DUMP-RESTORE">26.1.1. Restoring the Dump</a></span></dt><dt><span class="sect2"><a href="backup-dump.html#BACKUP-DUMP-ALL">26.1.2. Using <span class="application">pg_dumpall</span></a></span></dt><dt><span class="sect2"><a href="backup-dump.html#BACKUP-DUMP-LARGE">26.1.3. Handling Large Databases</a></span></dt></dl></div><p>3 The idea behind this dump method is to generate a file with SQL4 commands that, when fed back to the server, will recreate the5 database in the same state as it was at the time of the dump.6 <span class="productname">PostgreSQL</span> provides the utility program7 <a class="xref" href="app-pgdump.html" title="pg_dump"><span class="refentrytitle"><span class="application">pg_dump</span></span></a> for this purpose. The basic usage of this8 command is:9</p><pre class="synopsis">10pg_dump <em class="replaceable"><code>dbname</code></em> > <em class="replaceable"><code>dumpfile</code></em>11</pre><p>12 As you see, <span class="application">pg_dump</span> writes its result to the13 standard output. We will see below how this can be useful.14 While the above command creates a text file, <span class="application">pg_dump</span>15 can create files in other formats that allow for parallelism and more16 fine-grained control of object restoration.17 </p><p>18 <span class="application">pg_dump</span> is a regular <span class="productname">PostgreSQL</span>19 client application (albeit a particularly clever one). This means20 that you can perform this backup procedure from any remote host that has21 access to the database. But remember that <span class="application">pg_dump</span>22 does not operate with special permissions. In particular, it must23 have read access to all tables that you want to back up, so in order24 to back up the entire database you almost always have to run it as a25 database superuser. (If you do not have sufficient privileges to back up26 the entire database, you can still back up portions of the database to which27 you do have access using options such as28 <code class="option">-n <em class="replaceable"><code>schema</code></em></code>29 or <code class="option">-t <em class="replaceable"><code>table</code></em></code>.)30 </p><p>31 To specify which database server <span class="application">pg_dump</span> should32 contact, use the command line options <code class="option">-h33 <em class="replaceable"><code>host</code></em></code> and <code class="option">-p <em class="replaceable"><code>port</code></em></code>. The34 default host is the local host or whatever your35 <code class="envar">PGHOST</code> environment variable specifies. Similarly,36 the default port is indicated by the <code class="envar">PGPORT</code>37 environment variable or, failing that, by the compiled-in default.38 (Conveniently, the server will normally have the same compiled-in39 default.)40 </p><p>41 Like any other <span class="productname">PostgreSQL</span> client application,42 <span class="application">pg_dump</span> will by default connect with the database43 user name that is equal to the current operating system user name. To override44 this, either specify the <code class="option">-U</code> option or set the45 environment variable <code class="envar">PGUSER</code>. Remember that46 <span class="application">pg_dump</span> connections are subject to the normal47 client authentication mechanisms (which are described in <a class="xref" href="client-authentication.html" title="Chapter 21. Client Authentication">Chapter 21</a>).48 </p><p>49 An important advantage of <span class="application">pg_dump</span> over the other backup50 methods described later is that <span class="application">pg_dump</span>'s output can51 generally be re-loaded into newer versions of <span class="productname">PostgreSQL</span>,52 whereas file-level backups and continuous archiving are both extremely53 server-version-specific. <span class="application">pg_dump</span> is also the only method54 that will work when transferring a database to a different machine55 architecture, such as going from a 32-bit to a 64-bit server.56 </p><p>57 Dumps created by <span class="application">pg_dump</span> are internally consistent,58 meaning, the dump represents a snapshot of the database at the time59 <span class="application">pg_dump</span> began running. <span class="application">pg_dump</span> does not60 block other operations on the database while it is working.61 (Exceptions are those operations that need to operate with an62 exclusive lock, such as most forms of <code class="command">ALTER TABLE</code>.)63 </p><div class="sect2" id="BACKUP-DUMP-RESTORE"><div class="titlepage"><div><div><h3 class="title">26.1.1. Restoring the Dump <a href="#BACKUP-DUMP-RESTORE" class="id_link">#</a></h3></div></div></div><p>64 Text files created by <span class="application">pg_dump</span> are intended to65 be read in by the <span class="application">psql</span> program. The66 general command form to restore a dump is67</p><pre class="synopsis">68psql <em class="replaceable"><code>dbname</code></em> < <em class="replaceable"><code>dumpfile</code></em>69</pre><p>70 where <em class="replaceable"><code>dumpfile</code></em> is the71 file output by the <span class="application">pg_dump</span> command. The database <em class="replaceable"><code>dbname</code></em> will not be created by this72 command, so you must create it yourself from <code class="literal">template0</code>73 before executing <span class="application">psql</span> (e.g., with74 <code class="literal">createdb -T template0 <em class="replaceable"><code>dbname</code></em></code>). <span class="application">psql</span>75 supports options similar to <span class="application">pg_dump</span> for specifying76 the database server to connect to and the user name to use. See77 the <a class="xref" href="app-psql.html" title="psql"><span class="refentrytitle"><span class="application">psql</span></span></a> reference page for more information.78 Non-text file dumps are restored using the <a class="xref" href="app-pgrestore.html" title="pg_restore"><span class="refentrytitle"><span class="application">pg_restore</span></span></a> utility.79 </p><p>80 Before restoring an SQL dump, all the users who own objects or were81 granted permissions on objects in the dumped database must already82 exist. If they do not, the restore will fail to recreate the83 objects with the original ownership and/or permissions.84 (Sometimes this is what you want, but usually it is not.)85 </p><p>86 By default, the <span class="application">psql</span> script will continue to87 execute after an SQL error is encountered. You might wish to run88 <span class="application">psql</span> with89 the <code class="literal">ON_ERROR_STOP</code> variable set to alter that90 behavior and have <span class="application">psql</span> exit with an91 exit status of 3 if an SQL error occurs:92</p><pre class="programlisting">93psql --set ON_ERROR_STOP=on <em class="replaceable"><code>dbname</code></em> < <em class="replaceable"><code>dumpfile</code></em>94</pre><p>95 Either way, you will only have a partially restored database.96 Alternatively, you can specify that the whole dump should be97 restored as a single transaction, so the restore is either fully98 completed or fully rolled back. This mode can be specified by99 passing the <code class="option">-1</code> or <code class="option">--single-transaction</code>100 command-line options to <span class="application">psql</span>. When using this101 mode, be aware that even a minor error can rollback a102 restore that has already run for many hours. However, that might103 still be preferable to manually cleaning up a complex database104 after a partially restored dump.105 </p><p>106 The ability of <span class="application">pg_dump</span> and <span class="application">psql</span> to107 write to or read from pipes makes it possible to dump a database108 directly from one server to another, for example:109</p><pre class="programlisting">110pg_dump -h <em class="replaceable"><code>host1</code></em> <em class="replaceable"><code>dbname</code></em> | psql -h <em class="replaceable"><code>host2</code></em> <em class="replaceable"><code>dbname</code></em>111</pre><p>112 </p><div class="important"><h3 class="title">Important</h3><p>113 The dumps produced by <span class="application">pg_dump</span> are relative to114 <code class="literal">template0</code>. This means that any languages, procedures,115 etc. added via <code class="literal">template1</code> will also be dumped by116 <span class="application">pg_dump</span>. As a result, when restoring, if you are117 using a customized <code class="literal">template1</code>, you must create the118 empty database from <code class="literal">template0</code>, as in the example119 above.120 </p></div><p>121 After restoring a backup, it is wise to run <a class="link" href="sql-analyze.html" title="ANALYZE"><code class="command">ANALYZE</code></a> on each122 database so the query optimizer has useful statistics;123 see <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-STATISTICS" title="25.1.3. Updating Planner Statistics">Section 25.1.3</a>124 and <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="25.1.6. The Autovacuum Daemon">Section 25.1.6</a> for more information.125 For more advice on how to load large amounts of data126 into <span class="productname">PostgreSQL</span> efficiently, refer to <a class="xref" href="populate.html" title="14.4. Populating a Database">Section 14.4</a>.127 </p></div><div class="sect2" id="BACKUP-DUMP-ALL"><div class="titlepage"><div><div><h3 class="title">26.1.2. Using <span class="application">pg_dumpall</span> <a href="#BACKUP-DUMP-ALL" class="id_link">#</a></h3></div></div></div><p>128 <span class="application">pg_dump</span> dumps only a single database at a time,129 and it does not dump information about roles or tablespaces130 (because those are cluster-wide rather than per-database).131 To support convenient dumping of the entire contents of a database132 cluster, the <a class="xref" href="app-pg-dumpall.html" title="pg_dumpall"><span class="refentrytitle"><span class="application">pg_dumpall</span></span></a> program is provided.133 <span class="application">pg_dumpall</span> backs up each database in a given134 cluster, and also preserves cluster-wide data such as role and135 tablespace definitions. The basic usage of this command is:136</p><pre class="synopsis">137pg_dumpall > <em class="replaceable"><code>dumpfile</code></em>138</pre><p>139 The resulting dump can be restored with <span class="application">psql</span>:140</p><pre class="synopsis">141psql -f <em class="replaceable"><code>dumpfile</code></em> postgres142</pre><p>143 (Actually, you can specify any existing database name to start from,144 but if you are loading into an empty cluster then <code class="literal">postgres</code>145 should usually be used.) It is always necessary to have146 database superuser access when restoring a <span class="application">pg_dumpall</span>147 dump, as that is required to restore the role and tablespace information.148 If you use tablespaces, make sure that the tablespace paths in the149 dump are appropriate for the new installation.150 </p><p>151 <span class="application">pg_dumpall</span> works by emitting commands to re-create152 roles, tablespaces, and empty databases, then invoking153 <span class="application">pg_dump</span> for each database. This means that while154 each database will be internally consistent, the snapshots of155 different databases are not synchronized.156 </p><p>157 Cluster-wide data can be dumped alone using the158 <span class="application">pg_dumpall</span> <code class="option">--globals-only</code> option.159 This is necessary to fully backup the cluster if running the160 <span class="application">pg_dump</span> command on individual databases.161 </p></div><div class="sect2" id="BACKUP-DUMP-LARGE"><div class="titlepage"><div><div><h3 class="title">26.1.3. Handling Large Databases <a href="#BACKUP-DUMP-LARGE" class="id_link">#</a></h3></div></div></div><p>162 Some operating systems have maximum file size limits that cause163 problems when creating large <span class="application">pg_dump</span> output files.164 Fortunately, <span class="application">pg_dump</span> can write to the standard165 output, so you can use standard Unix tools to work around this166 potential problem. There are several possible methods:167 </p><p><strong>Use compressed dumps. </strong>168 You can use your favorite compression program, for example169 <span class="application">gzip</span>:170 171</p><pre class="programlisting">172pg_dump <em class="replaceable"><code>dbname</code></em> | gzip > <em class="replaceable"><code>filename</code></em>.gz173</pre><p>174 175 Reload with:176 177</p><pre class="programlisting">178gunzip -c <em class="replaceable"><code>filename</code></em>.gz | psql <em class="replaceable"><code>dbname</code></em>179</pre><p>180 181 or:182 183</p><pre class="programlisting">184cat <em class="replaceable"><code>filename</code></em>.gz | gunzip | psql <em class="replaceable"><code>dbname</code></em>185</pre><p>186 </p><p><strong>Use <code class="command">split</code>. </strong>187 The <code class="command">split</code> command188 allows you to split the output into smaller files that are189 acceptable in size to the underlying file system. For example, to190 make 2 gigabyte chunks:191 192</p><pre class="programlisting">193pg_dump <em class="replaceable"><code>dbname</code></em> | split -b 2G - <em class="replaceable"><code>filename</code></em>194</pre><p>195 196 Reload with:197 198</p><pre class="programlisting">199cat <em class="replaceable"><code>filename</code></em>* | psql <em class="replaceable"><code>dbname</code></em>200</pre><p>201 202 If using GNU <span class="application">split</span>, it is possible to203 use it and <span class="application">gzip</span> together:204 205</p><pre class="programlisting">206pg_dump <em class="replaceable"><code>dbname</code></em> | split -b 2G --filter='gzip > $FILE.gz'207</pre><p>208 209 It can be restored using <code class="command">zcat</code>.210 </p><p><strong>Use <span class="application">pg_dump</span>'s custom dump format. </strong>211 If <span class="productname">PostgreSQL</span> was built on a system with the212 <span class="application">zlib</span> compression library installed, the custom dump213 format will compress data as it writes it to the output file. This will214 produce dump file sizes similar to using <code class="command">gzip</code>, but it215 has the added advantage that tables can be restored selectively. The216 following command dumps a database using the custom dump format:217 218</p><pre class="programlisting">219pg_dump -Fc <em class="replaceable"><code>dbname</code></em> > <em class="replaceable"><code>filename</code></em>220</pre><p>221 222 A custom-format dump is not a script for <span class="application">psql</span>, but223 instead must be restored with <span class="application">pg_restore</span>, for example:224 225</p><pre class="programlisting">226pg_restore -d <em class="replaceable"><code>dbname</code></em> <em class="replaceable"><code>filename</code></em>227</pre><p>228 229 See the <a class="xref" href="app-pgdump.html" title="pg_dump"><span class="refentrytitle"><span class="application">pg_dump</span></span></a> and <a class="xref" href="app-pgrestore.html" title="pg_restore"><span class="refentrytitle"><span class="application">pg_restore</span></span></a> reference pages for details.230 </p><p>231 For very large databases, you might need to combine <code class="command">split</code>232 with one of the other two approaches.233 </p><p><strong>Use <span class="application">pg_dump</span>'s parallel dump feature. </strong>234 To speed up the dump of a large database, you can use235 <span class="application">pg_dump</span>'s parallel mode. This will dump236 multiple tables at the same time. You can control the degree of237 parallelism with the <code class="command">-j</code> parameter. Parallel dumps238 are only supported for the "directory" archive format.239 240</p><pre class="programlisting">241pg_dump -j <em class="replaceable"><code>num</code></em> -F d -f <em class="replaceable"><code>out.dir</code></em> <em class="replaceable"><code>dbname</code></em>242</pre><p>243 244 You can use <code class="command">pg_restore -j</code> to restore a dump in parallel.245 This will work for any archive of either the "custom" or the "directory"246 archive mode, whether or not it has been created with <code class="command">pg_dump -j</code>.247 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="backup.html" title="Chapter 26. Backup and Restore">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="backup.html" title="Chapter 26. Backup and Restore">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="backup-file.html" title="26.2. File System Level Backup">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 26. Backup and Restore </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 26.2. File System Level Backup</td></tr></table></div></body></html>