PostgreSQL的一些操作命令及SQL语句

1.查看pg库安装路径

ps -ef | grep postgres

执行 ps -ef | grep postgres 查看进程的情况得到:

pgsql 的位置 /usr/pgsql-12/bin/postgres

数据存储在 /var/lib/pgsql/data

2.执行以下语句重启

/usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/data/ restart

3.停止服务:

su postgres

pg_ctl -D /var/lib/pgsql/data stop

4、启动服务:

su postgres
systemctl start postgresql

5.查询服务:

[root@os bin]# cd /usr/lib/systemd/system

[root@os system]# ls

6.服务的关闭及开启

systemctl stop postgresql-9.5.service

systemctl start postgresql-9.5.service

systemctl restart postgresql-9.5.service

7.一些SQL查询语句:

— 查看当前最大连接数
SHOW max_connections;

— 查询当前活跃连接数
select count(1) as actConn from pg_stat_activity;

— 查询指定IP的活跃链接信息
select * from pg_stat_activity where client_addr in (‘10.0.0.1′) ORDER BY client_addr asc;

— 统计指定IP的活跃链接信息
select client_addr,count(1) as total from pg_stat_activity where client_addr in (‘10.0.0.2′,’10.0.0.1′) group by client_addr;

— 查询数据库当前连接数脚本
select count(*), usename from pg_stat_activity group by usename;
— 连接数修改:/var/lib/pgsql/12/data/postgresql.conf中,找到max_collections参数进行修改,最后重启数据库即可

 

Rsyslog+ELK日志分析系统

这里主要总结一下搭建过程

一.rsyslog

rsyslog是现在大多数linux自带的日志收集,这里主要说一下rsyslog的简单配置。

client端配置它只须要更改准备发送的日志以及在末尾加上Server端使用的协议和IP便可,例如:

#加入如下便可 *.* @10.144.100.32:514

二.logstash配置

logstash只须要将rsyslog收集起来并转发至elasticsearch,配置以下:

vi /etc/logstash.conf

input{
        syslog{
        type=>"system-syslog"
        port=>514
        host=>"10.144.100.32"
        }
        }

        filter{
        date{
        match=>["timestamp","yyyy-MM-dd-HH:mm:ss"]
        locale=>"cn"
        }
        }
        output{
        elasticsearch{
        hosts=>["10.24.180.19:9200"]
        user=>"elastic"
        password=>"123456"
        index=>"blockchain-%{type}-%{+YYYY.MM.dd}"
        codec=>"json"
        }
        stdout{
        codec=>json
        }
}
cd logstash-5.5.1/bin/ ./logstash -f /etc/logstash.conf

三.Elasticsearch搭建

elasticsearch搭建起来比较简单,直接在https://www.elastic.co/downloads/elasticsearch下载elasticsearch压缩包,解压下来。

cd elasticsearch-5.5.1/bin/
./elasticsearch &

本地浏览器访问http://localhost:9200浏览器

*注意:安装elasticsearch尽可能先改一下内核参数和资源参数,我在阿里云上搭建的,默认的内核参数和资源参数可能会致使启动失败。

vim /etc/sysctl.conf #修改或添加如下参数 

fs.file-max=65536 
vm.max_map_count = 262144 

vim /etc/security/limits.conf #修改或添加如下参数 
* soft nofile 65536 
* hard nofile 131072 
* soft nproc 2048 
* hard nproc 4096

elasticsearch有几个比较推荐的插件,这里介绍一下head插件安装,并经过head插件介绍一下elasticsrach的一些基本概念。restful

首先介绍一下head的安装,ES5.0以前head插件是直接能够经过elasticsearch-plugin install安装,5.0以后不能够直接插件安装,但依然是能够安装使用的。

首先,设置下ES同源访问策略:

vim elasticsearch-5.5.0/conf/elasticsearch.yml #在末尾添加以下内容:
 
http.cors.enabled: true 
http.cors.allow-origin: "*"

经过npm进行安装:

git clone git://github.com/mobz/elasticsearch-head.git cd elasticsearch-head npm install npm run start

elasticsearch若是直接暴露在外网上是很是危险的,这里安装一款x-pack插件,可将此端口设置帐户密码。

cd elasticsearch-5.5.0/bin/ ./ealsticseearch-plugin intsall x-pack

安装完x-pack后,直接访问9200端口会提示输入帐号和密码,这增长了elasticsearch的一些安全性,默认的帐号密码是elastic:changeme,更改帐号密码课经过调用restful API

curl -XPUT -u elastic 'localhost:9200/_xpack/security/user/elastic/_password' -d '{ "password" : "elastic" }'

四.Kibana搭建

kibana是配合Elasticsearch一块儿使用的可视化分析平台,说白了就是更形象的表示Elasticsearch的结果。

vim kibana/config/kibana.yml

server.port: 5601 
server.host: 0.0.0.0 
elasticsearch.url: "http://localhost:9200" 
elasticsearch.username: "elastic" 
elasticsearch.password: "changeme"

浏览器输入localhost:5601 查看是否成功

Rsyslog+kafka+ELK(单节点)部署

开源产品:Rsyslog、Kafka、ELK
处理流程为:Vm Rsyslog–> Rsyslog Server –omkafka–> Kafka –> Logstash –> Elasticsearch –> Kibana
ps:omkafka模块在rsyslog v8.7.0之后的版本才支持

环境:

ELK SERVER 10.10.27.123
Rsyslog Server 10.10.27.121
Rsyslog client 10.10.27.122

rsyslog日志收集

Rsyslog是高速的日志收集处理服务,它具有高性能、安全可靠和模块化设计的特点,能够接收来自各种来源的日志输入(例如:file,tcp,udp,uxsock等),并通过处理后将结果输出的不同的目的地(例如:mysql,mongodb,elasticsearch,kafka等),每秒处理日志量能够超过百万条。
Rsyslog作为syslog的增强升级版本已经在各linux发行版默认安装了,无需额外安装

1、rsyslog服务端

~]# cat /etc/rsyslog.conf 
# Provides UDP syslog reception
$ModLoad imudp
$UDPServerRun 514

# Provides TCP syslog reception
$ModLoad imtcp
$InputTCPServerRun 514

~]# cat /etc/rsyslog.d/default.conf
#### GLOBAL DIRECTIVES ####
# Use default timestamp format  # 使用自定义的日志格式
$ActionFileDefaultTemplate RSYSLOG_TraditionalFileFormat
$template myFormat,"%timestamp% %fromhost-ip% %syslogtag% %msg%\n"
$ActionFileDefaultTemplate myFormat

# 根据客户端的IP单独存放主机日志在不同目录,rsyslog需要手动创建
$template RemoteLogs,"/data/rsyslog/%fromhost-ip%/%fromhost-ip%_%$YEAR%-%$MONTH%-%$DAY%.log"
# 排除本地主机IP日志记录,只记录远程主机日志
:fromhost-ip, !isequal, "127.0.0.1" ?RemoteLogs
~]# systemctl restart rsyslog

为了把rsyslog server收集的日志数据导入到ELK中,需要在rsyslog server使用到omkafka的模块

~]# yum -y install rsyslog-kafka
~]# cat /etc//rsyslog.d/kafka.conf
# 加载omkafka和imfile模块
module(load="omkafka")
module(load="imfile")
 
# nginx template
template(name="SystemlogTemplate" type="string" string="%hostname%<-+>%syslogtag%<-+>%msg%\n")
 
# ruleset
ruleset(name="systemlog-kafka") {
    #日志转发kafka
    action (
        type="omkafka"
	template="SystemlogTemplate"
        topic="system-log"
        broker="10.10.27.123:9092"
    )
}

input(type="imfile" Tag="Systemlog" File="/data/rsyslog/*/*.log" Ruleset="systemlog-kafka"

~]# systemctl restart rsyslog

2、 Rsyslog客户端

~]# cat /etc/rsyslog.conf #追加一行
*.*	@10.10.27.121:514
#所有日志通过UDP传输给rsyslog server
~]# systemctl restart rsyslog

至此,rsyslog准备完毕,验证/data/rsyslog下是否产生日志文件

kafka搭建

1、搭建kafka依赖的zookeeper

~]# docker login
~]# docker pull wurstmeister/zookeeper
~]# mkdir -p /data/zookeeper
~]# docker run -d \
--name zookeeper \
--net=host \
-p 2181:2181 \
--restart always \
-v /data/zookeeper:/data/zookeeper \
-e ZOO_PORT=2181 \
-e ZOO_DATA_DIR=/data/zookeeper/data \
-e ZOO_DATA_LOG_DIR=/data/zookeeper/logs \
-e ZOO_MY_ID=1 \
-e ZOO_SERVERS="server.1=10.10.27.123:2888:3888" \
wurstmeister/zookeeper:latest

参数说明:
--net=host: 容器网络设置为 host, 能够和宿主机共享网络
-p 2181:2181: 容器的 2181 端口映射到宿主机的 2181 端口
-v /data/zookeeper:/data/zookeeper:容器的/data/zookeeper 目录挂载到宿主机的 /data/zookeeper 目录
ZOO_PORT:zookeeper 的运行端口
ZOO_DATA_DIR:数据存放目录
ZOO_DATA_LOG_DIR: 日志存放目录
ZOO_MY_ID:zk 的节点唯一标识
ZOO_SERVERS:zk 集群服务配置

如果需要部署zookeeper集群:其他 2 个节点同理部署, 只需要修改 ZOO_MY_ID; 节点 2:ZOO_MY_ID=2,节点 3:ZOO_MY_ID=3

验证

~]# docker exec -it zookeeper /bin/bash
bash # cd /opt/zookeeper-3.4.13/bin
bash # zkServer.sh status
ZooKeeper JMX enabled by default
Using config: /opt/zookeeper-3.4.13/bin/../conf/zoo.cfg
Mode: standalone

2、搭建kafka

~]# mkdir -p /data/kafka
~]# docker pull wurstmeister/kafka
~]# docker run -d \
--name kafka \
--net=host \
--restart always \
-v /data:/data \
-e KAFKA_BROKER_ID=1 \
-e KAFKA_PORT=9092 \
-e KAFKA_HEAP_OPTS="-Xms1g -Xmx1g" \
-e KAFKA_HOST_NAME=10.10.27.123 \
-e KAFKA_ADVERTISED_HOST_NAME=10.10.27.123 \
-e KAFKA_LOG_DIRS=/data/kafka \
-e KAFKA_ZOOKEEPER_CONNECT="10.10.27.123:2181" \
wurstmeister/kafka:latest

参数说明:
--net=host: 容器网络设置为 host, 能够和宿主机共享网络
-v /data:/data:容器的/data 目录挂载到宿主机的 /data 目录
KAFKA_BROKER_ID:kafka 的 broker 集群标识, 每台节点 broker 不一样
KAFKA_PORT:kafka 运行端口
KAFKA_HEAP_OPTS:kafka 启动时的 jvm 大小
KAFKA_HOST_NAME:kafka 主机名称,这里随便写,但是要与主机 IP 做 dns 映射
KAFKA_LOG_DIRS:kafka 日志存储目录
KAFKA_ZOOKEEPER_CONNECT:kafka 运行在 zk 里面,zk 提供的连接地址,集群的话写多个地址,逗号隔开

如果需要部署zookeeper集群:其他 2 个节点同理部署, 只需要修改 KAFKA_BROKER_ID、KAFKA_HOST_NAME、KAFKA_ADVERTISED_HOST_NAME 对应的值即可

验证

进入 kafka 容器
~]# docker exec -it kafka /bin/bash
bash-4.4# bin/kafka-topics.sh --list --zookeeper 10.10.27.123:2181
__consumer_offsets
log-api
system-log  #rsyslog自动创建的topic

ELK搭建

1、搭建elasticsearch

~]# docker pull elasticsearch:7.7.0
~]# mkdir /data/es/conf -p
~]# cd /data/es/conf
~]# vim elasticsearch.yml
cluster.name: es-cluster
network.host: 10.10.27.123
http.cors.enabled: true
http.cors.allow-origin: "*"
network.publish_host: 10.10.27.123
discovery.zen.minimum_master_nodes: 1
discovery.zen.ping.unicast.hosts: ["10.10.27.123"]
discovery.type: single-node

多节点集群使用以下配置:
~]# vim elasticsearch.yml
#集群名称,多个节点用一个名称
cluster.name: es-cluster
## es 1.0 版本的默认配置是 “0.0.0.0”,所以不绑定 ip 也可访问
network.host: 0.0.0.0
node.name: master
#跨域设置
http.cors.enabled: true
http.cors.allow-origin: "*"
node.master: true
node.data: true
network.publish_host: 192.168.1.140
discovery.zen.ping.unicast.hosts: ["192.168.1.140,192.168.1.142,192.168.1.147"]
discovery.zen.minimum_master_nodes: 1

~]# docker run -d \
--name=elasticsearch -p 9200:9200 -p 9300:9300 -p 5601:5601 \
--net=host \
--restart always \
-e ES_JAVA_OPTS="-Xms1g -Xmx1g" \   #启动内存设置
-v /data/es/conf/elasticsearch.yml:/usr/share/elasticsearch/config/elasticsearch.yml \
-v /data/es/data:/usr/share/elasticsearch/data \
elasticsearch:7.7.0

启动过程会有报错,提前做以下操作
~]# mkdir /data/es/data
~]# chmod u+x /data/es/data
~]# vim /etc/sysctl.conf
vm.max_map_count=655350
~]# sysctl -p

如果需要部署zookeeper集群:其他 2 个节点同样的部署思路,只需要修改 elasticsearch 的配置文件中的 node.name 和 network.publish_host

2、部署kibana

~]# docker pull daocloud.io/library/kibana:7.7.0
~]# mkdir -p /data/kibana/conf
~]# vi /data/kibana/conf/kibana.yml
server.port: 5601
server.host: "0.0.0.0"
# ES
elasticsearch.hosts: ["http://10.10.27.123:9200"]  
i18n.locale: "zh-CN"
xpack.security.enabled: false
ps:多集群可忽略此配置文件,使用默认即可

~]# docker run -d \
--restart always \
--name kibana \
--network=container:elasticsearch \   #容器网络要和连接的 es 容器共享网络
-v /data/kibana/config:/usr/share/kibana/config \
daocloud.io/library/kibana:7.7.0

3、部署logstash

~]# docker pull daocloud.io/library/logstash:7.7.0
~]# mkdir /data/logstash/conf -p
~]# vim logstash.conf
input{
   kafka{
        topics => ["system-log"]   #必须可前文的topic统一
        bootstrap_servers => ["10.10.27.123:9092"]
    }
}
output{
    elasticsearch {
        hosts => ["10.10.27.123:9200"]
        index => "system-log-%{+YYYY.MM.dd}"
        }
   stdout {
    codec => rubydebug
  }
}

~]# docker run -d \
--restart always \
--name logstash \
--net=host \
--link elasticsearch \
-v /data/logstash/conf/logstash.conf:/usr/share/logstash/pipeline/logstash.conf \
daocloud.io/library/logstash:7.7.0

kibana添加索引

 

微信截图_20240118175914 微信截图_20240118175924

镜像下载

由于中国下载docker镜像很慢,配置镜像仓库

--registry-mirror=https://registry.docker-cn.com

建议在本地电脑,比如win10中安装windows版docker,配置好上面镜像仓库地址(setting-Docker Engine),打开powershell进行下载打包镜像:

PS C:\Users\suixin> docker pull kibana:7.7.0
PS C:\Users\suixin> docker save -o kibana.gz kibana:7.7.0

然后上传到服务器导入:

# docker load -i kibana.gz

Mysql 8.0 关闭binlog日志

Mysql8.0默认开启binlog记录功能,导致磁盘空间占用很大,8.0关闭的方式跟之前5.x的不太一样。

1.清除binlog文件

$ mysql -u root -p
#进入数据库查看log_bin状态
mysql> show variables like ‘log_bin';
+—————+——-+
| Variable_name | Value |
+—————+——-+
| log_bin | ON |
+—————+——-+
1 row in set (0.01 sec)

#查看现有在用的binlog日志
mysql> show master logs;
+—————+————+———–+
| Log_name | File_size | Encrypted |
+—————+————+———–+
| binlog.000020 | 1073742151 | No |
| binlog.000021 | 1073747018 | No |
| binlog.000022 | 1073930151 | No |
| binlog.000023 | 1073733807 | No |
+—————+————+———–+
4 rows in set (0.03 sec)

#手动清除binlog日志
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)

#退出mysql
mysql> \q

2.关闭

#编辑配置文件/etc/my.cnf,添加disable_log_bin,有些版本可能是:skip-log-bin
$ vim /etc/my.cnf
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading “# ” to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It’s default setting is log_bin=binlog
disable_log_bin
#skip-log-bin

#重启mysql服务
$ service mysqld restart

$ mysql -u root -p
#再次进入数据库查看log_bin状态
mysql> show variables like ‘log_bin';
+—————+——-+
| Variable_name | Value |
+—————+——-+
| log_bin | OFF |
+—————+——-+
1 row in set (0.01 sec)

#查看binlog日志报错提醒没有开启binlog
mysql> show master logs;
ERROR 1381 (HY000): You are not using binary logging

3.清理binlog

除了使用reset master清理日志文件之外,还可以按照日期清理:

purge master logs before ‘2024-01-18 00:00:00′;

ELK日志系统架构:Elasticsearch、Logstash、Kibana

ElasticSearch 安装

1、下载 ElasticSearch,本文使用的版本为 5.5.1。

2、配置

path.data: /data/es #数据路径
path.logs: /data/logs/es #日志路径
network.host: 本机地址 #服务器地址
http.port: 9200 #端口

如果不修改配置的话,默认的数据和日志都位于elasticsearch文件夹下。

默认地址会使用 192.168.0.1 的地址,此时ElasticSearch运行于开发模式,只能从本机访问。如果修改为生产地址,就会进入生产模式,并且运行 bootstrap check 。

3、启动

./bin/elasticsearch

注意,elasticsearch 不能使用 root 用户启动,使用其他用户启动,要注意有文件夹的读写权限。

我在安装过程中还出现了下面几个警告信息

[2017-08-07T09:13:59,951][WARN ][o.e.b.JNANatives         ] unable to install syscall filter: 
java.lang.UnsupportedOperationException: seccomp unavailable: requires kernel 3.5+ with CONFIG_SECCOMP and CONFIG_SECCOMP_FILTER compiled in
    at org.elasticsearch.bootstrap.SystemCallFilter.linuxImpl(SystemCallFilter.java:350) ~[elasticsearch-5.5.1.jar:5.5.1]
    at org.elasticsearch.bootstrap.SystemCallFilter.init(SystemCallFilter.java:638) ~[elasticsearch-5.5.1.jar:5.5.1]
    at org.elasticsearch.bootstrap.JNANatives.tryInstallSystemCallFilter(JNANatives.java:245) [elasticsearch-5.5.1.jar:5.5.1]
    at org.elasticsearch.bootstrap.Natives.tryInstallSystemCallFilter(Natives.java:113) [elasticsearch-5.5.1.jar:5.5.1]
    at org.elasticsearch.bootstrap.Bootstrap.initializeNatives(Bootstrap.java:111) [elasticsearch-5.5.1.jar:5.5.1]
    at org.elasticsearch.bootstrap.Bootstrap.setup(Bootstrap.java:194) [elasticsearch-5.5.1.jar:5.5.1]
    at org.elasticsearch.bootstrap.Bootstrap.init(Bootstrap.java:351) [elasticsearch-5.5.1.jar:5.5.1]
    at org.elasticsearch.bootstrap.Elasticsearch.init(Elasticsearch.java:123) [elasticsearch-5.5.1.jar:5.5.1]
    at org.elasticsearch.bootstrap.Elasticsearch.execute(Elasticsearch.java:114) [elasticsearch-5.5.1.jar:5.5.1]
    at org.elasticsearch.cli.EnvironmentAwareCommand.execute(EnvironmentAwareCommand.java:67) [elasticsearch-5.5.1.jar:5.5.1]
    at org.elasticsearch.cli.Command.mainWithoutErrorHandling(Command.java:122) [elasticsearch-5.5.1.jar:5.5.1]
    at org.elasticsearch.cli.Command.main(Command.java:88) [elasticsearch-5.5.1.jar:5.5.1]
    at org.elasticsearch.bootstrap.Elasticsearch.main(Elasticsearch.java:91) [elasticsearch-5.5.1.jar:5.5.1]
    at org.elasticsearch.bootstrap.Elasticsearch.main(Elasticsearch.java:84) [elasticsearch-5.5.1.jar:5.5.1]
[2017-08-01T14:10:57,843][WARN ][o.e.b.BootstrapChecks    ] [VAfWGGZ] max file descriptors [65535] for elasticsearch process is too low, increase to at least [65536]
[2017-08-01T14:10:57,844][WARN ][o.e.b.BootstrapChecks    ] [VAfWGGZ] max number of threads [1024] for user [maserati] is too low, increase to at least [2048]
[2017-08-01T14:10:57,844][WARN ][o.e.b.BootstrapChecks    ] [VAfWGGZ] max virtual memory areas vm.max_map_count [65530] is too low, increase to at least [262144]
[2017-08-01T14:10:57,844][WARN ][o.e.b.BootstrapChecks    ] [VAfWGGZ] system call filters failed to install; check the logs and fix your configuration or disable system call filters at your own risk

针对文件描述符,调成 65536 ulimit -n 65536,如果提示没有权限,则可以在用户的 .bash_profile 中增加一行,退出用户重新登陆就可以。

针对 max number of threads 问题,修改 /etc/security/limits.d/90-nproc.conf 。

*          soft    nproc     2048
root       soft    nproc     unlimited

针对 max virtual memory areas ,修改 /etc/sysctl.conf。如果没有,就新增一行。

vm.max_map_count = 262144

针对 system_call_filter 可以,通过修改配置文件(elasticsearch.yml)关掉这个参数。

bootstrap.system_call_filter: false 

4、访问,出现下面的结果表示启动成功。

[root@iZ627x15h6pZ cloud]# curl http://localhost:9200
{
  "name" : "VAfWGGZ",
  "cluster_name" : "elasticsearch",
  "cluster_uuid" : "J9Tm5R2zRt2PkOSwtXj5Wg",
  "version" : {
    "number" : "5.5.1",
    "build_hash" : "19c13d0",
    "build_date" : "2017-07-18T20:44:24.823Z",
    "build_snapshot" : false,
    "lucene_version" : "6.6.0"
  },
  "tagline" : "You Know, for Search"
}

Logstash 安装

1、下载并解压 Logstash,本文用的 Logstash-5.5.1 版本

2、创建一个简单的配置文件 logstash_test.conf

input { stdin { } }
output {
  stdout { codec => rubydebug }
}

3、启动 logstash

./bin/logstash -f logstash_test.conf 

出现这些信息,表示启动成功了。

[2017-08-01T13:58:38,437][INFO ][logstash.pipeline        ] Pipeline main started
The stdin plugin is now waiting for input:
[2017-08-01T13:58:38,532][INFO ][logstash.agent           ] Successfully started Logstash API endpoint {:port=>9600}

4、与ElasticSearch配合。

Kibana 安装

1、下载 Kibana

2、修改配置

//启动端口 因为端口受限 所以变更了默认端口
server.port: 5601
//启动服务的ip
server.host: "本机ip"
//elasticsearch地址
elasticsearch.url: "http://localhost:9200”

3、启动程序

./bin/kibana

4、访问查看Kibana启动是否成功,并检索查看数据

http://localhost:5601

参考资料:
1、Download Logstash
2、ElasticSearch Download

1234114
 
Copyright © 2008-2021 lanxinbase.com Rights Reserved. | 粤ICP备14086738号-3 |