In our daily work, we often come into contact with the use of database tables and users and roles. Because we often use the default database table Spaces and schemas, we tend to ignore the concept and function of database table Spaces and schemas.

Next, let’s look at the definitions and functions of schemas and table Spaces.

What is Schema?

A database contains one or more named schemas, which in turn contain tables. Schemas can also contain other objects, including data types, functions, operators, and so on. The same object name can be used in different schemas without causing conflicts; For example, herSchema and MySchema can both contain a table named myTable. Unlike databases, schemas are not strictly separate: given permission, a user can access objects in any schema in the database to which he is connected.

We need patterns for a number of reasons:

  • Allow multiple users to use a database without disturbing other users.
  • Organize database objects into logical groups to make them easier to manage.
  • Third-party applications can be placed in different schemas so that they do not conflict with other object names.

Schemas are similar to directories at the operating system level, except that schemas cannot be nested.

What is a table space?

Table Spaces are where the actual data is stored. A database schema can exist in multiple tablespaces, and similarly, a table space can serve multiple schemas.

By using table Spaces, administrators can control the layout of disks. The most common use of table Spaces is to optimize performance, for example, a most-used index can be built on a very fast disk, while less-used tables can be built on an inexpensive disk, such as tables for archiving.

PostgreSQL Relationships between tablespaces, databases, schemas, tables, users, and roles

Relationship between roles and users

In PostgreSQL, there are two confusing concepts: roles/users. The two concepts are confusing because, for PostgreSQL, they are identical objects. The only difference is at creation time:

  1. Let me use the bottom onepsqlCreate a rolecustom:
CREATE ROLE custom PASSWORD 'custom';
Copy the code

I then log in using the newly created role Custom, and PostgreSQL gives me a rejection message:

FATAL: role'custom' is not permitted to log in.
Copy the code

Note This role does not have the login permission, and the system rejects its login

  1. I use the following one againpsqlCreate a userguest:
CREATE USER guest PASSWORD 'guest';
Copy the code

Then I logged in using guest and the login succeeded

Is there a difference between the two? Looking at the document, another paragraph explains: CREATE USER is the same as CREATE ROLE except that it implies LOGIN. —-CREATE USER Everything else is exactly the same as the CREATE ROLE.

To verify this statement, change the custom permission and add the LOGIN permission:

ALTER ROLE custom LOGIN;
Copy the code

Login with custom again, successful! So here’s the thing:

CREATE ROLE custom PASSWORD ‘custom’ LOGIN is equivalent to CREATE USER custom PASSWORD ‘custom’.

That’s the difference between ROLE/USER.

The relationship between database and schema

A schema is a logical partition of a database.

When the database is created, a schema –public — is created for the database by default, which is also the default schema for the database. All objects (tables, functions, attempts, indexes, sequences, etc.) created for this database are created in this schema:

  1. Create a database Mars
CREATE DATABASE mars;
Copy the code
  1. withcustomRole login tomarsDatabase, view all schemas in the database: \dn

Only one mode, public, is displayed.

  1. Create a test table
CREATE TABLE test(id integer not null);
Copy the code
  1. View the list of current databases: \d;

Table test belongs to the public schema. The test table is created in the public schema by default.

  1. Create a new schemacustom, corresponding to the login usercustom:
CREATE SCHEMA custom;

ALTER SCHEMA custom OWNER TO custom;
Copy the code
  1. Let’s create another onetestTable, this time the table is to specify the schema
CREATE TABLE custom.test (id integer not null);
Copy the code
  1. View the list of current databases: \d

Table test belongs to the custom schema. This table is created in the Custom schema.

A database has at least one schema, and all the objects in the database are created in the schema. After you log in to the system and connect to a database, you can run the SHOW search_path command to search for the schema search sequence. The sequence can also be changed by setting search_path TO ‘schema_name’.

The official recommendation is that after an administrator creates a specific database, create a schema with the same user name for all users that can connect to the database, and then set search_PATH to $user, which means that the default schema is the same as the user name.

The relationship between a tablespace and a database

Database creation statement:

CREATE DATABASE dbname;
Copy the code

The default database owner is the role that currently creates the database, and the default tablespace is the system default tablespace PG_default.

Why is this so?

In PostgreSQL, data is created by cloning database templates, which is the same mechanism as SQL SERVER. Because CREATE DATABASE dbname does not specify a DATABASE template, the system will clone the TEMPLate1 DATABASE by default to get the new DATABASE dbname. (By default, the new database will be created by cloning the standard system database template1)

The default table space for the TEMPLate1 database is PG_default, which is created when the database is initialized, so all objects in TEMPLate1 will be cloned synchronously to the new database.

A relatively complete syntax would look like this:

CREATE DATABASE dbname TEMPLATE template1 TABLESPACE tablespacename;
ALTER DATABASE dbname OWNER TO custom;
Copy the code
  1. Connect to thetemplate1Database, create a table as a tag:
CREATE TABLE test(id integer not null);
Copy the code

Insert data into a table

INSERT INTO test VALUES (1);
Copy the code
  1. Create table space;
CREATE TABLESPACE tsmars OWNER custom LOCATION '/tmp/data/tsmars';
Copy the code

Make sure the directory **/ TMP /data/tsmars** exists and is empty before doing this.

  1. Create a database, indicating that the tablespace of the database is newly createdtsmars:
CREATE DATABASE dbmars TEMPLATE template1 OWNERE custom TABLESPACE tsmars;
ALTER DATABASE dbmars OWNER TO custom;
Copy the code
  1. View the information of all databases in the system: \ L +

Dbmars database tablespace tsmars, owner is custom;

After careful analysis, it is not difficult to draw a conclusion:

In PostgreSQL, a table space is a directory that stores the physical files of the database it contains.

conclusion

A table space is a storage area, and it is possible to store multiple databases in a single table space, although PostgreSQL does not recommend it. A database and direct object storage table structure, but created at least one model in the database logic, create a table in the model objects, such as the different model assigned to the different roles, permissions can be separated, and can through authorization, the implementation pattern between the object of sharing, and there is a characteristic is: The public pattern stores objects that everyone needs to access.

Table Spaces are used to define the location of database objects on physical storage devices and are not specific to a single database. A database is a physical collection of database objects, and a Schema is a logical collection within a database used to organize and manage database objects. Under the schema namespace are objects that various applications can contact, such as tables, indexes, data types, functions, and operators.

A role (user) is a global permission control system of a database server (cluster) and is used to manage the permissions of all objects in various clusters. Therefore, roles are not specific to a single database, but they must be connected to a database if they need to log into the database management system. A role can own a variety of database objects.

Concern public number: JAVA 9:30 class, reply [information] to obtain 1T latest technical information!

Read the original