安装
详情参考官方安装文档
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# 下面这个仓库直接使用了清华大学镜像站的地址
echo "deb https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/apt/ `lsb_release -sc`-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update
安装指定版本:
sudo apt install -y postgresql-VERSION # 如postgresql-9.6, postgresql-10等等
配置
postgresql的配置在/etc/postgresql/VERSION/main/，主要的两个配置文件postgresql.conf(服务配置文件)和pg_hba.conf(客户端认证配置文件)。
服务配置文件优化可以通过PGTune生成，添加或修改PG默认的配置。主要是shared_buffers这个默认值非常少(128MB)，这个值建议根据实际内容调大。
如果需要开启postgresql远程访问，修改postgresql.conf:
listen_addresses = '*'
在pg_hba.conf添加:
host all all 0.0.0.0 md5
然后重启postgresql即可: sudo service postgresql restart
服务管理
sudo systemctl start/stop/restart/status postgresql
日常维护
使用管理员权限
postgresql的认证方式有点特殊，是通过pg_hba.conf文件定义的。详细的权限描述参考官方文档: https://www.postgresql.org/docs/current/client-authentication.html
默认postgresql的管理员账户为postgres，而且使用peer方式认证。因此必须在服务器上使用以下命令以postgres账户连接到postgresql交互式控制台上:
sudo -u postgres psql
类似的，其他postgresql相关的命令也需要通过这种方式使用postgres管理员权限:
sudo -u postgresql createdb/dropdb/pg_dump xxxx
使用其他普通用户连接数据库
psql -U 登录名 -h localhost [数据库]
代表通过TCP协议连接localhost，不加-h指定host则默认使用unix domain socket连接，则会命中pg_hba.conf中的local权限配置。会交互式询问登录密码，输入正确的密码即可登录psql。
注意
psql并不支持像mysql命令行那样直接把密码作为参数传递进去进行非交互式登录，必须使用password file或者环境变量。
常用的命令
常用的SQL命令:
-- 创建低权限账户
CREATE ROLE 登录名 LOGIN PASSWORD '密码';
-- 创建数据库并赋权限
CREATE DATABASE 数据库名 OWNER 登录名;
-- 查看当前数据库活跃的线程(类似于mysql的SHOW FULL PROCESSES)
SELECT * FROM pg_stat_activity;
-- 杀掉指定的线程(上述查询条件可以追加 WHERE state = 'active' 查看当前正在运行的pid)
SELECT pg_cancel_backend(<pid of the process>)
常用psql控制台命令:
\? # 查看帮助
\h [SQL] # 查看指定SQL帮助
\l[+] # 列出数据库
\d[+] # 列出当前数据库的表
\c DATABASE # 切换到指定数据库
备份与还原
官方文档的详细描述参见: https://www.postgresql.org/docs/current/backup.html
小型数据库使用pg_dump是最方便的，类似于mysqldump的效果。
pg_dump -U 用户名 -h localhost DATABASE | xz -9 > DATABASE.sql.xz
pg_dump产生的是SQL备份文件，因此可以直接使用psql还原:
# 注意如果DATABASE如果存在，需要DROP掉再CREATE一个空库
unxz -c DATABASE.sql.xz | psql -U 用户名 -h localhost DATABASE
注意: 上述命令如果需要
postgres管理员权限参考上述使用管理员权限连接数据库的内容。为了加快还原速度，可以考虑psql追加-1|--single-transaction参数，将整个SQL备份当做一个事务一把COMMIT。但是特别注意中间不能有任何报错，否则将导致整个还原被回滚，确定中间的SQL不会有报错再使用。推荐同时追加-v ON_ERROR_STOP=1参数，在导入失败的时候可以立即停止，查看报错信息，修复之后再重试。
定期备份可以写一个脚本使用crontab驱动就可以了。
