PostgreSQL数据库创建、删除方法

Postgre SQL 前端之家
前端之家收集整理的这篇文章主要介绍了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=#
原文链接：https://www.f2er.com/postgresql/196897.html

猜你在找的Postgre SQL相关文章

PosegreSQL基础回顾(第 4 章 SQL语法)
来源：http://www.postgres.cn/docs/11/ 4.1.1. 标识符和关键词 SQL标识符和关键...
PosegreSQL基础回顾(第 8 章 数据类型)
来源：http://www.postgres.cn/docs/11/ 8.1. 数字类型 数字类型由2、4或8字节的...
PosegreSQL基础回顾(第 5 章 数据定义)
来源：http://www.postgres.cn/docs/11/ 5.1. 表基础 SQL并不保证表中行的顺序。...
PosegreSQL基础回顾(第 6 章 数据操纵、第 7 章 查询)
来源：http://www.postgres.cn/docs/11/ 6.4. 从修改的行中返回数据 有时在修改行...
PosegreSQL基础回顾(第 13 章 并发控制)
来源：http://www.postgres.cn/docs/11/ 13.2.1. 读已提交隔离级别 读已提交是Po...
PostgreSQL安装和使用
青岛OJ系统用的关系型数据库是PostgreSQL,为此对PostgreSQL大致了解下。 今天的主要话题围...
PostgreSQL整体架构
 
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
专访唐成：从小工到专家，PostgreSQL的修炼之道
唐成，拥有十几年数据库、操作系统、存储领域的工作经验。目前任沃趣科技首席数据库架构师...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问