PostgreSQL（Pgsql）快速开始/ ubuntu、centos下PostgreSQL数据库安装配置/查看已经编译的pg 编译选项

一、PostgreSQL是什么

官网： https://www.postgresql.org/

PostgreSQL: The World’s Most Advanced Open Source Relational Database
PostgreSQL：世界上最先进的开源关系数据库

PostgreSQL标榜自己是世界上最先进的开源数据库。PostgreSQL的一些粉丝说它能与Oracle相媲美，而且没有那么昂贵的价格和傲慢的客服。最初是1985年在加利福尼亚大学伯克利分校开发的，作为Ingres数据库的后继。PostgreSQL是完全由社区驱动的开源项目。它提供了单个完整功能的版本，而不像MySQL那样提供了多个不同的社区版、商业版与企业版。PostgreSQL基于自由的BSD/MIT许可，组织可以使用、复制、修改和重新分发代码，只需要提供一个版权声明即可。

PostgreSQL则是诞生于加州伯克利，伯克利还是对计算机技术有过突出贡献的高校的，PostgreSQL、FreeBSD都是非常有代表性的。

1. Pgsql和Mysql的对比

【干货总结】:可能是史上最全的MySQL和PGSQL的对比材料
参考URL:https://www.cnblogs.com/lyhabc/p/11628042.html
MySQL与PostgreSQL全方位对比
参考URL: https://baijiahao.baidu.com/s?id=1669655888792593702&wfr=spider&for=pc
MySQL与PostgreSQL对比
参考URL: https://blog.csdn.net/uniquewonderq/article/details/87648717
Pgsql和Mysql的对比
参考URL: https://blog.csdn.net/weixin_45528849/article/details/109021353

**MySQL的背后是一个成熟的商业公司，而PostgreSQL的背后是一个庞大的志愿开发组。这使得MySQL的开发过程更为慎重，而PostgreSQL的反应更为迅速。**这样的两种背景直接导致了各自固有的优点和缺点。

PGSQL
只有社区版，没有其他任何分支版本，PGSQL官方统一开发，统一维护，社区版有所有功能，不像SQL Server和MySQL有标准版、企业版、经典版、社区版、开发版、web版之分
国内外还有一些基于PGSQL做二次开发的数据库厂商，例如：Enterprise DB、瀚高数据库等等，当然这些只是二次开发并不算独立分支

MySQL是C/C++混合开发，PostgreSQL则是完全的C语言开发。

大版本发布速度

PGSQL
PGSQL每年一个大版本发布，大版本发布的第二年就可以上生产环境，版本迭代速度很快
PGSQL 9.6正式版推出时间：2016年
PGSQL 10 正式版推出时间：2017年
PGSQL 11 正式版推出时间：2018年
PGSQL 12 正式版推出时间：2019年 （目前（2021-1），比较常用该版本，ubuntu20.4 默认安装的 是postgresql-12）

MySQL
MySQL的大版本发布一般是2年~3年，一般大版本发布后的第二年才可以上生产环境，避免有坑，版本发布速度比较慢
MySQL5.5正式版推出时间：2010年
MySQL5.6正式版推出时间：2013年
MySQL5.7正式版推出时间：2015年
MySQL8.0正式版推出时间：2018年

1. CPU限制
   GSQL
   没有CPU核心数限制，有多少CPU核就用多少

   MySQL
   能用128核CPU，超过128核用不上

2. 第三方工具依赖情况
   PGSQL
   只有高可用集群需要依靠第三方中间件，例如：patroni+etcd、repmgr

   MySQL
   大部分操作都要依靠percona公司的第三方工具（percona-toolkit，XtraBackup），工具命令太多，学习成本高，高可用集群也需要第三方中间件，官方MGR集群还没成熟

3. 高可用主从复制底层原理
   PGSQL
   物理流复制，属于物理复制，跟SQL Server镜像/AlwaysOn一样，严格一致，没有任何可能导致不一致，性能和可靠性上，物理复制完胜逻辑复制，维护简单

   MySQL
   主从复制，属于逻辑复制，（sql_log_bin、binlog_format等参数设置不正确都会导致主从不一致）
   大事务并行复制效率低，对于重要业务，需要依赖 percona-toolkit的pt-table-checksum和pt-table-sync工具定期比较和修复主从一致
   主从复制出错严重时候需要重搭主从MySQL的逻辑复制并不阻止两个不一致的数据库建立复制关系

4. 数据类型
   PostgreSQL支持多种高级数据类型，比如array，用户也可以定义类型，MySQL只支持标准类型。PostgreSQL支持布尔型，支持IP地址数据类型，支持常量和函数调用。PostgreSQL支持JSON和其他NoSQL功能，本机支持XML，允许索引JSON数据，MySQL支持JSON，不过不支持其他的NoSQL功能。PostgreSQL的对象统计功能也很强，这一点MySQL也有差距。

5. 线程
   PostgreSQL是多进程、MySQL是多线程。PostgreSQL支持大多数命令类型上触发的触发器。MySQL是异步复制，PostgreSQL支持同步、异步、半同步复制。PostgreSQL要求所有数据必须完全满足需求，只要出一个错误整个数据入库过程都要失败，不过MySQL没这样的问题。

二、ubuntu 下安装配置postgres sql 数据库

ubuntu下postgreSQL安装配置
参考URL: https://www.cnblogs.com/wwh/p/11605240.html

在Ubuntu下安装Postgresql后，会自动注册为服务，并随操作系统自动启动。
在Ubuntu下安装Postgresql后，会自动添加一个名为postgres的操作系统用户，密码是随机的。并且会自动生成一个名字为postgres的数据库，用户名也为postgres，密码也是随机的。

1. [推荐]apt-get 安装postgres

安装和配置

PostgreSQL 安装 & 用户配置
参考URL: https://www.cnblogs.com/xjnotxj/p/11198255.html
如何在 Ubuntu 上安装和配置 PostgreSQL
参考URL: https://linux.cn/article-11480-1.html

安装客户端

apt-get install postgresql-client

安装服务端

apt-get update
apt-get install postgresql

#查看已经安装的postgresql版本
apt show postgresql

ubuntu20.4 默认安装的 是postgresql-12

上面已经把数据库下载下来了 ,接下来我们 修改默认的postgres数据库用户的密码为123456。

你可以通过执行以下命令来检查 PostgreSQL 是否正在运行：

service postgresql status

通过 service 命令，你可以启动、关闭或重启 postgresql。输入 service postgresql 并按回车将列出所有选项。现在，登录该用户。

默认情况下，PostgreSQL 会创建一个拥有所权限的特殊用户 postgres。要实际使用 PostgreSQL，你必须先登录该账户：

su - postgres

你的提示符会更改为类似于以下的内容：

postgres@ubuntu-VirtualBox:/home/ubuntu$ 

现在，使用 psql 来启动 PostgreSQL Shell：

psql

你应该会看到如下提示符：

postgress=#

你可以输入 \q 以退出，输入 ? 获取帮助。
要查看现有的所有表，输入如下命令：

\l

输出内容类似于下图所示（单击 q 键退出该视图）：

使用 \du 命令，你可以查看 PostgreSQL 用户：
你可以使用以下命令更改任何用户（包括 postgres）的密码：

ALTER USER postgres WITH PASSWORD 'my_password';

注意：将 postgres 替换为你要更改的用户名，my_password 替换为所需要的密码。另外，不要忘记每条命令后面的 ;（分号）。

建议你另外创建一个用户（不建议使用默认的 postgres 用户）。为此，请使用以下命令：

CREATE USER my_user WITH PASSWORD 'my_password';

运行 \du，你将看到该用户，但是，my_user 用户没有任何的属性。来让我们给它添加超级用户权限：

ALTER USER my_user WITH SUPERUSER;

你可以使用以下命令删除用户：

DROP USER my_user;

要使用其他用户登录，使用 \q 命令退出，然后使用以下命令登录：

psql -U my_user

你可以使用 -d 参数直接连接数据库：

psql -U my_user -d my_db

你可以使用其他已存在的用户调用 PostgreSQL。例如，我使用 ubuntu。要登录，从终端执行以下命名：

psql -U my_user -d postgres

注意：你必须指定一个数据库（默认情况下，它将尝试将你连接到与登录的用户名相同的数据库）。

创建数据库，并制定所有者

postgres=# CREATE DATABASE my_db OWNER my_user;

当前数据库#
查看：

select current_database();

切换：

\c - next_db;

启停服务

找到数据库bin目录./pg_ctl执行

systemctl stop postgresql.service
systemctl start postgresql.service

开启远程访问

Ubuntu20.04 开启远程访问PostgreSQL

安 装PostgreSQL数据库之后，默认是只接受本地访问连接。

如果想在其他主机上配置远程连接PostgreSQL，需要修改pg_hba.conf和postgresql.conf

修改pg_hba.conf文件，开通局域网的访问权限，注意IP地址改为自己所在网络的IP。

1.修改pg_hba.conf文件，配置用户的访问权限

root@sheservice:~# find / -name pg_hba.conf
/etc/postgresql/12/main/pg_hba.conf
root@sheservice:~# vi /etc/postgresql/12/main/pg_hba.conf

 host    all       all         192.168.1.1/32      md5 --/32代表只允许192.168.1.1访问

 host    all       all         192.168.1.0/24     md5 --/24代表192.168.1.1~192.168.1.255都允许访问

 host    all       all         192.168.0.0/16      md5 --/16代表192.168.1.1~192.168.255.255都允许访问

 host    all       all         192.0.0.0/8          md5 --/8代表192.1.1.1~192.255.255.255都允许访问

 host    all       all         0.0.0.0/0          md5 --/0代表所有ip地址都允许访问

加入一行允许内网IP访问

2.修改postgresql.conf文件，将数据库服务器的监听模式修改为监听所有主机发出的连接请求。

定位到#listen_addresses=’localhost’。PostgreSQL安装完成后，默认是只接受来在本机localhost的连接请 求。

编辑配置文件postgresql.conf，去掉前面的#，修改为listen_addresses = ‘*’

root@sheservice:~# find / -name postgresql.conf
/usr/lib/tmpfiles.d/postgresql.conf
/etc/postgresql/12/main/postgresql.conf
root@sheservice:~# vi /etc/postgresql/12/main/postgresql.conf

listen_addresses = ‘*’

2. 源码安装

PostgreSQL11源码安装
参考URL: https://blog.csdn.net/u013457167/article/details/92849453

三、windows下安装以及操作

官网下载即可 https://www.postgresql.org/

1. 在PostgresSQL的安装过程中，默认用户postgres已经被创建。

2. 使用PSQL命令创建数据库，如下直接 搜 sql shell
   
   创建数据库时推荐将编码、使用地区等都一并标记，例如

CREATE DATABASE my_db_miniapp ENCODING 'UTF8';  

将数据库 my_db_miniapp 授予某个用户:

GRANT ALL PRIVILEGES ON DATABASE my_db_miniapp TO bot_user;  

将 bot_user 替换为你要授予权限的用户名。

如果需要,还可以使用以下命令将用户设置为数据库 my_db_miniapp 的所有者:

ALTER DATABASE my_db_miniapp OWNER TO bot_user;  

四、centos下PostgreSQL数据库安装配置

1. 源码编译安装

[推荐阅读]PostgreSQL的安装与配置
参考URL: http://www.hangdaowangluo.com/archives/2549
Linux环境Postgresql10源码安装
参考URL: https://blog.csdn.net/weixin_42009082/article/details/106717399

wget 压缩包网址是：https://www.postgresql.org/ftp/source/

1. 下载，解压缩

tar -zxvf postgresql-10.6.tar.gz

2. 编译
   cd到 解压缩后的目录
   ./configure --help 可以查看./configure 后跟的参数代表什么意思
   比如，我的安装目录是：

 ./configure --prefix=/usr/local/pgsql/ --with-openssl --with-segsize=4 --with-blocksize=16 --with-wal-segsize=512 --with-wal-blocksize=32 --enable-debug

常用编译选项说明：
–with-pgport ： 指定默认监听端口。后面可以通过配置文件修改。
–with-segsize=4 : 设置段大小，以千兆字节为单位。大表分为多个操作系统文件，每个文件的大小等于段大小
–with-wal-segsize=SEGSIZE
设置WAL段大小，以兆字节为单位。这是WAL日志中每个文件的大小。
调整此大小以控制WAL日志传送的粒度可能很有用。默认大小为16兆字节。该值必须是1到64（兆字节）之间的2的幂.
–with-wal-blocksize=BLOCKSIZE
设置WAL块大小，以千字节为单位。这是WAL日志中的存储和I / O单位。默认值为8千字节.该值必须是1到64（兆字节）之间的2的幂.
–enable-thread-safety

安装编译支持选项readline （安装PSQL的命令行，依赖该包，推荐安装命令行） 和zlib （在PSQL中pg_dump和pg_restort中压缩文档需要用到该包）

检查系统是否安装 readline 包
安装 readline-devel 包

yum install -y readline-devel
yum install -y zlib-devel

gmake install

gmake
显示：All of PostgreSQL successfully made. Ready to install.就可以install了

2. 关于PostgreSQL 自动补齐（readline 库）

PostgreSQL 自动补齐（readline）问题
参考URL: https://blog.csdn.net/weixin_34220623/article/details/89549625

平时我在使用数据库时，一般都是使用命令行操作，PG是我使用最方便的数据库，可以自动补全命令，命令提示，敲出来的命令肯定不会错。
但是在新建的一个系统中发现不能使用该功能，很苦恼，找了一下原因。

原因查找
查了一下原因，发现最初的原因，查看了一下数据库的基本信息

[postgres@erp-db01 ~]$ pg_config

可以发现编译时使用了参数 --without-readline

我们在数据库的安装数据目录bin中可以查看到psql命令的依赖库情况，发现也是缺少readline的。这就是问题所在

[postgres@erp-db28 usr]$ ldd pgsql/bin/psql
    linux-vdso.so.1 =>  (0x00007fffc011c000)
    libpq.so.5 => /usr/pgsql/lib/libpq.so.5 (0x00007fb706579000)
    libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fb70635d000)
    librt.so.1 => /lib64/librt.so.1 (0x00007fb706155000)
    libm.so.6 => /lib64/libm.so.6 (0x00007fb705e53000)
    libc.so.6 => /lib64/libc.so.6 (0x00007fb705a86000)
    libssl.so.10 => /lib64/libssl.so.10 (0x00007fb705814000)
    libcrypto.so.10 => /lib64/libcrypto.so.10 (0x00007fb7053b3000)
    libldap_r-2.4.so.2 => /lib64/libldap_r-2.4.so.2 (0x00007fb705154000)
    /lib64/ld-linux-x86-64.so.2 (0x00007fb7067bf000)
    libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007fb704f07000)
    libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007fb704c1e000)
    libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007fb704a1a000)
    libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007fb7047e7000)
    libdl.so.2 => /lib64/libdl.so.2 (0x00007fb7045e3000)
    libz.so.1 => /lib64/libz.so.1 (0x00007fb7043cd000)
    liblber-2.4.so.2 => /lib64/liblber-2.4.so.2 (0x00007fb7041be000)
    libresolv.so.2 => /lib64/libresolv.so.2 (0x00007fb703fa5000)
    libsasl2.so.3 => /lib64/libsasl2.so.3 (0x00007fb703d88000)
    libssl3.so => /lib64/libssl3.so (0x00007fb703b36000)
    libsmime3.so => /lib64/libsmime3.so (0x00007fb70390f000)
    libnss3.so => /lib64/libnss3.so (0x00007fb7035e2000)
    libnssutil3.so => /lib64/libnssutil3.so (0x00007fb7033b2000)
    libplds4.so => /lib64/libplds4.so (0x00007fb7031ae000)
    libplc4.so => /lib64/libplc4.so (0x00007fb702fa9000)
    libnspr4.so => /lib64/libnspr4.so (0x00007fb702d6b000)
    libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007fb702b5b000)
    libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007fb702957000)
    libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007fb702720000)
    libselinux.so.1 => /lib64/libselinux.so.1 (0x00007fb7024f9000)
    libfreebl3.so => /lib64/libfreebl3.so (0x00007fb7022f6000)
    libpcre.so.1 => /lib64/libpcre.so.1 (0x00007fb702094000)

readline 是一个强大的库，只要使用了它的程序，都可以用同一个配置文件配置，而且用同样的方法操作命令行，让你可以方便的编辑命令行

问题解决
这个问题的解决方法最好的就是重新编译数据库了。

首先需要把相关的依赖库安装

yum install readline
yum install readline-devel

在其他编译条件不变的情况下增加with-readline参数，编译安装
（在重新编译的时候，记得一定要将原来的编译环境make clean，不然会以原来的参数编译）
完成之后查看编译好的psql命令，可以发现是有readline了

[root@erp-db27 pgsql-10-text]# ldd bin/psql
    linux-vdso.so.1 =>  (0x00007ffd87dd4000)
    libpq.so.5 => /usr/pgsql-10-text/lib/libpq.so.5 (0x00007f05b3565000)
    libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f05b3349000)
    libreadline.so.6 => /lib64/libreadline.so.6 (0x00007f05b3103000)     <<<<<<<这里
    librt.so.1 => /lib64/librt.so.1 (0x00007f05b2efb000)
    libm.so.6 => /lib64/libm.so.6 (0x00007f05b2bf9000)
    libc.so.6 => /lib64/libc.so.6 (0x00007f05b282c000)
    libssl.so.10 => /lib64/libssl.so.10 (0x00007f05b25ba000)
    libcrypto.so.10 => /lib64/libcrypto.so.10 (0x00007f05b2159000)
    libldap_r-2.4.so.2 => /lib64/libldap_r-2.4.so.2 (0x00007f05b1efa000)
    /lib64/ld-linux-x86-64.so.2 (0x00007f05b37ab000)
    libtinfo.so.5 => /lib64/libtinfo.so.5 (0x00007f05b1cd0000)
    libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f05b1a83000)
    libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f05b179a000)
    libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f05b1596000)
    libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f05b1363000)
    libdl.so.2 => /lib64/libdl.so.2 (0x00007f05b115f000)
    libz.so.1 => /lib64/libz.so.1 (0x00007f05b0f49000)
    liblber-2.4.so.2 => /lib64/liblber-2.4.so.2 (0x00007f05b0d3a000)
    libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f05b0b21000)
    libsasl2.so.3 => /lib64/libsasl2.so.3 (0x00007f05b0904000)
    libssl3.so => /lib64/libssl3.so (0x00007f05b06b2000)
    libsmime3.so => /lib64/libsmime3.so (0x00007f05b048b000)
    libnss3.so => /lib64/libnss3.so (0x00007f05b015e000)
    libnssutil3.so => /lib64/libnssutil3.so (0x00007f05aff2e000)
    libplds4.so => /lib64/libplds4.so (0x00007f05afd2a000)
    libplc4.so => /lib64/libplc4.so (0x00007f05afb25000)
    libnspr4.so => /lib64/libnspr4.so (0x00007f05af8e7000)
    libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f05af6d7000)
    libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f05af4d3000)
    libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f05af29c000)
    libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f05af075000)
    libfreebl3.so => /lib64/libfreebl3.so (0x00007f05aee72000)
    libpcre.so.1 => /lib64/libpcre.so.1 (0x00007f05aec10000)

此时发现数据库也可以正常使用各种快捷命令了

centos8 报错 /bin/psql: error while loading shared libraries: libreadline.so.6: cannot open shared object file: No such file or directory

背景：
之前有一个在centos 7上正常运行的程序， 搬到一个centos 8的机器上以后，报错

error while loading shared libraries: libreadline.so.6: cannot open shared object file: No such file or directory

原因分析：
这个报错是因为libreadline.so.6的版本过时了，centos8 : yum install readline-devel 都是装7的版本。

修改思路：
将7做软连接为6就行。 或者 在centos8上重新编译你的程序。

五、编译安装常见问题

1. 如何查看已经编译的pg 当时编译的编译选项

进入到编译安装目录bin下，执行 ./pg_config 即可看到！

2. 编译选项会影响数据，不同编译选项最好，重新初始化数据库

The database cluster was initialized with RELSEG_SIZE 1048576, but the server was compiled with RELSEG_SIZE 8388608
参考URL: https://www.cnblogs.com/xiaotengyi/p/7517311.html

问题背景：
之前的编译选项：
./configure --prefix=/opt/postgresql --enable-debug

新的编译选项：
./configure --prefix=/opt/postgresql --with-pgport=8916 --with-segsize=4 --with-wal-segsize=64 --with-wal-blocksize=64 --enable-thread-safety
经过验证，编译选项会影响数据，不同编译选项最好，重新初始化数据库。

想通过复用文件的形式，复用数据库，结果pg启动，报错：
解决方法：

重新初始化数据库 或 使用 之前编译选项。 亲测通过！

其中最重要的是关于数据大小的几个：

--with-blocksize=BLOCKSIZE
                          set table block size in kB [8]
  --with-segsize=SEGSIZE  set table segment size in GB [1]
  --with-wal-blocksize=BLOCKSIZE
                          set WAL block size in kB [8]
  --with-wal-segsize=SEGSIZE
                          set WAL segment size in MB [16]

如有一个不一致，数据库程序将无法启动数据库。

编译数据库后的参数可以通过pg_controldata和pg_config查看。

六、常用命令

安装完成后，pg创建数据库目录和初始化

psql -h 127.0.0.1 -p 5432 -U postgres （如果是默认地址也可不用添加后面的参数直接使用./(pgsql路径)/bin/psql进入控制台）

创建pgsql数据库地址

mkdir -p /pgdata/12/data

修改pgsql数据库用户组：

chown -R postgres.postgres /pgdata/

只有 postgres 账号具有独写权限

chmod 0700 /pgdata/12/data

初始化数据库目录

initdb  -E UTF-8 -D /pgdata/12/data --locale=zh_CN.UTF-8

-W --期间设置下数据库的超级密码
-E UTF-8 支持中文

默认情况下，数据目录为空，我们需要先对数据目录进行初始化才能生成配置文件及其他相关目录, 必须切换到postgres用户才能初始化

切换用户到：

su postgresql

ubuntu pg初始化数据库报错：initdb: error: invalid locale name “zh_CN.UTF-8”

PostgreSQL initdb报错：先是：initdb: invalid locale name “zh_CN.UTF-8” 然后是： initdb: invalid locale settings; check LANG and LC_* environment variables
参考URL: https://www.cnblogs.com/kuang17/p/13164266.html

问题背景：
安装PostgreSQL时， initdb报错：
initdb: invalid locale name “zh_CN.UTF-8”

问题分析：
原因是在initdb时指定了编码为字符集为zn_CN.UTF-8，而系统没有这个编码导致的

查看当前安装的所有语言包
locale -a
查看是否有：
zh_CN.utf8 # 中文语言包
en_US.utf8 # 英文语言包（默认一般都会安装）

问题解决：
如果没有 en_US.utf8 ，需要安装英文文语言包，输入以下命令：

sudo apt-get install language-pack-en

然后添加英文支持：

locale-gen en_US.UTF-8

如果没有 zh_CN.utf8 ，需要安装中文语言包，输入以下命令：

sudo apt-get install language-pack-zh-hans

然后添加中文支持

locale-gen zh_CN.UTF-8

Ubuntu完整卸载postgresql

Ubuntu完整卸载postgresql
https://blog.csdn.net/qq_26656329/article/details/78562310

六、参考

ubuntu 下安装配置postgres sql 数据库
参考URL: https://blog.csdn.net/weixin_44763552/article/details/108832658