Postgres运维笔记

官网

https://www.postgresql.org/
版本选择: 12.1, 11.6, 10.11, 9.6.16, 9.5.20, 9.4.25

参考文档

电子书

PostgreSQL实战
PostgreSQL10.1中文手册
数据架构师的PostgreSQL修炼

macos下brew安装

暂时选择较流行的10.11版本

mv /usr/local/var/postgres /usr/local/var/postgres.old

brew install postgresql@10

echo 'export PATH="/usr/local/opt/postgresql@10/bin:$PATH"' >> ~/.zshrc

默认端口 5431，默认数据库 postgres，默认用户密码同与macos当前用户相同
查看版本号: pg_ctl -V
二进制路径: /usr/local/Cellar/postgresql@10/10.11
数据库路径: initdb /usr/local/var/postgres

brew服务启停

1
2
3

brew services run postgresql@10
brew services start postgresql@10
brew services stop postgresql@10

手工启停

1
2
3

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log status
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log stop -s -m fast

客户端

psql

1 2	psql -h 127.0.0.1 -p 5432 -d postgres -U yourname psql -d postgres

pgcli

https://www.pgcli.com/

一个增强的psql命令行工具，提供高亮与代码补全，可替代psql

1
2
3

pip3 install pgcli

pgcli -d postgres

GUI

pgAdmin4 WEB版UI较差，可适用于数据库管理，界面逻辑清晰，可显示DDL便于学习
Navicat 老版本对PG支持差需使用V15.X，UI较好但隐藏过多细节，可适用于数据库管理与SQL编写
DataGrip 仅适用于SQL编写，全平台，在IDEA中已集成，对PG支持略差
DBeaver 仅适用于SQL编写，全平台，对PG支持略好，UI较差

没有特别突出的方案。大而全选Navicat，偏重管理选择pgAdmin4，偏重开发运维选择DataGrip

psql控制台命令

\password：设置当前登录用户的密码
\h：查看SQL命令的解释，比如\h select。
?：查看psql命令列表。
\l：列出所有数据库。
\c [database_name]：连接其他数据库。
\d：列出当前数据库的所有表格。
\d [table_name]：列出某一张表格的结构。
\du：列出所有用户。
\e：打开文本编辑器。
\conninfo：列出当前数据库和连接的信息。
\password [user]: 修改用户密码
\q：退出

常用管理SQL

创建用户

1 2	CREATE USER testuser WITH PASSWORD 'testuser'; CREATE ROLE testuser WITH LOGIN PASSWORD 'testuser';

在PG中，用户和角色的概念相同

创建删除数据库

1 2	CREATE DATABASE testdb OWNER testuser; DROP DATABASE testdb;

创建SCHEMA

1	CREATE SCHEMA testschema2 AUTHORIZATION zorrouser;

将DB权限赋予用户

1	GRANT ALL PRIVILEGES ON DATABASE testdb to testuser;

用户赋权限

1 2	ALTER ROLE testuser CREATEDB; ALTER ROLE testuser WITH LOGIN;

其它赋权语句

1 2	ALTER SCHEMA public OWNER TO testuser; ALTER ROLE testuser WITH PASSWORD 'testuser';

常用数据类型

http://www.postgres.cn/docs/9.3/datatype.html

类型	解释	长度
integer	存储整数	4字节
bigint	存储整数，大范围	8字节
numeric	用户指定的精度，精确	可自定
serial	自动递增整数	4字节
bigserial	大的自动递增整数	8字节
varchar(size)	字符串
text	可变长度字符串
timestamp	日期和时间(无时区)	8字节
boolean	它指定true或false的状态	1字节

远程访问

vi postgresql.conf
listen_addresses = '*'

vi pg_hba.conf
# TYPE  DATABASE  USER  CIDR-ADDRESS  METHOD
host  all  all 0.0.0.0/0 md5

sudo service postgresql restart

BlueXIII's Blog