pg_dumpall.sgml 8.13 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
<refentry id="APP-PG-DUMPALL">
 <refmeta>
  <refentrytitle>
   <application>pg_dumpall</application>
  </refentrytitle>
  <refmiscinfo>Application</refmiscinfo>
 </refmeta>
 <refnamediv>
  <refname>
   <application>pg_dumpall</application>
  </refname>
  <refpurpose>
   Extract all <productname>Postgres</productname> databases into a script file
  </refpurpose>
 </refnamediv>
 <refsynopsisdiv>
  <refsynopsisdivinfo>
   <date>1998-10-04</date>
  </refsynopsisdivinfo>
  <synopsis>
21
pg_dumpall
22 23
pg_dumpall [ -h <replaceable class="parameter">host</replaceable> ] [ -p <replaceable class="parameter">port</replaceable> ] [ -a ] [ -d ] [ -D ] [ -o ] [ -s ] [ -u ] [ -v ] [ -x ]
  </synopsis>
24

25 26 27 28 29 30 31 32 33
  <refsect2 id="R2-APP-PG-DUMPALL-1">
   <refsect2info>
    <date>1998-10-04</date>
   </refsect2info>
   <title>
    Inputs
   </title>
   <para>
    <application>pg_dumpall</application> accepts the following command line arguments:
34

35 36 37 38 39 40 41 42 43
    <variablelist>
     <varlistentry>
      <term>-a</term>
      <listitem>
       <para>
	Dump out only the data, no schema (definitions).
       </para>
      </listitem>
     </varlistentry>
44

45 46 47 48 49 50 51 52
     <varlistentry>
      <term>-d</term>
      <listitem>
       <para>
	Dump data as proper insert strings.
       </para>
      </listitem>
     </varlistentry>
53

54 55 56 57 58 59 60 61
     <varlistentry>
      <term>-D</term>
      <listitem>
       <para>
	Dump data as inserts with attribute names
       </para>
      </listitem>
     </varlistentry>
62

63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225
     <varlistentry>
      <term>-n</term>
      <listitem>
       <para>
	Suppress double quotes around identifiers unless absolutely necessary.
	This may cause trouble loading this dumped data if there are reserved words
	used for identifiers.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-o</term>
      <listitem>
       <para>
	Dump object identifiers (<acronym>OID</acronym>s) for every table.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-s</term>
      <listitem>
       <para>
	Dump out only the schema (definitions), no data.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-u</term>
      <listitem>
       <para>
	Use password authentication. Prompts for username and password.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-v</term>
      <listitem>
       <para>
	Specifies verbose mode
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-x</term>
      <listitem>
       <para>
	Prevent dumping ACLs (grant/revoke commands) and table ownership information.
       </para>
      </listitem>
     </varlistentry>

    </variablelist>
   </para>

   <para>
    <application>pg_dumpall</application> also accepts 
    the following command line arguments for connection parameters:

    <variablelist>
     <varlistentry>
      <term>-h <replaceable class="parameter">host</replaceable></term>
      <listitem>
       <para>
	Specifies the hostname of the machine on which the 
	<application>postmaster</application>
	is running.  Defaults to using a local Unix domain socket
	rather than an IP connection..
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-p <replaceable class="parameter">port</replaceable></term>
      <listitem>
       <para>
	Specifies the Internet TCP/IP port or local Unix domain socket file 
	extension on which the <application>postmaster</application>
	is listening for connections.  The port number defaults to 5432,
	or the value of the <envar>PGPORT</envar>
	environment variable (if set).
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-u</term>
      <listitem>
       <para>
	Use password authentication. 
	Prompts for
	<replaceable class="parameter">username</replaceable>
	and <replaceable class="parameter">password</replaceable>.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>

  <refsect2 id="R2-APP-PG-DUMPALL-2">
   <refsect2info>
    <date>1998-10-04</date>
   </refsect2info>
   <title>
    Outputs
   </title>
   <para>
    <application>pg_dumpall</application> will create a file or
    write to <filename>stdout</filename>.

    <variablelist>
     <varlistentry>
      <term><computeroutput>
Connection to database 'template1' failed.
connectDB() failed: Is the postmaster running and accepting connections at 'UNIX Socket' on port '<replaceable class="parameter">port</replaceable>'?
       </computeroutput></term>
      <listitem>
       <para>
	<application>pg_dumpall</application> could not attach to the 
	<application>postmaster</application> 
	process on the specified host and port.  If you see this message,
	ensure that the <application>postmaster</application> 
	is running on the proper host and that you have specified the proper
	port.  If your site uses an authentication system, ensure that you
	have obtained the required authentication credentials.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><computeroutput>
Connection to database '<replaceable class="parameter">dbname</replaceable>' failed.
FATAL 1:  SetUserId: user '<replaceable class="parameter">username</replaceable>' is not in 'pg_shadow'
       </computeroutput></term>
      <listitem>
       <para>
	You do not have a valid entry in the relation <literal>pg_shadow</literal>
	and and will not be allowed to access <productname>Postgres</productname>. 
	Contact your <productname>Postgres</productname> administrator.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><computeroutput>
dumpSequence(<replaceable class="parameter">table</replaceable>): SELECT failed
       </computeroutput></term>
      <listitem>
       <para>
	You do not have permission to read the database.
	Contact your <productname>Postgres</productname> site administrator.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>

   <note>
226
    <para>
227 228 229 230 231
     <application>pg_dumpall</application> internally executes
     <command>SELECT</command> statements. If you have problems running
     <application>pg_dumpall</application>,
     make sure you are able to select information from the database using, for
     example, <application>psql</application>.
232
    </para>
233 234 235
   </note>
  </refsect2>
 </refsynopsisdiv>
236

237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262
 <refsect1 id="R1-APP-PG-DUMPALL-1">
  <refsect1info>
   <date>1998-10-04</date>
  </refsect1info>
  <title>
   Description
  </title>
  <para>
   <application>pg_dumpall</application>
   is a utility for dumping out all Postgres databases into one file.
   It also dumps the pg_shadow table, which is global to all databases.
   <application>pg_dumpall</application> includes in this file the proper commands
   to automatically create each dumped database before loading.
  </para>
  <para>
   <application>pg_dumpall</application> takes all <application>pg_dump</application>
   options, but <option>-f</option>, <option>-t</option> and 
   <replaceable class="parameter">dbname</replaceable>
   should be omitted.
  </para>
  <para>
   Refer to 
   <xref endterm="pg-dump" linkend="app-pg-dump">
   for more information on this capability.
  </para>
 </refsect1>
263

264 265 266 267 268 269 270 271 272
 <refsect1 id="R1-APP-PG-DUMPALL-2">
  <refsect1info>
   <date>1998-10-04</date>
  </refsect1info>
  <title>
   Usage
  </title>
  <para>
   To dump all databases:
273

274 275 276 277 278
   <programlisting>
% pg_dumpall -o > db.out
   </programlisting>

   <tip>
279
    <para>
280 281
     You can use most <application>pg_dump</application> options
     for <application>pg_dumpall</application>.
282
    </para>
283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316
   </tip>
  </para>
  <para>
   To reload this database:

   <programlisting>
% psql -e template1 < db.out
   </programlisting>
  </para>
  <tip>
   <para>
    You can use most <application>psql</application> options
    when reloading.
   </para>
  </tip>
 </refsect1>
</refentry>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->