PostgreSQL数据库笔记

PostgreSQL 是什么

PostgreSQL（简称Postgres或PG）是一个功能强大、可靠性高、可扩展性好的开源对象-关系数据库服务器（ORDBMS），它以加州大学伯克利分校计算机系开发的POSTGRES版本4.2为基础。
发展历程

起源与发展：PostgreSQL是伯克利的POSTGRES软件包的继承者，并经过不断的发展和完善，现已成为世界上最先进的开源数据库系统之一。
特点

许可证的灵活性：由于许可证的灵活，任何人都可以以任何目的免费使用、修改和分发PostgreSQL，无论是私用、商用还是学术研究使用。

对象-关系型数据库：PostgreSQL支持大部分SQL标准，并提供了许多其他现代特性，如复杂查询、外键、触发器、视图、事务完整性以及多版本并发控制（MVCC）等。
主要功能与特性

可扩展性：PostgreSQL可以通过增加新的数据类型、函数、操作符、聚集函数和索引方法等方式进行扩展。

数据类型丰富：支持包括文本、任意精度的数值数组、JSON数据、枚举类型、XML数据等在内的多种数据类型。

复杂查询与优化：支持复杂的查询操作，包括连接、子查询、聚合函数、窗口函数等，并配备了高效的查询规划器/优化器。

全文检索与NoSQL支持：通过Tsearch2或OpenFTS支持全文检索，并原生支持JSON、JSONB、XML、HStore等NoSQL数据类型。

数据仓库与ETL：能平滑迁移至同属PostgreSQL生态的GreenPlum、DeepGreen、HAWK等数据仓库，并使用FDW进行ETL（提取、转换、加载）。

安全性：提供了强大的安全性措施，包括用户身份验证、数据加密和访问控制等，确保数据的安全性和保密性。
应用场景

Web应用程序：如电子商务网站、社交媒体平台等。

科学和研究项目：用于存储和分析大规模的实验数据和研究结果。

地理信息系统（GIS）：用于存储和处理地理空间数据。

大数据分析：用于存储和分析大规模数据集。

企业级应用程序：在各种企业级应用中作为可靠的数据库管理系统。
官网

https://www.postgresql.org/
安装

在Linux上，可以使用RPM源安装或从源代码编译安装。
添加 PostgreSQL 12 软件包存储库

yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

下载 PostgreSQL 12 客户端、服务端

yum -y install postgresql12-server postgresql12

PostgreSQL 数据库初始化

/usr/pgsql-12/bin/postgresql-12-setup initdb

设置开机自启

systemctl enable postgresql-12 && systemctl start postgresql-12

PostgreSQL不推荐使用root管理，在安装成功postgreSQL后，他默认会给你创建一个用户：postgres。

输入 psql 进入到postgreSQL提供的客户端。

bash-4.2$ psql --help
could not change directory to "/root": 权限不够
psql is the PostgreSQL interactive terminal.Usage:psql [OPTION]... [DBNAME [USERNAME]]General options:-c, --command=COMMAND    run only single command (SQL or internal) and exit-d, --dbname=DBNAME      database name to connect to (default: "postgres")-f, --file=FILENAME      execute commands from file, then exit-l, --list               list available databases, then exit-v, --set=, --variable=NAME=VALUEset psql variable NAME to VALUE(e.g., -v ON_ERROR_STOP=1)-V, --version            output version information, then exit-X, --no-psqlrc          do not read startup file (~/.psqlrc)-1 ("one"), --single-transactionexecute as a single transaction (if non-interactive)-?, --help[=options]     show this help, then exit--help=commands      list backslash commands, then exit--help=variables     list special variables, then exitInput and output options:-a, --echo-all           echo all input from script-b, --echo-errors        echo failed commands-e, --echo-queries       echo commands sent to server-E, --echo-hidden        display queries that internal commands generate-L, --log-file=FILENAME  send session log to file-n, --no-readline        disable enhanced command line editing (readline)-o, --output=FILENAME    send query results to file (or |pipe)-q, --quiet              run quietly (no messages, only query output)-s, --single-step        single-step mode (confirm each query)-S, --single-line        single-line mode (end of line terminates SQL command)Output format options:-A, --no-align           unaligned table output mode--csv                CSV (Comma-Separated Values) table output mode-F, --field-separator=STRINGfield separator for unaligned output (default: "|")-H, --html               HTML table output mode-P, --pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)-R, --record-separator=STRINGrecord separator for unaligned output (default: newline)-t, --tuples-only        print rows only-T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)-x, --expanded           turn on expanded table output-z, --field-separator-zeroset field separator for unaligned output to zero byte-0, --record-separator-zeroset record separator for unaligned output to zero byteConnection options:-h, --host=HOSTNAME      database server host or socket directory (default: "local socket")-p, --port=PORT          database server port (default: "5432")-U, --username=USERNAME  database user name (default: "postgres")-w, --no-password        never prompt for password-W, --password           force password prompt (should happen automatically)For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.Report bugs to <[e-mail]>.

查看有哪些库：
su postgres

\l

如果是新安装的，有三个库，一个是postgres，template0，template1。

# 切换到postgres用户
[root@review ~]# su postgres
bash-4.2$ psql
could not change directory to "/root": 权限不够
psql (12.20)
Type "help" for help.postgres=# \lList of databasesName    |  Owner   | Encoding |   Collate   |    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/postgrestemplate1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          
+|          |          |             |             | postgres=CTc/postgres
(3 rows)postgres=# 

配置文件

[root@review ~]# ll /var/lib/pgsql/12/data
总用量 64
drwx------. 5 postgres postgres    41 10月 25 20:53 base
-rw-------. 1 postgres postgres    30 10月 25 20:33 current_logfiles
drwx------. 2 postgres postgres  4096 10月 25 20:32 global
drwx------. 2 postgres postgres    32 10月 25 20:31 log
drwx------. 2 postgres postgres     6 10月 25 20:31 pg_commit_ts
drwx------. 2 postgres postgres     6 10月 25 20:31 pg_dynshmem
-rw-------. 1 postgres postgres  4517 10月 25 20:33 pg_hba.conf
-rw-------. 1 postgres postgres  1636 10月 25 20:31 pg_ident.conf
drwx------. 4 postgres postgres    68 10月 25 20:53 pg_logical
drwx------. 4 postgres postgres    36 10月 25 20:31 pg_multixact
drwx------. 2 postgres postgres    18 10月 25 20:31 pg_notify
drwx------. 2 postgres postgres     6 10月 25 20:31 pg_replslot
drwx------. 2 postgres postgres     6 10月 25 20:31 pg_serial
drwx------. 2 postgres postgres     6 10月 25 20:31 pg_snapshots
drwx------. 2 postgres postgres     6 10月 25 20:31 pg_stat
drwx------. 2 postgres postgres    63 10月 25 20:56 pg_stat_tmp
drwx------. 2 postgres postgres    18 10月 25 20:31 pg_subtrans
drwx------. 2 postgres postgres     6 10月 25 20:31 pg_tblspc
drwx------. 2 postgres postgres     6 10月 25 20:31 pg_twophase
-rw-------. 1 postgres postgres     3 10月 25 20:31 PG_VERSION
drwx------. 3 postgres postgres    60 10月 25 20:31 pg_wal
drwx------. 2 postgres postgres    18 10月 25 20:31 pg_xact
-rw-------. 1 postgres postgres    88 10月 25 20:31 postgresql.auto.conf
-rw-------. 1 postgres postgres 26736 10月 25 20:31 postgresql.conf
-rw-------. 1 postgres postgres    58 10月 25 20:31 postmaster.opts
-rw-------. 1 postgres postgres   103 10月 25 20:31 postmaster.pid

远程连接
改用户名

alter user postgres with password ‘wkb456’;
PostgreSQL要基于配置文件修改，才能制定用户是否可以远程连接。
用户级别配置

修改 pg_hba.conf 文件

local：代表本地连接，host代表可以指定连接的ADDRESS。database：编写数据库名，如果写all，代表所有库都可以连接。user：编写连接的用户，可以写all，代表所有用户。address：代表那些IP地址可以连接。method：代表加密方式。

允许任意地址的全部用户连接所有数据库：

host    all             all             0.0.0.0/0               md5# TYPE  DATABASE        USER            ADDRESS                 METHOD# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

服务级别配置

修改 postgresql.conf 文件

# - Connection Settings -#listen_addresses = 'localhost'         # what IP address(es) to listen on;# comma-separated list of addresses;# defaults to 'localhost'; use '*' for all# (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
#unix_socket_directories = '/var/run/postgresql, /tmp'  # comma-separated list of directories# (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation# (change requires restart)
#bonjour = off                          # advertise server via Bonjour# (change requires restart)
#bonjour_name = ''                      # defaults to the computer name# (change requires restart)

修改为

listen_addresses = '*'         # what IP address(es) to listen on;

保存后退出，然后重启 postgresql-12

systemctl restart postgresql-12

数据库日志

postgresql.conf 文件，默认情况下，只保存7天的日志，循环覆盖。

# REPORTING AND LOGGING
#------------------------------------------------------------------------------# - Where to Log -log_destination = 'stderr'              # Valid values are combinations of# stderr, csvlog, syslog, and eventlog,# depending on platform.  csvlog# requires logging_collector to be on.# This is used when logging to stderr:
logging_collector = on                  # Enable capturing of stderr and csvlog# into log files. Required to be on for# csvlogs.# (change requires restart)# These are only used if logging_collector is on:
log_directory = 'log'                   # directory where log files are written,# can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.log'      # log file name pattern,# can include strftime() escapes
#log_file_mode = 0600                   # creation mode for log files,# begin with 0 to use octal notation
log_truncate_on_rotation = on           # If on, an existing log file with the# same name as the new log file will be# truncated rather than appended to.# But such truncation only occurs on# time-driven rotation, not on restarts# or size-driven rotation.  Default is# off, meaning append to existing files# in all cases.
log_rotation_age = 1d                   # Automatic rotation of logfiles will# happen after that time.  0 disables.
log_rotation_size = 0                   # Automatic rotation of logfiles will# happen after that much log output.# 0 disables.

代表日志是开启状态。
logging_collector = on

日志存放的路径，默认放到当前目录下的log里。
log_directory = ‘log’

日志的文件名，默认是postgresql为前缀，星期作为后缀。
log_filename = ‘postgresql-%a.log’

默认一周过后，日志文件会被覆盖。
log_truncate_on_rotation = on

一天产生一个日志文件。
log_rotation_age = 1d

没有限制日志文件的大小。
log_rotation_size = 0