Create and manage databases
prerequisite
A user must have the authority to create a database or the system administrator authority of the database to create a database. For the authority to create a database, see Managing Users and Permissions.
Background Information
Initially, openGauss contains two template databases template0, template1, and a default user database postgres. The default compatible database type of postgres is O (that is, DBCOMPATIBILITY = A), and an empty string is treated as NULL under this compatible type.
CREATE DATABASE actually creates a new database by copying the template database. By default, template0 is copied. Please avoid using client or other means to connect and operate the two template databases.
illustrate:
There is no user table in the template database, you can view the template database properties through the system table PG_DATABASE.
Template template0 does not allow users to connect; template template1 only allows database initial users and system administrators to connect, and ordinary users cannot connect.
There will be multiple databases in the database system, but the client program can only connect to one database at a time. It is also not possible to query each other between different databases. When there are multiple databases in one openGauss, you need to specify the corresponding database instance through the -d parameter to connect.
Precautions
If the encoding of the database is SQL_ASCII (you can view the current database storage encoding through the "show server_encoding;" command), when creating a database object, if the object name contains multi-byte characters (such as Chinese), the length of the database object name exceeds the limit ( 63 bytes), the database will truncate the last byte (instead of a character), which may result in half a character.
For this situation, follow the conditions below:
Ensure that the name of the data object does not exceed the limited length.
Change the default storage encoding set (server_encoding) of the database to utf-8 encoding set.
Do not use multibyte characters as object names.
The total number of databases created cannot exceed 128.
If the database object cannot be deleted due to truncation in the middle of multi-byte characters due to misoperation, please use the name of the database object before truncation to perform the deletion operation, or delete the object from the corresponding system table of each database node in sequence .
Steps
create database
a. Use the following command to create a new tablespace tpcds_local.
openGauss=# CREATE TABLESPACE tpcds_local RELATIVE LOCATION 'tablespace/tablespace_1' ;
CREATE TABLESPACE
b. Use the following command to create a new database db_tpcc.
openGauss=# CREATE DATABASE db_tpcc WITH TABLESPACE = tpcds_local;
CREATE DATABASE
illustrate:
Database names follow the general rules for SQL identifiers. The current role automatically becomes the owner of this new database.
If a database system is used to host users and projects that are independent of each other, it is recommended to put them in different databases.
If projects or users are interrelated and can use each other's resources, they should be placed in the same database, but can be planned in different schemas. A schema is just a purely logical structure, and access to a schema is controlled by the permissions system module.
When creating a database, if the length of the database name exceeds 63 bytes, the server will truncate the database name and keep the first 63 bytes. Therefore, it is recommended that the length of the database name not exceed 63 bytes.
view database
Use the \l metacommand to see a list of databases for a database system.
openGauss=# \l
Use the following command to query the database list through the system table pg_database.
openGauss=# SELECT datname FROM pg_database;
modify database
Users can use the following commands to modify database properties (such as: owner, name and default configuration properties).
Use the following command to set the default schema search path for the database.
openGauss=# ALTER DATABASE db_tpcc SET search_path TO pa_catalog,public;
ALTER DATABASE
Use the following commands to modify the database tablespace.
openGauss=# ALTER DATABASE db_tpcc SET TABLESPACE tpcds;
ALTER DATABASE
Use the following command to rename the database.
openGauss=# ALTER DATABASE db_tpcc RENAME TO human_tpcds;
ALTER DATABASE
delete database
Users can delete databases using the DROP DATABASE command. This command deletes the system catalogs in the database, and deletes the database catalogs with data on disk. The user must be the owner of the database or the system administrator to delete the database. The delete operation fails when someone is connected to the database. When deleting a database, please connect to another database first.
Delete the database using the following command:
openGauss=# DROP DATABASE human_tpcds;
DROP DATABASE
Top comments (0)