postgresql命令
wang 2023-11-06 postgresql
# 源码安装
postgresql下载地址：下载 postgresql-12.18.tar.gz安装包
# 安装
# 安装依赖包
yum -y install zlib zlib-devel readline readline-devel
# 安装到指定路径
./configure --prefix=/usr/local/pgsql
make & make install
1
2
3
4
5
6
7
2
3
4
5
6
7
# 创建用户和用户组
# 创建用户和用户组
groupadd postgres
useradd -g postgres postgres
# 创建数据存放路径
mkdir /usr/local/pgsql/data
# 配置用户权限
chown -R postgres.postgres /usr/local/pgsql
chmod 750 /usr/local/pgsql
# 切换postgres用户
su - postgres
# 初始化
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/
# 启动
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start
# 停止
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop
# 修改密码
alter user postgres password ‘123456’
# 开放端口
firewall-cmd --zone=public --query-port=5432/tcp
firewall-cmd --zone=public --add-port=5432/tcp --permanent
# 重新加载
firewall-cmd --reload
# 检查 用户组是否存在
cat /etc/group | grep postgres
cat /etc/passwd |grep postgres
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
# 配置远程访问
# 配置远程访问
vi /usr/local/pgsql/data/postgresql.conf
# 将旧的改成新的
旧：listen_addresses = 'localhost'
新：listen_addresses = '*'
vi /usr/local/pgsql/data/pg_hba.conf
# 将旧的改成新的
旧：host all all 127.0.0.1/0 trust
新：host all all 0.0.0.0/0 md5
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
# 环境变量
# 在profile文件添加pgsql的环境变量
vi /etc/profile
# 复制这个
export PATH=$PATH:/usr/local/pgsql/bin
1
2
3
4
5
2
3
4
5
# 连接
# 连接pgsql
psql -h localhost -U postgres -d postgres
# 修改密码
alter user postgres password "123456"
1
2
3
4
5
6
2
3
4
5
6
# postgresql慢查询
SELECT procpid, START, now() - START AS lap, current_query
FROM (SELECT backendid, pg_stat_get_backend_pid (S.backendid) AS procpid,
pg_stat_get_backend_activity_start (S.backendid) AS START,
pg_stat_get_backend_activity (S.backendid) AS current_query
FROM (SELECT pg_stat_get_backend_idset () AS backendid) AS S) AS S
WHERE current_query <> '<IDLE>' and procpid=进程id ORDER BY lap DESC;
1
2
3
4
5
6
2
3
4
5
6