Relationships between PostgreSQL table spaces, databases, schemas, tables, users/roles

Look at the official documents of POSTGRESQL9, the more I look confused, this table space, database, mode, table, user, role relationship between how in PostgreSQL so chaotic?
After a small experiment at noon, I gradually cleared up the ins and outs. Let me restore my experiment and clarify the relationship in a sequential order.

First, the relationship between the role and the user is tested.
In PostgreSQL, there are two confusing concepts: roles/Users. It is easy to confuse the two concepts because for PostgreSQL it is exactly the same two objects. The only difference is when you create it:
1. I created the role Kanon with the following psql:
CREATE ROLE kanon PASSWORD ' Kanon ';
Then I log in with the newly created role Kanon, and PostgreSQL gives the deny message:

Fatal:role ' Kanon ' is not permitted to log in.
Indicates that the role does not have logon rights and the system refuses to log on.
2. I also created the user kanon2 using the following psql:
CREATE USER kanon PASSWORD ' Kanon2 ';
Then I log in using Kanon2, log on successfully.
Is there a difference between the two? Look at the document, another paragraph stating: "Create USER is the same as create ROLE except that it implies LOGIN." ----Create user is identical to create role except that it has login permissions by default.
In order to verify this sentence, modify the permissions of Kanon, add login permission: ALTER ROLE Kanon login; log in again with Kanon, success!
Well, it's clear: Create ROLE kanon PASSWORD ' Kanon ' LOGIN equals create USER kanon PASSWORD ' Kanon '.
This is the difference between role/user.

then, the relationship between the database and the schema
Read the documentation to understand that the schema is a logical division of the database.
At the same time as the database was created, a schema--public has been created for the database by default, which is also the default mode for that database. All objects created for this database (tables, functions, attempts, indexes, sequences, and so on) are common in this pattern.
The experiment is as follows:
1. Create a database DBTT----create databases DBTT;
2. Log in to the DBTT database with the Kanon role to view all the modes in the DBTT database:/DN; The display result is only public one mode.
3. Create a test table----the CREATE TABLE test (ID integer not NULL);
4. View a list of current databases:/D; The result is that the table test belongs to the schema public. That is, the test table is created by default in public mode.
5. Create a new schema Kanon, corresponding to the login user kanon:create SCHEMA kanon OWNER Kanon;
6. Create a test table again, this time this table to indicate the mode----CREATE TABLE kanon.test (ID integer not NULL);
7. View a list of current databases:/D; The result is that the table test belongs to the pattern Kanon. That is, the test table is created in the Kanon mode.
The conclusion is that 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, connected to a database, is through the database Search_path to find the schema of the search order, can be ordered by the command show Search_path, the specific order can also be set Search_path to ' Schema_ Name ' to modify the order.
The official recommendation is this: After the administrator creates a specific database, you should create a different user name mode for all users who can connect to the database, and then set Search_path to "$user".
This way, when a user connects, the lookup or the defined object is positioned by default in a pattern with the same name. This is a good design architecture.

Next, we'll look at the relationship between the table space and the database.
Database creation Statement CREATE DATABASE dbname The default database owner is the role that is currently creating databases, and the default tablespace is the default tablespace--pg_default for the system.
Why is that so? Because in PostgreSQL, the creation of data is done by cloning a database template, which is the same mechanism as SQL Server.
Because the CREATE DATABASE dbname does not specify a template for the databases, the system will clone the TEMPLATE1 database by default and get a new database dbname. (By default, the new database is created by cloning the standard system database template1).

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 should look like this: CREATE DATABASE dbname OWNER kanon TEMPLATE template1 tablespace tablespacename;
Let's do an experiment to verify the following:
1. Connect to the TEMPLATE1 database and create a table as a token: the CREATE TABLE Tbl_flag (ID integer NOT NULL), insert data into the table inserts into Tbl_flag VALUES (1);
2. Creating a tablespace: Create tablespace tskanon OWNER kanon location '/tmp/data/tskanon '; Before this you should make sure that the directory/tmp/data/tskanon exists and that the directory is empty.
3. Create a database that indicates that the table space for the database is just created tskanon:create database Dbkanon TEMPLATE template1 ownere kanon tablespace Tskanon;
4. View information for all databases in the system:/L; it can be found that the table space of the Dbkanon database is Tskanon, the owner is Kanon;
5. Connect to the Dbkanon database to view all table structures:/D; it can be found that in the database just created a table tbl_flag, view the table data, the output of a column, the value of 1, indicating that the database is actually cloned from Template1.

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.

Finally, let's go back and summarize this network.
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 know the direct storage table structure and other objects, but in the database logic to create at least one schema, in the schema to create a table and other objects, the different patterns assigned to the different roles, you can achieve the separation of permissions, but also through authorization to achieve the sharing of objects between the pattern, and, Another feature is that public mode can store objects that everyone needs to access.
In this way, our nets are formed. However, since a table can specify a tablespace when it is created, is it possible to give a table a table space outside of the database table space where it resides?
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.

----

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

Relationships between PostgreSQL table spaces, databases, schemas, tables, users/roles