0. 前言
-
CentOS镜像版本：CentOS-7-x86_64-DVD-1804
-
postgresql版本：12
-
postgresql所有操作均是在postgresql的postgres用户权限下执行
1. 需求
每天将pg每天生成的csv日志导入到pg的指定表。pg中存csv的表名为 「tb年_月_日」,例如：tb2021_09_16。
- csv日志存到 postgresql数据库中的mizuho这个数据库中
2. 分析
- 首先pg需要生成csvlog，先去postgresql.conf配置
- 查官方文档发现可以利用file_fdw可用的 PostgreSQL 日志变成一个表用于查询
- 「每天」这样的要求可以利用crontab执行一个shell脚本，shell中利用psql -f 这样的参数执行sql文件。(需要注意shell使用psql肯定需要设置免密登录，否则shell里执行sql文件会被拦截.)
3. 流程
3.1 设置postgresql.conf
这一步目的是设置postgresql.conf能导出csvlog，postgresql.conf在 pg的data目录下。
我的data在下面这个目录
/var/lib/pgsql/12/data
vim /var/lib/pgsql/12/data/postgresql.conf
postgresql.conf 下面是我的参考(有些是默认打开的我怕漏)，如下：
#------------------------------------------------------------------------------
# REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
log_destination = 'csvlog
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y_%m_%d.log'
log_truncate_on_rotation = off
log_rotation_age = 1d
# - What to Log -
log_rotation_size = 20MB
#记录执行慢的sql
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_lock_waits = on
log_statement = 'all'
log_filename是我的需求格式，log_rotation_size 看自己需求。保存完毕后记得reload数据库哈~也可以
systemctl restart postgresql-12.service
参考：
3.2 为PostgreSQL CSV 日志创建外部表
官方文档的file_fdw的例子就是将csv日志入库,正好可以抄过来。我们先手动创建试试，后面再在shell执行sql自动将csv日志入库到指定表。
如果已经确保pg可以使用file_fdw和已创建一个外部服务器可以跳过这一步
[root@node4 ~]# su postgres
bash-4.2$ psql -d mizuhomizuho=#CREATE EXTENSION file_fdw;
如果爆出
ERROR:could not open extension control file “/usr/pgsql-12/share/extension/file_fdw.control“
该错误，可以参考 我的这篇文章。
之后跟着文档然后创建一个外部服务器接口SERVER ，之后我们sql文件里的建表sql用的也是这个外部服务器SERVER pglog
mizuho=#CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
最后创建外部表，使用CREATE FOREIGN TABLE命令。想了解每个字段具体什么含义可以参考我的这篇文章postgreSQL csvlog 源码字段分析记录。
CREATE FOREIGN TABLE pglog (
log_time timestamp(3) ,
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp ,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text
) SERVER pglog
OPTIONS ( filename '/var/lib/pgsql/12/data/log/postgresql-2021_09_16.csv ', format 'csv',header 'false' );
-
相比官方文档，我这里删了两处
with time zone，因为带上这个参数会导致原csvlog的时间字段和入库后表里的时间字段大相径庭。这个小坑解决于这篇文章。
-
后面的参数具体可以看官方文档有详细解释，这里仅解释这两个参数
- filename后面是文件名和绝对路径
- header表示第一行数据是否需要
-
删除外部表命令: drop foreign table 表名
创建成功会有CREATE FOREIGN TABLE提示
就是这样了 — 现在你能够直接查询你的日志了例如select * from pglog 。
3.3 编写shell脚本自动入库csv日志
3.2 我们已经手动执行了入库操作操作，接下来我们来使用shell自动化操作。
我参考linux 定时执行sql这篇文章后，我的shell和sql脚本（它们均在同一文件夹）如下：
- auto_csv_to_pg.sh，主shell。后面crontab、java代码使用的就是这个shell，逻辑很简单，就是如果发现pg中已有该csv日志表，就删除旧日志表建立新日志表获得最新的所有数据。否则直接建立日志表。（有个缺陷就是如果一天调用很多次该shell，会在csv日志中记录很多次建表sql，导入到pg表中真正想查的sql可能被大量的无效sql淹没）
CURRENTDATE=`date +%Y_%m_%d`
delete_csv(){
#复制sql模板为一个执行的sql文件,目的是为了替换的字符串,占位符固定是replace_tbName
cp deleteCSV_template.sql deleteCSV.sql
#替换里面的replace_tbName为指定格式日期
sed -i "s|replace_tbName|${CURRENTDATE}|" deleteCSV.sql
psql -h 192.168.76.203 -p 5432 -U postgres -f "deleteCSV.sql"
}
save_csv(){
cp createCSV_template.sql createCSV.sql
sed -i "s|replace_tbName|${CURRENTDATE}|" createCSV.sql
psql -h 192.168.76.203 -p 5432 -U postgres -f "createCSV.sql"
}
#这个函数用来判断pg数据库中是否存在今天的日志表
get_count(){
cp get_count_template.sql get_count.sql
sed -i "s|replace|$CURRENTDATE|" get_count.sql
res=`psql -h 192.168.76.203 -p 5432 -U postgres -f "get_count.sql"`
ans=`echo $res | awk '{print $(NF-2)}'`
return $ans
}
get_count
#如果函数返回值说明表已经建立了
if [ $? -gt 0 ];then
delete_csv
save_csv
echo "`date`:foreign tb${CURRENTDATE} already rebuild"
else
save_csv
fi
- createCSV_template.sql，就是3.2的建表语句，记得切成自己的库,SERVER
\c mizuho;
CREATE FOREIGN TABLE tbreplace_tbName (
log_time timestamp(3),
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text
) SERVER pglog
options (filename '/var/lib/pgsql/12/data/log/postgresql-replace_tbName.csv',format 'csv',header 'false');
- deleteCSV_template.sql，记得切成自己的库
\c mizuho;
drop foreign table tbreplace_tbName;
- get_count_template.sql，记得切成自己的库
\c mizuho;
select count(*) from pg_class where relname='tbreplace_tbName';
跑auto_csv_to_pg.sh之前，需要设置shell的免密登录。
参考这篇文章，也可以查看 官方文档这一节。
我选择直接使用export命令设置环境变量，将数据库密码赋值给PGPASSWORD
export PGPASSWORD=“自己pg数据库的密码”
终于可以跑auto_csv_to_pg.sh了！
成功！
3.4 crontab执行定制任务
这里放篇文章大家自己参考写吧~Linux 计划任务 crontab
4. 后续
之后可以走jdbc啥的在 java在后端拿到数据如下,不过那是后话了~
public class PostgresqlDBHelper {
private static String URL = "jdbc:postgresql://192.168.76.203:5432/mizuho";
private static String USER = "postgres";
private static String PASSWORD = "mizuho";
private static String DRIVER = "org.postgresql.Driver";
private static Connection conn;
private static Statement stmt;
private static ResultSet rs;
static {
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// public static void main(String[] args) throws SQLException {
// PostgreSQLJDBC postgreSQLJDBC = new PostgreSQLJDBC();
// String name = "tb2021_09_17";
// List<Map<String, Object>> list = postgreSQLJDBC.selectCSV(name);
// System.out.println(Arrays.asList(list));
// }
public Connection getConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* @Description:考虑到返回全字段并且sql暂时不会更改
* 就只需要传入需要查询csv文件名即可
* @param name: 待查询csv的文件名
**/
public List<Map<String, Object>> selectCSV(String name) {
List<Map<String, Object>> list = null;
try {
String sql = "select log_time,user_name,database_name,process_id,connection_from," +
"session_id,session_line_num,command_tag,session_start_time,virtual_transaction_id,transaction_id," +
"error_severity,sql_state_code,message,detail,hint,internal_query,internal_query_pos,context," +
"query,query_pos,location,application_name " +
" from " + name ;
conn = getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
list = new ArrayList<>();
while (rs.next()) {
String log_time = rs.getString("log_time");
String user_name = rs.getString("user_name");
String database_name = rs.getString("database_name");
String process_id = rs.getString("process_id");
String connection_from = rs.getString("connection_from");
String session_id = rs.getString("session_id");
String session_line_num = rs.getString("session_line_num");
String command_tag = rs.getString("command_tag");
String session_start_time = rs.getString("session_start_time");
String virtual_transaction_id = rs.getString("virtual_transaction_id");
Integer transaction_id = rs.getInt("transaction_id");
String error_severity = rs.getString("error_severity");
String sql_state_code = rs.getString("sql_state_code");
String message = rs.getString("message");
String detail = rs.getString("detail");
String hint = rs.getString("hint");
String internal_query = rs.getString("internal_query");
Integer internal_query_pos = rs.getInt("internal_query_pos");
String context = rs.getString("context");
String query = rs.getString("query");
Integer query_pos = rs.getInt("query_pos");
String location = rs.getString("location");
String application_name = rs.getString("application_name");
Map<String, Object> t = new HashMap<>(32);
t.put("log_time", log_time);
t.put("user_name", user_name);
t.put("database_name", database_name);
t.put("process_id", process_id);
t.put("connection_from", connection_from);
t.put("session_id", session_id);
t.put("session_line_num", session_line_num);
t.put("command_tag", command_tag);
t.put("session_start_time", session_start_time);
t.put("virtual_transaction_id", virtual_transaction_id);
t.put("transaction_id", transaction_id);
t.put("error_severity", error_severity);
t.put("sql_state_code", sql_state_code);
t.put("message", message);
t.put("detail", detail);
t.put("hint", hint);
t.put("internal_query", internal_query);
t.put("internal_query_pos", internal_query_pos);
t.put("context", context);
t.put("query", query);
t.put("query_pos", query_pos);
t.put("location", location);
t.put("application_name", application_name);
list.add(t);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
this.close(conn,stmt,rs);
}
return list;
}
public void close(Connection conn, Statement stmt) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void close(Connection conn, Statement stmt, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
this.close(conn, stmt);
}
}
结果：
1819
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
