Ubuntu20.04 安装和配置 postgresql

安装
sudo apt update
sudo apt install postgresql postgresql-contrib


查看运行状态
sudo service postgresql status

进入 sql 命令行
sudo su - postgresql
psql

创建用户和授权
postgres=# create user test with password 'test';
CREATE ROLE
postgres=#
postgres=# alter user test with Superuser;
ALTER ROLE

创建数据库
postgres=# create database test;
CREATE DATABASE


修改访问IP，允许远程访问
1. 修改 postgresql.conf
默认是127.0.0.1，只能在本地访问

sudo vi /etc/postgresql/12/main/postgresql.conf

设置以下参数：

## 允许本机的任务一个ip地址访问postgresql
listen_addresses = '*'


2. 修改 pg_hba.conf
sudo vi /etc/postgresql/12/main/pg_hba.conf

在文件中加入这一行：

host all all 0.0.0.0/0 md5

3. 重启服务
sudo service postgresql restart

记录历史 SQL
1. 编辑配置文件 postgresql.conf
sudo vi /etc/postgresql/12/main/postgresql.conf

加入以下配置：

logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600

2. 重启服务
sudo service postgresql restart
1
3. 查看 SQL 查询日志
ubuntu@ubuntu:/var/lib/postgresql$ sudo tail -f /var/lib/postgresql/12/main/log/postgresql-2022-03-10_151933.log
2022-03-10 15:27:54.015 CST [114476] test@test LOG: execute <unnamed>: SELECT pp.oid as poid, pp.* FROM pg_catalog.pg_proc pp WHERE pp.proname ILIKE $1 AND pp.pronamespace IN ($2,$3) ORDER BY pp.proname LIMIT 10
2022-03-10 15:27:54.015 CST [114476] test@test DETAIL: parameters: $1 = '城市id', $2 = '2200', $3 = '11'
2022-03-10 15:27:54.740 CST [114476] test@test LOG: execute <unnamed>: SELECT pc.oid,pc.relname,pc.relnamespace,pc.relkind FROM pg_catalog.pg_class pc WHERE pc.relkind in('r','v','m','f') AND pc.relname ILIKE $1 AND pc.relnamespace IN ($2,$3) ORDER BY pc.relname LIMIT 10
2022-03-10 15:27:54.740 CST [114476] test@test DETAIL: parameters: $1 = 'dtl_supply_order_open_summary', $2 = '2200', $3 = '11'
2022-03-10 15:27:54.741 CST [114476] test@test LOG: execute <unnamed>: SELECT pp.oid as poid, pp.* FROM pg_catalog.pg_proc pp WHERE pp.proname ILIKE $1 AND pp.pronamespace IN ($2,$3) ORDER BY pp.proname LIMIT 9
2022-03-10 15:27:54.741 CST [114476] test@test DETAIL: parameters: $1 = 'dtl_supply_order_open_summary', $2 = '2200', $3 = '11'
2022-03-10 15:27:56.913 CST [114476] test@test LOG: execute <unnamed>: select * from dtl_supply_order_open_summary
2022-03-10 15:27:56.916 CST [114476] test@test LOG: execute <unnamed>: SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname, a.attnotnull OR (t.typtype = 'd' AND t.typnotnull), a.attidentity != '' OR pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = a.attrelid AND d.adnum = a.attnum) JOIN (SELECT 16397 AS oid , 1 AS attnum UNION ALL SELECT 16397, 2 UNION ALL SELECT 16397, 3 UNION ALL SELECT 16397, 4 UNION ALL SELECT 16397, 5 UNION ALL SELECT 16397, 6 UNION ALL SELECT 16397, 7 UNION ALL SELECT 16397, 8) vals ON (c.oid = vals.oid AND a.attnum = vals.attnum)
2022-03-10 15:27:57.119 CST [114476] test@test LOG: execute <unnamed>: SELECT current_schema(),session_user
2022-03-10 15:27:57.120 CST [114476] test@test LOG: execute <unnamed>: SHOW search_path
————————————————
版权声明：本文为CSDN博主「白月蓝山」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_47298890/article/details/123380926

https://blog.csdn.net/weixin_47298890/article/details/123380926?spm=1001.2101.3001.6650.1&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7Edefault-1.pc_relevant_aa&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7Edefault-1.pc_relevant_aa&utm_relevant_index=2