From f5f448fb3e6896584a240ca8e5c2fd616212fbf6 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 21 Jun 2004 04:06:07 +0000 Subject: Rename the built-in tablespaces to pg_default and pg_global, and prohibit creation of user-defined tablespaces with names starting with 'pg_', as per suggestion of Chris K-L. Also install admin-guide tablespace documentation from Gavin. --- doc/src/sgml/diskusage.sgml | 15 +-- doc/src/sgml/manage-ag.sgml | 192 ++++++++++++++++++++++----------------- doc/src/sgml/ref/postmaster.sgml | 14 +-- 3 files changed, 117 insertions(+), 104 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/diskusage.sgml b/doc/src/sgml/diskusage.sgml index 344141210fc..ce09f7bc978 100644 --- a/doc/src/sgml/diskusage.sgml +++ b/doc/src/sgml/diskusage.sgml @@ -1,5 +1,5 @@ @@ -124,20 +124,15 @@ SELECT relname, relpages FROM pg_class ORDER BY relpages DESC; If you cannot free up additional space on the disk by deleting - other things you can move some of the database files to other file - systems and create a symlink from the original location. But - note that pg_dump cannot save the location layout - information of such a setup; a restore would put everything back in - one place. To avoid running out of disk space, you can place the - WAL files or individual databases in other locations while creating - them. See the initdb documentation and for more information about that. + other things, you can move some of the database files to other file + systems by making use of tablespaces. See for more information about that. Some file systems perform badly when they are almost full, so do - not wait until the disk is full to take action. + not wait until the disk is completely full to take action. diff --git a/doc/src/sgml/manage-ag.sgml b/doc/src/sgml/manage-ag.sgml index 34737ffd4fe..030620d91ec 100644 --- a/doc/src/sgml/manage-ag.sgml +++ b/doc/src/sgml/manage-ag.sgml @@ -1,5 +1,5 @@ @@ -295,86 +295,6 @@ ALTER DATABASE mydb SET geqo TO off; - - Alternative Locations - - - XXX this is entirely dead now, and needs to be replaced by a DBA-level - description of tablespaces. - - - - It is possible to create a database in a location other than the - default location for the installation. But 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 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. - - - - To create the variable in the environment of the server process - you must first shut down the server, define the variable, - initialize the data area, and finally restart the server. (See also - and .) To set an environment variable, type - -PGDATA2=/home/postgres/data -export PGDATA2 - - in Bourne shells, or - -setenv PGDATA2 /home/postgres/data - - in csh or tcsh. You have to make sure that this environment - variable is always defined in the server environment, otherwise - you won't be able to access that database. Therefore you probably - want to set it in some sort of shell start-up file or server - start-up script. - - - - initlocation - To create a data storage area in PGDATA2, ensure that - 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 - -initlocation PGDATA2 - - (not initlocation - $PGDATA2). Then you can restart the server. - - - - To create a database within the new location, use the command - -CREATE DATABASE name WITH LOCATION 'location'; - - where location is the environment variable you - used, PGDATA2 in this example. The createdb - command has the option - - - Databases created in alternative locations can be - accessed and dropped like any other database. - - - Destroying a Database @@ -410,6 +330,116 @@ dropdb dbname the database with the current user name.) + + + Tablespaces + + + Tablespaces in PostgreSQL allow database superusers 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. + + + + By using tablespaces, a database administrator can control the disk + layout of a PostgreSQL installation. This is useful in + at least two ways. Firstly, if the partition or volume on which the cluster + was initialized runs out of space and cannot be extended logically + or otherwise, a tablespace can be created on a different partition + and used until the system can be reconfigured. + + + + Secondly, tablespaces allow a database administrator to arrange data + locations based on the usage patterns of database objects. For + example, an index which is very heavily used can be placed on 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. + + + + Databases, schemas, tables, indexes and sequences can all be placed in + 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 + a table in the tablespace space1: + +CREATE TABLE foo(i int) TABLESPACE space1; + + + + + The tablespace associated with a database is used to store the system + catalogs of that database, as well as any temporary files created by + server processes using that database. Furthermore, it is the default + tablespace selected for any objects created within the database, if + no specific TABLESPACE clause is given when those objects + are created. If a database is created without specifying a tablespace + for it, it uses the same tablespace as the template database it is copied + from. + + + + 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 + no tablespace is mentioned when creating a schema, it inherits its + default tablespace from the current database. + + + + The default choice of tablespace for an index is the same tablespace + already assigned to the table the index is for. + + + + Another way to state the above rules is that when a schema, table, index + or sequence 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 + tablespace of the schema it is being created in; an index will be created + in the tablespace of the table underlying the index. + + + + Two tablespaces are automatically created by initdb. The + pg_global tablespace is used for shared system catalogs. The + pg_default tablespace is the default tablespace of the + template1 and template0 databases (and, therefore, + will be the default tablespace for everything else as well, unless + explicit TABLESPACE clauses are used somewhere along the + line). + + + + 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. + + + + To simplify the implementation of tablespaces, + PostgreSQL makes extensive use of symbolic links. This + means that tablespaces can be used only on systems + that support symbolic links. + + + + The directory $PGDATA/pg_tblspc 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. Two warnings: do not do so + while the postmaster is running; and after you restart the postmaster, + update the pg_tablespace catalog to show the new + locations. (If you do not, pg_dump will continue to show + the old tablespace locations.) + + + @@ -417,18 +417,6 @@ PostgreSQL documentation - - others - - - - Other environment variables may be used to designate alternative - data storage locations. See for more - information. - - - - -- cgit v1.2.3