summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/manage-ag.sgml
diff options
context:
space:
mode:
Diffstat (limited to 'doc/src/sgml/manage-ag.sgml')
-rw-r--r--doc/src/sgml/manage-ag.sgml558
1 files changed, 558 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..b1035d0
--- /dev/null
+++ b/doc/src/sgml/manage-ag.sgml
@@ -0,0 +1,558 @@
+<!-- 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>
+ <productname>PostgreSQL</productname> makes use of symbolic links
+ to simplify the implementation of tablespaces. This
+ means that tablespaces can be used <emphasis>only</emphasis> on systems
+ that support symbolic links.
+ </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>