Shulou(Shulou.com)06/02 Report--

This article mainly talks about "what is the schema, table, space and user relationship in PostgreSQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what is the schema, table, space and user relationship in PostgreSQL?"

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 tablespaces and schemas (Schema), we often ignore the concepts and functions of database tablespaces and schemas.

Next, let's first introduce the definition and role of schema and tablespaces.

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, both herschema and myschema can contain a table named mytable. Unlike databases, schemas are not strictly separated: as long as a user has permissions, a user can access objects in any schema in the database to which he is connected.

There are many reasons why we need patterns:

Allow multiple users to use one 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 modes so that they do not conflict with the names of other objects.

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

What is a tablespace?

A tablespace is where the actual data is stored. A database schema may exist in multiple tablespaces, similarly, a tablespace can serve multiple schema.

By using tablespaces, administrators can control the layout of disks. The most common use of tablespaces is to optimize performance. For example, a most commonly used index can be built on a very fast hard disk, while less commonly used tables can be built on a cheap hard disk, such as a table used to archive files.

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

In PostgreSQL, there are two concepts that are easily confused: roles / users. The two concepts are easily confused because they are exactly the same objects for PostgreSQL. The only difference is when it is created:

I created the character custom with the following psql:

CREATE ROLE custom PASSWORD 'custom'

Then I log in with the newly created role custom, and PostgreSQL gives a rejection message:

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

Indicates that the role does not have login permissions, and the system rejects its login

I also created the user guest using the following psql:

CREATE USER guest PASSWORD 'guest'

Then I logged in using guest, and the login was successful

Is there a difference between the two? Looking at the document, there is another paragraph that says: CREATE USER is the same as CREATE ROLE except that it implies LOGIN. -CREATE USER is exactly the same as CREATE ROLE except that it has LOGIN permission by default.

To verify this statement, modify the permissions of custom and increase the permissions of LOGIN:

ALTER ROLE custom LOGIN

Log in with custom again, successful! Then the thing is clear:

CREATE ROLE custom PASSWORD 'custom' LOGIN is equivalent to CREATE USER custom PASSWORD' custom'..

This is the difference between ROLE/USER.

The relationship between database and schema

The schema is the logical segmentation of the database (database).

At the same time as the database is created, the default is that the database has created a schema, public, 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:

Create a database mars

CREATE DATABASE mars

Log in to the mars database with the custom role and view all the schemas in the database:\ dn

There is only one mode of public for the display result.

Create a test table

CREATE TABLE test (id integer not null)

View the list of current databases:\ d

The result is that the table test belongs to the schema public. That is, the test table is created in the public schema by default.

Create a new schema custom that corresponds to the logged-in user custom:

CREATE SCHEMA custom;ALTER SCHEMA custom OWNER TO custom

Create a test table again, this time indicating the schema

CREATE TABLE custom.test (id integer not null)

View the list of current databases:\ d

The result is that the table test belongs to the schema custom. That is, the test table is created in the custom schema.

It is concluded that the database is segmented by schema, a database has at least one schema, and all objects within the database (object) are created in the schema. After the user logs in to the system and connects to a database, the search_path of the database is used to find the schema search order, which can be modified by either the specific order of the command SHOW search_path; or SET search_path TO 'schema_name'.

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 search_path to $user, that is, the default mode is the same schema as the user name.

The relationship between tablespace and database

Database creation statement:

CREATE DATABASE dbname

The default database owner is the role currently creating the database, and the default tablespace is the system's default tablespace pg_default.

Why is this the case?

Because in PostgreSQL, data creation is achieved by cloning database templates, which is the same mechanism as SQL SERVER. Because CREATE DATABASE dbname does not specify the database template, the system will clone the template1 database by default to get a new database dbname. (By default, the new database will be 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 template1 will be synchronously cloned to the new database.

A relatively complete grammar should look like this:

CREATE DATABASE dbname TEMPLATE template1 TABLESPACE tablespacename;ALTER DATABASE dbname OWNER TO custom

Connect to the template1 database and create a table as a tag:

CREATE TABLE test (id integer not null)

Insert data into the table

INSERT INTO test VALUES (1)

Create a tablespace:

CREATE TABLESPACE tsmars OWNER custom LOCATION'/ tmp/data/tsmars'

Before that, you should make sure that the directory * * / tmp/data/tsmars** exists and that the directory is empty.

Create a database that indicates that the tablespace of the database is the tsmars you just created:

CREATE DATABASE dbmars TEMPLATE template1 OWNERE custom TABLESPACE tsmars;ALTER DATABASE dbmars OWNER TO custom

View information about all databases in the system:\ l+

It can be found that the tablespace of the dbmars database is tsmars and the owner is custom.

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

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

Summary

A tablespace is a storage area where multiple databases can be stored in one tablespace, which is not recommended by PostgreSQL, but it is perfectly feasible. A database does not know how to directly store objects such as table structures, but logically creates at least one schema in the database, creates tables and other objects in the database, assigns different schemas to the different roles, and can achieve the separation of permissions and the sharing of objects between schemas through authorization, and another feature is that public schema can store objects that everyone needs to access.

Tablespaces are used to define the location of database objects on a physical storage device and are not specific to a single database. A database is a physical collection of database objects, while schema is a logical collection of database objects used to organize and manage database objects. Under the schema namespace, there are objects that various applications will come into contact with, such as tables, indexes, data types, functions, operators and so on.

The role (user) is the permission control system in the global scope of the database server (cluster), which is used for the rights management of all objects in various clusters. Therefore, the role is not specific to a single database, but the role must connect to a database if it needs to log in to the database management system. Roles can have a variety of database objects.

At this point, I believe you have a deeper understanding of "what is the schema, table, space, and user relationship in PostgreSQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.