[db/note] Postgres 使用入门

记录 PG 一些语法（用法）与 MySQL 的不同；内容来自检索到的各种博客，来源太多就不一一引用了，总体而言是做一个汇总，方便自己再次安装。

0. 安装

ubuntu直接执行：

apt install postgresql

查看是否安装成功：

$ psql --version
psql (PostgreSQL) 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1)

1. 登陆以及远程连接

postgres安装之后会创建一个不带密码的用户 postgres，且默认只能通过 postgres 本地登陆；我们可以先登陆上去创建一个新用户。

su postgres
psql

# or use 
sudo -u postgres psql

这将会登陆 postgre 用户的 postgres 数据库，要指定用户名、数据库等参数可以通过命令：psql -U user_name -d database_name -h 127.0.0.1 -p 5432

创建用户、对应数据库，并授权：

CREATE USER batina WITH password 'your_passwd';
CREATE DATABASE your_db_name OWNER batina;
GRANT ALL PRIVILEGES ON DATABASE your_db_name TO batina;

随后就可以用刚创建的用户（这里创建的是 batina）登陆pg了；不过还有一件事，pg默认不开启远程登录，且只能通过操作系统上与数据库user名称对应的user才能直接登入pg，想通过密码远程登陆还要去修改配置文件 postgresql.conf 和 pg_hba.conf。

这两个文件的位置可以在pg中执行 show config_file 和 show hba_file 来找到。

在 postgresql.conf 添加 listen_addresses = '*'

在 pg_hba.conf 中添加 host all all 0.0.0.0/0 md5

然后重启 pg 使改动生效，便可以通过 psql -U batina -d your_db_name -h host_name 远程（或者本地使用127.0.0.1）登陆pg了。

2. 常用命令

与 MySQL 的使用方式略微有些不同，这里对比记录一下常用命令：

show databases;  #  \l
show tables;     #  \dt
desc table_name  #  \d table_name

quit             #  \q


// drop database database_name  -->

cmd line: dropdb tool; man dropdb; dropdb --help
dropdb database_name

pg_dump
pg_restore

关于 explain 得到的 cost 字段如何理解：

select relpages, reltuples from pg_class where relname='aka_name';

page_number * seq_page_cost(default 1.0) + row_number * cpu_tuple_cost(default 0.01)

扫描页面数和行数可以用上述命令进行查询；

http://www.postgres.cn/docs/9.5/using-explain.html 中文文档 https://www.modb.pro/db/70740 墨天轮

默认顺序访问page 和随机访问设定的 cost 参数是不同的（想想也很合理

width：每行的平均宽度（单位为字节数）

3. hypopg插件安装及使用

安装hypopg插件

首先去hypopg的仓库下载源代码

  git clone https://github.com/HypoPG/hypopg.git

文档中注明需要PostgreSQL头文件，一开始看到了但不知什么意思。后来发现make时会报错No such file or directory: postgres.h

头文件安装方法：

  sudo apt install postgresql-server-dev-XX

可以先 apt search 查看有什么版本，我目前得到的版本有这些：

  postgresql-server-dev-14/jammy-updates,jammy-security 14.5-0ubuntu0.22.04.1 amd64
    development files for PostgreSQL 14 server-side programming

  postgresql-server-dev-all/jammy 238 amd64
    extension build tool for multiple PostgreSQL versions

随便装一个就行。

使用插件

通过命令 CREATE EXTENSION hypopg; 在PG内开启插件。然后可以通过插件提供的 hypopg_create_index 函数来创建一个 hypo index。
```
1
  SELECT * FROM hypopg_create_index('CREATE INDEX ON hypo (id)');
```
语法看起来有些奇怪。创建好之后可以通过SELECT * FROM hypopg_list_indexes;查看创建出来的 hypo index，此时使用 explain 就能看到 hypo index 会被纳入优化器的考虑之中。更多用法介绍请参考官方文档[1][2]

关于 hypothetical index 的原理、源码介绍，计划另开一贴进行详细的说明，这里就不过多展开。

4. Python连接PG

import psycopg2
conn = psycopg2.connect(database="db_name", user="batina", password="XXX", host="your_host_name", port="5432")

cursor = conn.cursor()
cursor.execute("select * from test1;")
print(cursor.fetch_all())

py通过 psycopg2 组件连接 pg，安装好之后用上面短短的几行代码就能完成连接和读写；但是我在 Ubuntu 22.04 / Python 3.10 企图安装这东西的时候差点被气到吐血（在20.04倒是装得很顺利）。

pip 安装一直 retry 最后下载失败，我以为是 pip 国内源的问题，但是切回官方源也是一样结果。

然后尝试源码安装，这回装是装上了，打开 pycharm 写代码的时候 import 一看还是没找到这个包；然后去命令行打开 python 试了一下，哎，命令行里 import 是正常的。得，把项目的py解释器换成系统里安装python的路径吧，这下才能正常开始写代码。

迟早得被py气死😡

2023-03-27 update:

今天在实验室服务器装环境，发现可以用命令

pip install psycopg2-binary

安装成功（ubuntu 20.04/python 3.8）

以及 python 的 venv 和包管理还算是好用的，之前是自己不知道虚拟环境的用法（用了虚拟环境的解释器那么包也要装在虚拟环境里）。不过好歹现在从自己电脑换到服务器上并没有花太多功夫就能跑起来了，配合 pycharm 的远程连接运行调试，终于可以~~愉快的~~开始实验了呜呜呜

0. 安装

1. 登陆以及远程连接

2. 常用命令

3. hypopg插件安装及使用

4. Python连接PG

CATALOG