Several object concepts and their relationships in PostgreSQL

This article references: http://blog.csdn.net/kanon_lgt/article/details/5931522

http://blog.csdn.net/kanon_lgt/article/details/6220928

DB instance and schema: schema is the logical division of a DB instance.

The database is segmented by schema (schema), a database has at least one schema, and all objects inside the database (object) are created in the schema. After the user logs on to the system and connects to a database, it is through the search_path of the database to find the schema's search order:

You can view the specific search order by using the command show Search_path (default as follows):

postgres=# show Search_path; Search_path----------------"$user", public

You can also modify the order by set Search_path to ' schema_name '.

You can view the schema that the table belongs to by \d the table name:

postgres=# \d test data table "postgres.test" field | Type | Modifier------+---------+--------ID | Integer |

The official recommendation is this: After the administrator creates a specific database, you should create a schema that is the same as the user name for all users who can connect to the database, and then set the Search_path to "$user" (that is, the default mode is the same as the user name), so Any time a user connects, the found or defined object is positioned in a pattern with the same name as the default. This is a good design architecture.

Role and User: For PostgreSQL, this is exactly the same as two objects.

The only difference is when you create it:

CREATE ROLE kanon PASSWORD ' Kanon ';  --(default does not have login permission when role is created) create USER kanon PASSWORD ' Kanon2 '; --(The login permission is provided by default when user is created)

Create ROLE Kanon PASSWORD ' Kanon ' LOGIN;---equivalent to create USER kanon PASSWORD ' Kanon ';

Role does not have the default login permission, which is the difference between role/user.

Tablespace and database: A table space can store multiple databases (although not recommended, but feasible)

Database Creation statement CREATE DATABASE dbname The default database owner is the user who is currently creating the databases, and the default tablespace is the default tablespace--pg_default for the system.

In PostgreSQL, the creation of a database is done by cloning a database template, which is the same mechanism as SQL Server. The CREATE database dbname does not specify a DB template, so the default Template1 database is cloned. The default tablespace for the Template1 database is Pg_default, which is created when the database is initialized, so all objects in the template1 are cloned to the new database synchronously.

The relatively complete syntax for database creation should be:

CREATE DATABASE dbname OWNER kanon TEMPLATE template1 tablespace tablespacename;

table Space concept: table space is a simple directory, its main purpose is divided into two:

1. Expand the table space separately, once the disk or partition is exhausted, you can create a table space above other disks or partitions.
2. Distinguish between storage locations for different objects, such as placing an index on a table space on a faster disk, and putting fixed data on a table space on a slower disk.
Unlike table spaces in an Oracle database, the tablespace of PostgreSQL can be shared. When a table space is created, the tablespace can be used by database objects, such as multiple databases, tables, indexes, and so on. Achieve the purpose of separating and classifying objects.
There are two system self-built tablespaces in PostgreSQL: Pg_global and Pg_default.

The former is the system global table space, which stores the key shared system directories. The latter is the system global table space, which stores the key shared system directories.
The latter is the system default tablespace, which can be specified as a different tablespace through the set default Tablespace=tablespacename, and when database objects such as databases, tables, indexes are established, the system automatically creates the objects into the default tablespace if no tablespace parameters are specified. such as CREATE TABLE TT (ID int) tablespace space1. The statement is equivalent to the SET default Tablespace=space1;create table TT (ID int);

Tablespace records are stored in the Pg_tablspace table in the system database Postgrse, and can be viewed using the command/db or the SQL statement SELECT * FROM Pg_tablespace command.

List of tablespaces Name |             Owner | Location-----------+----------+-----------------------------------Pg_default | Postgres |pg_global | Postgres |ts_licai | Kanon | D:/postgresql90/tablespc/ts_licai

After careful analysis, it is not difficult to conclude: in PostgreSQL, the tablespace is a directory, which stores the various physical files of the database it contains.

Summarize:

A tablespace is a storage area that can store multiple databases in a single table space , although PostgreSQL does not recommend this, but we do so in a very practical way.

A database does not directly store objects such as table structure, but rather creates at least one schema in the database, creates objects such as tables in the schema, assigns different patterns to different roles, enables separation of permissions, and enables the sharing of objects between schemas through authorization , and Another feature is that public mode can store objects that everyone needs to access.

When a table can specify a table space when it is created, can you give a table a table space outside of the database table space it is located in? The answer is YES! It is perfectly possible to do so: then this is not a violation of the table belongs to the schema, and the schema belongs to the database, the database finally exists in the specified table space this network model?! Yes, it does seem counterintuitive, but it does make sense, and in reality, we often need to do this: table space on a slower disk for table data, and table space on a fast disk for the table's index.

But we look at the pattern that the table belongs to remains the same, and it still belongs to the specified pattern. So it's not counterintuitive. In fact, PostgreSQL does not restrict a table to a particular table space, and we think so because when the relationship is progressive, a concept is changed: the schema is logically present, and it is not constrained by the table space.

Several object concepts and their relationships in PostgreSQL