postgresql 9.x 叫做 xlog、location 在实际应用中经常需要根据 lsn/location 获取 wal/xlog 文件名 postgresql 10.x postgres=# select pg_current_wal_lsn(); pg_current_wal_lsn --- 0/1656FE0(1row)postgres=# select pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn()), pg_walfile_...
select pg_xlog_location_diff(pg_current_xlog_location(),replay_location)/1024/1024 as MB from pg_stat_replication; select pg_xlog_location_diff(pg_current_xlog_location(),replay_location)/1024/1024/1024 as GB from pg_stat_replication; 级联复制 select pg_xlog_location_diff(pg_last_xlog_repl...
pg_current_xlog_insert_location pg_current_xlog_location pg_last_xlog_receive_location pg_last_xlog_replay_location pg_tablespace_location (5 rows) 2)数据库中可以查询到当前的日志编号情况: apple=# select pg_current_xlog_location(); pg_current_xlog_location --- 0/45000098 (1 row) 可以看到:...
select pg_conf_load_time(); 3、查看当前wal的buffer中有多少字节未写入磁盘 select pg_xlog_location_diff(pg_current_xlog_insert_location(),pg_current_xlog_location()); 4、查询最耗时的5个sql select * from pg_stat_statements order by total_time desc limit 5; 备注:需要开启pg_stat_statements ...
postgres=# select pg_current_wal_lsn(); pg_current_wal_lsn --- 32AF/8CCE8000 (1 row) 1. 2. 3. 4. 5. #9.x版本 postgres=# select pg_current_xlog_location(); pg_current_xlog_location --- 110/9C8294B0 (1 row) 1. 2. 3. 4. 5. 查延时时间和事务...
PG_SETTINGS/PG_FILE_SETTINGS：配置参数 • PG_CONTROL_CHECKPOINT/PG_CONTROL_SYSTEM：基本信息 • PG_EXTENSION:插件 • pg_database/pg_user/pg_tablespace：数据库信息 • pg_stat_replication/pg_stat_replication_slots：复制 • pg_current_wal_lsn/pg_current_xlog_location/pg_control_checkpoint ...
selectpg_xlog_location_diff(pg_current_xlog_insert_location(),pg_current_xlog_location()); 4、查询最耗时的5个sql select*frompg_stat_statementsorderbytotal_timedesclimit5; 备注:需要开启pg_stat_statements 5、获取执行时间最慢的3条SQL,并给出CPU占用比例 ...
cp-r/usr/local/pgsql/data/media/ 但需要注意的是,此种方式由于比较直接,不管是否数据库有无IO情况,因此,备份的时候需要先停止数据库,恢复的时候要删除原数据库文件,重新覆盖回去后,才可以在启动数据库,如果在数据库启动的时候备份,那么,可能会造成数据备份不全,也可以理解为冷备方式。
PostgreSQL的安装目录下有个叫做pg_xlogdump的命令可以解析WAL文件,下面看一个例子。 -bash-4.1$pg_xlogdump/pgsql/data/pg_xlog/0000000100000555000000D5-b ... rmgr:Heap len(rec/tot):14/171,tx:301170263,lsn:555/D5005080,prev555/D50030A0,desc:UPDATEoff30xmax301170263;newoff20xmax0 ...
selectpg_xlog_location_diff(pg_current_xlog_insert_location(),pg_current_xlog_location()); 4、查询最耗时的5个sql select*frompg_stat_statementsorderbytotal_timedesclimit5; 备注:需要开启pg_stat_statements 5、获取执行时间最慢的3条SQL,并给出CPU占用比例 ...