PostgreSQL

official website: https://www.postgresql.org/ https://www.postgresql.org/download/linux/redhat/

1. Install

1.1. MacOS 安装

brew install postgresql
brew postgresql-upgrade-database
brew services start postgresql
 
# 数据的目录
cd /opt/homebrew/var/postgres
 
# 日志的目录
cd /opt/homebrew/var/log
 
# 系统意外 reboot , after that, delete this file.
/opt/homebrew/var/postgres/postmaster.pid

1.2. Windows安装

准备好安装包postgresql-11.5-1-windows-x64-binaries.zip
默认情况下，会将当前的 Windows 用户作为管理员，例如“Chris”注意在初始化连接之后，在防火墙中开放5432端口。

解压到E盘根目录中

初始化
bin\initdb.exe -D data -E UTF8
bin\initdb.exe --username=postgres --encoding=UTF8 --lc-collate=C --lc-ctype=en_US.utf8 --data-checksums -D data
 
启动
bin\pg_ctl -D data -l logfile.txt start
 
连接
E:>bin\psql postgres

1.3. CentOS在线安装

到目前为止(2019-12-4)，CentOS7 默认携带了9.2.24版本的PostgreSQL，我们可以直接从源进行安装:

# 在线安装
sudo yum install postgresql postgresql-server postgresql-contrib
 
# 初始化数据库
sudo postgresql-setup initdb
sudo systemctl start postgresql
 
# 设置为开机自启动
sudo systemctl enable postgresql
 
# 重启服务
systemctl restart postgresql

1.4. CentOS离线安装

下载地址：https://yum.postgresql.org/rpmchart.php

下载地址： https://yum.postgresql.org/11/redhat/rhel-7-x86_64/repoview https://download.postgresql.org/pub/repos/yum/17/redhat/rhel-9-x86_64

# 先安装依赖包
yum -y install libicu libxslt perl-lib systemd-libs
 
# 按照如下顺序安装好所有的包
[root@node1 opt]# rpm -ivh postgresql11-libs-11.2-1PGDG.rhel7.x86_64.rpm
[root@node1 opt]# rpm -ivh postgresql11-11.2-1PGDG.rhel7.x86_64.rpm
[root@node1 opt]# rpm -ivh postgresql11-contrib-11.2-1PGDG.rhel7.x86_64.rpm
[root@node1 opt]# rpm -ivh postgresql11-server-11.2-1PGDG.rhel7.x86_64.rpm
 
- 注意：注意离线的安装方式，版本号会影响相关的文件路径。例如 `/var/lib/pgsql` 变成 `/var/lib/pgsql/11` `systemctl status postgresql-11`
 
- 注意：程序会被按照到 `/usr/pgsql-11/` 这个目录
 
# 初始化DB
/usr/pgsql-11/bin/postgresql-11-setup initdb
Initializing database ... OK
 
# 启动DB服务
systemctl start postgresql-11
systemctl stop postgresql-11
 
# 设置开机启动
systemctl enable postgresql-11
 
# 关闭防火墙
systemctl stop firewalld
systemctl disable firewalld

2. Set up

2.1. 配置用户

# 修改postgres用户密码：
[root@node1 bin]# passwd postgres # [email protected]
 
# 切换到postgres用户：
[root@node1 bin]# su - postgres
 
# 修改PostgreSQL的postgres用户的密码：注意：Linux上的postgres用户用来访问数据库，PostgreSQL 中的 postgres 用户用来管理数据库
bash-4.2$ psql -d template1 -c "ALTER USER postgres WITH PASSWORD '[email protected]';" # 这里我们测试用：[email protected]
 
# 访问数据库：
bash-4.2$ psql postgres
psql (11.2)
Type "help" for help.
postgres=#

2.2. 远程访问

# 配置文件位置，如下是默认的配置文件路径：
vi /var/lib/pgsql/11/data/pg_hba.conf
 
# 在配置文件的下面添加如下内容，允许所有地址进行访问，通过md5加密的密码，注意该文件的优先级是从上到下的，所以需要写在默认的上面
host all all 0.0.0.0/0 md5
host all all 127.0.0.1/32 ident # 这里是默认的位置
host all all 172.26.114.88/32 md5 # 允许单个机器登录，使用md5密码。
host all all 0.0.0.0/0        md5 # 允许所有机器登录，使用md5密码。
host all all 127.0.0.1/32  trust # 允许本地机器登录，不使用密码。
 
# 修改配置文件
vi /var/lib/pgsql/11/data/postgresql.conf
listen_addresses = '*' # 监听所有ip，默认是localhost
listen_addresses = '172.26.114.87' # 修改成本机IP
max_connections = 1000 # 默认100
tcp_keepalives_idle = 600 # TCP_KEEPIDLE, in seconds;
tcp_keepalives_interval = 10 # TCP_KEEPINTVL, in seconds;
tcp_keepalives_count = 6 # TCP_KEEPCNT;
systemctl restart postgresql-11.service
 
# 验证端口开放情况
[root@loandb /]# netstat -tunlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 3504/sshd
tcp 0 0 172.26.114.87:5432 0.0.0.0:* LISTEN 17512/postmaster
udp 0 0 0.0.0.0:68 0.0.0.0:* 3014/dhclient
udp 0 0 127.0.0.1:323 0.0.0.0:* 2021/chronyd
udp6 0 0 ==1:323 :==* 2021/chronyd
 
# 重启服务
systemctl restart postgresql-11.service
 
# 在其他机器上链接即可
psql -h 172.26.114.87 -U postgres

2.3. 配置 .pgpass

在数据库的日常维护中，在每次链接的时候，我们都需要输入密码，使用该文件可以自动输入密码。

# postgres 用户，并赋予 600 权限。
vim ~/.pgpass
172.26.221.137:5432:database:username:password
# 链接服务
psql -h 172.26.221.137 -p 5432 database username

2.4. 更改数据存放目录

PostgreSQL 在 Centos 7 系统更改数据存放目录
# 创建data目录，赋予权限并修改启动文件
mkdir -p /opt/pgsql/data && chown postgres:postgres /opt/pgsql/data
mkdir -p /opt/pgsql/pg_archive && chown postgres:postgres /opt/pgsql/pg_archive
 
cd /opt/pgsql && chmod 700 data
cd /opt/pgsql && chmod 700 pg_archive
 
# 修改启动脚本的数据文件路径，前提是已经将服务添加到systemctl进行管理了。
vim /usr/lib/systemd/system/postgresql-11.service
Environment=PGDATA=/opt/pgsql/data/
systemctl daemon-reload # 更新
 
# 把原data目录下的文件copy到新的data的目录下,并改变属组：
cp -r /var/lib/pgsql/11/data/* /opt/pgsql/data/
cd /opt/pgsql && chown -R postgres:postgres data
 
# 重新启动服务
systemctl restart postgresql-11.service

2.5. 初始化

在基本程序安装完成之后，使用该命令初始化数据库。 数据目录(data directory | data area)，在初始化的时候使用 -D 参数来控制。 注意：数据目录已经存在且初始化了的话，initdb将不会运行。 注意：initdb 同时为 database 初始化默认的locale，一般而言，它只是使用本地locale设置并将其应用到初始化的数据库中，在template数据库中使用的order是不会改变的。使用其他的locale而不是使用C或POSIX会对性能产生影响。因此在第一次正确的选择选项是很重要的。 注意：initdb 同时为 database 设置默认的字符集和encoding，一般而言应该选择匹配locale设置。

案例

# 初始化方式1
initdb -D /usr/local/pgsql/data
# 初始化方式2
pg_ctl -D /usr/local/pgsql/data initdb
 
# 修改服务自启动配置：
/usr/lib/systemd/system/postgresql-12.service
# 修改数据存放位置：
Environment=PGDATA=/home/root/database
# 重新加载文件并启动服务：
systemctl daemon-reload
systemctl restart postgresql-12.service

other parameters:

-U username/—username=username: 选择数据库superuser的用户名。这默认为运行initdb的用户的名称。 -E encoding/—encoding=encoding选择模板数据库的编码。这也是您稍后创建的任何数据库的默认编码， —lc-collate/—lc-ctype: 更改默认的排序顺序或字符集类。 -k/—data-checksums：在数据页上使用校验和来帮助检测I/O系统的损坏，否则系统将是静默的。启用校验和可能会导致显著的性能损失。如果设置，则计算所有数据库中所有对象的校验和。所有校验和失败将在pg_stat_database视图中报告。 所以postgresql初始化可能使用的命令是：

su - pguser001 -c "/u01/pgsql/bin/initdb --username=pguser001 --encoding=UTF8 --lc-collate=C --lc-ctype=en_US.utf8  --data-checksums -D /data"

2.6. 优化 连接数管理

APP连接串：
jdbc:postgresql://172.16.1.83:5432/lms?tcpKeepAlive=true&autoReconnect=true
1、当APP连接保持连接不中断的时候，就不能让这个连接被服务器中断，所以使用tcpKeepAlive参数，来响应连接。
2、当APP服务停止时，就需要关闭这个链接，所以服务器就需要调整idle配置。
在网络上连接远程服务器postgresql时，不活动时间稍长就会自动断开连接，不利于操作

Linux默认时间是2小时，也就是说频繁重启APP测试程序，连接可能还在，
在PG中的TCP_KEEPLIVE机制：
有三个系统变量tcp_keepalives_idle，tcp_keepalives_interval ，tcp_keepalives_count 来设置postgresql如何处理死连接。
对于每个连接，postgresql会对这个连接空闲tcp_keepalives_idle秒后，主动发送tcp_keeplive包给客户端，以侦探客户端是否还活着 ，当发送tcp_keepalives_count个侦探包，每个侦探包在tcp_keepalives_interval秒内没有回应，postgresql就认为这个连接是死的。于是切断这个死连接。
Linux下面默认是2小时，tcp的keepalives包发送间隔以及重试次数。 如果你的网络环境中有设备自动断开空闲会话，那么建议你设置心跳时间小于网络设备的断链接阈值。

— 查看哪些用户在链接数据库(这个是一个视图)

select * from pg_stat_activity;

— 基础优化内容

show tcp_keepalives_idle; — idle 7200

show tcp_keepalives_interval; — interval 75

show tcp_keepalives_count; — the count of packet 9

— 默认配置

show tcp_keepalives_idle; — 7200 # 2小时

show tcp_keepalives_interval; — 75

show tcp_keepalives_count; — 9

— 调整后配置

tcp_keepalives_idle = 600

tcp_keepalives_interval = 10

tcp_keepalives_count = 6

— 最大连接数

show max_connections;

/*

杀掉进程：取消查询或者关闭进程

pg_cancel_backend 只是取消当前某一个进程的查询操作，但不能释放数据库连接

pg_terminate_backend 可以在pg的后台杀死这个进程，从而释放出宝贵的连接资源

*/

select pg_terminate_backend(10598); — 杀掉指定进程

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE application_name=‘dbvis’; — 关闭一些活动链接

Copy

每一个TCP连接会创建一个进程？

ps -ef|grep postgres

postgres 10271 1 0 16:59 ? 00:00:00 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/

postgres 10274 10271 0 16:59 ? 00:00:00 postgres: logger

postgres 10276 10271 0 16:59 ? 00:00:00 postgres: checkpointer

postgres 10277 10271 0 16:59 ? 00:00:00 postgres: background writer

postgres 10278 10271 0 16:59 ? 00:00:00 postgres: walwriter

postgres 10279 10271 0 16:59 ? 00:00:00 postgres: autovacuum launcher

postgres 10280 10271 0 16:59 ? 00:00:00 postgres: stats collector

postgres 10281 10271 0 16:59 ? 00:00:00 postgres: logical replication launcher

postgres 10293 10271 0 16:59 ? 00:00:00 postgres: postgres postgres 172.26.114.87(40214) idle

postgres 10296 10271 0 16:59 ? 00:00:00 postgres: postgres postgres 172.26.114.87(40216) idle

postgres 10299 10271 0 16:59 ? 00:00:00 postgres: postgres postgres 172.26.114.87(40218) idle

Copy

2.7. 优化 慢SQL

在PostgreSQL数据库正式运行一段时间后，我们需要关注执行慢的SQL，找到这些SQL，才能提升数据库整体的性能。
PostgreSQL提供了pg_stat_statements来存储SQL的运行次数，总运行时间，shared_buffer命中次数，shared_buffer read次数等统计信息。
注意：这里我用的是 PG11.2 版本。

• 开启统计配置

# 4. 在postgresql.conf 配置文件中，开启如下配置，然后重启数据库服务。
 
shared_preload_libraries = 'pg_stat_statements'
 
pg_stat_statements.max = 10000
 
pg_stat_statements.track = all

• 相关的表

— 创建扩展

CREATE EXTENSION pg_stat_statements;

— 查看统计表中的内容

select * from pg_stat_statements;

— 重置所有的统计结果

SELECT pg_stat_statements_reset();

Copy

• 基本的查询语句

— 查询慢SQL语句 1

SELECT

  (total_time / 1000 / 60) as total_minutes,

  (total_time / calls) as average_time,

  query

FROM pg_stat_statements

ORDER BY 1 DESC

LIMIT 100;

— 查询慢SQL语句 2

SELECT

  query,

  calls,

  total_time,

  (total_time/calls) as average ,

  rows,

  100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent

FROM pg_stat_statements

ORDER BY average DESC LIMIT 10;

Copy

• 参考文档
https://www.postgresql.org/docs/current/pgstatstatements.html

2.8. 优化 explain

PostgreSQL 优化 - EXPLAIN 优化SQL

explain select * from public.account;

3. 用户和权限

使用 create user 创建的用户有 login 权限；
使用 create role 创建的用户默认没有 login 权限；
权限
-- 查询所有的 role
select * from pg_roles order by rolname;
-- 创建一个 role 仅有 rolinhert 权限，不能进行登录。
create role test_role1;
-- 删掉一个 role
drop role test_role1;
-- 修改一个 role
alter role test_role1 rename to test_role2;
-- rolcanlogin 权限，只有当存在 login 权限的时候才能进行登录。
create role test_role2 login; -- 添加 rolcanlogin 权限
alter role test_role2 nologin; -- 删除 rolcanlogin 权限
-- rolsuper 权限，数据库的最高权限
create role test_role2 superuser;
alter role test_role2 nosuperuser;
所有的权限
rolsuper 超级用户
rolinherit
rolcreaterole 创建role
rolcreatedb 创建数据库
rolcanlogin 登录
rolreplication
rolconnlimit
rolpassword
rolvaliduntil
rolbypassrls
rolconfig
数据库权限
-- 修改数据库所有者。
alter database dbName owner to userName;
-- 默认情况下，只有数据库的所有者可以对其中的数据表进行操作。
grant 权限 on 数据表 to 用户名称；
表权限
alter table table_1 owner to testuser_1;
密码
create user postgres superuser;
create role test_role2 login password '123456';

4. 常用命令

4.1. pg_dump

# 导出数据库
su - postgres
bin\pg_dump -E UTF-8 -h localhost -p 5432 -U postgres -f db.sql fss
 
# 备份远程数据库
D:\Postgresql16\bin\pg_dump.exe --file "C:\Users\ducha\Desktop\fss0324.sql" --host 39.100.7.228 --port 5432 --username "postgres" --encoding "UTF8" --verbose fss
 
# 导入数据库
su - postgres
psql
create database lms;
 
# -d 后面指定要导入的数据库 -U指定将要给哪个用户导入，-f指定要导入的文件
psql -d fss -U postgres -f /opt/db.sql
 
导入报错：invalid byte sequence for encoding "UTF8": 0xff 
解决方法：将文件编码都转换成UTF-8，在导出的时候直接指定编码最好
file create.sql 
create.sql: Little-endian UTF-16 Unicode text, with CRLF CR Line terminators 
iconv转换文件编码 
iconv -f UTF-16 -t UTF-8 create.sql > createutf8.sql
 
 
# 导出单个表，不带数据(-s 只导出表结构，不带数据)
pg_dump -h host -p port -U username -s -t tablename dbname > struct.sql
 
# 导出表带有数据（去掉 -s 就可以了）
pg_dump -h host -p port -U username -t tablename dbname > struct.sql
 
# 导入单个表(需要删除原来的表)
psql -d dbname -U postgres -f /tmp/beap_customers.sql

4.2. copy 导出导入数据

• 这里我们是在Windows上进行学习的，需要留意操作系统之间的差异。
• 注意：多次导入数据，导致重复的问题。
• 注意：数据中的 “\” 字符会产生问题。

-- 查看客户端字符编码，留意中文字符问题，如果是从“文本文件”导入数据，这个文件的字符编码需要和客户端的编码一致。
-- 1. 数据文件编码
-- 2. PG客户端编码
-- 3. PG服务器编码？（库表）？
show client_encoding;
set client_encoding='UTF8';
 
-- 切换到我们的目标数据库上。
\c aoye
 
# 创建一个临时表，用来存放数据。
create table tb_tmp5(col_1 varchar(50),col_2 varchar(50),col_3 varchar(50),col_4 varchar(50),col_5 varchar(50));
create table tb_tmp7(col_1 varchar(50),col_2 varchar(50),col_3 varchar(50),col_4 varchar(50),col_5 varchar(50),col_6 varchar(50),col_7 varchar(50));
 
# 写法 1 ：指定数据列的分隔符为逗号。
copy tb_tmp7 from 'K://Aoye/X.Hotel 130516/2-2013-05-16.txt' using delimiters ','; # 互联网推荐的路径写法
copy tb_tmp7 from 'K:\Aoye\X.Hotel 130516\3-2013-05-16.txt' using delimiters ','; # Windwos 系统路径写法
copy tb_tmp7 from 'K:/Aoye/X.Hotel 130516/3-2013-05-16.txt' using delimiters ','; # Linux 系统路径写法，个人推荐
 
# 导出数据到一个csv文件中。
copy table_name to 'D://table_name.csv' with csv header;
 
# 从csv文件中导入数据到表，前提是已经创建好了这个表。
copy table_name from 'D://table_name.csv' with csv header;

4.3. psql

official client.

# 连接到数据库
psql -d [database_name] -U [user_name] -h [server_host] -p 5432
 
Copy
\?                     查看所有帮助内容
\? [commands]          显示反斜线命令的帮助
\? options             显示 psql 命令行选项的帮助
\? variables           显示特殊变量的帮助
\h [NAME]              SQL命令语法上的说明，用*显示全部命令的语法说明

postgres=# \h create database
命令：       CREATE DATABASE
描述：       建立新的数据库
语法：
CREATE DATABASE 名称
           [ [ WITH ] [ OWNER [=] 用户名 ]
           [ TEMPLATE [=] 模版 ]
           [ ENCODING [=] 字符集编码 ]
           [ LC_COLLATE [=] 排序规则 ]
           [ LC_CTYPE [=] 字符分类 ]
           [ TABLESPACE [=] 表空间的名称 ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] 连接限制 ]
           [ IS_TEMPLATE [=] istemplate ] ]
 
URL: https://www.postgresql.org/docs/12/sql-createdatabase.html
 
# Demo
create database b with encoding=utf8;
 
# 其他常用
\l # 查看所有数据库列表
\c [database_name] # 切换数据库
\d # 列出表、视图、序列（需要切换到数据库上）
\ds # 列出序列列表
\dt # 列出表列表
\dT # 列出数据类型列表
\d [table_name] # 查看表结构信息
\i [C:/db.sql] # 执行外部的SQL文件，注意在Windows下的路径，是/而不是\，否则会提示Permission denied。
\du # Database User 查看用户信息
\q # 退出pg命令行
\h # 查看所有的sql关键字
\? # 命令行操作的帮助

# 查看数据库配置参数命令
show max_connections
 
-- 创建用户，创建数据库并指定归属用户
createuser test_user
createdb test_db -O test_user
 
-- 查看所有表的信息
select * from pg_tables;
-- public下的表，也就是我们的用户表
select * from pg_tables where schemaname = 'public';
-- 查看所有视图
select * from pg_views;

5. Function

pg_backend_pid()
nextval('hibernate_sequence') 序列的下一个值
select random();  -- 0~1之间的随机数。

6. Sequence

create sequence approval_form_id;
create sequence if not exists sq_2022 increment by 1 minvalue 1 no maxvalue start with 1;
select nextval('approval_form_id');

• 注意，例如某些场景，需要每年使用新的序列，就需要在代码层控制年份，但是创建sequence的时候，年份数字需要放在后面。 http://www.manongjc.com/detail/24-uxfqgrmcoggqvrv.html

    /**
     * 生成审批单编号，用户定的规则，每年创建一个新的sequence，重新计数
     * e.g. 保组字（上）【2022】0001号，审批单编号字段自动填充规则
     * @return
     */
    @Secured(['ROLE_ADMINISTRATOR', 'ROLE_OPPORTUNITY_EDIT'])
    @Transactional
    def generateApprovalFormID() {
        Calendar calendar = Calendar.getInstance()
        String currentYear = (calendar.get(Calendar.YEAR)) as String
        String sequenceName = "approval_form_id_" + currentYear
        String createSequenceSql = "create sequence if not exists " + sequenceName
        String queryNextValSql = "select nextval('" + sequenceName + "')"
        def sql = new Sql(dataSource)
        sql.execute(createSequenceSql)
        int id = sql.firstRow(queryNextValSql)['nextval']
        String approvalFormID = "保组字（上）【" + currentYear + "】" + String.format("%04d", id) + "号"
        render([approvalFormID: approvalFormID] as JSON)
    }

-- Show nothing if there is no sequence.
fss=# \ds
                     关联列表
 架构模式 |        名称          |  类型  |  拥有者
----------+--------------------+--------+----------
 public   | hibernate_sequence | 序列数 | postgres
 
 
-- Get the next value of the sequence.
select nextval('hibernate_sequence');
 
-- Get the current value of the sequence.
select currval('hibernate_sequence');

7. About Time

current_date -- 2019-08-15
current_time -- 17:21:03
current_timestamp -- 2019-08-15 17:21:03
localtime -- 17:21:03
localtimestamp -- 2019-08-15 17:21:03
now() -- 2019-08-15 17:21:03
transaction_timestamp -- 2019-08-15 17:21:03
now() - now() -- 00:00:00
date_trunc('month',current_date) -- 2019-08-01 00:00:00 获取当月的第一天
 
 
-- created_date 是 timestamp 类型的字段，不能直接 = like <> , 需要先转成可以进行比较的字符串。
select created_date==varchar(10) from public.collateral where created_date==varchar(10) = '2019-05-07'
select to_char(created_date,'YYYY-MM-DD') from public.collateral;

7.1. Other

-- 添加字段
 
alter table vehicle add modify_by_id BIGINT;
 
alter table vehicle add foreign key(modify_by_id) references login_user(id);
 
select data_part()
 
select (current_timestamp - collateral.created_date) as passtime from public.collateral;
 
select * from public.right_certification;
 
select * from public.component
 
select extract(day FROM (age('2017-12-10'==date , '2017-12-01'==date)));
 
select (now()-interval '1 day'); -- 昨天
 
select extract(epoch FROM (now() - (now()-interval '3 day') )); -- 计算时间差（秒）259200.0
 
EXTRACT（关健字 FROM 日期或时间型字段）
 
例如：我们想从一个入库表(RK)的"入库时间(INTIME)"（此入库时间为 TIMESTAMP 型）字段内提取相应的时间数据。有如下形式：
 
语名 说明
 
SELECT EXTRACT(YEAR FROM INTIME) FROM RK 从INTIME字段中提取年份
 
SELECT EXTRACT(MONTH FROM INTIME) FROM RK 从INTIME字段中提取月份
 
SELECT EXTRACT(DAY FROM INTIME) FROM RK 从INTIME字段中提取日
 
SELECT EXTRACT(HOUR FROM INTIME) FROM RK 从INTIME字段中提取时
 
SELECT EXTRACT(MINUTE FROM INTIME) FROM RK 从INTIME字段中提取分
 
SELECT EXTRACT(SECOND FROM INTIME) FROM RK 从INTIME字段中提取秒
 
-- 根据生日计算年龄
 
SELECT (CURRENT_DATE - '1990-01-01')/365 age;
 
select (CURRENT_DATE - birthday)/365 age from contact;
 
-- 将时间间隔改成 整数
 
SELECT EXTRACT(epoch FROM my_interval)/3600

7.2. date_trunc 函数

select date_trunc('month',now()) +interval '12 h';
 
//每月1号 12点
 
select date_trunc('month',now()) + interval '15 d 9 h 30 min';
 
//每月15号9点半
 
select date_trunc('day',now()) + interval '9 h';
 
//每天9点
 
select date_trunc('day',now()) + interval '7 d';
 
//每周的今天
 
select date_trunc('weak',now()) + interval '1d 1minute';
 
//每周的周二第一分钟
 
select date_trunc('h',now()) + interval '30 minute';
 
//每小时
 
select date_trunc('minute',now()) + interval '30 second';
 
//每分钟
 
select date_trunc('minute',now()) + interval '30 minute 30 second';
 
//每30分钟
 
select date_trunc('quarter',now()) + interval '15 d 15 h 15 minute 30 second';
 
//本季度的第15天,15小时 15分 30秒
 
select date_trunc('quarter',now() ) - interval '1 h';
 
//每个季度最后一天的晚上11点
 
select date_trunc('quarter',now() + interval '3 month') - interval '1 h';
 
//每个季度的最后一天的晚上的11点(从下个季度开始算起).

7.3. 按照时间分组

--按年分组查看
 
select to_char(time_field, 'YYYY') as d , count(id) as total_count,sum (count_field) as total_amount from table_name
 
where time_field between start_time and end_time group by d
 
--按月分组查看
 
select to_char(time_field, 'YYYY-MM') as d , count(id) as total_count,sum (count_field) as total_amount from table_name
 
where time_field between start_time and end_time group by d
 
--按天分组查看
 
select to_char(time_field, 'YYYY-MM-DD') as d , count(id) as total_count,sum (count_field) as total_amount from table_name
 
where time_field between start_time and end_time group by d
 
--按小时分组查看
 
select to_char(time_field, 'YYYY-MM-DD HH24' ) as d , count(id) as total_count,sum (count_field) as total_amount from table_name
 
where time_field between start_time and end_time group by d order by d
 
--按秒分组查看
 
select to_char(time_field, 'YYYY-MM-DD HH24:MI:SS' ) as d , count(id) as total_countl,sum (count_field) as total_amount from table_name
 
where time_field between start_time and end_time group by d

7.4. age函数获取间隔时间数量

使用 age 函数. 返回2个日期直接的间隔， 生成一个使用年、月的”符号化”的结果

EXTRACT(YEAR from age('2013-04-01', timestamp '2010-01-01')),
 
EXTRACT(MONTH from age('2013-04-01', timestamp '2010-01-01')),
 
EXTRACT(DAY from age('2013-04-01', timestamp '2010-01-01'));

7.5. to_char 函数

https://www.postgresql.org/docs/13/functions-formatting.html

8. SQL

SELECT
    a.attnum,
    a.attname     AS field,
    t.typname     AS type,
    a.attlen      AS LENGTH,
    a.atttypmod   AS lengthvar,
    a.attnotnull  AS notnull,
    b.description AS COMMENT
FROM
    pg_class c,
    pg_attribute a
LEFT OUTER JOIN
    pg_description b
ON
    a.attrelid=b.objoid
AND a.attnum = b.objsubid,
    pg_type t
WHERE
    c.relname = 'account'
AND a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid
ORDER BY
    a.attnum;

8.1. Update URL in some column

-- check the url first.
SELECT 
    file_url,
    regexp_replace(file_url, '(https?://)(file.jinfupawn.com)(/.*)?$', '\1file.primafinance.com.cn\3', 'g') as new_url
FROM attachments;
-- then update all rows.
UPDATE attachments
SET thumbnail_url = regexp_replace(thumbnail_url, '(https?://)(file.jinfupawn.com)(/.*)?$', '\1file.primafinance.com.cn\3', 'g');

8.2. Select the count of records about all table.

select
	relname as TABLE_NAME,
	reltuples as rowCounts
from
	pg_class
where
	relkind = 'r'
	and relnamespace =
      (
	select
		oid
	from
		pg_namespace
	where
		nspname = 'public')
order by
	rowCounts desc;

8.3. Select the constraint of table.

SELECT
    tc.constraint_name, tc.table_name, kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name,
    tc.is_deferrable,tc.initially_deferred
FROM
    information_schema.table_constraints AS tc
        JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
        JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name = 'opportunity';

8.4. Disk space used of all databases.

SELECT
    d.datname AS Name,
    pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE
        WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS SIZE
FROM
    pg_catalog.pg_database d
ORDER BY
    CASE
        WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first
    LIMIT 20;

8.5. Disk space used of all tables.

SELECT
    table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS
    size
FROM
    information_schema.tables
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC

9. Script

9.1. 备份

# 在数据库机器的postgrs用户下，编写如下脚本
vim /var/lib/pgsql/daily_backup.sh
#!/bin/bash
date=`date "+%Y_%m_%d"`
pg_dump -h 172.26.114.87 -p 5432 -U postgres fss > /var/lib/pgsql/backup/fss_"$date".sql
 
# 赋予可执行权限
chmod +x daily_backup.sh
 
# 配置如下定时任务，每天 0 点 1 分备份数据。
1 0 * * * /var/lib/pgsql/daily_backup.sh

9.2. 远程主机拷贝

# 远程主机和数据库主机配置密钥登陆
# 编写如下拷贝脚本
mkdir /root/fss_db_back
vim /root/scp_fss_db.sh
#!/bin/bash
date=`date "+%Y_%m_%d"`
scp [email protected]:/var/lib/pgsql/backup/fss_"$date".sql /root/fss_db_back
 
# 赋予可执行权限
chmod +x scp_fss_db.sh
 
# 配置如下定时任务，每天 0 点 5 分将数据拷贝到远程主机数据。
5 0 * * * /root/scp_fss_db.sh

10. 数据类型

10.1. char varchar text 差异

类型	长度	说明
varchar(n)	变长	有长度限制
char(n)	定长	不足补空白
text	变长	无长度限制

简单来说，varchar的长度可变，而char的长度不可变，对于postgresql数据库来说varchar和char的区别仅仅在于前者是变长，而后者是定长，最大长度都是10485760（1GB）
varchar不指定长度，可以存储最大长度（1GB）的字符串，而char不指定长度，默认则为1，这点需要注意。
text类型：在postgresql数据库里边，text和varchar几乎无性能差别，区别仅在于存储结构的不同
对于char的使用，应该在确定字符串长度的情况下使用，否则应该选择varchar或者text.

官方解读：
SQL定义了两种基本的字符类型：character varying(n) 和character(n)，这里的n 是一个正整数。两种类型都可以存储最多n个字符的字符串（没有字节）。
试图存储更长的字符串到这些类型的字段里会产生一个错误，除非超出长度的字符都是空白，这种情况下该字符串将被截断为最大长度。
这个看上去有点怪异的例外是SQL标准要求的。
如果要存储的字符串比声明的长度短，类型为character的数值将会用空白填满（个别时候校验字符长度的时候在空白字符上会出问题）；
而类型为character varying的数值将只是存储短些的字符串。

如果我们明确地把一个数值转换成character varying(n) 或character(n)，那么超长的数值将被截断成n个字符，且不会抛出错误。这也是SQL标准的要求。

varchar(n)和char(n) 分别是character varying(n) 和character(n)的别名，没有声明长度的character等于character(1)；
如果不带长度说明词使用character varying，那么该类型接受任何长度的字符串。后者是PostgreSQL的扩展。

另外，PostgreSQL提供text类型，它可以存储任何长度的字符串。尽管类型text不是SQL 标准，但是许多其它SQL数据库系统也有它。

character类型的数值物理上都用空白填充到指定的长度n，并且以这种方式存储和显示。
不过，填充的空白是无语意的。在比较两个character 值的时候，填充的空白都不会被关注，在转换成其它字符串类型的时候， character值里面的空白会被删除。
请注意，在character varying和text数值里，结尾的空白是有语意的。并且当使用模式匹配时，如LIKE，使用正则表达式。

一个简短的字符串（最多126个字节）的存储要求是1个字节加上实际的字符串，其中包括空格填充的character。更长的字符串有4个字节的开销，而不是1。长的字符串将会自动被系统压缩，因此在磁盘上的物理需求可能会更少些。更长的数值也会存储在后台表里面，这样它们就不会干扰对短字段值的快速访问。不管怎样，允许存储的最长字符串大概是1GB 。允许在数据类型声明中出现的n 的最大值比这还小。修改这个行为没有什么意义，因为在多字节编码下字符和字节的数目可能差别很大。如果你想存储没有特定上限的长字符串，那么使用text 或没有长度声明的character varying，而不要选择一个任意长度限制。

提示: 这三种类型之间没有性能差别，除了当使用填充空白类型时的增加存储空间，和当存储长度约束的列时一些检查存入时长度的额外的CPU周期。虽然在某些其它的数据库系统里，character(n) 有一定的性能优势，但在PostgreSQL里没有。事实上，character(n)通常是这三个中最慢的，因为额外存储成本。在大多数情况下，应该使用text 或character varying。

11. QA

处理锁表
— 要更新条记录，但是执行语句就会卡住！
UPDATE public.workflow_instance_stage SET execution_type=‘Sequence’ WHERE id in (‘439433’,‘439431’);
— 分开执行第一条顺利执行。
UPDATE workflow_instance_stage SET execution_type=‘Sequence’ WHERE id = 439433;
— 分开执行第二条卡住。
UPDATE workflow_instance_stage SET execution_type=‘Sequence’ WHERE id = 439431;

— 查看 workflow_instance_stage 是否被锁定了。
SELECT * FROM pg_class WHERE relname LIKE ‘workflow_instance_stage’;
SELECT oid FROM pg_class WHERE relname LIKE ‘workflow_instance_stage’;

— 查看产生锁的进程ID，需要带入上面查询出来的oid
SELECT pid FROM pg_locks WHERE relation=‘83547’

— 释放锁定
SELECT pg_cancel_backend(18558)
SELECT pg_terminate_backend(18558);

Maximum size for a database?
在 PostgreSQL 中，对一个数据库的大小，是没有限制的。
Maximum size for a table?
在 PostgreSQL 中，对一个表的限制是最大不能超过 32T。
Maximum size for a row?
在 PostgreSQL 中，对一行记录的限制是最大不能超过400G。
Maximum size for a field?
在 PostgreSQL 中，对一个字段的限制是最大不能超过1G。
Maxumum number of rows in a table?
unlimited，对于一个表可以存多少行记录，没有限制。
Maxumum unmber of columns in a table?
250 - 1600，根据字段类型可以存250 - 1600个。
Maxumum unmber of indexes on a table?
一个表上可以创建多少个索引是没有限制的。

12. Other

PostgreSQL 一主多从(多副本,强同步)简明手册 - 配置、压测、监控、切换、防脑裂、修复、0丢失 - 珍藏级
https://www.cnblogs.com/EikiXu/p/9883596.html
德哥的PostgreSQL私房菜 - 史上最屌PG资料合集
https://blog.csdn.net/linuxpassion/article/details/52338064
PG_HGDB的博客
我们是中国基础软件先行者，其中自主产品HighgoDB依托于国际开源数据库软件PostgreSql数据库进行商业化版本的运作和研发。 同时作为中国开源软件推进联盟PostgreSQL分会的主办单位，在…
https://blog.csdn.net/pg_hgdb/category_7263715.html
PgSQL · 性能优化 · 如何潇洒的处理每天上百TB的数据增量
http://mysql.taobao.org/monthly/2016/04/05/
官方文档：https://www.postgresql.org/docs/current/