PostgreSQL tutorial of xiii: details of database management

Overview:

The       database can be thought of as a named collection of SQL objects (database objects). Typically, 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 precisely, a database is a collection of schemas, which contain SQL objects such as tables, functions, and so on. So the full object hierarchy should look like this: server, database, schema, table, or other type of object.

When establishing a connection to a database server,       can only be associated with one database and does not allow access to multiple databases in one session. If you log in as an postgres user, the default database that the user can access is postgres. If you execute the SQL statement below after logging in, you will receive the relevant error message given by PostgreSQL.

    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 for them is also done at the session level. However, a pattern is only a logical object management structure, and whether the objects of a pattern can be accessed is controlled by the permission system.
      performs the following system-based query statement to list the existing database collections.
 

    SELECT datname FROM pg_database;

 

Note: the \l meta-command and -l command-line options for the psql application can also be used to list existing databases on the current server.  
     
create database:

      creates the database by executing the following SQL statement on the PostgreSQL server.
 

    CREATE DATABASE db_name;

 

After the database is successfully created, the current login role automatically becomes the owner of this new database. Privileges for that user are also required when the database is deleted. If you want the current database to be owned by another role, you can execute the SQL statement below.
 

    CREATE DATABASE db_name OWNER role_name;

 

3. Modify the database configuration:

The       PostgreSQL server provides a large number of runtime configuration variables. We can specify a special value for a configuration variable of a database according to our actual situation. By executing the SQL command below, a configuration of the database can be set to the specified value instead of the default value.
 

    ALTER DATABASE db_name SET varname TO new_value;

 

      thus the modified configuration values are already in effect in subsequent sessions based on the database. If you want to undo such a setting and revert to the original default, you can execute the following SQL command.
 

    ALTER DATABASE dbname RESET varname;

 

Delete database:

      only the owner and superuser of the database can delete the database. Deleting the database will delete all the objects included in the database, which is not recoverable. See below to delete the SQL command:
 

    DROP DATABASE db_name;

 

     
v. table Spaces:

In PostgreSQL, the table space represents the directory location where a set of files are stored. After creation, you can create database objects on this table space. By using table Spaces, administrators can control the disk layout of an PostgreSQL server. In this way, the administrator can plan the storage location of database objects according to their data volume and data usage frequency, so as to reduce the waiting time of IO and optimize the overall performance of the system. For example, place a frequently used index on a very reliable and efficient disk device, such as a solid-state drive. Instead, rarely used database objects are stored on relatively slow disk systems. The SQL command below is used to create the table space.
 

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

 

      notes that the location specified by the table space must be an existing empty directory and belong to users of the PostgreSQL system, such as postgres. After successful creation, all objects created on the table space will be stored in a file in this directory.
      in PostgreSQL only superusers can create table Spaces, but after successful creation, regular database users are allowed to create database objects on them. To do this, these users must be granted CREATE privileges on the table space. Tables, indexes, and entire databases can be placed in specific table Spaces. See the following SQL command:
 

    CREATE TABLE foo(i int) TABLESPACE space1;

 

In addition, we can modify the default_tablespace configuration variable to make the specified table space the default table space, so that when any database object is created, if the specified table space is not displayed, it will be created in the default table space, such as
 

    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, as well as temporary files created by any server process that USES the database.
      to delete an empty table space, you can use the DROP TABLESPACE command directly, but to delete a table space containing database objects, you need to delete all the objects on the table space before you can delete the table space.

To retrieve which table Spaces are in the current system,       can perform the following query, where pg_tablespace is the system table in PostgreSQL.
 

    SELECT datname FROM pg_database;

 

0
We can also list the existing table Spaces using the \db meta command of the psql program.