Ubuntu 安装和配置 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

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

这篇好文章是转载于：学新通技术网