【ubuntu22.04安装配置PostgreSQL】

1.安装

sudo apt update;
sudo apt install postgresql postgresql-contrib;

查看版本:
sudo -u postgres psql -c "SELECT version();"
查看运行状态:
sudo service postgresql status;

2. PostgreSQL 角色和身份认证方式

PostgreSQL 数据库访问权限是通过角色来处理的。一个角色代表一个数据库用户或者一个数据库用户组。

PostgreSQL 支持多种身份认证方式。最常用的方法如下：

• Trust - 只要满足pg_hba.conf定义的条件，一个角色就可以不使用密码就能连接服务器
• Password - 通过密码，一个角色可以连接服务器。密码可以被存储为 scram-sha-256, md5, 和 password(明文)。
• Ident - 仅仅支持 TCP/IP 连接。它通常通过一个可选的用户名映射表，获取客户端操作系统用户名。
• Peer - 和 Ident 一样，仅仅支持本地连接。
  PostgreSQL 客户端身份验证通常被定义在pg_hba.conf文件中。默认情况下，对于本地连接，PostgreSQL 被设置成身份认证防范 peer。

为了以postgres用户身份登录 PostgreSQL 服务器，首先切换用户，然后使用psql工具访问 PostgreSQL。
sudo su - postgresql; psql;
从这里开始，你可以与 PostgreSQL 实例交互。退出 PostgreSQL Shell，输入：
\q退出交互环境。

也可以直接使用sudo -u postgres psql,来访问PostgreSQL,而不切换用户。

常用的指令:
psql 数据库名 --连接数据库
select rolname,rolpassword from pg_authid;–查看用户名密码
select usename,passwd from pg_shadow;–查看用户名密码
select version(); – 查看版本
select current_database();–查看当前数据库
\l --查看所有数据库
select pg_database_size(‘postgres’); --查看单个数据库的大小
select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database; --查看所有数据库的大小
select pg_size_pretty(pg_database_size(‘postgres’)); --以KB，MB，GB的方式来查看数据库大小
\dt --查看表
\d test --查看test表的定义
\password username --修改密码
\password --设置密码。
? --查看psql命令列表。
\c [database_name] --连接其他数据库，切换数据库。
\conninfo --列出当前数据库和连接的信息。
\d --列出当前数据库的所有表格。
\d [table_name] --列出某一张表格的结构。
\du --列出所有用户。
\e --打开文本编辑器。
help --帮助
\h --查看SQL命令的解释，比如\h select。
\q --退出

JDBC的URL的格式:
jdbc:mysql://[host][,failoverhost...][:port]/[database] [?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]... 例如：jdbc:mysql://127.0.0.1/uba_db?useUnicode=true&characterEncoding=UTF-8
URL用于标识数据库的位置，通过URL地址告诉JDBC程序连接哪个数据库，postgre jdbc支持的URL的写法为：

jdbc:postgresql:database
jdbc:postgresql:/
jdbc:postgresql://host/database
jdbc:postgresql://host/
jdbc:postgresql://host:port/database
jdbc:postgresql://host:port/
jdbc:postgresql://host1:port1,host2:port2/database

ipv6地址:
jdbc:postgresql://[::1]:5740/database

也可以在url中加入其他参数如：
?uer=root&password=root&useUnicode=true&characterEncoding=utf8
参见Ubuntu下PostgreSQL的安装和简单操作

3.创建 PostgreSQL 角色和数据库

1. 新建用户test ，sudo su - postgres -c "createuser test"；
2. 新建数据库testdb ，sudo su - postgres -c "createdb testdb"；
3. 通过sudo -u postgres psql连接到postgrersql的shell环境,执行grant all privileges on database testdb to test;来授权用户test可以all操作testdb数据库；

4.启动远程访问PostgreSQL服务器数据库

默认情况下，PostgreSQL 服务器仅仅监听本地网络接口：127.0.0.1。

为了允许远程访问你的 PostgreSQL 服务器，打开配置文件postgresql.conf并且在CONNECTIONS AND AUTHENTICATION一节添加listen_addresses = '*'。

sudo vim /etc/postgresql/14/main/postgresql.conf

1. 重启使生效sudo service postgresql restart;
2. 确认修改效果ss -nlt | grep 5432;
3. 编辑pg_hba.conf文件,配置服务器接受远程连接:
   sudo vi /etc/postgresql/14/main/pg_hba.conf指定目的用户的ip来源,例如
   host all all 0.0.0.0/0 md5,重启服务生效
4. 防火墙上打开端口5432端口:
5. 开启日志
   编辑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                    

查看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