diff options
| -rw-r--r-- | doc/src/sgml/admin.sgml | 15 | ||||
| -rw-r--r-- | doc/src/sgml/backup.sgml | 414 | ||||
| -rw-r--r-- | doc/src/sgml/manage-ag.sgml | 424 | ||||
| -rw-r--r-- | doc/src/sgml/start-ag.sgml | 123 | ||||
| -rw-r--r-- | doc/src/sgml/user-manag.sgml | 2 |
5 files changed, 649 insertions, 329 deletions
diff --git a/doc/src/sgml/admin.sgml b/doc/src/sgml/admin.sgml index c8fb66b4112..7eb80a71073 100644 --- a/doc/src/sgml/admin.sgml +++ b/doc/src/sgml/admin.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/Attic/admin.sgml,v 1.23 2000/06/18 21:24:51 petere Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/Attic/admin.sgml,v 1.24 2000/06/30 16:14:21 petere Exp $ Postgres Administrator's Guide. Derived from postgres.sgml. @@ -21,16 +21,15 @@ Derived from postgres.sgml. <!entity install SYSTEM "install.sgml"> <!entity installw SYSTEM "install-win32.sgml"> <!entity layout SYSTEM "layout.sgml"> -<!entity manage-ag SYSTEM "manage-ag.sgml"> <!entity ports SYSTEM "ports.sgml"> -<!entity recovery SYSTEM "recovery.sgml"> -<!entity regress SYSTEM "regress.sgml"> <!entity release SYSTEM "release.sgml"> <!entity runtime SYSTEM "runtime.sgml"> <!entity client-auth SYSTEM "client-auth.sgml"> +<!entity manage-ag SYSTEM "manage-ag.sgml"> <!entity user-manag SYSTEM "user-manag.sgml"> -<!entity start-ag SYSTEM "start-ag.sgml"> -<!entity trouble SYSTEM "trouble.sgml"> +<!entity backup SYSTEM "backup.sgml"> +<!entity recovery SYSTEM "recovery.sgml"> +<!entity regress SYSTEM "regress.sgml"> <!entity biblio SYSTEM "biblio.sgml"> ]> @@ -113,9 +112,9 @@ Your name here... &installw; &runtime; &client-auth; - &user-manag; - &start-ag; &manage-ag; + &user-manag; + &backup; &recovery; ®ress; &release; diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml new file mode 100644 index 00000000000..842cfec9d4c --- /dev/null +++ b/doc/src/sgml/backup.sgml @@ -0,0 +1,414 @@ +<!-- $Header: /cvsroot/pgsql/doc/src/sgml/backup.sgml,v 2.1 2000/06/30 16:14:21 petere Exp $ --> +<chapter id="backup"> + <title>Backup and Restore</title> + + <para> + As everything that contains valuable data, <productname>Postgres</> + databases should be backed up regularly. While the procedure is + essentially simple, it is important to have a basic understanding of + the underlying techniques and assumptions. + </para> + + <para> + There are two fundamentally different approaches to backing up + <productname>Postgres</> data: + <itemizedlist> + <listitem><para><acronym>SQL</> dump</para></listitem> + <listitem><para>File system level backup</para></listitem> + </itemizedlist> + </para> + + <sect1> + <title><acronym>SQL</> Dump</title> + + <para> + The idea behind this method is to generate a text file with SQL + commands that, when fed back to the server, will recreate the + database in the same state as it was at the time of the dump. + <productname>Postgres</> provides the utility program + <application>pg_dump</> for this purpose. The basic usage of this + command is: +<synopsis> +pg_dump <replaceable class="parameter">dbname</replaceable> > <replaceable class="parameter">outfile</replaceable> +</synopsis> + As you see, <application>pg_dump</> writes its results to the + standard output. We will see below how this can be useful. + </para> + + <para> + <application>pg_dump</> is a regular <productname>Postgres</> + client application (albeit a particularly clever one). This means + that you can do this backup procedure from any remote host that has + access to the database. But remember that <application>pg_dump</> + does not operate with special permissions. In particular, you must + have read access to all tables that you want to back up, so in + practice you almost always have to be a database superuser. + </para> + + <para> + To specify which databaser server <application>pg_dump</> should + contact, use the command line options <option>-h + <replaceable>host</></> and <option>-p <replaceable>port</></>. The + default host is the local host or whatever your + <envar>PGHOST</envar> environment variable specifies. Similarly, + the default port is indicated by the <envar>PGPORT</envar> + environment variable or, failing that, by the compiled-in default. + (Conveniently, the server will normally have the same compiled-in + default.) + </para> + + <para> + As any other <productname>Postgres</> client application, + <application>pg_dump</> will by default connect with the database + user name that is equal to the current Unix user name. To override + this, either specify the <option>-u</option> option to force a prompt for + the user name, or set the environment variable + <envar>PGUSER</envar>. Remember that <application>pg_dump</> + connections are subject to the normal client authentication + mechanisms (which are described in <xref + linkend="client-authentication">). + </para> + + <para> + Dumps created by <application>pg_dump</> are internally consistent, + that is, updates to the database while <application>pg_dump</> is + running will not be in the dump. <application>pg_dump</> does not + block other operations on the database while it is working. + (Exceptions are those operations that need to operate with an + exclusive lock, such as <command>VACUUM</command>.) + </para> + + <important> + <para> + When your database schema relies on OIDs (for instances as foreign + keys) you must instruct <application>pg_dump</> to dump the OIDs + as well. To do this, use the <option>-o</option> command line + option. + </para> + </important> + + <sect2> + <title>Restoring the dump</title> + + <para> + The text files created by <application>pg_dump</> are intended to + be read in by the <application>psql</application> program. The + general command form to restore a dump is +<synopsis> +psql <replaceable class="parameter">dbname</replaceable> < <replaceable class="parameter">infile</replaceable> +</synopsis> + where <replaceable class="parameter">infile</replaceable> is what + you used as <replaceable class="parameter">outfile</replaceable> + for the pg_dump command. The database <replaceable + class="parameter">dbname</replaceable> will not be created by this + command, you must do that yourself before executing + <application>psql</> (e.g., with <userinput>createdb <replaceable + class="parameter">dbname</></userinput>). <application>psql</> + supports similar options to <application>pg_dump</> for + controlling the database server location and the user names. See + its reference page for more information. + </para> + + <para> + If the objects in the original database were owned by different + users, then the dump will instruct <application>psql</> to connect + as each affected user in turn and then create the relevant + objects. This way the original ownership is preserved. This also + means, however, that all these user must already exist, and + furthermore that you must be allowed to connect as each of them. + It might therefore be necessary to temporarily relax the client + authentication settings. + </para> + + <para> + The ability of <application>pg_dump</> and <application>psql</> to + write or read from pipes also make it possible to dump a database + directory from one server to another, for example + <informalexample> +<programlisting> +pg_dump -h <replaceable>host1</> <replaceable>dbname</> | psql -h <replaceable>host2</> <replaceable>dbname</> +</programlisting> + </informalexample> + </para> + </sect2> + + <sect2> + <title>Using <command>pg_dumpall</></title> + + <para> + The above mechanism is cumbersome and inappropriate when backing + up an entire database cluster. For this reason the + <application>pg_dumpall</> program is provided. + <application>pg_dumpall</> backs up each database in a given + cluster and also makes sure that the state of global data such as + users and groups is preserved. The call sequence for + <application>pg_dumpall</> is simply +<synopsis> +pg_dumpall > <replaceable>outfile</> +</synopsis> + The resulting dumps can be restored with <application>psql</> as + described above. But in this case it is definitely necessary that + you have database superuser access, as that is required to restore + the user and group information. + </para> + + <para> + <application>pg_dumpall</application> has one little flaw: It is + not prepared for interactively authenticating to each database it + dumps. If you are using password authentication then you need to + set it the environment variable <envar>PGPASSWORD</envar> to + communicate the password the the underlying calls to + <application>pg_dump</>. More severely, if you have different + passwords set up for each database, then + <application>pg_dumpall</> will fail. You can either choose a + different authentication mechanism for the purposes of backup or + adjust the <filename>pg_dumpall</filename> shell script to your + needs. + </para> + </sect2> + + <sect2> + <title>Large Databases</title> + + <note> + <title>Acknowledgement</title> + <para> + Originally written by Hannu Krosing + (<email>hannu@trust.ee</email>) on 1999-06-19 + </para> + </note> + + <para> + Since <productname>Postgres</productname> allows tables larger + than the maximum file size on your system, it can be problematic + to dump the table to a file, since the resulting file will likely + be larger than the maximum size allowed by your system. As + <application>pg_dump</> writes to the standard output, you can + just use standard *nix tools to work around this possible problem. + </para> + + <formalpara> + <title>Use compressed dumps.</title> + <para> + Use your favorite compression program, for example + <application>gzip</application>. + +<programlisting> +pg_dump <replaceable class="parameter">dbname</replaceable> | gzip > <replaceable class="parameter">filename</replaceable>.gz +</programlisting> + + Reload with + +<programlisting> +createdb <replaceable class="parameter">dbname</replaceable> +gunzip -c <replaceable class="parameter">filename</replaceable>.gz | psql <replaceable class="parameter">dbname</replaceable> +</programlisting> + + or + +<programlisting> +cat <replaceable class="parameter">filename</replaceable>.gz | gunzip | psql <replaceable class="parameter">dbname</replaceable> +</programlisting> + </para> + </formalpara> + + <formalpara> + <title>Use <application>split</>.</title> + <para> + This allows you to split the output into pieces that are + acceptable in size to the underlying file system. For example, to + make chunks of 1 megabyte: + + <informalexample> +<programlisting> +pg_dump <replaceable class="parameter">dbname</replaceable> | split -b 1m - <replaceable class="parameter">filename</replaceable> +</programlisting> + </informalexample> + + Reload with + + <informalexample> +<programlisting> +createdb <replaceable class="parameter">dbname</replaceable> +cat <replaceable class="parameter">filename</replaceable>.* | psql <replaceable class="parameter">dbname</replaceable> +</programlisting> + </informalexample> + </para> + </formalpara> + + </sect2> + + <sect2> + <title>Caveats</title> + + <para> + <application>pg_dump</> (and by implication + <application>pg_dumpall</>) has a few limitations which stem from + the difficulty to reconstruct certain information from the system + catalogs. + </para> + + <para> + Specifically, the order in which <application>pg_dump</> writes + the objects is not very sophisticated. This can lead to problems + for example when functions are used as column default values. The + only answer is to manually reorder the dump. If you created + circular dependencies in your schema then you will have more work + to do. + </para> + + <para> + Large objects are not handled by <application>pg_dump</>. The + directory <filename>contrib/pg_dumplo</> of the + <productname>Postgres</> source tree contains a program that can + do that. + </para> + + <para> + Please familiarize yourself with the + <citerefentry><refentrytitle>pg_dump</></> reference page. + </para> + </sect2> + </sect1> + + <sect1> + <title>File system level backup</title> + + <para> + An alternative backup strategy is to directly copy the files that + <productname>Postgres</> uses to store the data in the database. In + <xref linkend="creating-cluster"> it is explained where these files + are located, but you have probably found them already if you are + interested in this method. You can use whatever method you prefer + for doing usual file system backups, for example + <informalexample> +<programlisting> +tar -cf backup.tar /usr/local/pgsql/data +</programlisting> + </informalexample> + </para> + + <para> + There are two restrictions, however, which make this method + impractical, or at least inferior to the <application>pg_dump</> + method: + + <orderedlist> + <listitem> + <para> + The database server <emphasis>must</> be shut down in order to + get a usable backup. Half-way measures such as disallowing all + connections will not work as there is always some buffering + going on. For this reason it is also not advisable to trust file + systems that claim to support <quote>consistent + snapshots</quote>. Information about stopping the server can be + found in <xref linkend="postmaster-shutdown">. + </para> + + <para> + Needless to say that you also need to shut down the server + before restoring the data. + </para> + </listitem> + + <listitem> + <para> + If you have dug into the details of the file system layout you + may be tempted to try to back up or restore only certain + individual tables or databases from their respective files or + directories. This will <emphasis>not</> work because the + information contained in these files contains only half the + truth. The other half is in the file + <filename>pg_log</filename>, which contains the commit status of + all transactions. A table file is only usable with this + information. Of course it is also impossible to restore only a + table and the associated <filename>pg_log</filename> file + because that will render all other tables in the database + cluster useless. + </para> + </listitem> + </orderedlist> + </para> + + <para> + Also note that the file system backup will not necessarily be + smaller than an SQL dump. On the contrary, it will most likely be + larger. (<application>pg_dump</application> does not need to dump + the contents of indices for example, just the commands to recreate + them.) + </para> + + </sect1> + + <sect1> + <title>Migration between releases</title> + + <para> + As a general rule, the internal data storage format is subject to + change between releases of <productname>Postgres</>. This does not + apply to different <quote>patch levels</quote>, these always have + compatible storage formats. For example, releases 6.5.3, 7.0.1, and + 7.1 are not compatible, whereas 7.0.2 and 7.0.1 are. When you + update between compatible versions, then you can simply reuse the + data area in disk by the new executables. Otherwise you need to + <quote>back up</> your data and <quote>restore</> it on the new + server, using <application>pg_dump</>. (There are checks in place + that prevent you from doing the wrong thing, so no harm can be done + by confusing these things.) The precise installation procedure is + not subject of this section, the <citetitle>Installation + Instructions</citetitle> carry these details. + </para> + + <para> + The least downtime can be achieved by installing the new server in + a different directory and running both the old and the new servers + in parallel, on different ports. Then you can use something like +<informalexample> +<programlisting> +pg_dumpall -p 5432 | psql -d template1 -p 6543 +</programlisting> +</informalexample> + to transfer your data, or use an intermediate file if you want. + Then you can shut down the old server and start the new server at + the port the old one was running at. You should make sure that the + database is not updated after you run <application>pg_dumpall</>, + otherwise you will obviously lose that data. See <xref + linkend="client-authentication"> for information on how to prohibit + access. In practice you probably want to test your client + applications on the new setup before switching over. + </para> + + <para> + If you cannot or do not want to run two servers in parallel you can + do the back up step before installing the new version, bring down + the server, move the old version out of the way, install the new + version, start the new server, restore the data. For example: +<informalexample> +<programlisting> +pg_dumpall > backup +kill -INT `cat /usr/local/pgsql/postmaster.pid` +mv /usr/local/pgsql /usr/local/pgsql.old +cd /usr/src/postgresql-7.1 +gmake install +initdb -D /usr/local/pgsql/data +postmaster -D /usr/local/pgsql/data +psql < backup +</programlisting> +</informalexample> + See <xref linkend="runtime"> about ways to start and stop the + server and other details. The installation instructions will advise + you of strategic places to perform these steps. + </para> + + <note> + <para> + When you <quote>move the old installation out of the way</quote> + it is no longer perfectly usable. Some parts of the installation + contain information about where the other parts are located. This + is usually not a big problem but if you plan on using two + installations in parallel for a while you should assign them + different installation directories at build time. + </para> + </note> + </sect1> +</chapter> diff --git a/doc/src/sgml/manage-ag.sgml b/doc/src/sgml/manage-ag.sgml index d27a2094a07..a05b984ef55 100644 --- a/doc/src/sgml/manage-ag.sgml +++ b/doc/src/sgml/manage-ag.sgml @@ -1,79 +1,221 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/manage-ag.sgml,v 2.8 2000/05/02 20:01:52 thomas Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/manage-ag.sgml,v 2.9 2000/06/30 16:14:21 petere Exp $ --> - <chapter id="manage-ag"> - <title>Managing a Database</title> +<chapter id="managing-databases"> + <title>Managing Databases</title> + + <para> + A database is a named collection of SQL objects (<quote>database + objects</quote>); every database object (tables, function, etc.) + belongs to one and only one database. An application that connects + to the database server specifies with its connection request the + name of the database it wants to connect to. It is not possible to + access more than once database per connection. (But an application + is not restricted in the number of connections it opens to the same + or other databases.) + </para> + + <note> + <para> + <acronym>SQL</> calls databases <quote>catalogs</>, but there is no + difference in practice. + </para> + </note> + + <para> + In order to create or drop databases, the <productname>Postgres</> + <application>postmaster</> must be up and running (see <xref + linkend="postmaster-start">). + </para> + + <sect1> + <title>Creating a Database</title> <para> - If the <productname>Postgres</productname> - <application>postmaster</application> is up and running we can create - some databases to experiment with. Here, we describe the - basic commands for managing a database. + Databases are created with the query language command + <command>CREATE DATABASE</command>: +<synopsis> +CREATE DATABASE <replaceable>name</> +</synopsis> + where <replaceable>name</> can be chosen freely. (Depending on the + current implementation, certain characters that are special to the + underlying operating system might be prohibited. There will be + run-time checks for that.) The current user automatically becomes + the owner of the new database. It is the privilege of the owner of + a database to remove it later on (which also removes all the + objects in it, even if they have a different owner). </para> - <sect1> - <title>Creating a Database</title> + <para> + The creation of databases is a restricted operation. See <xref + linkend="user-attributes"> how to grant permission. + </para> + <formalpara> + <title>Bootstrapping</title> <para> - Let's say you want to create a database named mydb. - You can do this with the following command: + Since you need to be connected to the database server in order to + execute the <command>CREATE DATABASE</command> command, the + question remains how the <emphasis>first</> database at any given + site can be created. The first database is always created by the + <command>initdb</> command when the data storage area is + initialized. (See <xref linkend="creating-cluster">.) This + database is called <literal>template1</> and cannot be deleted. So + to create the first <quote>real</> database you can connect to + <literal>template1</>. + </para> + </formalpara> - <programlisting> -% createdb <replaceable class="parameter">dbname</replaceable> - </programlisting> + <para> + The name <quote>template1</quote> is no accident: When a new + database is created, the template database is essentially cloned. + This means that any changes you make in <literal>template1</> are + propagated to all subsequently created databases. This implies that + you should not use the template database for real work, but when + used judiciously this feature can be convenient. + </para> + + <para> + As an extra convenience, there is also a program that you can + execute from the shell to create new databases, + <filename>createdb</>. + +<synopsis> +createdb <replaceable class="parameter">dbname</replaceable> +</synopsis> + + <filename>createdb</> does no magic. It connects to the template1 + database and executes the <command>CREATE DATABASE</> command, + exactly as described above. It uses <application>psql</> program + internally. The reference page on createdb contains the invocation + details. In particular, createdb without any arguments will create + a database with the current user name, which may or may not be what + you want. + </para> + + <sect2> + <title>Alternative Locations</title> + + <para> + It is possible to create a database in a location other than the + default. Remember that all database access occurs through the + database server backend, so that any location specified must be + accessible by the backend. + </para> + + <para> + Alternative database locations are referenced by an environment + variable which gives the absolute path to the intended storage + location. This environment variable must have been defined before + the backend was started. Any valid environment variable name may + be used to reference an alternative location, although using + variable names with a prefix of <literal>PGDATA</> is recommended + to avoid confusion and conflict with other variables. + </para> + + <para> + To create the variable in the environment of the server process + you must first shut down the server, define the variable, + initialize the data area, and finally restart the server. (See + <xref linkend="postmaster-shutdown"> and <xref + linkend="postmaster-start">.) To set an environment variable, type + <informalexample> +<programlisting> +PGDATA2=/home/postgres/data +</programlisting> + </informalexample> + in Bourne shells, or + <informalexample> +<programlisting> +setenv PGDATA2 /home/postgres/data +</programlisting> + </informalexample> + in csh or tcsh. You have to make sure that this environment + variable is always defined in the server environment, otherwise + you won't be able to access that database. Therefore you probably + want to set it in some sort of shell startup file or server + startup script. + </para> - <productname>Postgres</productname> allows you to create - any number of databases - at a given site and you automatically become the - database administrator of the database you just created. - Database names must have an alphabetic first - character and are limited to 31 characters in length. - Not every user has authorization to become a database - administrator. If <productname>Postgres</productname> - refuses to create databases - for you, then the site administrator needs to grant you - permission to create databases. Consult your site - administrator if this occurs. + <para> + To create a data storage area in <envar>PGDATA2</>, ensure that + <filename>/home/postgres</filename> already exists and is writable + by the user account that runs the server (see <xref + linkend="postgres-user">). Then from the command line, type + <informalexample> +<programlisting> +initlocation PGDATA2 +</programlisting> + </informalexample> + The you can restart the server. </para> - </sect1> - <sect1> - <title>Accessing a Database</title> + <para> + To create a database at the new location, use the command +<synopsis> +CREATE DATABASE <replaceable>name</> WITH LOCATION = '<replaceable>location</>' +</synopsis> + where <replaceable>location</> is the environment variable you + used, <envar>PGDATA2</> in this example. The <command>createdb</> + command has the option <option>-D</> for this purpose. + </para> <para> - Once you have constructed a database, you can access it - by: - - <itemizedlist spacing="compact" mark="bullet"> - <listitem> - <para> - running the <productname>Postgres</productname> terminal monitor program - (<application>psql</application>) which allows you to interactively - enter, edit, and execute <acronym>SQL</acronym> commands. - </para> - </listitem> - - <listitem> - <para> - writing a C program using the <literal>libpq</literal> subroutine - library. This allows you to submit <acronym>SQL</acronym> commands - from C and get answers and status messages back to - your program. This interface is discussed further - in the <citetitle>PostgreSQL Programmer's Guide</citetitle>. - </para> - </listitem> - </itemizedlist> - - You might want to start up <application>psql</application>, - to try out the examples in this manual. It can be activated for the - <replaceable class="parameter">dbname</replaceable> database by typing the command: + Database created at alternative locations using this method can be + accessed and dropped like any other database. + </para> + + <note> + <para> + It can also be possible to specify absolute paths directly to the + <command>CREATE DATABASE</> command without defining environment + variables. This is disallowed by default because it is a security + risk. To allow it, you must compile <productname>Postgres</> with + the C preprocessor macro <literal>ALLOW_ABSOLUTE_DBPATHS</> + defined. One way to do this is to run the compilation step like + this: <userinput>gmake COPT=-DALLOW_ABSOLUTE_DBPATHS all</>. + </para> + </note> + + </sect2> + </sect1> + + <sect1> + <title>Accessing a Database</title> + + <para> + Once you have constructed a database, you can access it by: + + <itemizedlist spacing="compact" mark="bullet"> + <listitem> + <para> + running the <productname>Postgres</productname> terminal monitor program + (<application>psql</application>) which allows you to interactively + enter, edit, and execute <acronym>SQL</acronym> commands. + </para> + </listitem> + + <listitem> + <para> + writing a C program using the <literal>libpq</literal> subroutine + library. This allows you to submit <acronym>SQL</acronym> commands + from C and get answers and status messages back to + your program. This interface is discussed further + in the <citetitle>PostgreSQL Programmer's Guide</citetitle>. + </para> + </listitem> + </itemizedlist> + + You might want to start up <application>psql</application>, + to try out the examples in this manual. It can be activated for the + <replaceable class="parameter">dbname</replaceable> database by typing the command: <programlisting> psql <replaceable class="parameter">dbname</replaceable> </programlisting> - You will be greeted with the following message: + You will be greeted with the following message: <programlisting> Welcome to psql, the PostgreSQL interactive terminal. @@ -138,151 +280,39 @@ Type: \copyright for distribution terms are denoted by "<literal>/* ... */</literal>", a convention borrowed from <productname>Ingres</productname>. </para> - </sect1> + </sect1> - <sect1> - <title>Destroying a Database</title> + <sect1> + <title>Destroying a Database</title> - <para> - If you are the database administrator for the database - mydb, you can destroy it using the following Unix command: - - <programlisting> -% dropdb <replaceable class="parameter">dbname</replaceable> - </programlisting> - - This action physically removes all of the Unix files - associated with the database and cannot be undone, so - this should only be done with a great deal of forethought. - </para> - - <para> - It is also possible to destroy a database from within an - <acronym>SQL</acronym> session by using - - <programlisting> -> drop database <replaceable class="parameter">dbname</replaceable> - </programlisting> - </para> - </sect1> - - <sect1> - <title>Backup and Restore</title> - - <caution> - <para> - Every database should be backed up on a regular basis. Since - <productname>Postgres</productname> manages it's own files in the - file system, it is <emphasis>not advisable</emphasis> to rely on - system backups of your file system for your database backups; - there is no guarantee that the files will be in a usable, - consistant state after restoration. - </para> - </caution> - - <para> - <productname>Postgres</productname> provides two utilities to - backup your system: <application>pg_dump</application> to backup - individual databases and - <application>pg_dumpall</application> to backup your installation - in one step. - </para> - - <para> - An individual database can be backed up using the following - command: - - <programlisting> -% pg_dump <replaceable class="parameter">dbname</replaceable> > <replaceable class="parameter">dbname</replaceable>.pgdump - </programlisting> - - and can be restored using - - <programlisting> -cat <replaceable class="parameter">dbname</replaceable>.pgdump | psql <replaceable class="parameter">dbname</replaceable> - </programlisting> - </para> - - <para> - This technique can be used to move databases to new - locations, and to rename existing databases. - </para> - - <sect2> - <title>Large Databases</title> - - <note> - <title>Author</title> - <para> - Written by <ulink url="hannu@trust.ee">Hannu Krosing</ulink> on - 1999-06-19. - </para> - </note> - - <para> - Since <productname>Postgres</productname> allows tables larger - than the maximum file size on your system, it can be problematic - to dump the table to a file, since the resulting file will likely - be larger than the maximum size allowed by your system.</para> + <para> + Databases are destroyed with the command <command>DROP DATABASE</command>: +<synopsis> +DROP DATABASE <replaceable>name</> +</synopsis> + Only the owner of the database (i.e., the user that created it) can + drop databases. Dropping a databases removes all objects that were + contained within the database. The destruction of a database cannot + be undone. + </para> - <para> - As <application>pg_dump</application> writes to stdout, - you can just use standard *nix tools - to work around this possible problem: - - <itemizedlist> - <listitem> - <para> - Use compressed dumps: - - <programlisting> -% pg_dump <replaceable class="parameter">dbname</replaceable> | gzip > <replaceable class="parameter">filename</replaceable>.dump.gz - </programlisting> - - reload with - - <programlisting> -% createdb <replaceable class="parameter">dbname</replaceable> -% gunzip -c <replaceable class="parameter">filename</replaceable>.dump.gz | psql <replaceable class="parameter">dbname</replaceable> - </programlisting> - -or - - <programlisting> -% cat <replaceable class="parameter">filename</replaceable>.dump.gz | gunzip | psql <replaceable class="parameter">dbname</replaceable> - </programlisting> - </para> - </listitem> - - <listitem> - <para> - Use split: - - <programlisting> -% pg_dump <replaceable class="parameter">dbname</replaceable> | split -b 1m - <replaceable class="parameter">filename</replaceable>.dump. - </programlisting> - -reload with - - <programlisting> -% createdb <replaceable class="parameter">dbname</replaceable> -% cat <replaceable class="parameter">filename</replaceable>.dump.* | pgsql <replaceable class="parameter">dbname</replaceable> - </programlisting> - </para> - </listitem> - </itemizedlist> - </para> + <para> + You cannot execute the <command>DROP DATABASE</command> command + while connected to the victim database. You can, however, be + connected to any other database, including the template1 database, + which would be the only option for dropping the last database of a + given cluster. + </para> - <para> - Of course, the name of the file - (<replaceable class="parameter">filename</replaceable>) and the - content of the <application>pg_dump</application> output need not - match the name of the database. Also, the restored database can - have an arbitrary new name, so this mechanism is also suitable - for renaming databases. - </para> - </sect2> - </sect1> + <para> + For convenience, there is also a shell program to drop databases: +<synopsis> +dropdb <replaceable class="parameter">dbname</replaceable> +</synopsis> + (Unlike <command>createdb</>, it is not the default action to drop + the database with the current user name.) + </para> + </sect1> </chapter> <!-- Keep this comment at the end of the file diff --git a/doc/src/sgml/start-ag.sgml b/doc/src/sgml/start-ag.sgml deleted file mode 100644 index 939100ff541..00000000000 --- a/doc/src/sgml/start-ag.sgml +++ /dev/null @@ -1,123 +0,0 @@ -<!-- -$Header: /cvsroot/pgsql/doc/src/sgml/Attic/start-ag.sgml,v 1.11 2000/06/18 21:24:51 petere Exp $ -- This file currently contains several small chapters. -- Each chapter should be split off into a separate source file... -- - thomas 1998-02-24 ---> - - <chapter id="disk"> - <title>Disk Management</title> - - <sect1> - <title>Alternate Locations</title> - - <para> - It is possible to create a database in a location other than the default - location for the installation. Remember that all database access actually - occurs through the database backend, so that any location specified must - be accessible by the backend. - </para> - - <para> - Alternate database locations are created and referenced by an environment variable - which gives the absolute path to the intended storage location. - This environment variable must have been defined before the backend was started - and must be writable by the postgres administrator account. - Any valid environment variable name may be used to reference an alternate - location, although using variable name with a prefix of PGDATA is recommended - to avoid confusion and conflict with other variables. - </para> - - <note> - <para> - In previous versions of <productname>Postgres</productname>, - it was also permissable to use an absolute path name - to specify an alternate storage location. - The environment variable style of specification - is to be preferred since it allows the site administrator more flexibility in - managing disk storage. - If you prefer using absolute paths, you may do so by defining - "ALLOW_ABSOLUTE_DBPATHS" and recompiling <productname>Postgres</productname> - To do this, either add this line - - <programlisting> -#define ALLOW_ABSOLUTE_DBPATHS 1 - </programlisting> - - to the file <filename>src/include/config.h</filename>, or by specifying - - <programlisting> - CFLAGS+= -DALLOW_ABSOLUTE_DBPATHS - </programlisting> - - in your <filename>Makefile.custom</filename>. - </para> - </note> - - <para> - Remember that database creation is actually performed by the database backend. - Therefore, any environment variable specifying an alternate location must have - been defined before the backend was started. To define an alternate location - PGDATA2 pointing to <filename>/home/postgres/data</filename>, first type - - <programlisting> -% setenv PGDATA2 /home/postgres/data - </programlisting> - - to define the environment variable to be used with subsequent commands. - Usually, you will want to define this variable in the - <productname>Postgres</productname> superuser's - <filename>.profile</filename> - or - <filename>.cshrc</filename> - initialization file to ensure that it is defined upon system startup. - Any environment variable can be used to reference alternate location, - although it is preferred that the variables be prefixed with "PGDATA" - to eliminate confusion and the possibility of conflicting with or - overwriting other variables. - </para> - - <para> - To create a data storage area in PGDATA2, ensure - that <filename>/home/postgres</filename> already exists and is writable - by the postgres administrator. - Then from the command line, type - - <programlisting> -% setenv PGDATA2 /home/postgres/data -% initlocation $PGDATA2 -Creating Postgres database system directory /home/postgres/data - -Creating Postgres database system directory /home/postgres/data/base - - </programlisting> - - </para> - <para> - To test the new location, create a database <database>test</database> by typing - - <programlisting> -% createdb -D PGDATA2 test -% dropdb test - </programlisting> - - </para> - </sect1> - </chapter> - -<!-- 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: ---> diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml index 255b5f9801a..942dde5b35f 100644 --- a/doc/src/sgml/user-manag.sgml +++ b/doc/src/sgml/user-manag.sgml @@ -56,7 +56,7 @@ CREATE USER <replaceable>name</replaceable> constrained in its login name by her real name.) </para> - <sect2> + <sect2 id="user-attributes"> <title>User attributes</title> <para> |