Relationship between PostgreSQL tablespace, database, mode, table, user/role

first, experiment shows the relationship between roles and users
in PostgreSQL, there are two confusing concepts: Role/Use User. These two concepts are confusing because for PostgreSQL, they are two identical objects. The only difference is:
1. I used the following Psql to create the role kanon:
Create role kanon password 'kanon';
then I used the newly created role kanon to log on. PostgreSQL gave a denial message:

fatal: Role 'kanon' is not permitted to log in.
this role has no logon permission, The system rejects logon.
2. I used the following Psql to create the user kanon2:
create user kanon password 'kanon2';
then I used kanon2 to log on successfully.
Are there any differences between the two? For more information, see: "create user is the same as create role t that it implies login. "---- create user has the same permissions as create role except login by default.
to verify this sentence, modify the kanon permission and add the login permission: Alter role kanon login; log on again with kanon, succeeded!
then, it is clear that: Create role kanon password 'kanon' login is equivalent to create user kanon password 'kanon'.
This is the difference between role and user.

Then, the relationship between the database and the mode
According to the document, schema is a logical division of databases, which is equivalent to a namespace.
When a database is created, a public mode is created for the database by default, which is also the default mode of the database. All objects (tables, functions, attempts, indexes, sequences, etc.) created for this database are common in this mode.
The experiment is as follows:
1. Create a database dbtt ---- create database dbtt;
2. log on to the dbtt database with the kanon role and view all the modes in the dbtt Database: \ DN. Only one public mode is displayed.
3. Create a test table ---- create table test (ID integer not null );
4. view the list of the current database: \ D. The result is that the test table belongs to the public mode, that is, the test table is created in the public mode by default.
5. Create a new mode kanon, which corresponds to the logon user kanon: Create schema kanon owner kanon;
6. Create a test table again. This time, specify the mode ---- create table kanon. Test (ID integer not null );
7. view the list of the current database: \ D; the result is that the table test belongs to the mode kanon. That is, the test table is created in the kanon mode.
It is concluded that the database is split by schema. A database has at least one schema, and all objects in the database are created in the schema. After you log on to the system and connect to a database, you can use the search_path of the database to find the schema search sequence. You can run the show search_path command to view the specific sequence, you can also set search_path to 'schema _ name' to modify the sequence.
The official suggestion is as follows: after the Administrator creates a specific database, he/she should create the same mode as the user name for all users who can connect to the database. Then, set search_path to "$ user ",
In this way, when a user connects, all the objects to be searched or defined will be located in the pattern with the same name by default. This is a good design architecture.

Next, we will study the relationship between tablespaces and databases.
Database creation statement create database dbname the default database owner is the role currently created for the database, and the default tablespace is the system's default tablespace -- pg_default.
Why? In PostgreSQL, data is created by cloning database templates, which is the same as SQL Server.
Because the create database dbname does not specify the database template, the system will clone the template1 database by default to obtain the new database dbname. (By default, the new database will be created by cloning the standard system database template1 ).

The default tablespace of the template1 database is pg_default, which is created during database initialization. Therefore, all objects in template1 will be cloned to the new database.
The complete syntax should be: Create Database dbname owner kanon template template1 tablespace tablespacename;
Let's make an experiment to verify it:
1. Connect to the template1 database and create a table as the tag: Create Table tbl_flag (ID integer not null); insert into tbl_flag values (1) into the table );
2. Create a tablespace: Create tablespace tskanon owner kanon location '/tmp/data/tskanon'. Before that, make sure that the directory/tmp/data/tskanon exists and the directory is empty.
3. Create a database and specify that the tablespace of the database is the created tskanon: Create Database dbkanon template template1 ownere kanon tablespace tskanon;
4. view the information of all databases in the system: \ L; you can find that the tablespace of the dbkanon database is tskanon and the owner is kanon;
5. connect to the dbkanon database and view all the table structures: \ D; you can find that a table tbl_flag exists in the newly created database, view the table data, and output the result in one row and one column, the value is 1, indicating that the database is indeed cloned from template1.

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

Finally, let's take a look at this network.
A tablespace is a storage area that can store multiple databases in a tablespace. Although PostgreSQL does not recommend this, it is completely feasible.
A database does not directly store objects such as table structures. Instead, it creates at least one mode in the database logic and objects such as tables in the mode, assign different roles in different modes to achieve permission separation, and share objects in different modes through authorization. Another feature is: the public mode can store objects that all users need to access.
In this way, our network is formed. However, since a table can be specified when it is created, can you specify a tablespace other than the tablespace in the database where it is located for a table?
The answer is yes! This can be done completely: Isn't it against the table's pattern, while the pattern belongs to the database. Does the database eventually exist in the network model of the specified tablespace ?!
Yes, it seems that this is really unreasonable, but there is a reason for doing so, and in reality, we often need to do this: store the table data in a slow tablespace on the disk, and store the table index in a fast tablespace on the disk.
However, we can see that the table's schema remains unchanged. It still belongs to the specified schema. Therefore, this does not violate common sense. In fact, PostgreSQL does not limit that a table must belong to a specific tablespace. The reason we think so is that when the link is progressive, we steal the concept that the mode is logical, it is not restricted by tablespaces.