PostgreSQL 数据库与模式(一)
基本概念
数据库管理系统(DBMS)是用于管理数据库的软件系统。常见的关系型DBMS有Oracle、MySQL、Microsoft SQL Server、PostgreSQL、Db2等。常见的NoSQLDBMS有Redis、MongoDB、Cassandra、Neo4j等。
数据库系统由实例(Instance)和物理数据库集群(Database Cluster)组成。通常所说的数据库管理系统也就是指数据库系统。
实例(Instance)由PostgreSQL后台进程和相关的内存组成。启动服务器进程时创建一个实例,关闭服务器进程时实例随之关闭。启动PostgreSQL服务器进程之后,可以通过操作系统的ps命令查询相关的后台进程
[hui@hadoop201 ~]$ ps -ef | grep postgres postgres 2643 1 0 09:41 ? 00:00:00 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/ postgres 2645 2643 0 09:41 ? 00:00:00 postgres: logger postgres 2647 2643 0 09:41 ? 00:00:00 postgres: checkpointer postgres 2648 2643 0 09:41 ? 00:00:00 postgres: background writer postgres 2649 2643 0 09:41 ? 00:00:00 postgres: walwriter postgres 2650 2643 0 09:41 ? 00:00:00 postgres: autovacuum launcher postgres 2651 2643 0 09:41 ? 00:00:00 postgres: stats collector postgres 2652 2643 0 09:41 ? 00:00:00 postgres: logical replication launcher root 2657 2298 0 09:41 pts/3 00:00:00 su postgres postgres 2658 2657 0 09:41 pts/3 00:00:00 bash postgres 2664 2658 0 09:44 pts/3 00:00:00 psql -U postgres postgres 2665 2643 0 09:44 ? 00:00:00 postgres: postgres postgres [local] idle postgres 2685 2643 0 09:56 ? 00:00:00 postgres: postgres postgres 192.168.124.1(62215) idle root 2964 2436 0 13:00 pts/2 00:00:00 psql -h hadoop201 -p 5432 -U wdh postgres postgres 2966 2643 0 13:00 ? 00:00:00 postgres: wdh postgres 192.168.124.141(44540) idle hui 3208 3141 0 15:01 pts/0 00:00:00 grep --color=auto postgres [hui@hadoop201 ~]$
![](https://img2023.cnblogs.com/blog/1534499/202303/1534499-20230318150506207-1514445901.png)
创建数据库
创建数据库之前,先看下默认的数据库有哪些
方式1:postgres用户登录,通过psql工具连接,使用 \l 查看
[hui@hadoop201 preserve]$ su postgres Password: bash-4.2$ psql psql (12.13) Type "help" for help. postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres
方式2:sql查询
select * from pg_database ;
系统默认创建了3个数据库,其中template0和template1是模板数据库,创建新的数据库时默认基于template1进行复制;postgres数据库是为postgres用户创建的默认数据库。
使用SQL语CREATE DATABASE创建一个数据库:
CREATE DATABASE name;
其中的name指定了数据库的名称。举个例子创建一个名为testdb的数据库
postgres=# CREATE DATABASE testdb; CREATE DATABASE postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
创建数据库时还可以指定许多选项,例如字符集编码、拥有者、默认表空间、最大连接数等等。具体参考官方文档中完整的CREATE DATABASE语句。
修改数据库
创建之后,可以通过ALTER DATABASE语句修改数据库的属性和配置:
ALTER DATABASE name action;
其中,action指定了要执行的修改操作,例如修改数据库的名称、所有者、默认表空间、数据库会话变量的默认值等等。以下语句修改testdb的名称:
postgres=# ALTER DATABASE testdb RENAME TO test; ALTER DATABASE postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (4 rows)
然后将数据库test的拥有者修改为wdh:
postgres=# alter database test owner to wdh; ALTER DATABASE postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres test | wdh | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
除了修改常见的属性之外,ALTER DATABASE语句还可以用于修改运行时配置变量的会话默认值。当用户连接数据库时,PostgreSQL使用配置文件postgresql.conf或者启动命令postgres中设置的变量值作为默认值。使用ALTER DATABASE语句可以设置指定数据库的这些配置:
ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
对于随后连接的会话,PostgreSQL将会使用该命令设置的值覆盖postgresql.conf文件或者命令行参数的值。注意,只有超级用户或者数据库的拥有者才能修改数据库的默认会话变量。例如,以下语句将会默认禁用数据库test中的索引扫描:
postgres=# ALTER DATABASE test SET enable_indexscan TO off; ALTER DATABASE
使用以下命令可以还原默认配置:
ALTER DATABASE newdb RESET enable_indexscan;