PostgreSQL Tutorial (13): Detailed database Management _postgresql

First, overview:

A database can be considered a named collection of SQL objects (database objects), and typically, each database object (table, function, and so on) belongs to only one database. However, for some system tables, such as Pg_database, it belongs to the whole cluster. More precisely, a database is a collection of schemas, and schemas contain SQL objects such as tables, functions, and so on. So the complete object hierarchy should be this: server, database, schema, table, or other type of object.

When a connection is made to the database server, the connection can only be associated with a single database and does not allow access to multiple databases in a single session. If you log on as a postgres user, the default database that the user has access to is postgres, and if you execute the following SQL statement after you log in, you will receive PostgreSQL related error messages.

 code as follows:

postgres=# 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 access control is performed at the session level. However, the schema is only a logical object management structure, and whether the object that can access a pattern is controlled by the permission system.
Execute the following query statement based on the system tables to list the existing database collections.

 code as follows:

SELECT datname from Pg_database;

Note: The Psql application's \l command and-L command-line options can also be used to list existing databases in the current server.

Second, create a database:

Execute the following SQL statement on the PostgreSQL server to create the database.

 code as follows:

CREATE DATABASE db_name;

After the database is successfully created, the current login role automatically becomes the owner of this new database. The user's privileges are also required when the database is deleted. If you want the owner of the currently created database to be a different role, you can execute the following SQL statement.

 code as follows:

CREATE DATABASE db_name OWNER role_name;

Third, modify the database configuration:

The PostgreSQL server provides a large number of run-time configuration variables, and we can assign special values to a particular configuration variable for a database based on our actual situation, and by executing the following SQL command, you can make one configuration of that database set to the specified value instead of using the default value.

 code as follows:

ALTER DATABASE db_name SET varname to New_value;

This allows the modified configuration value to take effect in subsequent sessions based on the database. If you want to undo this setting and revert to the original defaults, you can execute the following SQL command.

 code as follows:

ALTER DATABASE dbname RESET varname;

IV. Delete database:

Only the owner of the database and the superuser can delete the database. Deleting a database deletes all the objects included in the database and is not recoverable. See the following delete SQL command:

code as follows:

DROP DATABASE db_name;

Five, table space:

In PostgreSQL, a table space represents the directory location where a set of files resides. After it is created, you can create database objects on that table space. By using tablespace, an administrator can control the disk layout of a PostgreSQL server. This allows administrators to plan the storage location of these objects based on reference to the amount of data used by the database objects and the frequency of data usage, in order to reduce IO waiting, thereby optimizing the overall performance of the system. For example, put a frequently used index on a very reliable, efficient disk device, such as a solid-state drive. Instead, store infrequently used database objects on a relatively slow disk system. The following SQL command is used to create a table space.

 code as follows:

CREATE tablespace fastspace LOCATION '/mnt/sda1/postgresql/data ';

It should be noted that the location specified in the table space must be an existing empty directory and belong to the PostgreSQL system user, such as Postgres. After successful creation, all objects created on the table space will be stored in the file in this directory.
Only Superuser can create tablespace in PostgreSQL, but after successful creation, you can allow ordinary database users to create database objects on them. To do this, you must grant create permissions to these users on the tablespace. tables, indexes, and the entire database can all be placed in a particular table space. See the following SQL command:

 code as follows:

CREATE TABLE foo (i int) tablespace Space1;

In addition, we can modify the Default_tablespace configuration variable so that the specified tablespace becomes the default tablespace, so that when any database object is created, if the specified table space is not displayed, the object is created in the default table space, such as:

 code as follows:

SET default_tablespace = Space1;
CREATE TABLE foo (i int);

The table space associated with the database is used to store the system tables for the database and any temporary files created by the server process that uses the database.
To remove an empty tablespace, you can use the drop tablespace command directly, but to delete a table space containing database objects, you need to delete all objects on that tablespace before you can delete the tablespace.

To retrieve what table space is in the current system, you can execute the following query, where Pg_tablespace is the system table in PostgreSQL.

 code as follows:

SELECT spcname from Pg_tablespace;

We can also use the Psql program's \db command column to appear in the table space.