一个 PostgreSQL 实例可以创建多个数据库，以便多个业务共用一个数据库实例，不同的业务使用不同的数据库名称，彼此之间通过数据库名称进行区分和隔离。

1. 创建数据库

创建数据库语法：

CREATE DATABASE name
    [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ STRATEGY [=] strategy ] ]
           [ LOCALE [=] locale ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ ICU_LOCALE [=] icu_locale ]
           [ LOCALE_PROVIDER [=] locale_provider ]
           [ COLLATION_VERSION = collation_version ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ]
           [ OID [=] oid ]

创建数据库示例：

CREATE DATABASE db1;
CREATE DATABASE db2 encoding utf8;
CREATE DATABASE db3 locale 'en_US.UTF-8';
CREATE DATABASE db4 lc_collate 'en_US.UTF-8';
CREATE DATABASE db5 lc_ctype 'en_US.UTF-8';

列出所有数据库名称：

postgres=# \l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
 db1       | antpg | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 db2       | antpg | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 db3       | antpg | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 db4       | antpg | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 db5       | antpg | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | antpg | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | antpg | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/antpg         +
           |       |          |             |             | antpg=CTc/antpg
 template1 | antpg | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/antpg         +
           |       |          |             |             | antpg=CTc/antpg
(7 rows)

切换不同的数据库需要断开已有连接，然后连接到新的数据库，在 psql 中可通过 \c 命令进行数据库的切换，切换之后连接的进程号已发生改变，如下所示：

postgres=# select pg_backend_pid();
 pg_backend_pid
----------------
           1412
(1 row)

postgres=# \c db2
You are now connected to database "db2" as user "antpg".
db2=# select pg_backend_pid();
 pg_backend_pid
----------------
           1625
(1 row)

2. 删除数据库

删除数据库语法：

DROP DATABASE [ IF EXISTS ] name [ [ WITH ] ( option [, ...] ) ]

where option can be:

    FORCE

删除数据库示例：

drop database db1;
drop database if exists db2;

文章评论