Movatterモバイル変換

CREATE DATABASE actually works by copying an existing database. By default, it copies the standard system database namedtemplate1. Thus that database is the“template” from which new databases are made. If you add objects totemplate1, 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 languagePL/Perl intemplate1, it will automatically be available in user databases without any extra action being taken when those databases are created.

However,CREATE DATABASE does not copy database-levelGRANT permissions attached to the source database. The new database has default database-level permissions.

There is a second standard system database namedtemplate0. This database contains the same data as the initial contents oftemplate1, that is, only the standard objects predefined by your version ofPostgres Pro.template0 should never be changed after the database cluster has been initialized. By instructingCREATE DATABASE to copytemplate0 instead oftemplate1, you can create a“pristine” 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 intemplate1. This is particularly handy when restoring apg_dump 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 totemplate1 later on.

Another common reason for copyingtemplate0 instead oftemplate1 is that new encoding and locale settings can be specified when copyingtemplate0, whereas a copy oftemplate1 must use the same settings it does. This is becausetemplate1 might contain encoding-specific or locale-specific data, whiletemplate0 is known not to.

To create a database by copyingtemplate0, use:

CREATE DATABASEdbname TEMPLATE template0;

from the SQL environment, or:

createdb -T template0dbname

from the shell.

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 forCREATE DATABASE. It is important to understand, however, that this is not (yet) intended as a general-purpose“COPY DATABASE” facility. The principal limitation is that no other sessions can be connected to the source database while it is being copied.CREATE DATABASE will fail if any other connection exists when it starts; during the copy operation, new connections to the source database are prevented.

Two useful flags exist inpg_database for each database: the columnsdatistemplate anddatallowconn.datistemplate can be set to indicate that a database is intended as a template forCREATE DATABASE. If this flag is set, the database can be cloned by any user withCREATEDB privileges; if it is not set, only superusers and the owner of the database can clone it. Ifdatallowconn is false, then no new connections to that database will be allowed (but existing sessions are not terminated simply by setting the flag false). Thetemplate0 database is normally markeddatallowconn = false to prevent its modification. Bothtemplate0 andtemplate1 should always be marked withdatistemplate = true.