1. Overview:
A database can be regarded as a named collection of SQL objects (database objects). Generally speaking, each database object (table, function, etc.) belongs to only one database. However, for some system tables, such as pg_database, they belong to the entire cluster. More precisely, a database is a collection of schemas, and schemas contain 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 does not allow access to multiple databases in one session. If you log in as the postgres user, the default database that the user can access is postgres. After logging in, if you execute the following SQL statement, you will receive the relevant error message given by PostgreSQL.
postgres=# SELECT * FROM MyTest."MyUser".testtables;
ERROR: cross-database references are not implemented: ""
LINE 1: select * from
In PostgreSQL, databases are physically isolated from each other, and access control of them is also performed at the session level. However, the pattern is just a logical object management structure, and whether objects in a certain pattern can be accessed is controlled by the permission system.
Execute the following system table-based query statement to list the existing database collection.
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:
Execute the following SQL statement on the PostgreSQL server to create a database.
CREATE DATABASE db_name;
After the database is successfully created, the current login role will automatically become the owner of this new database. The user's privileges are 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 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 special values for a configuration variable of a database according to our actual situation. By executing the following SQL command, a configuration of the database can be set to a specified value without using the default value.
ALTER DATABASE db_name SET varname TO new_value;
In this way, in subsequent sessions based on the database, the modified configuration value has taken effect. If you want to undo such a setting and restore it to the original default value, you can execute the following SQL command.
ALTER DATABASE dbname RESET varname;
4. Delete the database:
Only the database owner and superuser can delete the database. Deleting a database will delete all objects included in the database, and this operation is not recoverable. See the following delete SQL command:
DROP DATABASE db_name;
5. Table space:
In PostgreSQL, the tablespace represents the directory location where a set of files is stored. After creation, you can create a database object on that tablespace. By using tablespaces, administrators can control the disk layout of a PostgreSQL server. In this way, the administrator can plan the storage location of these objects based on references such as the data volume and data usage frequency of the database objects, so as to reduce IO waiting 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 drive. Instead, store rarely used database objects on relatively slow disk systems. The following SQL command is used to create tablespaces.
CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';
It should be noted that the location specified in the tablespace must be an existing empty directory and belong to the PostgreSQL system user, such as postgres. After successful creation, all objects created on the tablespace will be stored in the file in this directory.
In PostgreSQL, only superusers can create tablespaces, but after successful creation, ordinary database users can be allowed to create database objects on it. To do this, you must grant CREATE permissions to these users on the tablespace. Tables, indexes, and the entire database can be placed in a specific tablespace. See the following SQL command:
CREATE TABLE foo(i int) TABLESPACE space1;
In addition, we can also modify the default_tablespace configuration variable to make the specified tablespace the default tablespace. In this way, when creating any database object, if the specified tablespace is not displayed, the object will be created in the default tablespace, such as:
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, as well as any temporary files created by the server process using the database.
To delete an empty tablespace, you can directly use the DROP TABLESPACE command. However, to delete a tablespace containing database objects, you need to delete all objects on the tablespace before you can delete the tablespace.
To retrieve what tablespaces are in the current system, you can execute the following query, where pg_tablespace is the system table in PostgreSQL.
SELECT spcname FROM pg_tablespace;
We can also use the \db meta command column of the psql program to appear in the existing tablespace.