PostgreSQL数据库综述
学习PostgreSQL
关于PostgreSQL的设计、构建和性能调优的笔记。
安装和初始设置
1. 架设 yum 仓库并安装 PostgreSQL。
yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum -y install postgresql11-server
##パッケージをインストールすると下記postgresユーザやpostgresグループが作成される。
[root@postgredb ~]# id postgres
uid=26(postgres) gid=26(postgres) groups=26(postgres)
构建数据库集群
#データベースクラスタをデフォルトの/var/lib/pgsql/11/dataから変更する
cp -p /usr/lib/systemd/system/postgresql-11.service /etc/systemd/system/
sed -i 's:/var/lib/pgsql/11/data:/postgres/data:g' /etc/systemd/system/postgresql-11.service
systemctl daemon-reload
sed -i 's:/var/lib/pgsql/11/data:/postgres/data:g' /var/lib/pgsql/.bash_profile
echo 'export PATH=/usr/pgsql-11/bin:${PATH}' >> /var/lib/pgsql/.bash_profile
#初期セットアップ(=データベースクラスタを所定の場所に作る)
PGSETUP_INITDB_OPTIONS="-E UTF8 --locale=C" /usr/pgsql-11/bin/postgresql-11-setup initdb
systemctl enable postgresql-11.service --now
3. 使用默认用户进行通讯确认并重新设置密码
su - postgres
-bash-4.2$ psql -U postgres
psql (11.4)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=# \q
psql -c "alter user postgres with password 'P#ssw0rd'"
创建用户和数据库
# DBユーザー [cent] を登録
createuser cent
# テストデータベース作成 (オーナーは上記ユーザー)
createdb testdb -O cent
#データベース接続
psql testdb -U cent
参数调整（postgresql.conf）
在Oracle中，有一个称为“初始化参数”的概念，而在Postgre中也存在类似的设置参数。
基本上，这些参数是在postgresql.conf文件或包含在postgresql.conf文件中的文件中进行持久管理的。
select name,setting,unit,context from pg_settings;
还可以通过参数将设置和应用的时机分开管理。
*只需要一种选项，请将以下内容以中文本地语言进行释义：
※在PostgreSQL中，SIGHUP信号或重新启动命令（而不是systemd命令）的用法。
（叹气）
pg_ctl reload -D PD_DATA目录名
（重新启动）
pg_ctl restart -D PD_DATA目录名
根据参数的不同，可能有各种限制，如允许动态更改，或需要重启才能进行更改等。以下是用SQL判断这些限制的方法。
#1.動的変更可能なパラメータ(SET文で同一セッション内での変更も可)
select name,setting,unit,context from pg_settings where context in ('user','superuser');
#2.SIGHUPシグナル受信時に反映
select name,setting,unit,context from pg_settings where context='sighup';
#3.Posgre再起動時のみ反映
select name,setting,unit,context from pg_settings where context='postmaster';
此外，在即时反映之外，还可以通过在psql提示符上使用ALTER SYSTEM语句进行设置更改，但这些更改将在下次重新启动时才生效。执行ALTER SYSTEM语句后，值将被附加到postgresql.auto.conf文件中，并覆盖并反映在postgresql.conf文件中。使用此方法可以在执行ALTER SYSTEM语句时检查设置错误，并且由能够远程登录的超级用户可以从远程修改设置，这是一个优点。
#パラメータの動的変更(superuserでないと実施不可能)
postgres=# alter system set shared_buffers="1024MB";
ALTER SYSTEM
-bash-4.2$ cat /postgres/data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
shared_buffers = '1024MB'
#パラメータの詳細情報閲覧
select name,setting,unit from pg_settings;
对于各个参数值的审查和采用政策（postgresql.conf）
监听地址
绑定端口的地址。
如果允许远程连接或者允许访问，则填写相应的IP地址。
例如）listen_addresses=”
端口
等待接受连接的端口号
例）端口=5432
最大连接数(max_connections)是一个设置项，用于限制可以与客户端建立的最大连接数量。该值不包括PostgreSQL自身的后台进程。例如，设置max_connection为300。
shared_buffers是用来确定分配给PostgreSQL数据缓存的内存量的设置参数。
如果系统有超过1GB的RAM，shared_buffers的理想初始值是系统内存的1/4作为推荐值。
如果SHMMAX比这个值小，将在PostgreSQL启动时出现错误。
示例：shared_buffers=’1024MB’
有效缓存大小，指的是用于PostgreSQL的缓冲区，包括内核和PostgreSQL共享缓冲区以及可用作磁盘缓存的估计大小。该值由优化器使用，并作为估计使用索引的成本的因素。如果将值设置得更高，将更多地使用索引扫描，而如果将其设置得较小，则更多地使用顺序扫描。如果希望更多地使用索引扫描，则推荐将此值设置为OS内存的50%以上，75%以下。然而，该设置仅作为优化器判断执行计划的参考位置，并不会特别占用内存。
工作内部有序整理和哈希表操作所使用的内存大小。
在执行需要排序、表连接和聚合等复杂查询时使用。
每个操作使用值的数量决定，如果在一次查询中执行多个相关操作，将使用此值乘以倍数的内存。
基于基本的思路，如果将其视为专用于数据库的服务器，则可以认为 (操作系统内存-shared_buffers)/max_connection=work_mem 是合理的。
内存占用发生在执行处理时，因此不会在 PostgreSQL 启动期间一直存在。
例如) work_mem=’5MB’
指定一个用于维护型操作（如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY）的最大内存量。默认值较低，建议增加。根据经验，大约256MB是一个合适的参考值。例如，将maintenance_work_mem设为’256MB’。
wal_buffers是用于尚未写入磁盘的WAL数据的共享内存容量。默认情况下，它会自动调整为shared_buffers的1/32大小，因此如果不超过几兆字节，可以使用默认值。例如，wal_buffers = 16MB。
同步提交
用于指定事务在向客户端返回”success”之前，是否等待WAL记录被写入磁盘。默认且安全的设置是开启。
关于off的情况，在向客户端报告成功的时刻和事务确实变得安全以对抗服务器崩溃之间可能会发生延迟。
尽管存在数据库崩溃可能导致最近提交的某些事务部分丢失的风险，但如果性能比数据一致性更为重要，那么可以将其设为off以加速性能。此外，还可以在会话级别进行指定，仅适用于更重要的性能要求会话。
例如，synchronous_commit=’on’。
随机页面成本
表示在将顺序访问时间设定为基准值1.0时，随机访问所花费的时间比率。
随机访问包括索引扫描等。如果使用像AWS的EBS之类的高速存储介质（如SSD），由于随机访问也足够快，将该值设为1会增加选择索引扫描的可能性，从而提高性能。
示例）random_page_cost = 1
有效io并发是指假设PostgreSQL可以同时执行的并发磁盘输入/输出操作的数量。
通常情况下，执行一次从磁盘读取一个块（每次IO = 1）的操作，但当设置大于1时，可以预读数据并同时获取多个块。
一般应该指定磁盘驱动器的数量。例如，如果将两个EBS组合进行条带化，则设置为2。
示例）random_page_cost = 2
请参考以下链接来优化您的PostgreSQL服务器：https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
数据库管理
以下是创建和删除数据库的基本命令。在Oracle中，实例（在RAC中是数据库）相当于Postgresql中的数据库。LC_COLLATE和LC_CTYPE在创建后无法更改。在AWS的RDS上，由于默认创建的是en_US.UTF-8的LC_COLLATE和LC_CTYPE，如果预期不同，则需要稍后重新创建。
#データベース作成
CREATE DATABASE db01
OWNER = app
TEMPLATE = template0
ENCODING = 'UTF8'
LC_COLLATE = 'C'
LC_CTYPE = 'C';
#データベース削除
DROP DATABASE db01
#データベース覧
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | pguser01 | UTF8 | C | C |
(4 rows)
管理用户、角色和架构。
不仅限于PostgreSQL，一般的数据库管理系统（DBMS）中存在以下概念，进行详细解释。
用户是指登录数据库并发出任何查询的主体。此外，它不属于特定的数据库，而是作为整个数据库集群来定义。
角色是将权限进行整合的概念。通过给用户分配角色，用户可以行使与角色相关的权限。（也可以直接给用户分配权限而不是分配给角色）同时，它不属于特定的数据库，而是定义在整个数据库集群中。
架构
一个表示对象所属的概念。通过架构名称.表名称的形式指示特定的对象。在同一个数据库中，属于同一架构名称.对象名称的对象是不存在的。它同时属于特定的数据库。
注意するべきPostgreSQLの点は、ユーザとロールがほぼ同じと間違われやすいことです。Oracleでははっきりと分けられており、ロールによるデータベースへのログインはできませんが、PostgreSQLではロールにLOGIN権限があれば、ロールでログインすることが可能です。そのため、最初は私もロールとユーザが同じものかと考えましたが、明確には以下のように考える方が良さそうです。
在个人看来，我希望明确地区分角色和用户，所以不会给角色赋予LOGIN权限，而是将权限通过角色进行汇总，然后再分配给用户，这是我想要的设计。
#Postgresではロール=ユーザの概念?
--ユーザ作成(ROLEと出てくるのでミスリードしやすい)
testdb=> create user pguser01 with login password 'P#ssw0rd';
CREATE ROLE
--ロール作成
CREATE ROLE pguser01;
--ロール作成(パスワードも付与するとロールでログイン化の仕組み)
CREATE ROLE pguser01 with login password 'P#ssw0rd';
--ユーザへ権限付与
ALTER USER pguser01 SUPERUSER;
--ロールへ権限付与
ALTER ROLE pguser01 SUPERUSER;
--ユーザ削除
testdb=> drop user pguser01;
DROP ROLE
--ロール削除
drop role pguser01;
--現在のログインユーザ(ロール)確認
select current_user;
--ユーザ一覧(ロールも一緒に各レコードとして出てくるので混在)
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
pguser01 | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
※系统权限清单（这些权限不能通过角色间接授予）
--スキーマ作成
postgres=# create schema XXX;
CREATE SCHEMA
--!!!スキーマを作成するにはコマンドを実行するロールがスーパーユーザーか、スキーマを作成するデータベースで CREATE 権限を持っている必要がある。
#スキーマ削除
postgres=# drop schema XXX;
DROP SCHEMA
#現在のスキーマ確認
select current_schema;
#カレントスキーマ変更
SET search_path = XXX;
#スキーマ一覧
postgres=# \dn
List of schemas
Name | Owner
-------+----------
xwork | postgres
(1 row)
权限管理
以下是基本的权限相关指令。包括授权、删除和确认。
总的来说，有分为授予角色和授予对象权限两种情况。
此外，角色还可以继承。角色的继承是指继承源角色具有的权限直接传递给继承目标角色。
通过这种继承赋予的权限在上述权限确认场景中不会显示。
--pguser01ロールの権限をpguser03ロールへと付与
grant pguser01 to pguser03;
--特定オブジェクトへの権限付与
--test01テーブルに対するselect,insert権限をpguser03ロールに付与
grant select,insert on test01 to pguser03;
--特定オブジェクトからの権限削除
###test01テーブルに対するdelete権限をpguser03ロールから削除
revoke delete on test01 from pguser03;
---権限確認
###test01テーブルに対するselect,insert権限がpguser0３ロールに付与されている
¥z
アクセス権
スキーマ | 名前 | 型 | アクセス権 | 列のアクセス権限
----------+----------+----------+---------------------------+------------------
public | test1 | テーブル | pguser01=arwdDxt/pguser01+|
| | | pguser03=ar/pguser01 |
--特定オブジェクトへの全権限付与
###test01テーブルに対する全権限をpguser03ロールに付与
grant all on test01 to pguser03;
权限的观点 de
此外，我认为有时候我们也可能希望无条件地操作特定模式或数据库的所有对象。在那种情况下，请执行以下步骤：
https://www.postgresql.jp/document/10/html/sql-grant.html
--xxxworkの全テーブルへの権限付与
GRANT ALL ON ALL TABLES IN SCHEMA xxxwork TO user02;
--xxxworkの全テーブルからの権限剥奪
REVOKE ALL ON ALL TABLES IN SCHEMA xxxwork TO user02;
--xxxworkスキーマへの全権限付与
GRANT ALL ON SCHEMA xxxwork TO user02;
--xxxworkスキーマからの全権限剥奪
REVOKE ALL ON SCHEMA xxxwork FROM user02;
--db01データベースへの全権限付与
GRANT ALL ON DATABSE db01 TO user02;
--db01データベースからの全権限剥奪
REVOKE ALL ON DATABSE db01 FROM user02;
日志管理
在PostgreSQL中，可以输出记录错误、警告等问题的服务器日志。通常可以选择syslog输出格式和单独的文本文件两种方式，但考虑到单独的文本文件不会与操作系统的信息混在一起，因此认为这种方式更合适。
服务器日志设计
用于基本服务器日志输出的参数候选。在postgresql.conf中设置。
日志目标、日志收集器
log_destination是决定将日志输出到哪里的参数，可以设置为syslog、stderr（标准错误日志）或csvlog。如果设置为syslog，则会输出到操作系统的syslog中。如果设置为stderr或csvlog，并且同时将log_collector设置为on，则会将日志输出到文本文件或csv中。
log_destination=’stderr’
log_collector=’on’
当将og_destination设置为’stderr’或’csv’并启用logging_collector以进行PostgreSQL独立记录时，可以通过设置来确定将日志文件输出到哪个目录以及以何种名称。
log_directory=’/var/log/pgsql’
log_filename=’postgresql-%Y-%m-%d-%H-%M-%S.log’
log_line_prefix = ‘[%t][%p][%u][%d] ‘是一个用于指定输出日志消息前缀的选项。默认情况下，只输出消息本身，因此建议设置为%t（时间）、%u（用户名）、%d（数据库名）和%p（进程ID）来获得更好的结果。
日志轮转年龄，日志轮转大小是指定日志轮转的时间和大小的参数。log_rotation_age是以分钟为单位指定的时间参数，log_rotation_size是以千字节为单位指定的大小参数，可以根据大小来切换日志。log_rotation_age的切换时间是以PostgreSQL启动的日期的0时0分0秒作为基准进行计算的。
log_rotation_age=1天
log_rotation_size=102400 千字节
log_min_error_statement控制是否将导致错误条件的SQL语句记录在服务器日志中。所有导致设置的错误级别或更高级别的错误的SQL语句都会被记录在日志中。是否将其设置为’error’会更合适。
将log_hostname设置为off表示通常情况下不会记录连接到数据库的原始访问来源IP地址，但连接主机的主机名仍会通过反向解析显示在日志中。然而，由于反向解析IP地址可能成为瓶颈，因此将其设置为off是合适的。
设计长时间化处理检测以及其他处理记录在日志中。
log_autovacuum_min_duration参数用于设置autovacuum活动的最小执行时间（单位为毫秒），当达到该时间时，会将每个autovacuum活动的日志记录下来。这对于追踪autovacuum活动非常有用。例如，设置为5000表示只记录执行时间超过5秒的活动。
log_min_duration_statement是用于指定以毫秒为单位输出慢查询的阈值。
如果设置为-1，则不输出。
例如：检测超过1分钟的查询。
log_min_duration_statement=60000
log_checkpoints
将检查点和重启点记录到服务器日志中。此日志消息包含一些统计信息，如写入的缓冲区数和所需的写入时间。
log_checkpoints = 打开
审计日志设计
在当前情况下，使用数据库来获取审计日志已经成为安全要求的常见做法。我想整理一下审计日志获取的方法。
登录审计
log_connections
启用此选项可以将客户端认证成功或连接服务器的尝试记录在日志中。
log_connections=on
启用log_disconnections可以将客户端连接的断开记录在日志中。log_disconnections=on。
SQL审计
log_statement_all
指定要输出到日志的SQL语句。
如果指定为”none”，则无输出。
如果指定为”ddl”，则输出包括CREATE、ALTER、DROP等DDL操作的语句。
如果指定为”mod”，则在DDL输出的基础上还输出包括INSERT、UPDATE、DELETE等DML操作的语句。
如果指定为”all”，则输出所有SQL语句。
※但是，如果明显存在语法错误的SQL语句被执行，则不会输出到日志。
如果要输出这样的错误信息，请通过定义log_min_error_statement为”error”来输出信息。
在上述内容中，可以控制PostgreSQL的SQL审计日志，可以分别在系统级、数据库级和用户级进行控制。
1)システム単位
log_statement_allをpostgresql.confに設定
2)データベース単位
#追加
ALTER DATABASE <DB名> SET log_statement = '<none|ddl|mod|all>';
#削除
ALTER DATABASE <DB名> RESET log_statement
#確認
SELECT * FROM pg_db_role_setting;
3)ユーザ単位
#追加
ALTER ROLE <ユーザ名> SET log_statement = '<none|ddl|mod|all>';
#削除
ALTER ROLE <ユーザ名> RESET log_statement ;
#確認
SELECT * FROM pg_db_role_setting;
補充：「pg_db_role_setting」是存放数据库和角色的参数设置信息的系统目录。
将CSV数据输入的方法
虽然可以简单地执行插入操作，但是 PostgreSQL 有一个专门用于大量数据导入的命令 COPY FROM。COPY 命令可以从外部读取制表符分隔(.tsv)或逗号分隔(.csv)的文件，并且比 INSERT 更快。
*在初始加载时，通常在创建索引之前加载而不是创建索引，后续再执行 CREATE INDEX 的速度更快。
1. 创建表 (仅包括表定义和 CHECK 约束)
2. 使用 COPY FROM 进行数据加载
3. 添加索引、包含索引的约束和外键约束
COPY <tbl名> FROM 'CSVファイルパス' with DELIMITER ',' csv header;
※delimiter '区切り文字' 区切り文字を表現
※csv header ヘッダーがあることを表現
###RDSでは管理者権限(superuser)が使えないため通常のcopyは利用できない。下記で代替。
\copy source-table from 'test.csv' with DELIMITER ',' csv header;
✔︎大规模生成测试数据的技巧
作为备忘录，记录在PostgreSQL中创建大量测试数据的方法。因为经常需要数据来进行验证。
- generate_series関数を利用
---10000000件のテストデータ作成。凡そ600M程度の大きさのテーブルになる
create table tbl1 (col1 integer,col2 char(20));
insert into tbl1 select generate_series(1,10000000),'XXXXXXXXXXXXXXXXXXXX';
让CPU保持高效工作的SQL技巧
为了在测试等场景下将CPU利用率保持在最高水平，使用PostgreSQL的SQL语句
-
- generate_series関数を利用
- 交差結合を利用
由於在同一個表中將每一行與該表的所有行進行結合，所以行數越多，結合處理就會變得相當複雜。結合後的行數，就簡單地變為原始表格的平方。如果要使用多個核心進行加速，只需再執行類似的SQL語句即可。
--10000000件のテストデータを自テーブルと交差結合させる。
create table tbl1 (col1 integer,col2 char(20));
insert into tbl1 select generate_series(1,10000000),'XXXXXXXXXXXXXXXXXXXX';
select count(*) from tbl1 x,tbl1 y;
调查容量
既然在运营数据库，对于表的大小和记录数，以及数据库本身的大小进行统计是经常需要的。关于 PostgreSQL，以下是一种调查方法。
--データブロックのサイズを確認
show block_size;
--データベースオブジェクトのサイズ降順
SELECT relowner,relname,reltuples as rows,(relpages::int8 * 8192) as bytes FROM pg_class order by bytes desc ;
--各テーブルのサイズ(byte)と件数を出力。上記結果をrelpagesの乗数として入れる。
SELECT relowner,relname,reltuples as rows,(relpages::int8 * 8192) as bytes FROM pg_class WHERE relname = 'テーブル名'
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;
请参考以下链接来了解更多信息：
https://www.postgresql.jp/document/10/html/catalog-pg-class.html
序列控制
在PostgreSQL中使用简单顺序号的方法。例如，可以用于对表的ID列进行编号。
与MySQL不同，没有auto_increment这样的列定义语法。
/* PostgreSQLの場合 */
DROP SEQUENCE t_id_seq;
CREATE SEQUENCE t_id_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
DROP TABLE product;
CREATE TABLE product (
id int DEFAULT nextval('t_id_seq') PRIMARY KEY NOT NULL,
name text not null,
price int not null
);
##セッション内での直近のシーケンス値を確認
select currval('t_id_seq');
##次に発行されるシーケンス値を確認(実際のシーケンス値も更新される)
select nextval('t_id_seq');
##シーケンス値をリセット(現在のテーブルのID列のMAX値にcurrvalをセットする
SELECT setval('t_id_seq', (SELECT MAX(id) FROM product));
SSL连接
为了加密与Postgresql的连接，必须进行设置。
如果没有加密，与数据库进行通信的数据将以明文形式传输，因此建议进行安全措施。仅仅是网络通信的加密。
-
- まずはSSL証明書をサーバ側(DB側)で作成することが必要
SSL秘密鍵、CRS、証明書を作成
SSL秘密鍵のパスワードは削除。これを実施しないと、起動時に毎回パスワードを聞かれる
#サーバ秘密鍵の作成
-RSA形式、256bitのAES形式、かつ2048bitの鍵長で作成
openssl genrsa -aes256 2048 > server.key
#CSRの作成
req CSRファイル作成
-new 新規にCSR作成
-key 秘密鍵ファイル指定
openssl req -new -key server.key > server.csr
#サーバ証明書作成
x509 X.509形式証明書
-in CSRファイル CSRファイル指定
-days 証明書有効期限
-req 入力ファイルがCSRであること明示
-signkey秘密鍵 秘密鍵ファイル指定
openssl x509 -in server.csr -days 3650 -req -signkey server.key > server.crt
Tips:サーバ秘密鍵からパスフレーズ削除
cp -p server.key server.key_org
openssl rsa -in server.key_org > server.key
接下来，将生成的私钥和证书注册到conf中。文件的路径可以是相对于数据目录的相对路径。
ssl = ‘on’
ssl_cert_file = ‘ssl/server.crt’
ssl_key_file = ‘ssl/server.key’
ssl_ciphers = ‘ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384’
SSL开启
SSL证书文件 = ‘ssl/server.crt’
SSL密钥文件 = ‘ssl/server.key’
SSL密码套件 = ‘ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384’
请问SSL加密协议应该根据这个推荐进行设置吗？
https://wiki.mozilla.org/Security/Server_Side_TLS#Recommended_configurations
PostgreSQL的psql默认使用SSL。如果服务器不支持SSL，则会使用非SSL连接。如果需要强制禁用SSL，则需将PGSSLMODE=disable设置为环境变量。
※使用SSL连接时，将会显示与SSL连接相关的信息如下。
[root@vm1 ~]# psql -U pguser01 -h db1 db01
使用pguser01用户通过TLSv1.2协议和ECDHE-RSA-AES256-GCM-SHA384加密方式连接到db1服务器。
数据库db01已成功连接。
请输入命令或输入”help”获取帮助。
db01=>
事务隔离级别
直白地说，事务隔离级别是指在保持关系数据库管理系统（RDBMS）可靠性（ACID）的特性中，容许多少程度的数据不一致问题。
隔离性是指在事务过程中的操作不会对其他事务产生影响的特性。换句话说，每个事务必须在分离的状态下进行操作。
如果不遵守这一点，可能会发生以下类似的数据不一致情况。正如开头所述，定义了在事务隔离级别中容许多少这种问题的程度。
-ダーティリード (Dirty Read)
トランザクションBでコミットされていないデータをトランザクションAで読み取ってしまう問題が発生する。
- 反復不能読み取り/ファジーリード/ノンリピータブルリード (Fuzzy Read / Non-Repeatable Read)
トランザクションAでデータを複数回読み取っている途中で、トランザクションBでデータを更新(update)してコミットした場合、トランザクションAでトランザクション内の前回と違う結果のデータを読み取ってしまう問題が発生
- ファントムリード (Phantom Read)
トランザクションAで一定の範囲のレコードに対して処理を行っている途中で、トランザクションBでデータを追加(insert)・削除(delete)してコミットした場合、トランザクションAで幻影のようにデータが反映されるため、処理の結果が変わってしまう問題が発生。
尽管一开始很难区分不可重复读与幻读的区别，但前者主要关注同一事务内记录值的不一致性问题，而后者除此之外还关注记录数量的一致性问题。
在Postgresql中，可以根據應用程式的需求來選擇以下分離層級來應對上述問題。分離層級越低，可能會出現處理序列失敗的情況，因此需要考慮失敗事務的重新執行等恢復措施。大多數情況下，使用Read Commited可以滿足應用程式的需求。此外，還可以通過在每個事務中使用BEGIN TRANSACTION ISOLATION LEVEL子句來指定分離層級，這樣就可以在存在數據庫默認的情況下，為特定的事務進行更改以適應需求。
✔️备份
关于在PostgreSQL中实现数据备份的方法。
作为工具，可以使用pg_dump和pg_dumpall。
最大で、データベース単位でバックアップするユーティリティ。
データベースを使用中であっても一貫性のあるバックアップを作成することができる。 pg_dumpは他のユーザによるデータベースへのアクセス（読み書き）をブロックしません。バックアップの範囲は細かくオプションで指定が可能。
https://www.postgresql.jp/document/10/html/app-pgdump.html
https://www.postgresql.jp/document/11/html/app-pgrestore.html
1) 纯文本格式
创建使用纯文本的SQL脚本。这是默认操作。恢复操作可以使用psql命令执行。
例如，可以执行如下示例。
pg_dump -v --format=plain -h kai1 -U pguser01 testdb mydb > mydb_dump.sql
2) 自定义存档格式（推荐）
创建二进制格式的备份。
要恢复使用该格式创建的备份，需要使用 pg_restore 命令。
优点是可以压缩，占用空间较少。此外，可以仅分离所需的表和模式定义进行恢复。
例如，可按以下方式执行。
#バックアップ
pg_dump -v --format=custom -h kai1 -U pguser01 testdb mydb > mydb_dump.custom
#リストア(フルリストア)
pg_restore -C -d postgres < mydb_dump.tar
创建可以在pg_restore中进行恢复的备份文件，类似于自定义存档文件格式。
优点是在查看tar文件内容时可以了解操作系统中存储的表结构等信息。
要恢复使用此格式创建的备份，需要使用pg_restore命令。
例如，可以执行以下命令作为示例。
#バックアップ
pg_dump --format=tar -h kai1 -U pguser01 testdb mydb > mydb_dump.tar
#リストア(フルリストア)
pg_restore -C -d postgres < mydb_dump.tar
✔️锁住 (Suǒ zhù)
表级锁
PostgreSQL可以为表获取明确的锁，以确保事务的并发执行。
行级锁
行级锁不会对数据查询产生影响。
行级锁仅会阻塞同一行的写入和锁定操作。比如，在执行UPDATE语句时，更新位于同一行的数据会导致等待锁的情况发生。
✔︎按照顺序比对
在PostgreSQL中，通过LC_COLLATE来控制排序顺序。
我们创建了三种不同的数据库，并验证了排序顺序的影响。
---C
CREATE DATABASE "pgtestC" WITH OWNER = "pguser01" ENCODING = 'UTF8'
LC_COLLATE = 'C' LC_CTYPE = 'C' TEMPLATE = template0 CONNECTION LIMIT = -1;
---en_US
CREATE DATABASE "pgtestUS" WITH OWNER = "pguser01" ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TEMPLATE = template0 CONNECTION LIMIT = -1;
---ja_JP
CREATE DATABASE "pgtestJP" WITH OWNER = "pguser01" ENCODING = 'UTF8'
LC_COLLATE = 'ja_JP.UTF8' LC_CTYPE = 'ja_JP.UTF-8' TEMPLATE = template0 CONNECTION LIMIT = -1;
testdb=# select * from moji;
col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 | col10
------+--------+------------+------------+------+---------------+------------------------+----------------------+----------+------------
1 | 森島 | モリシマ | 1965-05-03 | 54 | 森島 結依 | rKtddmA4d@test.net | http://example.co.jp | 滋賀県 | あいうえお
2 | 江頭 | エトウ | 1987-04-09 | 32 | 江頭 信男 | X7gXd@sample.com | http://sample.com | 福岡県 | かきくけこ
3 | 豊永 | トヨナガ | 1988-02-19 | 31 | 豊永 盛夫 | WsG5TfdE@example.jp | http://example.net | 富山県 | さしすせそ
4 | 山中 | ヤマナカ | 1989-03-20 | 30 | 山中 瑞貴 | MT01FvOa@example.net | http://test.net | 広島県 | たちつてと
5 | 熊谷 | クマタニ | 1972-01-12 | 47 | 熊谷 勝美 | LhUlXDUhCV@example.jp | http://sample.net | 福井県 | なにぬねの
6 | 春名 | ハルナ | 1988-10-08 | 30 | 春名 金治 | etKwM@sample.org | http://test.net | 長崎県 | はひふへほ
7 | 小河 | オガワ | 1954-06-14 | 65 | 小河 勝利 | jzSxIcn0Hq@test.org | http://example.com | 山口県 | まみむめも
8 | 小松崎 | コマツザキ | 1997-09-28 | 21 | 小松崎 日菜子 | hpbo4P@test.net | http://sample.co.jp | 鳥取県 | やゆよ
9 | 米原 | コメハラ | 1995-12-29 | 23 | 米原 将文 | VNKuCm@sample.co.jp | http://example.com | 神奈川県 | らりるれろ
10 | 一瀬 | カズセ | 2006-11-11 | 12 | 一瀬 葉奈 | NMpwQIu_@example.co.jp | http://sample.com | 岐阜県 | わをん
(10 rows)
在此状态下，尝试在pgtestC、pgtestJP和pgtestUS三个数据库上分别执行相同的SQL语句（select * from moji order by colX），并比较它们的输出结果。结果显示，对于col1、col4、col5和col8，三个数据库的排序顺序是相同的，但在以下列上存在差异。
只有ja JP的DB的排序顺序与其他不同。C和en_US相同。
→ 未知？是否按照字符编码顺序排序？
仅以一种中文选项进行释义：
en_US的排序顺序在数据库中与其他排序顺序有所不同。而ja_JP和C是相同的。
→ja_JP/C按照日语的あいうえお（アイウエオ）顺序进行排序。en_US则按照字节数较少的顺序进行排序。
[root@base ~]# psql -h kai1 -U pguser01 -c 'select * from moji order by 3' pgtestJP
col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 | col10
------+--------+------------+------------+------+---------------+------------------------+----------------------+----------+------------
2 | 江頭 | エトウ | 1987-04-09 | 32 | 江頭 信男 | X7gXd@sample.com | http://sample.com | 福岡県 | かきくけこ
7 | 小河 | オガワ | 1954-06-14 | 65 | 小河 勝利 | jzSxIcn0Hq@test.org | http://example.com | 山口県 | まみむめも
10 | 一瀬 | カズセ | 2006-11-11 | 12 | 一瀬 葉奈 | NMpwQIu_@example.co.jp | http://sample.com | 岐阜県 | わをん
5 | 熊谷 | クマタニ | 1972-01-12 | 47 | 熊谷 勝美 | LhUlXDUhCV@example.jp | http://sample.net | 福井県 | なにぬねの
8 | 小松崎 | コマツザキ | 1997-09-28 | 21 | 小松崎 日菜子 | hpbo4P@test.net | http://sample.co.jp | 鳥取県 | やゆよ
9 | 米原 | コメハラ | 1995-12-29 | 23 | 米原 将文 | VNKuCm@sample.co.jp | http://example.com | 神奈川県 | らりるれろ
3 | 豊永 | トヨナガ | 1988-02-19 | 31 | 豊永 盛夫 | WsG5TfdE@example.jp | http://example.net | 富山県 | さしすせそ
6 | 春名 | ハルナ | 1988-10-08 | 30 | 春名 金治 | etKwM@sample.org | http://test.net | 長崎県 | はひふへほ
1 | 森島 | モリシマ | 1965-05-03 | 54 | 森島 結依 | rKtddmA4d@test.net | http://example.co.jp | 滋賀県 | あいうえお
4 | 山中 | ヤマナカ | 1989-03-20 | 30 | 山中 瑞貴 | MT01FvOa@example.net | http://test.net | 広島県 | たちつてと
(10 rows)
[root@base ~]# psql -h kai1 -U pguser01 -c 'select * from moji order by 3' pgtestUS
col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 | col10
------+--------+------------+------------+------+---------------+------------------------+----------------------+----------+------------
2 | 江頭 | エトウ | 1987-04-09 | 32 | 江頭 信男 | X7gXd@sample.com | http://sample.com | 福岡県 | かきくけこ
7 | 小河 | オガワ | 1954-06-14 | 65 | 小河 勝利 | jzSxIcn0Hq@test.org | http://example.com | 山口県 | まみむめも
10 | 一瀬 | カズセ | 2006-11-11 | 12 | 一瀬 葉奈 | NMpwQIu_@example.co.jp | http://sample.com | 岐阜県 | わをん
6 | 春名 | ハルナ | 1988-10-08 | 30 | 春名 金治 | etKwM@sample.org | http://test.net | 長崎県 | はひふへほ
5 | 熊谷 | クマタニ | 1972-01-12 | 47 | 熊谷 勝美 | LhUlXDUhCV@example.jp | http://sample.net | 福井県 | なにぬねの
9 | 米原 | コメハラ | 1995-12-29 | 23 | 米原 将文 | VNKuCm@sample.co.jp | http://example.com | 神奈川県 | らりるれろ
3 | 豊永 | トヨナガ | 1988-02-19 | 31 | 豊永 盛夫 | WsG5TfdE@example.jp | http://example.net | 富山県 | さしすせそ
1 | 森島 | モリシマ | 1965-05-03 | 54 | 森島 結依 | rKtddmA4d@test.net | http://example.co.jp | 滋賀県 | あいうえお
4 | 山中 | ヤマナカ | 1989-03-20 | 30 | 山中 瑞貴 | MT01FvOa@example.net | http://test.net | 広島県 | たちつてと
8 | 小松崎 | コマツザキ | 1997-09-28 | 21 | 小松崎 日菜子 | hpbo4P@test.net | http://sample.co.jp | 鳥取県 | やゆよ
仅需要一个选项，下面是对该句子的中文修改:
en_US的照合顺序的数据库的排序顺序有所不同。C和ja_JP相同。
→ja_JP/C的排序顺序是大写字母→小写字母顺序。en_US的排序顺序是小写字母→大写字母顺序。
[root@base ~]# psql -h kai1 -U pguser01 -c 'select * from moji order by 7' pgtestJP
col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 | col10
------+--------+------------+------------+------+---------------+------------------------+----------------------+----------+------------
5 | 熊谷 | クマタニ | 1972-01-12 | 47 | 熊谷 勝美 | LhUlXDUhCV@example.jp | http://sample.net | 福井県 | なにぬねの
4 | 山中 | ヤマナカ | 1989-03-20 | 30 | 山中 瑞貴 | MT01FvOa@example.net | http://test.net | 広島県 | たちつてと
10 | 一瀬 | カズセ | 2006-11-11 | 12 | 一瀬 葉奈 | NMpwQIu_@example.co.jp | http://sample.com | 岐阜県 | わをん
9 | 米原 | コメハラ | 1995-12-29 | 23 | 米原 将文 | VNKuCm@sample.co.jp | http://example.com | 神奈川県 | らりるれろ
3 | 豊永 | トヨナガ | 1988-02-19 | 31 | 豊永 盛夫 | WsG5TfdE@example.jp | http://example.net | 富山県 | さしすせそ
2 | 江頭 | エトウ | 1987-04-09 | 32 | 江頭 信男 | X7gXd@sample.com | http://sample.com | 福岡県 | かきくけこ
6 | 春名 | ハルナ | 1988-10-08 | 30 | 春名 金治 | etKwM@sample.org | http://test.net | 長崎県 | はひふへほ
8 | 小松崎 | コマツザキ | 1997-09-28 | 21 | 小松崎 日菜子 | hpbo4P@test.net | http://sample.co.jp | 鳥取県 | やゆよ
7 | 小河 | オガワ | 1954-06-14 | 65 | 小河 勝利 | jzSxIcn0Hq@test.org | http://example.com | 山口県 | まみむめも
1 | 森島 | モリシマ | 1965-05-03 | 54 | 森島 結依 | rKtddmA4d@test.net | http://example.co.jp | 滋賀県 | あいうえお
(10 rows)
[root@base ~]# psql -h kai1 -U pguser01 -c 'select * from moji order by 7' pgtestUS
col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 | col10
------+--------+------------+------------+------+---------------+------------------------+----------------------+----------+------------
6 | 春名 | ハルナ | 1988-10-08 | 30 | 春名 金治 | etKwM@sample.org | http://test.net | 長崎県 | はひふへほ
8 | 小松崎 | コマツザキ | 1997-09-28 | 21 | 小松崎 日菜子 | hpbo4P@test.net | http://sample.co.jp | 鳥取県 | やゆよ
7 | 小河 | オガワ | 1954-06-14 | 65 | 小河 勝利 | jzSxIcn0Hq@test.org | http://example.com | 山口県 | まみむめも
5 | 熊谷 | クマタニ | 1972-01-12 | 47 | 熊谷 勝美 | LhUlXDUhCV@example.jp | http://sample.net | 福井県 | なにぬねの
4 | 山中 | ヤマナカ | 1989-03-20 | 30 | 山中 瑞貴 | MT01FvOa@example.net | http://test.net | 広島県 | たちつてと
10 | 一瀬 | カズセ | 2006-11-11 | 12 | 一瀬 葉奈 | NMpwQIu_@example.co.jp | http://sample.com | 岐阜県 | わをん
1 | 森島 | モリシマ | 1965-05-03 | 54 | 森島 結依 | rKtddmA4d@test.net | http://example.co.jp | 滋賀県 | あいうえお
9 | 米原 | コメハラ | 1995-12-29 | 23 | 米原 将文 | VNKuCm@sample.co.jp | http://example.com | 神奈川県 | らりるれろ
3 | 豊永 | トヨナガ | 1988-02-19 | 31 | 豊永 盛夫 | WsG5TfdE@example.jp | http://example.net | 富山県 | さしすせそ
2 | 江頭 | エトウ | 1987-04-09 | 32 | 江頭 信男 | X7gXd@sample.com | http://sample.com | 福岡県 | かきくけこ
(10 rows)
尝试了各种方法，但可能涉及数据依赖，而具体的机制应参考下面的链接，这是对区域设置规范的更准确解释：
https://lets.postgresql.jp/documents/technical/text-processing/2
✔作为监视项目，应该获取和保存好性能信息。
我认为无论是将其设为常驻Shell还是作为监控用的Zabbix等软件，都应该定期获取有关PostgreSQL的信息。
###DB全体
SELECT datname,round(blks_hit*100/(blks_hit+blks_read), 2) AS cache_hit_ratio FROM pg_stat_database
WHERE blks_read > 0;
###テーブルごと
SELECT relname,round(heap_blks_hit*100/(heap_blks_hit+heap_blks_read), 2) AS cache_hit_ratio FROM pg_statio_user_tables
WHERE heap_blks_read > 0 ORDER BY cache_hit_ratio;
###インデックスごと
SELECT relname, indexrelname,round(idx_blks_hit*100/(idx_blks_hit+idx_blks_read), 2) AS cache_hit_ratio FROM pg_statio_user_indexes
WHERE idx_blks_read > 0 ORDER BY cache_hit_ratio;
SELECT datname, xact_commit, xact_rollback, (xact_commit + xact_rollback) as sum_transaction FROM pg_stat_database;
###バックグラウンド含む
SELECT count(*) FROM pg_stat_activity ;
###バックグラウンド含まない
SELECT count(*) FROM pg_stat_activity where datname is not null;
select datname, temp_files, pg_size_pretty(temp_bytes) as temp_bytes, pg_size_pretty(round(temp_bytes/temp_files,2)) as temp_file_size
from pg_stat_database where temp_files > 0;
-
- temp_files: 作成された一時ファイルの数
-
- pg_size_pretty(temp_bytes): 一時ファイルの合計サイズ
- pg_size_pretty(round(temp_bytes/temp_files,2)): 一時ファイルの平均サイズ
select pg_size_pretty(pg_database_size('testdb')) as database_size;
select lock.locktype, class.relname, lock.pid, lock.mode
from pg_locks lock
left outer join pg_stat_activity act on lock.pid = act.pid left outer
join pg_class class on lock.relation = class.oid where not lock.granted
order by lock.pid;
✔️检查点
在PostgreSQL中，当commit完成时，基本上会将更新内容刷新到WAL，因此不会丢失数据，但也会定期将脏页数据刷新到磁盘上。如果不这样做，当操作系统突然断电等情况发生时，服务器会花费很长时间来进行崩溃恢复，而这个过程被称为检查点。对于已经完成检查点的部分，可以保证它们存在于磁盘上，因此不需要进行崩溃恢复。
检查点是一种成本高昂的处理方式，有以下两个含义。
-
- 現時点の全てのダーティバッファを書き出す必要があるため。
full_page_writes が有効化されている場合、各チェックポイント対象の各ページに関しては、その後初回のWAL書き込みの際にページ全体がWALに書き込まれるため、
为此，建议将检查点参数设置得较高，以确保检查点不会过于频繁发生。关于检查点，可以通过控制时间和更新的WAL量来控制其发生频率。实际上，可以通过设置这些参数并观察一段时间来确认没有问题。此外，在更新量达到峰值时，可以假设服务器崩溃并进行崩溃恢复时间的测试，以确认恢复时间没有问题。进行这样的测试是合理的。
checkpoint_timeout
この時間が前回のチェックポイントから経過するとチェックポイント実行。
max_wal_size
この更新量が前回のチェックポイントから発生するとチェックポイント実行
控制每个检查点节点的完成时间（从检查点执行到脏页刷新完成）的参数是checkpoint_completion_target。默认值为0.5，这意味着检查点将在从一次执行到下一次执行之间的时间间隔的一半内完成。该参数存在的原因是，如果脏页在检查点执行后立即被一次性刷新到磁盘上，会导致临时的负载激增。
另外，作为记录频繁检查点发生的方法，可以使用checkpoint_warning函数将参数设置为指定范围内的时间。当检查点（即超过max_wal_size限制的更新）在此时间内发生时，将在日志中记录相关信息。通常情况下，可以设置一个低于checkpoint_timeout的值进行观察，如果频繁在日志中记录，可以考虑增加max_wal_size以缓解频繁检查点处理所带来的负荷增加。
在执行检查点并生成日志后，检查点的位置将保存在pg_control文件中。因此，在发生崩溃后进行恢复时，PostgreSQL首先参考pg_control，然后读取检查点记录。然后，通过从检查点记录内指示的日志位置向前进行扫描，进行崩溃恢复时的REDO处理。
请参考以下链接，其中包含了关于PostgreSQL WAL配置和内部原理的文档：
1. PostgreSQL WAL配置文档：https://www.postgresql.jp/document/11/html/wal-configuration.html
2. PostgreSQL WAL内部原理文档：https://www.postgresql.jp/document/11/html/wal-internals.html
✔️ 页面缓存
检查在 shared_buffer 上的对象的方法。使用扩展功能 pg_buffercache。
首先，在目标数据库中执行 create extension pg_buffercache。
SELECT c.relname, count(*) AS buffers
FROM pg_buffercache b INNER JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid) AND
b.reldatabase IN (0, (SELECT oid FROM pg_database
WHERE datname = current_database()))
GROUP BY c.relname
ORDER BY 2 DESC
LIMIT 10;
select c.relname,b.relblocknumber,b.isdirty,b.pinning_backends
from pg_buffercache b INNER JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid) AND b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
where c.relname='リレーション名' order by 1,2;
关于将块固定在缓冲区中，请给出更多详细信息。。。
在关系上的块被加载到共享内存缓冲区中，但在操作过程中需要将这种关联关系固定。在PostgreSQL实例中，由其他会话或后台进程运行的进程可以加载不同的块到共享内存缓冲区，并且可能在不知不觉中发生替换并被驱逐。通过固定缓冲区，可以防止任意替换的发生。
[参考]
http://www.nminoru.jp/~nminoru/postgresql/pg-table-and-block-structure.html
【仅提供一个选项】：
请参考以下链接：
http://www.nminoru.jp/~nminoru/postgresql/pg-table-and-block-structure.html
✔️吸尘器
gaiyō (吸尘概要)
在PostgreSQL中，需要定期执行一种称为“vacuum”的处理。
PostgreSQL采用了追加式架构，即在执行update语句时，并不会更新现有行，而是以插入语句的形式将更新后的行添加到表的末尾。通过这种方式，可以避免在更新时获取行锁，从而防止执行更新操作的事务失败。对于现有行，当它们不再被任何事务引用时，将标记为删除，并且以后将不再被引用。
然而，尽管可能不会被引用，但并不意味着它们在物理上被删除为磁盘空间，也不是可重新利用的空间。通过进行清理，我们可以物理上删除这些不必要的空间或将其转化为可重新利用的空间。
吸尘器通常分为两种不同的类型。
-
- FULL VACUUM
-
- 不要タプルを物理的に削除してディスク領域をOSに返却。OSから見たときに物理的にディスク容量が空く。
- デメリットしては、実行時対象のテーブルに対してテーブル全体の排他ロックがかかる、かつ長時間かかる。
###データベース全体に対して実行
VACUUM FULL;
###特定のテーブルに対して実行
VACUUM FULL tbl1;
###詳細出力モード
VACUUM FULL VERBOSE tbl1;
-
- 通常VACUUM(Concurrent Vacuum)
-
- 不要タプルを物理的には削除しないが、DBMSとして再利用な領域として定義する。実行時特にロック等はかからずFULL VACUUMに比べて短時間で完了する。また基本的に前回のvacuumから更新のあった行に対してのみ実行するので早い。AutoVacuumで実行されるのはこちらの領域。
-
- デメリットとしては、
ディスク領域をOSに返す訳ではなくOSから見たディスク容量が枯渇の可能性がある点.
再利用可能な領域はSELECT時にディスク走査されるため、FULL VACUUMのようにOSにディスク領域として返した時と比べてパフォーマンスが悪くなる点。(例えば、データが１件しか入っていないテーブルのフルスキャンが通常速攻終わるのに、再利用可能な領域が10000000件あるとより時間がかかるなど)
###データベース全体に対して実行
VACUUM;
###特定のテーブルに対して実行
VACUUM tbl1;
###詳細出力モード
VACUUM VERBOSE tbl1;
在通常情况下，我们通常使用常规的VACUUM操作。但在紧急情况下，比如磁盘空间压力过大，或者对于数据不再增长且可重用空间的比例始终过高的表，则应当考虑执行FULL VACUUM操作，以避免触发表锁定。
「Auto VACUUM」的每个参数的详细信息。
select schemaname,relname,n_live_tup,n_dead_tup,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze from pg_stat_user_tables;
关于共享缓冲区的详细信息
代码示例
JDBC测试用（Java。截断⇒从CSV插入⇒查询结果）
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class mojia {
public static void main(String[] args) {
String URL = "jdbc:postgresql://kai1:5432/testdb";
String USER = "pguser01";
String PASS = "P#ssw0rd";
String SQL1 = "truncate table MOJI";
String SQL2 = "select * from MOJI";
//TRUNCATE & INSERT
try {
Connection conn = DriverManager.getConnection(URL, USER, PASS);
Statement stmt = conn.createStatement();
try{
stmt.executeUpdate(SQL1);
File file = new File("/root/csv/utf8.csv");
FileInputStream input = new FileInputStream(file);
InputStreamReader stream = new InputStreamReader(input,"UTF8");
BufferedReader br = new BufferedReader(stream);
String s;
while (( s = br.readLine()) != null ) {
String[] data = s.split(",", 0);
stmt.executeUpdate("INSERT INTO MOJI VALUES ('"
+ data[0] + "','"
+ data[1] + "','"
+ data[2] + "','"
+ data[3] + "','"
+ data[4] + "','"
+ data[5] + "','"
+ data[6] + "','"
+ data[7] + "','"
+ data[8] + "','"
+ data[9] + "')"
);
}
br.close();
} catch (IOException e) {
System.out.println(e);
}
stmt.close();
conn.close();
}
catch (Exception e) {
e.printStackTrace();
}
//SELECT
try(Connection conn =
DriverManager.getConnection(URL, USER, PASS);
PreparedStatement ps = conn.prepareStatement(SQL2)){
try(ResultSet rs = ps.executeQuery()){
int i=0;
while (rs.next()) {
System.out.println(
rs.getInt("col1") + "," +
rs.getString("col2") + "," +
rs.getString("col3") + "," +
rs.getDate("col4") + "," +
rs.getString("col5") + "," +
rs.getString("col6") + "," +
rs.getString("col7") + "," +
rs.getString("col8") + "," +
rs.getString("col9") + "," +
rs.getString("col10"));
}
rs.close();
ps.close();
conn.close();
};
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}
PHP PDO连接测试
<?php
//DB接続定義
$dsn = 'pgsql:host=xxxxx;port=5432;dbname=testdb';
$user='pguser01';
$password='P#ssw0rd';
$dbh = new PDO($dsn, $user, $password);
//SQL実行部分
$sth = $dbh->query('select * from AAA');
foreach($sth as $row){
print($row['col1'].",".$row['col2']."\n");
}
?>
请提供相关链接
-
- https://aws.amazon.com/jp/blogs/news/managing-postgresql-users-and-roles/
-
- https://employment.en-japan.com/engineerhub/entry/2017/09/05/110000
-
- 内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 4.5
-
- https://lets.postgresql.jp/documents/technical/statistics/2#db_level_stats
- https://www.percona.com/blog/2018/08/10/tuning-autovacuum-in-postgresql-and-autovacuum-internals/
