PostgreSQL learning Manual (Database Management)

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.
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.
Select datname fromPg_database;
Note: Psql Application Program Of \ L Meta commands and -L The command line option 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.
Create DatabaseDb_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.
Create DatabaseDb_nameOwnerRole_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.
Alter DatabaseDb_nameSetVarnameToNew_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.
Alter DatabaseDbnameResetVarname;

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:
DROP DATABASEDb_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.
Create tablespaceFastspaceLocation'/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:
Create TableFoo (I INT)TablespaceSpace1;
In addition, we can modify Default_tablespacE Configure the variables to make the specified tablespace the default tablespace. If the specified tablespace is not displayed when any database object is created, the object will be created in the default tablespace, for example:
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 Drop tablespace To delete a tablespace that contains a database object, you must delete all objects in the tablespace before deleting the tablespace.
To retrieve the tablespaces in the current system, run the following query, where pg_tablespace is the system table in PostgreSQL.
Select spcname fromPg_tablespace;
We can also use Psql Program \ DB Some tablespaces appear in the metadatabase command column.