使用树莓派学习PostgreSQL

知乎上刷到一个口水仗，大意是mysql和postgresql哪个好。这种问题，正如Java和C#哪个好之类的，我一向都是当八卦浏览的，只看不参与，又不是豆花该吃甜还是该吃咸这种原则问题对吧。

不过还是看到点东西的，把我的好奇心又勾了起来，肝了两个晚上在树莓派上玩了一把。同时给自己定了两个小目标：

1、装上PostgreSQL体验一下；
2、移植一个以前做着玩的以mysql为后台的小软件到PostgreSQL上。

一、树莓派上安装PostgreSQL
为了这次的学习，特地重刷了系统。之前看到某东自营品牌的SSD有活动，就买了一个专供树莓派使用。不得不说树莓派4+SSD移动硬盘做USB BOOT用起来实在是太方便了。这次直接刷了Lite。

在树莓派官方操作系统上安装PostgreSQL（以下简称pgsql）很简单，一条语句搞定：

sudo apt install postgresql

pgsql默认使用端口5432（很好记吧）。保险起见用装完后用 netstat -nutlp | grep 5432 确认本机已在5432端口上监听。

修改 /etc/postgresql/13/main/postgresql.conf：
找到listen_addresses语句，反注释，并修改为：listen_addresses = '0.0.0.0'
当然如果给树莓派设个固定IP，并且改成这个固定IP地址安全性会更高。

修改同级目录下的 pg_hba.conf，追加：
host    all    all    0.0.0.0/0    md5
host    all    all    ::/0    md5

然后重启pgsql服务：

sudo systemctl restart postgresql

二、PostgreSQL初始配置
1、修改默认用户的密码。pgsql初始默认用户名是postgres，默认数据库也叫postgres。

sudo -u postgres psql postgres

以postgres身份执行psql（pgsql的命令行客户端工具），连接到本机的postgres数据库（即psql后的参数）。然后直接修改密码：

\password postgres

按提示输入两次密码，成功后就把默认用户postgres的密码改成你自己的了。

2、添加自己的用户。个人认为在账号管理方面pgsql比mysql繁琐，甚至比mssql都繁。比如我要加个用户（pgsql称之为role）pi，并能操作一个新数据库a：

CREATE USER pi WITH PASSWORD '*****';
CREATE DATABASE A;
GRANT ALL PRIVILEGES ON DATABASE A TO pi;

这就完了么？并没有。进入数据库A后还要继续操作：

\c a;
GRANT ALL PRIVILEGES ON all tables in schema public TO pi;
GRANT USAGE,SELECT ON ALL SEQUENCES in schema public TO pi;

否则后续会遇到一堆权限问题。如果只需要单表授权：GRANT SELECT ON TABLE 某张表 TO pi;。另外，pgsql对自增长类型的字段也需要特意授权。

三、用户表创建（数据库移植）
相对mysql而言，pgsql建表的语句更接近mssql。我在移植过程中发现的几个比较有意思的区别，试举几例：

表创建好以后再建立索引的方式也是pgsql和mssql比较像，都是 create index，而mysql则可以使用alter table ... add index 语法。

表建完后可以在psql环境中使用 \d 表名 命令查看。

四、代码移植
我使用了Npgsql的驱动，总体说来很方便，但还是有点不一样。因为我想移植的软件是以前开发的，基于framework 4.5.1，所以从一开始的思路就是直接找驱动，但未能如愿，看着挺官方的文档给出的下载链接最终跳转到了推荐无线路由器的网页，最后还是下载了完整的数据库的安装包（地址：Download PostgreSQL）、通过仅安装Application Stack Builder组件、再用它来下载需要的驱动解决。默认安装路径：C:\Program Files (x86)\PostgreSQL\Npgsql\bin\net451\，project里引用里面的dll就好。版本倒是正好，也是4.5.1。后续想等空了用.net core 重写试试。

代码方面，using Npgsql;，然后参照SqlClient的那套，只不过把本来Sql开头的那套对象改成NpgsqlConnection、NpgsqlCommand、NpgsqlDataReader等等。

当然具体移植过程中也踩到了坑。第一个不同之处在于连接字符串，server=xxxx要改为 Host=xxxx；第二个要注意的地方是insert into 表名，pgsql这里into不可省略（mssql和mysql都能省）；

第三条则有关自增长类型的取值。
我的代码逻辑里有一条是插完表后需要当场取出新得到的ID值。在mysql的版本里用了 LAST_INSERT_ID()，pgsql里则需要改成LASTVAL()。另外注意到了在pgsql里有一种写法，insert into 表名(...) values (...)  returning ID。这里的ID就是该表的自增长类型的字段名。C#代码里可以直接使用cmd.ExecuteScalar().ToString()得到该值。

五、图形化管理界面
当前是 pgAdmin4。可从数据库安装包中选择该组件，也可独立下载安装：Download

参考文献：
C# PostgreSQL - programming PostgreSQL in C#
PostgreSQL function for last inserted ID - Postgresql (w3guides.com)