From c2f14a7cdf0b5e839f4a313655dfbb03e522db99 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 7 Aug 2004 19:02:43 +0000 Subject: [PATCH] Improve tablespace discussion, and bring it up to date with code. --- doc/src/sgml/manage-ag.sgml | 73 +++++++++++++++++++++++++++---------- 1 file changed, 53 insertions(+), 20 deletions(-) diff --git a/doc/src/sgml/manage-ag.sgml b/doc/src/sgml/manage-ag.sgml index 030620d91e..4ee4c9896c 100644 --- a/doc/src/sgml/manage-ag.sgml +++ b/doc/src/sgml/manage-ag.sgml @@ -1,5 +1,5 @@ @@ -32,8 +32,8 @@ $PostgreSQL: pgsql/doc/src/sgml/manage-ag.sgml,v 2.31 2004/06/21 04:06:03 tgl Ex are accessible from each database within the cluster.) More accurately, a database is a collection of schemas and the schemas contain the tables, functions, etc. So the full hierarchy is: - server, database, schema, table (or something else instead of a - table). + server, database, schema, table (or some other kind of object, + such as a function). @@ -58,8 +58,8 @@ $PostgreSQL: pgsql/doc/src/sgml/manage-ag.sgml,v 2.31 2004/06/21 04:06:03 tgl Ex - SQL calls databases catalogs, but there is no - difference in practice. + The SQL standard calls databases catalogs, but there + is no difference in practice. @@ -68,14 +68,16 @@ $PostgreSQL: pgsql/doc/src/sgml/manage-ag.sgml,v 2.31 2004/06/21 04:06:03 tgl Ex Creating a Database - In order to create a databases, the PostgreSQL + In order to create a database, the PostgreSQL server must be up and running (see ). - Databases are created with the SQL command CREATE - DATABASE:CREATE DATABASE + Databases are created with the SQL command + :CREATE + DATABASE CREATE DATABASE name; @@ -105,7 +107,7 @@ CREATE DATABASE name; - The name template1 is no accident: When a new + The name template1 is no accident: when a new database is created, the template database is essentially cloned. This means that any changes you make in template1 are propagated to all subsequently created databases. This implies that @@ -280,8 +282,8 @@ createdb -T template0 dbname GEQO 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 SET geqo - TO off;. To make this setting the default you can - execute the command + TO off;. To make this setting the default within a particular + database, you can execute the command ALTER DATABASE mydb SET geqo TO off; @@ -299,12 +301,13 @@ ALTER DATABASE mydb SET geqo TO off; Destroying a Database - Databases are destroyed with the command DROP - DATABASE:DROP DATABASE + Databases are destroyed with the command + :DROP DATABASE DROP DATABASE name; - Only the owner of the database (i.e., the user that created it) or + 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 @@ -335,7 +338,7 @@ dropdb dbname Tablespaces - Tablespaces in PostgreSQL allow database superusers to + Tablespaces in PostgreSQL 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. @@ -360,7 +363,37 @@ dropdb dbname - Databases, schemas, tables, indexes and sequences can all be placed in + To define a tablespace, use the command, for + example:CREATE TABLESPACE + +CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data'; + + The location must be an existing, empty directory that is owned by + the PostgreSQL system user. All objects subsequently + created within the tablespace will be stored in files underneath this + directory. + + + + + There is usually not much point in making more than one + tablespace per logical filesystem, since you can't control the location + of individual files within a logical filesystem. However, + PostgreSQL does not enforce any such limitation, and + indeed it's not directly aware of the filesystem boundaries on your + system. It just stores files in the directories you tell it to use. + + + + + Creation of the tablespace itself must be done as a database superuser, + but after that you can allow ordinary database users to make use of it. + To do that, grant them the CREATE privilege on it. + + + + Databases, schemas, tables, and indexes can all be assigned to particular tablespaces. To do so, a user with the CREATE privilege on a given tablespace must pass the tablespace name as a parameter to the relevant command. For example, the following creates @@ -385,7 +418,7 @@ CREATE TABLE foo(i int) TABLESPACE space1; A schema does not in itself occupy any storage (other than a system catalog entry), so assigning a tablespace to a schema does not in itself do anything. What this actually does is to set a default tablespace - for tables, indexes, and sequences later created within the schema. If + for tables later created within the schema. If no tablespace is mentioned when creating a schema, it inherits its default tablespace from the current database. @@ -396,10 +429,10 @@ CREATE TABLE foo(i int) TABLESPACE space1; - Another way to state the above rules is that when a schema, table, index - or sequence is created without specifying a tablespace, the object + Another way to state the above rules is that when a schema, table, or index + is created without specifying a tablespace, the object inherits its logical parent's tablespace. A schema will be created in the - current database's tablespace; a table or sequence will be created in the + current database's tablespace; a table will be created in the tablespace of the schema it is being created in; an index will be created in the tablespace of the table underlying the index. -- 2.40.0