From 0729c4a61fbd1d897dd200bc774796cd3c2fd401 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 18 Nov 2001 00:38:00 +0000 Subject: [PATCH] Clean up treatment of creating/dropping databases in User's Guide and Admin Guide. Move discussion of template databases out of footnotes in CREATE DATABASE ref page and into a section of the Admin Guide. Clean up various obsolete claims, do some copy-editing. --- doc/src/sgml/manage-ag.sgml | 261 ++++++++++++++++++---------------- doc/src/sgml/manage.sgml | 150 +++++-------------- doc/src/sgml/ref/create_database.sgml | 48 ++----- doc/src/sgml/ref/drop_database.sgml | 64 ++------- 4 files changed, 188 insertions(+), 335 deletions(-) diff --git a/doc/src/sgml/manage-ag.sgml b/doc/src/sgml/manage-ag.sgml index 8c713eb043..2bf38f0bef 100644 --- a/doc/src/sgml/manage-ag.sgml +++ b/doc/src/sgml/manage-ag.sgml @@ -1,5 +1,5 @@ @@ -9,9 +9,13 @@ $Header: /cvsroot/pgsql/doc/src/sgml/manage-ag.sgml,v 2.17 2001/11/12 19:19:39 p A database is a named collection of SQL objects (database - objects); 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 + objects). Generally, every database object (tables, functions, + etc.) belongs to one and only one database. (But there are a few system + catalogs, for example pg_database, that belong to a whole + installation and are accessible from each database within the + installation.) + An application that connects + to the database server specifies in its connection request the name of the database it wants to connect to. It is not possible to access more than one database per connection. (But an application is not restricted in the number of connections it opens to the same @@ -40,7 +44,8 @@ $Header: /cvsroot/pgsql/doc/src/sgml/manage-ag.sgml,v 2.17 2001/11/12 19:19:39 p CREATE DATABASE name - where name can be chosen freely. (Depending on the + where name follows the usual rules for SQL identifiers. + (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 @@ -51,7 +56,7 @@ CREATE DATABASE name The creation of databases is a restricted operation. See how to grant permission. + linkend="user-attributes"> for how to grant permission. @@ -62,8 +67,8 @@ CREATE DATABASE name question remains how the first database at any given site can be created. The first database is always created by the initdb command when the data storage area is - initialized. (See .) This - database is called template1 and cannot be deleted. So + initialized. (See .) By convention + this database is called template1. So to create the first real database you can connect to template1. @@ -75,7 +80,8 @@ CREATE DATABASE name This means that any changes you make in 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. + used judiciously this feature can be convenient. More details appear + below. @@ -88,29 +94,130 @@ createdb dbname createdb does no magic. It connects to the template1 - database and executes the CREATE DATABASE command, - exactly as described above. It uses psql program + database and issues the CREATE DATABASE command, + exactly as described above. It uses the psql program internally. The reference page on createdb contains the invocation - details. In particular, createdb without any arguments will create + details. Note that createdb without any arguments will create a database with the current user name, which may or may not be what you want. - + + Template Databases + + + CREATE DATABASE actually works by copying an existing + database. By default, it copies the standard system database named + template1. Thus that database is the template + from which new databases are made. If you add objects to + template1, 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 plpgsql in template1, it will + automatically be available in user databases without any extra action + being taken when those databases are made. + + + + There is a second standard system database named template0. + This database contains the same data as the initial contents of + template1, that is, only the standard objects predefined by + your version of Postgres. template0 should never be changed + after initdb. By instructing CREATE DATABASE to + copy template0 instead of template1, you can + create a virgin user database that contains none of the + site-local additions in template1. This is particularly + handy when restoring a pg_dump dump: the dump script should + be restored in a virgin database to ensure that one recreates the + correct contents of the dumped database, without any conflicts with + additions that may now be present in template1. + + + + It is possible to create additional template databases, and indeed + one might copy any database in an installation by specifying its name + as the template for CREATE DATABASE. It is important to + understand, however, that this is not (yet) intended as + a general-purpose COPY DATABASE facility. In particular, it is + essential that the source database be idle (no data-altering transactions + in progress) + for the duration of the copying operation. CREATE DATABASE + will check + that no backend processes (other than itself) are connected to + the source database at the start of the operation, but this does not + guarantee that changes cannot be made while the copy proceeds, which + would result in an inconsistent copied database. Therefore, + we recommend that databases used as templates be treated as read-only. + + + + Two useful flags exist in pg_database for each + database: datistemplate and + datallowconn. datistemplate + may be set to indicate that a database is intended as a template for + CREATE DATABASE. If this flag is set, the database may be + cloned by + any user with CREATEDB privileges; if it is not set, only superusers + and the owner of the database may clone it. + If datallowconn is false, then no new connections + to that database will be allowed (but existing sessions are not killed + simply by setting the flag false). The template0 + database is normally marked datallowconn = + false to prevent modification of it. + Both template0 and template1 + should always be marked with datistemplate = + true. + + + + After preparing a template database, or making any changes to one, + it is a good idea to perform + VACUUM FREEZE or VACUUM FULL FREEZE in that + database. If this is done when there are no other open transactions + in the same database, then it is guaranteed that all tuples in the + database are frozen and will not be subject to transaction + ID wraparound problems. This is particularly important for a database + that will have datallowconn set to false, since it + will be impossible to do routine maintenance VACUUMs on + such a database. + See for more information. + + + + + template1 and template0 do not have any special + status beyond the fact that the name template1 is the default + source database name for CREATE DATABASE and the default + database-to-connect-to for various scripts such as createdb. + For example, one could drop template1 and recreate it from + template0 without any ill effects. This course of action + might be advisable if one has carelessly added a bunch of junk in + template1. + + + + + + Alternative Locations 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. + default location for the installation. Remember that all database access + occurs through the + database server, so any location specified must be + accessible by the server. 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 + location. This environment variable must be present in the server's + environment, so it must have been defined before the server + was started. (Thus, the set of available alternative locations is + under the site administrator's control; ordinary users can't + change it.) Any valid environment variable name may be used to reference an alternative location, although using variable names with a prefix of PGDATA is recommended to avoid confusion and conflict with other variables. @@ -144,7 +251,8 @@ setenv PGDATA2 /home/postgres/data initlocation To create a data storage area in PGDATA2, ensure that - /home/postgres already exists and is writable + the containing directory (here, /home/postgres) + already exists and is writable by the user account that runs the server (see ). Then from the command line, type @@ -156,7 +264,7 @@ initlocation PGDATA2 - To create a database at the new location, use the command + To create a database within the new location, use the command CREATE DATABASE name WITH LOCATION = 'location' @@ -166,7 +274,7 @@ CREATE DATABASE name WITH LOCATION = 'location' - Database created at alternative locations using this method can be + Databases created in alternative locations can be accessed and dropped like any other database. @@ -188,107 +296,6 @@ gmake CPPFLAGS=-DALLOW_ABSOLUTE_DBPATHS all - - Accessing a Database - - - Once you have constructed a database, you can access it by: - - - - - running the Postgres terminal monitor program - (psql) which allows you to interactively - enter, edit, and execute SQL commands. - - - - - - writing a C program using the libpq subroutine - library. This allows you to submit SQL commands - from C and get answers and status messages back to - your program. This interface is discussed further - in the PostgreSQL Programmer's Guide. - - - - - You might want to start up psql, - to try out the examples in this manual. It can be activated for the - dbname database by typing the command: - - -psql dbname - - - You will be greeted with the following message: - - -Welcome to psql, the PostgreSQL interactive terminal. - -Type: \copyright for distribution terms - \h for help with SQL commands - \? for help on internal slash commands - \g or terminate with semicolon to execute query - \q to quit - -dbname=> - - - - - This prompt indicates that the terminal monitor is listening - to you and that you can type SQL queries into a - workspace maintained by the terminal monitor. - The psql program responds to escape - codes that begin - with the backslash character, \. For example, you - can get help on the syntax of various - Postgres SQL commands by typing: - - -dbname=> \h - - - Once you have finished entering your queries into the - workspace, you can pass the contents of the workspace - to the Postgres server by typing: - - -dbname=> \g - - - This tells the server to process the query. If you - terminate your query with a semicolon, the backslash-g is not - necessary. psql will automatically - process semicolon terminated queries. - To read queries from a file, instead of - entering them interactively, type: - - -dbname=> \i filename - - - To get out of psql and return to Unix, type - - -dbname=> \q - - - and psql will quit and return - you to your command shell. (For more escape codes, type - backslash-h at the monitor prompt.) - White space (i.e., spaces, tabs and newlines) may be - used freely in SQL queries. - Single-line comments are denoted by two dashes - ("--"). Everything after the dashes up to the end of the - line is ignored. Multiple-line comments, and comments within a line, - are denoted by /* ... */, a convention borrowed - from Ingres. - - - Destroying a Database @@ -297,8 +304,9 @@ Type: \copyright for distribution terms DROP DATABASE name - Only the owner of the database (i.e., the user that created it) can - drop databases. Dropping a databases removes all objects that were + Only the owner of the database (i.e., the user that created it), 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. @@ -306,8 +314,9 @@ DROP DATABASE name You cannot execute the DROP DATABASE 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 + connected to any other database, including the template1 + database, + which would be the only option for dropping the last user database of a given cluster. diff --git a/doc/src/sgml/manage.sgml b/doc/src/sgml/manage.sgml index 0b58ddd76b..9304992176 100644 --- a/doc/src/sgml/manage.sgml +++ b/doc/src/sgml/manage.sgml @@ -1,5 +1,5 @@ @@ -18,22 +18,25 @@ $Header: /cvsroot/pgsql/doc/src/sgml/Attic/manage.sgml,v 1.17 2001/09/28 08:15:3 designated the database administrator. This assignment of responsibilities occurs when a database is created. A user may be assigned explicit privileges to create databases and/or to create new users. - A user assigned both privileges can perform most administrative task + A user assigned both privileges can perform most administrative tasks within Postgres, but will not by default have the same operating system privileges as the site administrator. - The Database Administrator's Guide covers these topics in more detail. + The Administrator's Guide covers these topics in + more detail. Database Creation - Databases are created by the CREATE DATABASE issued from - within Postgres. createdb is a command-line - utility provided to give the same functionality from outside Postgres. + Databases are created by the CREATE DATABASE + command issued from within + Postgres. createdb + is a shell script provided to give the same functionality from the + Unix command line. @@ -64,111 +67,19 @@ ERROR: CREATE DATABASE: Permission denied. - Postgres allows you to create any number of databases - at a given site and you automatically become the + You automatically become the database administrator of the database you just created. Database names must have an alphabetic first - character and are limited to 32 characters in length. + character and are limited to 31 characters in length. + Postgres allows you to create any number of + databases at a given site. - - - - Alternate Database Locations - - - 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. - - - - 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 postmaster was started - and the location it points to must be writable by the administrator account. - Consult with the site administrator - regarding preconfigured alternative database locations. - Any valid environment variable name may be used to reference an alternate location, - although using variable names with a prefix of PGDATA is recommended - to avoid confusion - and conflict with other variables. - - - - - In previous versions of Postgres, - it was also permissible to use an absolute path name to specify - an alternate storage location. - Although the environment variable style of specification - is to be preferred since it allows the site administrator more flexibility in - managing disk storage, it is also possible to use an absolute path - to specify an alternate location. - The administrator's guide discusses how to enable this feature. - - - - - For security and integrity reasons, - any path or environment variable specified has some - additional path fields appended. - Alternate database locations must be prepared by running - initlocation. - - - - To create a data storage area using the environment variable - PGDATA2 (for this example set to /alt/postgres), - ensure that /alt/postgres already exists and is writable by - the Postgres administrator account. - Then, from the command line, type - -% initlocation PGDATA2 -The location will be initialized with username "postgres". -This user will own all the files and must also own the server process. - -Creating directory /alt/postgres/data -Creating directory /alt/postgres/data/base - -initlocation is complete. -You can now create a database using - CREATE DATABASE <name> WITH LOCATION = 'PGDATA2' -in SQL, or - createdb <name> -D 'PGDATA2' -from the shell. - - - - - To create a database in the alternate storage area PGDATA2 - from the command line, use the following command: - -% createdb -D PGDATA2 mydb - - - and to do the same from within psql type - -=> CREATE DATABASE mydb WITH LOCATION = 'PGDATA2'; - - - - - If you do not have the privileges required to create a database, you will see - the following: - -ERROR: CREATE DATABASE: permission denied - - - - - If the specified location does not exist or the database backend does not have - permission to access it or to write to directories under it, you will see - the following: - -ERROR: The database path '/no/where' is invalid. This may be due to a character that is not allowed or because the chosen path isn't permitted for databases. - - + + The Administrator's Guide discusses database creation + in more detail, including advanced options of the CREATE + DATABASE command. + @@ -189,11 +100,17 @@ enter, edit, and execute SQL commands. - writing a C program using the LIBPQ subroutine - library. This allows you to submit SQL commands - from C and get answers and status messages back to - your program. This interface is discussed further - in The PostgreSQL Programmer's Guide. +writing a C program using the LIBPQ subroutine +library. This allows you to submit SQL commands +from C and get answers and status messages back to +your program. This interface is discussed further +in The PostgreSQL Programmer's Guide. + + + + +writing a program in other languages for which there are available interface +libraries. @@ -224,7 +141,8 @@ mydb=> This prompt indicates that psql is listening to you and that you can type SQL queries into a workspace maintained by the terminal monitor. - The psql program responds to escape codes that begin +The psql program itself responds to special +commands that begin with the backslash character, \. For example, you can get help on the syntax of various PostgreSQL SQL commands by typing: @@ -271,7 +189,11 @@ mydb=> \q If you are the owner of the database - mydb, you can destroy it using the following Unix command: + mydb, you can destroy it using the SQL command + +=> DROP DATABASE mydb; + + or the Unix shell script % dropdb mydb diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml index 2988c01351..77fa3fe1c3 100644 --- a/doc/src/sgml/ref/create_database.sgml +++ b/doc/src/sgml/ref/create_database.sgml @@ -1,5 +1,5 @@ @@ -202,7 +202,8 @@ CREATE DATABASE name by writing TEMPLATE = template0, you can create a virgin database containing only the standard objects predefined by your version of Postgres. This is useful if you wish to avoid copying - any installation-local objects that may have been added to template1. + any installation-local objects that may have been added to + template1. @@ -259,44 +260,11 @@ comment from Olly; response from Thomas... --> - Although it is possible to copy a database other than template1 by - specifying its name as the template, this is not (yet) intended as - a general-purpose COPY DATABASE facility. In particular, it is - essential that the source database be idle (no data-altering transactions - in progress) - for the duration of the copying operation. CREATE DATABASE will check - that no backend processes (other than itself) are connected to - the source database at the start of the operation, but this does not - guarantee that changes cannot be made while the copy proceeds. Therefore, - we recommend that databases used as templates be treated as read-only. - - - - Two useful flags exist in pg_database for each - database: datistemplate and - datallowconn. datistemplate - may be set to indicate that a database is intended as a template for - CREATE DATABASE. If this flag is set, the database may be cloned by - any user with CREATEDB privileges; if it is not set, only superusers - and the owner of the database may clone it. - If datallowconn is false, then no new connections - to that database will be allowed (but existing sessions are not killed - simply by setting the flag false). The template0 - database is normally marked this way to prevent modification of it. - - - - After preparing a template database, or making any changes to one, - it is a good idea to perform - VACUUM FREEZE or VACUUM FULL FREEZE in that - database. If this is done when there are no other open transactions - in the same database, then it is guaranteed that all tuples in the - database are frozen and will not be subject to transaction - ID wraparound problems. This is particularly important for a database - that will have datallowconn set to false, since it - will be impossible to do routine maintenance VACUUMs on - such a database. - See the Administrator's Guide for more information. + Although it is possible to copy a database other than template1 + by specifying its name as the template, this is not (yet) intended as + a general-purpose COPY DATABASE facility. + We recommend that databases used as templates be treated as read-only. + See the Administrator's Guide for more information. diff --git a/doc/src/sgml/ref/drop_database.sgml b/doc/src/sgml/ref/drop_database.sgml index 438603229a..07d596c626 100644 --- a/doc/src/sgml/ref/drop_database.sgml +++ b/doc/src/sgml/ref/drop_database.sgml @@ -1,5 +1,5 @@ @@ -68,57 +68,18 @@ DROP DATABASE name - ERROR: user 'username' is not allowed to create/drop databases + DROP DATABASE: cannot be executed on the currently open database - You must have the special CREATEDB privilege to drop databases. - See . - - - - - - ERROR: dropdb: cannot be executed on the template database - - - The template1 database cannot be removed. It's not in - your interest. - - - - - - ERROR: dropdb: cannot be executed on an open database - - - You cannot be connected to the database your are about to remove. - Instead, you could connect to template1 or any other + You cannot be connected to the database you are about to remove. + Instead, connect to template1 or any other database and run this command again. - ERROR: dropdb: database 'name' does not exist - - - This message occurs if the specified database does not exist. - - - - - - ERROR: dropdb: database 'name' is not owned by you - - - You must be the owner of the database. Being the owner usually means that - you created it as well. - - - - - - ERROR: dropdb: May not be called in a transaction block. + DROP DATABASE: may not be called in a transaction block You must finish the transaction in progress before you can call this command. @@ -126,17 +87,6 @@ DROP DATABASE name - - NOTICE: The database directory 'xxx' could not be removed. - - - The database was dropped (unless other error messages came up), but the - directory where the data is stored could not be removed. You must delete - it manually. - - - - @@ -156,6 +106,10 @@ DROP DATABASE name it). + + DROP DATABASE cannot be undone. Use it with care! + + 1999-12-11 -- 2.11.0