运维开发笔记

Appearance

PostgreSQL 数据库相关记录 ​

官方文档地址：https://www.postgresql.org/docs/

pg 客户端命令记录 ​

密码推荐通过环境变量 PGPASSWORD 来设置

检测连通性 ​

bash
pg_isready -h {host} -p {port} -U {user}
pg_isready -h {host} -p {port} -U {user} -d {dbname}

执行 sql ​

bash
psql -h {host} -p {port} -U {user} -d {dbname} "{sql}"
psql -h {host} -p {port} -U {user} -d {dbname} -f {sql_filename}

# 指定密码
PGPASSWORD="{password}" psql -h {host} -p {port} -U {user} -d {dbname} "{sql}"

常用 sql 语句 ​

系统库表查询 ​

sql
-- 查询所有表格
select table_name from information_schema.tables where table_schema='public';

-- 查询表格索引
select indexname, indexdef from pg_indexes where tablename='{table}';

-- select 时可以使用 || 拼接
select 'ALTER TABLE ' || table_name || ' OWNER TO "{owner}";' from information_schema.tables where table_schema='public';
select tablename || '__' || indexname, indexdef from pg_indexes where tablename='{table}';

权限相关 ​

sql
-- 创建用户+密码+权限
CREATE ROLE zhangsan WITH LOGIN NOSUPERUSER CREATEDB CREATEROLE INHERIT NOREPLICATION NOBYPASSRLS CONNECTION LIMIT -1 PASSWORD '123456';

-- 将新用户/角色权限附加到当前用户上
GRANT zhangsan TO postgres WITH ADMIN OPTION;

-- 创建新数据库并指定其 owner
CREATE DATABASE "zhangsan_test" WITH OWNER = zhangsan ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF8' LC_CTYPE = 'en_US.UTF8' CONNECTION LIMIT = -1 IS_TEMPLATE = False;

-- 切换数据库
\c zhangsan_test

-- 将所有表权限赋值给指定用户/角色；每次用了其它账号创建的表格都需要重新分配一下权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO zhangsan;


-- 更改表格的 owner
ALTER TABLE table_name OWNER TO new_owner;

-- 更改数据库的 owner
ALTER DATABASE "{dbname}" OWNER TO "{owner}";

权限说明 ​

  • 不仅数据库有 owner ，表格也有 owner；
  • 数据库的 owner 不代表它有此数据库中所有表格的权限
  • 表格权限需要另分配（获取修改表格的 owner）
  • 数据库和表格的默认 owner 是当前创建它的用户

web 版客户端/代理 ​

python 库 ​

bash
pip install "psycopg[binary]"

使用 Psycopg3，用法与 PyMySQL 的封装使用 基本一致，代码封装如下：

python
import psycopg


class PostgreSQL():
    """postgresql 的封装"""
    def __init__(self, **conn_info):
        # conninfo = ' '.join([f'{k}={v}' for k, v in conn_info.items()])
        conninfo = psycopg.conninfo.make_conninfo(**conn_info)
        self.conn = psycopg.connect(conninfo)

    def execute(self, sql):
        try:
            with self.conn.cursor() as cur:
                cur.execute(sql)
                self.conn.commit()
        except Exception:
            import traceback
            print(traceback.format_exc())
            # 异常后回滚
            self.conn.rollback()
        finally:
            self.conn.close()

    def select(self, sql):
        """查询"""
        with self.conn.cursor() as cur:
            cur.execute(sql)
            res = cur.fetchall()
            return res


if __name__ == '__main__':
    conn_info = {
        'host': '127.0.0.1',
        'port': 5432,
        'user': 'root',
        'password': '123456',
        'dbname': 'mytest',
        'connect_timeout': 10,
    }
    pg = PostgreSQL(**conn_info)

centos7 安装 psql 高版本 ​

bash
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install postgresql13