PostgreSQL tutorial (13): database management details, postgresql details

PostgreSQL tutorial (13): database management details, postgresql details

I. Overview:

A database can be considered as a name set of an SQL object (database object). Generally, each database object (table, function, etc.) belongs to only one database. However, for some system tables, such as pg_database, it belongs to the entire cluster. More accurately, a database is a set of patterns, and a schema contains SQL objects such as tables and functions. Therefore, the complete object hierarchy should be like this: Server, database, schema, table, or other types of objects.

When establishing a connection with the database server, the connection can only be associated with one database, and access to multiple databases in one session is not allowed. For example, if you log on with postgres, the default database that this user can access is postgres. After you log on, if you execute the following SQL statement, you will receive an error message from PostgreSQL.
Copy codeThe Code is as follows:
S = # SELECT * FROM MyTest. "MyUser". testtables;
ERROR: cross-database references are not implemented: "otherdb. otheruser. sometable"
LINE 1: select * from otherdb. otheruser. sometable
 
In PostgreSQL, databases are physically isolated from each other, and their access control is also performed at the session level. However, the mode is only a logical object management structure. whether an object in a mode can be accessed is controlled by the permission system.
Execute the following system table-based query statement to list some database sets.
 Copy codeThe Code is as follows:
SELECT datname FROM pg_database;
 
Note: The \ l meta command and-l command line options of the psql application can also be used to list existing databases on the current server.

2. Create a database:

Run the following SQL statement on the PostgreSQL server to create a database.
 Copy codeThe Code is as follows:
Create database db_name;
 
After the database is successfully created, the current logon role automatically becomes the owner of the new database. This user privilege is also required when deleting the database. If you want the owner of the currently created database to be another role, you can execute the following SQL statement.
 Copy codeThe Code is as follows:
Create database db_name OWNER role_name;
 

3. modify Database Configuration:

The PostgreSQL server provides a large number of runtime configuration variables. You can specify a special value for a configuration variable in a database based on your actual situation, you can run the following SQL command to set a configuration of the database to a specified value instead of the default value.
 Copy codeThe Code is as follows:
Alter database db_name SET varname TO new_value;
 
In this way, the modified configuration value takes effect in subsequent sessions based on the database. If you want to cancel this setting and restore it to the original default value, you can execute the following SQL command.
 Copy codeThe Code is as follows:
Alter database dbname RESET varname;
 

4. delete a database:

Only the database owner and super user can delete the database. Deleting a database will delete all objects in the database. This operation cannot be recovered. See the following command to delete an SQL statement:
 Copy codeThe Code is as follows:
Drop database db_name;
 

V. tablespace:

In PostgreSQL, The tablespace represents the directory location where a group of files are stored. After creation, you can create database objects on the tablespace. By using tablespaces, the administrator can control the disk layout of a PostgreSQL server. In this way, the administrator can plan the storage location of these objects based on the data volume and data usage frequency of the database objects, so as to reduce the IO wait and optimize the overall operating performance of the system. For example, place a frequently used index on a very reliable and efficient disk device, such as a solid state disk. The seldom used database objects are stored in a relatively slow disk system. The following SQL command is used to create a tablespace.
 Copy codeThe Code is as follows:
Create tablespace fastspace LOCATION '/mnt/sda1/postgresql/data ';
 
It must be noted that the specified tablespace location must be an existing empty directory and belongs to PostgreSQL system users, such as S. After successful creation, all objects created in the tablespace will be stored in the files in this directory.
In PostgreSQL, only super users can create tablespaces, but after successful creation, normal database users can create database objects on them. To complete this operation, you must grant the CREATE permission to these users on the tablespace. Tables, indexes, and the entire database can all be placed in a specific tablespace. See the following SQL command:
 Copy codeThe Code is as follows:
Create table foo (I int) TABLESPACE space1;
 
In addition, you can modify the default_tablespace configuration variable to make the specified tablespace the default tablespace. In this way, if no specified tablespace is displayed when any database object is created, the object will be created in the default tablespace, for example:
 Copy codeThe Code is as follows:
SET default_tablespace = space1;
Create table foo (I int );
 
The tablespace associated with the database is used to store the system tables of the database and any temporary files created by server processes using the database.
To delete an empty TABLESPACE, you can directly use the drop tablespace command. However, to delete a TABLESPACE that contains database objects, you must first delete all objects in the TABLESPACE, to delete the tablespace.

To retrieve the tablespaces in the current system, run the following query, where pg_tablespace is the system table in PostgreSQL.
 Copy codeThe Code is as follows:
SELECT spcname FROM pg_tablespace;
 
We can also use the \ db command column of the psql program to display some tablespaces.