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=# \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 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=#

另一種命令列建立方法：

[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.為其他資料庫角色建立資料庫。

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=#

 命令列方法：

[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. 使用模板資料庫建立，此時，對模板的更改會引起所有基於它建立的資料庫物件發生相同的變更。

postgres=# CREATE DATABASE pg_datebase_5  TEMPLATE template0; /*SQL方式*/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_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 ~]$ 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_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 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=#

以命令列方式刪除:

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=#