PostgreSQL数据库创建、删除方法

1.在数据库服务器安装完成后，默认有三个数据库，可以通过下面两种方法查看。

postgres=# SELECT * FROM pg_database; datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datc onfig | 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=#

这三个数据库均是由initdb生成的，其中template0 和template1 为数据库模板，创建时直接可以使用其克隆一个新数据库。

2. 创建方法，由什么用户创建，默认数据库owner就为此用户。

[postgres@kevin ~]$ psql postgres psql (8.4.2) Type "help" for help. postgres=# CREATE DATABASE pg_databse_test_1; CREATE DATABASE postgres=# \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=#

另一种命令行创建方法：

[postgres@kevin ~]$ createdb pg_database_test_2; [postgres@kevin ~]$ psql psql (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.为其他数据库角色创建数据库。

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 DB postgres=# CREATE DATABASE pg_database_3 OWNER pg_test_user_4; CREATE DATABASE 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_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=#

命令行方法：

[postgres@kevin ~]$ createdb -O pg_test_user_3 pg_database_4; [postgres@kevin ~]$ psql psql (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. 使用模板数据库创建，此时，对模板的更改会引起所有基于它创建的数据库对象发生相同的变更。

postgres=# CREATE DATABASE pg_datebase_5 TEMPLATE template0; /*SQL方式*/ CREATE DATABASE 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 | pg_datebase_5 | 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 (8 rows) postgres=# \q [postgres@kevin ~]$ createdb -T template0 pg_database_6 /*命令行方式*/ [postgres@kevin ~]$ psql psql (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_6 | 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 | pg_datebase_5 | 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=#

5. 删除数据库。

以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 DATABASE postgres=# DROP DATABASE pg_database_4; DROP DATABASE postgres=# DROP DATABASE pg_database_6; DROP DATABASE postgres=# DROP DATABASE pg_database_7; DROP DATABASE 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=#

以命令行方式删除:

postgres=# \q [postgres@kevin ~]$ dropdb pg_database_test_2 [postgres@kevin ~]$ dropdb pg_databse_test_1 [postgres@kevin ~]$ psql psql (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=#