PostgreSQL database creation and deletion Methods

1. After the database server is installed, there are three databases by default. You can view them in the following two ways.

postgres=# SELECT * FROM pg_database;  datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datconfig |               datacl-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+---------------+-----------+------------------------------------- template1 |     10 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | t             | t            |           -1 |         11563 |          648 |          1663 |      | {=c/postgres,postgres=CTc/postgres} template0 |     10 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | t             | f            |           -1 |         11563 |          648 |          1663 |      | {=c/postgres,postgres=CTc/postgres} postgres  |     10 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | f             | t            |           -1 |         11563 |          648 |          1663 |      |(3 rows)postgres=# \l                                  List of databases   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges-----------+----------+----------+-------------+-------------+----------------------- postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres                                                             : postgres=CTc/postgres template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres                                                             : postgres=CTc/postgres(3 rows)postgres=#

These three databases are composedInitdbGenerated, where template0 and template1 are database templates, you can directly use them to clone a new database.

2. Create a method. The default database owner is the user.

[postgres@kevin ~]$ psql postgrespsql (8.4.2)Type "help" for help.postgres=# CREATE DATABASE pg_databse_test_1;CREATE DATABASEpostgres=# \l                                      List of databases       Name        |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges-------------------+----------+----------+-------------+-------------+----------------------- pg_databse_test_1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | postgres          | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | template0         | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres                                                                     : postgres=CTc/postgres template1         | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres                                                                     : postgres=CTc/postgres(4 rows)postgres=#

Another method for creating a command line is as follows:

[postgres@kevin ~]$ createdb pg_database_test_2;[postgres@kevin ~]$ psqlpsql (8.4.2)Type "help" for help.postgres=# \l                                      List of databases        Name        |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges--------------------+----------+----------+-------------+-------------+----------------------- pg_database_test_2 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | pg_databse_test_1  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | postgres           | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | template0          | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres                                                                      : postgres=CTc/postgres template1          | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres                                                                      : postgres=CTc/postgres(5 rows)postgres=#

3. Create a database for another database role.

postgres=# \du              List of roles   Role name    | Attributes  | Member of----------------+-------------+----------- pg_test_user_3 | Create DB   | {} pg_test_user_4 | Create role | {}                : Create DB postgres       | Superuser   | {}                : Create role                : Create DBpostgres=# CREATE DATABASE pg_database_3 OWNER pg_test_user_4;CREATE DATABASEpostgres=# \l                                         List of databases        Name        |     Owner      | Encoding |  Collation  |    Ctype    |   Access privileges--------------------+----------------+----------+-------------+-------------+----------------------- pg_database_3      | pg_test_user_4 | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | pg_database_test_2 | postgres       | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | pg_databse_test_1  | postgres       | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | postgres           | postgres       | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | template0          | postgres       | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres                                                                            : postgres=CTc/postgres template1          | postgres       | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres                                                                            : postgres=CTc/postgres(6 rows)postgres=#

Command Line Method:

[postgres@kevin ~]$ createdb -O pg_test_user_3 pg_database_4;[postgres@kevin ~]$ psqlpsql (8.4.2)Type "help" for help.postgres=# \l                                         List of databases        Name        |     Owner      | Encoding |  Collation  |    Ctype    |   Access privileges--------------------+----------------+----------+-------------+-------------+----------------------- pg_database_3      | pg_test_user_4 | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | pg_database_4      | pg_test_user_3 | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | pg_database_test_2 | postgres       | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | pg_databse_test_1  | postgres       | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | postgres           | postgres       | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | template0          | postgres       | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres                                                                            : postgres=CTc/postgres template1          | postgres       | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres                                                                            : postgres=CTc/postgres(7 rows)postgres=#

4. Create a database using a template. Changes to the template will cause identical changes to all database objects created based on the template.

Postgres = # create database pg_datebase_5 template template0; /* SQL Method */create databasepostgres =#\ l list of databases name | Owner | encoding | collation | ctype | access privileges accept + signature + ---------- + ------------- + export pg_database_3 | pg_test_user_4 | utf8 | zh_CN.UTF-8 | zh_CN.UTF-8 | pg_database_4 | pg_test_user_3 | utf8 | zh_CN.UTF-8 | Zh_CN.UTF-8 | pg_database_test_2 | Postgres | utf8 | zh_CN.UTF-8 | zh_CN.UTF-8 | pg_databse_test_1 | Postgres | utf8 | zh_CN.UTF-8 | zh_CN.UTF-8 | pg_datebase_5 | Postgres | utf8 | zh_CN.UTF-8 | Postgres | utf8 | zh_CN.UTF-8 | zh_CN.UTF-8 | template0 | Postgres | utf8 | zh_CN.UTF-8 | = C/Postgres: postgres = CTC/Postgres template1 | Postgres | utf8 | zh_cn.ut F-8 | zh_CN.UTF-8 | = C/Postgres: ctc s = CTC/Postgres (8 rows) Postgres = # \ Q [Postgres @ Kevin ~] $ Createdb-T template0 pg_database_6/* command line mode */[S @ Kevin ~] $ Psqlpsql (8.4.2) type "help" for help. postgres = # \ L list of databases name | Owner | encoding | collation | ctype | access privileges tables + ---------- + ------------- + export pg_database_3 | utf8 | zh_CN.UTF-8 | pg_database_4 | pg_test_user_3 | utf8 | zh_CN.UTF-8 | zh_CN.UTF-8 | pg_database_6 | Postgres | utf8 | zh_CN.UTF-8 | zh_CN.UTF-8 | pg_database_test_2 | utf8 | zh_CN.UTF-8 | zh_CN.UTF-8 | UTs | utf8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | pg_datebase_5 | Postgres | utf8 | zh_CN.UTF-8 | zh_CN.UTF-8 | Postgres | utf8 | zh_CN.UTF-8 | template0 | Postgres | utf8 | zh_CN.UTF-8 | zh_CN.UTF-8 | = C/Postgres: postgres = CTC/Postgres template1 | Postgres | utf8 | zh_CN.UTF-8 | = C/Postgres: ctc s = CTC/Postgres (9 rows) Postgres = #

5. Delete the database.

Delete using SQL:

postgres=# \l                                         List of databases        Name        |     Owner      | Encoding |  Collation  |    Ctype    |   Access privileges--------------------+----------------+----------+-------------+-------------+----------------------- pg_database_3      | pg_test_user_4 | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | pg_database_4      | pg_test_user_3 | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | pg_database_6      | postgres       | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | pg_database_7      | postgres       | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | pg_database_test_2 | postgres       | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | pg_databse_test_1  | postgres       | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | postgres           | postgres       | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | template0          | postgres       | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres                                                                            : postgres=CTc/postgres template1          | postgres       | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres                                                                            : postgres=CTc/postgres(9 rows)postgres=# DROP DATABASE pg_database_3;DROP DATABASEpostgres=# DROP DATABASE pg_database_4;DROP DATABASEpostgres=# DROP DATABASE pg_database_6;DROP DATABASEpostgres=# DROP DATABASE pg_database_7;DROP DATABASEpostgres=# \l                                      List of databases        Name        |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges--------------------+----------+----------+-------------+-------------+----------------------- pg_database_test_2 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | pg_databse_test_1  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | postgres           | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | template0          | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres                                                                      : postgres=CTc/postgres template1          | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres                                                                      : postgres=CTc/postgres(5 rows)postgres=#

Delete in the command line mode:

postgres=# \q[postgres@kevin ~]$ dropdb pg_database_test_2[postgres@kevin ~]$ dropdb pg_databse_test_1[postgres@kevin ~]$ psqlpsql (8.4.2)Type "help" for help.postgres=# \l                                  List of databases   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges-----------+----------+----------+-------------+-------------+----------------------- postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres                                                             : postgres=CTc/postgres template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres                                                             : postgres=CTc/postgres(3 rows)postgres=#