PostgreSQL数据库创建、删除方法
PostgreSQL数据库创建、删除方法
1.在PostgreSQL数据库服务器安装完成后,默认有三个数据库,可以通过下面两种方法查看。
- 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=#
|
评论暂时关闭