diff options
Diffstat (limited to 'doc/src/sgml/manage-ag.sgml')
| -rw-r--r-- | doc/src/sgml/manage-ag.sgml | 551 |
1 files changed, 551 insertions, 0 deletions
diff --git a/doc/src/sgml/manage-ag.sgml b/doc/src/sgml/manage-ag.sgml new file mode 100644 index 0000000..f9fe47b --- /dev/null +++ b/doc/src/sgml/manage-ag.sgml @@ -0,0 +1,551 @@ +<!-- doc/src/sgml/manage-ag.sgml --> + +<chapter id="managing-databases"> + <title>Managing Databases</title> + + <indexterm zone="managing-databases"><primary>database</primary></indexterm> + + <para> + Every instance of a running <productname>PostgreSQL</productname> + server manages one or more databases. Databases are therefore the + topmost hierarchical level for organizing <acronym>SQL</acronym> + objects (<quote>database objects</quote>). This chapter describes + the properties of databases, and how to create, manage, and destroy + them. + </para> + + <sect1 id="manage-ag-overview"> + <title>Overview</title> + + <indexterm zone="manage-ag-overview"> + <primary>schema</primary> + </indexterm> + + <para> + A small number of objects, like role, database, and tablespace + names, are defined at the cluster level and stored in the + <literal>pg_global</literal> tablespace. Inside the cluster are + multiple databases, which are isolated from each other but can access + cluster-level objects. Inside each database are multiple schemas, + which contain objects like tables and functions. So the full hierarchy + is: cluster, database, schema, table (or some other kind of object, + such as a function). + </para> + + <para> + When connecting to the database server, a client must specify the + database name in its connection request. + It is not possible to access more than one database per + connection. However, clients can open multiple connections to + the same database, or different databases. + Database-level security has two components: access control + (see <xref linkend="auth-pg-hba-conf"/>), managed at the + connection level, and authorization control + (see <xref linkend="ddl-priv"/>), managed via the grant system. + Foreign data wrappers (see <xref linkend="postgres-fdw"/>) + allow for objects within one database to act as proxies for objects in + other database or clusters. + The older dblink module (see <xref linkend="dblink"/>) provides a similar capability. + By default, all users can connect to all databases using all connection methods. + </para> + + <para> + If one <productname>PostgreSQL</productname> server cluster is planned to contain + unrelated projects or users that should be, for the most part, unaware + of each other, it is recommended to put them into separate databases and + adjust authorizations and access controls accordingly. + If the projects or users are interrelated, and thus should be able to use + each other's resources, they should be put in the same database but probably + into separate schemas; this provides a modular structure with namespace + isolation and authorization control. + More information about managing schemas is in <xref linkend="ddl-schemas"/>. + </para> + + <para> + While multiple databases can be created within a single cluster, it is advised + to consider carefully whether the benefits outweigh the risks and limitations. + In particular, the impact that having a shared WAL (see <xref linkend="wal"/>) + has on backup and recovery options. While individual databases in the cluster + are isolated when considered from the user's perspective, they are closely bound + from the database administrator's point-of-view. + </para> + + <para> + Databases are created with the <command>CREATE DATABASE</command> command + (see <xref linkend="manage-ag-createdb"/>) and destroyed with the + <command>DROP DATABASE</command> command + (see <xref linkend="manage-ag-dropdb"/>). + To determine the set of existing databases, examine the + <structname>pg_database</structname> system catalog, for example +<synopsis> +SELECT datname FROM pg_database; +</synopsis> + The <xref linkend="app-psql"/> program's <literal>\l</literal> meta-command + and <option>-l</option> command-line option are also useful for listing the + existing databases. + </para> + + <note> + <para> + The <acronym>SQL</acronym> standard calls databases <quote>catalogs</quote>, but there + is no difference in practice. + </para> + </note> + </sect1> + + <sect1 id="manage-ag-createdb"> + <title>Creating a Database</title> + + <indexterm><primary>CREATE DATABASE</primary></indexterm> + + <para> + In order to create a database, the <productname>PostgreSQL</productname> + server must be up and running (see <xref + linkend="server-start"/>). + </para> + + <para> + Databases are created with the SQL command + <xref linkend="sql-createdatabase"/>: +<synopsis> +CREATE DATABASE <replaceable>name</replaceable>; +</synopsis> + where <replaceable>name</replaceable> follows the usual rules for + <acronym>SQL</acronym> identifiers. The current role automatically + becomes the owner of the new database. It is the privilege of the + owner of a database to remove it later (which also removes all + the objects in it, even if they have a different owner). + </para> + + <para> + The creation of databases is a restricted operation. See <xref + linkend="role-attributes"/> for how to grant permission. + </para> + + <para> + 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</emphasis> database at any given + site can be created. The first database is always created by the + <command>initdb</command> command when the data storage area is + initialized. (See <xref linkend="creating-cluster"/>.) This + database is called + <literal>postgres</literal>.<indexterm><primary>postgres</primary></indexterm> So to + create the first <quote>ordinary</quote> database you can connect to + <literal>postgres</literal>. + </para> + + <para> + Two additional databases, + <literal>template1</literal><indexterm><primary>template1</primary></indexterm> + and + <literal>template0</literal>,<indexterm><primary>template0</primary></indexterm> + are also created during database cluster initialization. Whenever a + new database is created within the + cluster, <literal>template1</literal> is essentially cloned. + This means that any changes you make in <literal>template1</literal> are + propagated to all subsequently created databases. Because of this, + avoid creating objects in <literal>template1</literal> unless you want them + propagated to every newly created database. + <literal>template0</literal> is meant as a pristine copy of the original + contents of <literal>template1</literal>. It can be cloned instead + of <literal>template1</literal> when it is important to make a database + without any such site-local additions. More details + appear in <xref linkend="manage-ag-templatedbs"/>. + </para> + + <para> + As a convenience, there is a program you can + execute from the shell to create new databases, + <command>createdb</command>.<indexterm><primary>createdb</primary></indexterm> + +<synopsis> +createdb <replaceable class="parameter">dbname</replaceable> +</synopsis> + + <command>createdb</command> does no magic. It connects to the <literal>postgres</literal> + database and issues the <command>CREATE DATABASE</command> command, + exactly as described above. + The <xref linkend="app-createdb"/> reference page contains the invocation + details. Note that <command>createdb</command> without any arguments will create + a database with the current user name. + </para> + + <note> + <para> + <xref linkend="client-authentication"/> contains information about + how to restrict who can connect to a given database. + </para> + </note> + + <para> + Sometimes you want to create a database for someone else, and have them + become the owner of the new database, so they can + configure and manage it themselves. To achieve that, use one of the + following commands: +<programlisting> +CREATE DATABASE <replaceable>dbname</replaceable> OWNER <replaceable>rolename</replaceable>; +</programlisting> + from the SQL environment, or: +<programlisting> +createdb -O <replaceable>rolename</replaceable> <replaceable>dbname</replaceable> +</programlisting> + from the shell. + Only the superuser is allowed to create a database for + someone else (that is, for a role you are not a member of). + </para> + </sect1> + + <sect1 id="manage-ag-templatedbs"> + <title>Template Databases</title> + + <para> + <command>CREATE DATABASE</command> actually works by copying an existing + database. By default, it copies the standard system database named + <literal>template1</literal>.<indexterm><primary>template1</primary></indexterm> Thus that + database is the <quote>template</quote> from which new databases are + made. If you add objects to <literal>template1</literal>, these objects + will be copied into subsequently created user databases. This + behavior allows site-local modifications to the standard set of + objects in databases. For example, if you install the procedural + language <application>PL/Perl</application> in <literal>template1</literal>, it will + automatically be available in user databases without any extra + action being taken when those databases are created. + </para> + + <para> + However, <command>CREATE DATABASE</command> does not copy database-level + <command>GRANT</command> permissions attached to the source database. + The new database has default database-level permissions. + </para> + + <para> + There is a second standard system database named + <literal>template0</literal>.<indexterm><primary>template0</primary></indexterm> This + database contains the same data as the initial contents of + <literal>template1</literal>, that is, only the standard objects + predefined by your version of + <productname>PostgreSQL</productname>. <literal>template0</literal> + should never be changed after the database cluster has been + initialized. By instructing + <command>CREATE DATABASE</command> to copy <literal>template0</literal> instead + of <literal>template1</literal>, you can create a <quote>pristine</quote> user + database (one where no user-defined objects exist and where the system + objects have not been altered) that contains none of the site-local additions in + <literal>template1</literal>. This is particularly handy when restoring a + <literal>pg_dump</literal> dump: the dump script should be restored in a + pristine database to ensure that one recreates the correct contents + of the dumped database, without conflicting with objects that + might have been added to <literal>template1</literal> later on. + </para> + + <para> + Another common reason for copying <literal>template0</literal> instead + of <literal>template1</literal> is that new encoding and locale settings + can be specified when copying <literal>template0</literal>, whereas a copy + of <literal>template1</literal> must use the same settings it does. + This is because <literal>template1</literal> might contain encoding-specific + or locale-specific data, while <literal>template0</literal> is known not to. + </para> + + <para> + To create a database by copying <literal>template0</literal>, use: +<programlisting> +CREATE DATABASE <replaceable>dbname</replaceable> TEMPLATE template0; +</programlisting> + from the SQL environment, or: +<programlisting> +createdb -T template0 <replaceable>dbname</replaceable> +</programlisting> + from the shell. + </para> + + <para> + It is possible to create additional template databases, and indeed + one can copy any database in a cluster by specifying its name + as the template for <command>CREATE DATABASE</command>. It is important to + understand, however, that this is not (yet) intended as + a general-purpose <quote><command>COPY DATABASE</command></quote> facility. + The principal limitation is that no other sessions can be connected to + the source database while it is being copied. <command>CREATE + DATABASE</command> will fail if any other connection exists when it starts; + during the copy operation, new connections to the source database + are prevented. + </para> + + <para> + Two useful flags exist in <literal>pg_database</literal><indexterm><primary>pg_database</primary></indexterm> for each + database: the columns <literal>datistemplate</literal> and + <literal>datallowconn</literal>. <literal>datistemplate</literal> + can be set to indicate that a database is intended as a template for + <command>CREATE DATABASE</command>. If this flag is set, the database can be + cloned by any user with <literal>CREATEDB</literal> privileges; if it is not set, + only superusers and the owner of the database can clone it. + If <literal>datallowconn</literal> is false, then no new connections + to that database will be allowed (but existing sessions are not terminated + simply by setting the flag false). The <literal>template0</literal> + database is normally marked <literal>datallowconn = false</literal> to prevent its modification. + Both <literal>template0</literal> and <literal>template1</literal> + should always be marked with <literal>datistemplate = true</literal>. + </para> + + <note> + <para> + <literal>template1</literal> and <literal>template0</literal> do not have any special + status beyond the fact that the name <literal>template1</literal> is the default + source database name for <command>CREATE DATABASE</command>. + For example, one could drop <literal>template1</literal> and recreate it from + <literal>template0</literal> without any ill effects. This course of action + might be advisable if one has carelessly added a bunch of junk in + <literal>template1</literal>. (To delete <literal>template1</literal>, + it must have <literal>pg_database.datistemplate = false</literal>.) + </para> + + <para> + The <literal>postgres</literal> database is also created when a database + cluster is initialized. This database is meant as a default database for + users and applications to connect to. It is simply a copy of + <literal>template1</literal> and can be dropped and recreated if necessary. + </para> + </note> + </sect1> + + <sect1 id="manage-ag-config"> + <title>Database Configuration</title> + + <para> + Recall from <xref linkend="runtime-config"/> that the + <productname>PostgreSQL</productname> server provides a large number of + run-time configuration variables. You can set database-specific + default values for many of these settings. + </para> + + <para> + For example, if for some reason you want to disable the + <acronym>GEQO</acronym> optimizer for a given database, you'd + ordinarily have to either disable it for all databases or make sure + that every connecting client is careful to issue <literal>SET geqo + TO off</literal>. To make this setting the default within a particular + database, you can execute the command: +<programlisting> +ALTER DATABASE mydb SET geqo TO off; +</programlisting> + This will save the setting (but not set it immediately). In + subsequent connections to this database it will appear as though + <literal>SET geqo TO off;</literal> had been executed just before the + session started. + Note that users can still alter this setting during their sessions; it + will only be the default. To undo any such setting, use + <literal>ALTER DATABASE <replaceable>dbname</replaceable> RESET + <replaceable>varname</replaceable></literal>. + </para> + </sect1> + + <sect1 id="manage-ag-dropdb"> + <title>Destroying a Database</title> + + <para> + Databases are destroyed with the command + <xref linkend="sql-dropdatabase"/>:<indexterm><primary>DROP DATABASE</primary></indexterm> +<synopsis> +DROP DATABASE <replaceable>name</replaceable>; +</synopsis> + Only the owner of the database, or + a superuser, can drop a database. Dropping a database removes all objects + that were + contained within the database. The destruction of a database cannot + be undone. + </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 <literal>template1</literal> + database. + <literal>template1</literal> would be the only option for dropping the last user database of a + given cluster. + </para> + + <para> + For convenience, there is also a shell program to drop + databases, <xref linkend="app-dropdb"/>:<indexterm><primary>dropdb</primary></indexterm> +<synopsis> +dropdb <replaceable class="parameter">dbname</replaceable> +</synopsis> + (Unlike <command>createdb</command>, it is not the default action to drop + the database with the current user name.) + </para> + </sect1> + + <sect1 id="manage-ag-tablespaces"> + <title>Tablespaces</title> + + <indexterm zone="manage-ag-tablespaces"> + <primary>tablespace</primary> + </indexterm> + + <para> + Tablespaces in <productname>PostgreSQL</productname> allow database administrators to + define locations in the file system where the files representing + database objects can be stored. Once created, a tablespace can be referred + to by name when creating database objects. + </para> + + <para> + By using tablespaces, an administrator can control the disk layout + of a <productname>PostgreSQL</productname> installation. This is useful in at + least two ways. First, if the partition or volume on which the + cluster was initialized runs out of space and cannot be extended, + a tablespace can be created on a different partition and used + until the system can be reconfigured. + </para> + + <para> + Second, tablespaces allow an administrator to use knowledge of the + usage pattern of database objects to optimize performance. For + example, an index which is very heavily used can be placed on a + very fast, highly available disk, such as an expensive solid state + device. At the same time a table storing archived data which is + rarely used or not performance critical could be stored on a less + expensive, slower disk system. + </para> + + <warning> + <para> + Even though located outside the main PostgreSQL data directory, + tablespaces are an integral part of the database cluster and + <emphasis>cannot</emphasis> be treated as an autonomous collection + of data files. They are dependent on metadata contained in the main + data directory, and therefore cannot be attached to a different + database cluster or backed up individually. Similarly, if you lose + a tablespace (file deletion, disk failure, etc.), the database cluster + might become unreadable or unable to start. Placing a tablespace + on a temporary file system like a RAM disk risks the reliability of + the entire cluster. + </para> + </warning> + + <para> + To define a tablespace, use the <xref + linkend="sql-createtablespace"/> + command, for example:<indexterm><primary>CREATE TABLESPACE</primary></indexterm>: +<programlisting> +CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data'; +</programlisting> + The location must be an existing, empty directory that is owned by + the <productname>PostgreSQL</productname> operating system user. All objects subsequently + created within the tablespace will be stored in files underneath this + directory. The location must not be on removable or transient storage, + as the cluster might fail to function if the tablespace is missing + or lost. + </para> + + <note> + <para> + There is usually not much point in making more than one + tablespace per logical file system, since you cannot control the location + of individual files within a logical file system. However, + <productname>PostgreSQL</productname> does not enforce any such limitation, and + indeed it is not directly aware of the file system boundaries on your + system. It just stores files in the directories you tell it to use. + </para> + </note> + + <para> + Creation of the tablespace itself must be done as a database superuser, + but after that you can allow ordinary database users to use it. + To do that, grant them the <literal>CREATE</literal> privilege on it. + </para> + + <para> + Tables, indexes, and entire databases can be assigned to + particular tablespaces. To do so, a user with the <literal>CREATE</literal> + privilege on a given tablespace must pass the tablespace name as a + parameter to the relevant command. For example, the following creates + a table in the tablespace <literal>space1</literal>: +<programlisting> +CREATE TABLE foo(i int) TABLESPACE space1; +</programlisting> + </para> + + <para> + Alternatively, use the <xref linkend="guc-default-tablespace"/> parameter: +<programlisting> +SET default_tablespace = space1; +CREATE TABLE foo(i int); +</programlisting> + When <varname>default_tablespace</varname> is set to anything but an empty + string, it supplies an implicit <literal>TABLESPACE</literal> clause for + <command>CREATE TABLE</command> and <command>CREATE INDEX</command> commands that + do not have an explicit one. + </para> + + <para> + There is also a <xref linkend="guc-temp-tablespaces"/> parameter, which + determines the placement of temporary tables and indexes, as well as + temporary files that are used for purposes such as sorting large data + sets. This can be a list of tablespace names, rather than only one, + so that the load associated with temporary objects can be spread over + multiple tablespaces. A random member of the list is picked each time + a temporary object is to be created. + </para> + + <para> + The tablespace associated with a database is used to store the system + catalogs of that database. Furthermore, it is the default tablespace + used for tables, indexes, and temporary files created within the database, + if no <literal>TABLESPACE</literal> clause is given and no other selection is + specified by <varname>default_tablespace</varname> or + <varname>temp_tablespaces</varname> (as appropriate). + If a database is created without specifying a tablespace for it, + it uses the same tablespace as the template database it is copied from. + </para> + + <para> + Two tablespaces are automatically created when the database cluster + is initialized. The + <literal>pg_global</literal> tablespace is used for shared system catalogs. The + <literal>pg_default</literal> tablespace is the default tablespace of the + <literal>template1</literal> and <literal>template0</literal> databases (and, therefore, + will be the default tablespace for other databases as well, unless + overridden by a <literal>TABLESPACE</literal> clause in <command>CREATE + DATABASE</command>). + </para> + + <para> + Once created, a tablespace can be used from any database, provided + the requesting user has sufficient privilege. This means that a tablespace + cannot be dropped until all objects in all databases using the tablespace + have been removed. + </para> + + <para> + To remove an empty tablespace, use the <xref + linkend="sql-droptablespace"/> + command. + </para> + + <para> + To determine the set of existing tablespaces, examine the + <link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname> + </link> system catalog, for example +<synopsis> +SELECT spcname FROM pg_tablespace; +</synopsis> + The <xref linkend="app-psql"/> program's <literal>\db</literal> meta-command + is also useful for listing the existing tablespaces. + </para> + + <para> + The directory <filename>$PGDATA/pg_tblspc</filename> contains symbolic links that + point to each of the non-built-in tablespaces defined in the cluster. + Although not recommended, it is possible to adjust the tablespace + layout by hand by redefining these links. Under no circumstances perform + this operation while the server is running. Note that in PostgreSQL 9.1 + and earlier you will also need to update the <structname>pg_tablespace</structname> + catalog with the new locations. (If you do not, <literal>pg_dump</literal> will + continue to output the old tablespace locations.) + </para> + + </sect1> +</chapter> |