PostgreSQL

PostgreSQL是2017年流行度上升最快的数据库.

吐槽:官方的管理工具pgAdmin真难用啊, 命令也没有MySQL易记.

安装

参考官方文档即可.

# CentOS例子
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 安装client,server,extension
sudo yum install -y postgresql12-server postgresql12-contrib
# init and auto start
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
sudo systemctl enable postgresql-12
sudo systemctl start postgresql-12
# Ubuntu
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql
sudo ss -atnp | grep 5432
sudo -u postgres psql

# 删除老版本
sudo apt purge postgresql-9.5 postgresql-10 postgresql-12

WSL安装:

# 默认安装PostgreSQL 10
sudo apt install postgresql
sudo passwd postgres
# 启动服务
sudo service postgresql start
# 连接
sudo -u postgres psql

配置

  • 端口: 5432
  • 默认用户: postgres
  • 配置默认目录: /var/lib/pgsql/ (CentOS) 或 /etc/postgresql/14/main (Ubuntu)

postgresql.conf

# 默认localhost,如果需要远程访问修改为*
listen_addresses = '*'

pg_hba.conf控制认证方式

  • 默认认证方式: ident,以当前登录的系统用户为数据库用户名连接
  • 密码认证: md5
  • 无密码: trust
  • 使用Linux系统账号: peer(仅限于本地连接)
# 默认配置
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# host    all             all             127.0.0.1/32            ident


# 开放远程访问
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all            192.168.100.1/24            md5

注: hba 代表 host-based authentication.

常用命令

# 连接服务器
psql -h hostname database user
# 创建数据库
sudo -u postgres createuser xulz -s
sudo -u postgres createdb mydb
> createuser
     -E  --encrypted (store password)
     -s   --superuser
     -P  --pwprompt
> createdb
> dropdb -W -U postgres -h localhost your_db_name

PSQL:

-- 连接
sudo -u postgres psql
-- 创建用户
CREATE USER xulz WITH PASSWORD 'password';
-- 更改密码
ALTER USER "user_name" WITH PASSWORD 'new_password';
-- 修改postgres密码
alter user postgres password 'postgres';  #或者 \password postgres
    
-- 创建数据库
CREATE DATABASE grafana;    
    
-- 用户授权
-- GRANT ALL PRIVILEGES ON DATABASE 授予 CREATE, CONNECT和 TEMPORARY 权限
grant ALL PRIVILEGES on database grafana to xulz;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO xulz;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO xulz;
-- pg14
grant pg_read_all_data to xulz;
grant pg_write_all_data to xulz;

-- 重命名数据库
ALTER DATABASE people RENAME TO "customers";

-- 设置密码
export PGPASSWORD=xxxxxxxx

PSQL Command:

\l      list database
\dt   list tables
\du   list users
\password user_name
\dn  list schemas

\?    
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo} #Connection

表操作

# 清空表数据
TRUNCATE bigtable RESTART IDENTITY;

关闭所有session连接

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'dbname'
AND pid <> pg_backend_pid();
-- 避免关闭当前连接
REVOKE CONNECT ON DATABASE dbname FROM PUBLIC, username; 
-- Revoke CONNECT权限以避免创建新连接

信息查询

查询数据库所有表的行数

SELECT
    nspname AS schemaname,relname,reltuples
FROM pg_class C
         LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
        nspname NOT IN ('pg_catalog', 'information_schema') AND
        relkind='r'
ORDER BY reltuples DESC;

备份和还原

这里主要指逻辑备份(而不是物理备份)。

pg_dump的几个主要选项:

 -U, --username=NAME      connect as specified database user    
 -F, --format=c|t|p       output file format (custom, tar, plain text)     
 -f, --file=FILENAME      output file name

SQL格式文件备份

## 备份
pg_dump -U postgres grafana -f grafana_bak.sql -W -h localhost
pg_dump dbname -f db_bak.sql
# 或者
pg_dump -Fp dbname  > db_bak.sql


## psql命令还原
psql -U username -f db_bak.sql dbname 
# 或者直接在psql终端还原
postgres=# \i db_bak.sql

自定义格式和tar格式备份


pg_dump -Fc dbname -f filename 
# 还原
pg_restore -Fc -d dbname filename

# tar格式与此类似,-Ft

注: pg_dumpall 用于集群级别备份,使用psql命令还原。

从数据库删除所有表

-- —clean 清除数据库
pg_dump -U postgres -h localhost -p 5432 --clean --file=sandbox.sql sandbox
pg_dumpall -U postgres -h localhost -p 5432 --clean --globals-only --file=globals.sql
psql -W -U postgres -h localhost your_db_name < backup.sql
drop schema public cascade;
create schema public;

方式二:

DO $$ DECLARE
r RECORD;
BEGIN
-- 注意替换current_schema为要删除的schema
FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
END LOOP;
END $$

扩展

产生UUID

create extension "uuid-ossp";
select uuid_generate_v4();

Troubleshooting

  1. 在Mac下使用pip安装psycopg2时报错:“pg_config executable not found”

解决: export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/9.6/bin

与MySQL比较

  • 更完整的事务支持
  • 更快的列添加
  • 更好的多核CPU支持

资源

客户端工具

参考