PostgreSQL数据库创建、删除方法
1.在数据库服务器安装完成后，默认有三个数据库，可以通过下面两种方法查看。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=# \lList of databasesName |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/postgrestemplate1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres: postgres=CTc/postgres(3 rows)postgres=#
这三个数据库均是由initdb生成的，其中template0 和template1 为数据库模板，创建时直接可以使用其克隆一个新数据库。
2. 创建方法，由什么用户创建，默认数据库owner就为此用户。
$ psql postgrespsql (8.4.2)Type "help" for help.postgres=# CREATE DATABASE pg_databse_test_1;CREATE DATABASEpostgres=# \lList of databasesName |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/postgrestemplate1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres: postgres=CTc/postgres(4 rows)postgres=# 另一种命令行创建方法：
$ createdb pg_database_test_2;$ psqlpsql (8.4.2)Type "help" for help.postgres=# \lList of databasesName |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/postgrestemplate1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres: postgres=CTc/postgres(5 rows)postgres=#
3.为其他数据库角色创建数据库。
postgres=# \duList of rolesRole name | Attributes| Member of----------------+-------------+-----------pg_test_user_3 | Create DB | {}pg_test_user_4 | Create role | {}: Create DBpostgres | Superuser | {}: Create role: Create DBpostgres=# CREATE DATABASE pg_database_3 OWNER pg_test_user_4;CREATE DATABASEpostgres=# \lList of databasesName | 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/postgrestemplate1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres: postgres=CTc/postgres(6 rows)postgres=#
命令行方法：
$ createdb -O pg_test_user_3 pg_database_4;$ psqlpsql (8.4.2)Type "help" for help.postgres=# \lList of databasesName | 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/postgrestemplate1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres: postgres=CTc/postgres(7 rows)postgres=# 4. 使用模板数据库创建，此时，对模板的更改会引起所有基于它创建的数据库对象发生相同的变更。
postgres=# CREATE DATABASE pg_datebase_5TEMPLATE template0; /*SQL方式*/CREATE DATABASEpostgres=# \lList of databasesName | 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/postgrestemplate1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres: postgres=CTc/postgres(8 rows)postgres=# \q$ createdb -T template0 pg_database_6 /*命令行方式*/$ psqlpsql (8.4.2)Type "help" for help.postgres=# \lList of databasesName | 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/postgrestemplate1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres: postgres=CTc/postgres(9 rows)postgres=# 5. 删除数据库。
以SQL方法删除：
postgres=# \lList of databasesName | 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/postgrestemplate1 | 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=# \lList of databasesName |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/postgrestemplate1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres: postgres=CTc/postgres(5 rows)postgres=#
以命令行方式删除:
postgres=# \q$ dropdb pg_database_test_2$ dropdb pg_databse_test_1$ psqlpsql (8.4.2)Type "help" for help.postgres=# \lList of databasesName |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/postgrestemplate1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres: postgres=CTc/postgres(3 rows)postgres=#
页:
[1]