---

一、PostgreSQL简介

PostgreSQL数据库是目前功能强大的开源数据库，支持丰富的数据类型(如JSON和JSONB类型、数组类型)和自定义类型。而且他提供了丰富的接口，可以很容易的扩展它的功能，如可以再GiST框架下实现自己的索引类型等。PostgreSQL是完全的事务安全性数据库，完整地支持外键、联合、视图、触发器和存储过程（并支持多种语言开发存储过程）。它支持了大多数的SQL:2008标准的数据类型，包括整型、数值值、布尔型、字节型、字符型、日期型、时间间隔型和时间型，它也支持存储二进制的大对像，包括图片、声音和视频。PostgreSQL对很多高级开发语言有原生的编程接口，如C/C++、Java、.Net、Perl、Python、Ruby、Tcl 和ODBC以及其他语言等，也包含各种文档。

从技术角度来讲，PostgreSQL 采用的是比较经典的C/S（client/server）结构，也就是一个客户端对应一个服务器端守护进程的模式，这个守护进程分析客户端来的查询请求，生成规划树，进行数据检索并最终把结果格式化输出后返回给客户端。为了便于客户端的程序的编写，由数据库服务器提供了统一的客户端 C 接口。而不同的客户端接口都是源自这个 C 接口，比如ODBC，JDBC，Python，Perl，Tcl，C/C++，ESQL等， 同时也要指出的是，PostgreSQL 对接口的支持也是非常丰富的，几乎支持所有类型的数据库客户端接口。这一点也可以说是 PostgreSQL 一大优点。

PostgreSQL强壮的一个原因源于它的架构。和商业数据库一样，PostgreSQL可以用于C/S(客户/服务器)环境。这对于用户和开发人员有很多好处。

二、Linux 上安装 PostgreSQL

linux下安装PostgreSQL可采用三种方式，二进制已编绎安装包、yum安装、源码安装三种方式进行安装

1.二进制已编绎安装包方式安装

(1)下载二进制包

https://www.enterprisedb.com/download-postgresql-binaries
https://get.enterprisedb.com/postgresql/postgresql-10.22-1-linux-x64-binaries.tar.gz

(2)创建postgres用户

#创建用户
useradd postgres
#设置密码
passwd postgres

(3)解压

tar -xvf postgresql-10.12-1-linux-x64-binaries.tar.gz -C /home/postgres/

(4)创建data目录

mkdir -p /home/postgres/pgsql/data
mkdir -p /home/postgres/pgsql/logs

(5)初始化

./bin/initdb -E utf8 -D /home/postgres/pgsql/data

初始化结果如下：

[postgres@localhost pgsql]$ ./bin/initdb -E utf8 -D /home/postgres/pgsql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /home/postgres/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... PRC
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    ./bin/pg_ctl -D /home/postgres/pgsql/data -l logfile start

(6)启动

./bin/pg_ctl -D /home/postgres/pgsql/data -l /home/postgres/pgsql/logs/pgsql.log start

# 重启
./pg_ctl -D /home/postgres/pgsql/data restart 

(7)关闭

./bin/pg_ctl -D /home/postgres/pgsql/data stop

(8)登录postgresql数据库

./psql

(9)创建用户和数据库并授权

create user test_user with password '123456'; // 创建用户
create database test_db owner test_user; // 创建数据库
grant all privileges on database test_db to test_user; // 授权

(10)退出psql（输入 \q 再按回车键即可）

\q

(11)连接数据库

./bin/psql -h 127.0.0.1 -d test_db -U test_user -p 5432

(12)开启远程访问

####修改postgresql.conf文件，取消 listen_addresses 的注释，将参数值改为“*”

####修改pg_hba.conf文件，增加下图红框部分内容
host    all             all             0.0.0.0/0            md5

####navicat
https://www.cnblogs.com/zhi-leaf/p/11432054.html

2.yum安装PostgreSQL

(1)安装过程官网参考

https://www.postgresql.org/download/linux/redhat/

(2)安装过程步骤

# Install the repository RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL:
sudo yum install -y postgresql10-server

## 采用ln -s方式挂载/var/lib/pgsql/10/data目录用来修改数据目录,以/home/postgresdata为例
mkdir /home/postgresdata -p
chown -R postgres:postgres /home/postgresdata
rm -rf /var/lib/pgsql/10/data
ln -s /home/postgresdata /var/lib/pgsql/10/data
chown -R postgres:postgres /var/lib/pgsql/10/data

# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-10/bin/postgresql-10-setup initdb
sudo systemctl enable postgresql-10
sudo systemctl start postgresql-10

3.源码方式安装PostgreSQL

第三种安装方式：通过源码安装
部分内容参考了此文https://www.cnblogs.com/wsum/p/15522211.html

(1)下载源码

##下载地址和源码
https://www.postgresql.org/download/
postgresql-14.5.tar.gz

(2)创建postgres用户

#创建用户
useradd postgres
#设置密码
passwd postgres

(3)进行源码安装

以安装到/opt/postgresql目录下为例

##1.解压
tar -xvf postgresql-14.5.tar.gz -C /opt/

##2.yum依赖
yum install -y gcc gcc-c++
yum install -y readline-devel
yum install -y zlib-devel

##3.编绎，并安装到/opt/postgresql目录
mkdir /opt/postgresql
cd /opt/postgresql-14.5
./configure --prefix=/opt/postgresql
make
make install

#4.准备数据目录
mkdir -p /opt/postgresql/pgsqldata
chown -R postgres:postgres /opt/postgresql/pgsqldata

#5.切换到postgres用户
su postgres
/opt/postgresql/bin/initdb -D /opt/postgresql/pgsqldata #初始化数据库
mkdir /opt/postgresql/pgsqldata/logs
/opt/postgresql/bin/pg_ctl -D /opt/postgresql/pgsqldata -l /opt/postgresql/pgsqldata/logs/pgsql.log start #启动
/opt/postgresql/bin/createdb test #创建测试库
/opt/postgresql/bin/psql test #进入数据库

#6.修改管理员密码
ALTER USER postgres WITH PASSWORD '123456';

(4)设置postgresql 服务自启动

如果需要随开机启机，可以制作成自启动服务如下：

######postgresql 服务
cat > /usr/lib/systemd/system/postgresql.service << EOF
[Unit]
Description=postgreSQL Server
After=network.target

[Service]
User=postgres
Group=postgres
Type=forking
TimeoutSec=0
PermissionsStartOnly=true
PIDFile=/opt/postgresql/pgsqldata/postmaster.pid
ExecStart=/opt/postgresql/bin/pg_ctl -D /opt/postgresql/pgsqldata -l /opt/postgresql/pgsqldata/logs/pgsql.log start
ExecReload=/opt/postgresql/bin/pg_ctl -D /opt/postgresql/pgsqldata reload
ExecStop=/opt/postgresql/bin/pg_ctl -D /opt/postgresql/pgsqldata stop
PrivateTmp=true

LimitNOFILE = 65535
Restart=on-failure
RestartSec=3
RestartPreventExitStatus=1
PrivateTmp=false


[Install]
WantedBy=multi-user.target
EOF

######服务启动停止
systemctl daemon-reload
systemctl stop postgresql
systemctl start postgresql
systemctl enable postgresql

4.docker安装

# 下载镜像
docker pull postgres:15.8

# 创建目录
mkdir /home/liutest/postgresqldata

# 启动镜像
docker run -itd --name postgres \
-v /home/liutest/postgresqldata:/var/lib/postgresql/data \
-p 5432:5432 \
-e POSTGRES_USER=test \
-e POSTGRES_PASSWORD=123456 \
--restart=always postgres:15.8

# 另一种自建docker的方式记录一下（以下的sendi_postgresql:1.0是通过ubuntu:22.04自行构建的镜像，仅记录下，可以不用看这一部分内容）
# 默认使用docker内部目录存储数据
docker run --name sendi_postgresql -p 5432:5432 -itd 192.168.56.1:6666/sendi_postgresql:1.0

# 如果要映射到外部目录存储数据，可以先使用上面的默认方式启动后，再将数据拷贝到宿主机目录，再外载方式启动，就能持久化了
docker run -v /home/liutest/pgsql:/home/liutest/pgsql --name sendi_postgresql -p 5432:5432 -itd  192.168.56.1:6666/sendi_postgresql:1.0  

• 以容器启动的配置文件在哪里

#
docker exec -it my_postgres bash

#
导航到 PostgreSQL 的数据目录（通常是 /var/lib/postgresql/data 或 /var/lib/postgresql/15/data）并编辑 postgresql.conf 文件：

docker run -itd --name postgres \
-v /home/liutest/postgresqldata:/var/lib/postgresql/data \
-p 5432:5432 \
-e POSTGRES_USER=webuser \
-e POSTGRES_PASSWORD=123456 \
--restart=always postgres:15.8

三、基本操作

1.表相关操作

查看当前的数据库列表:

####查看当前的数据库列表
\l 

建表:

####建表
create table test(
   id bigint ,
   name varchar(50),
   age int,
   password varchar(30)
 );

插入与查询

####插入
insert into test values(1,'test',21,'123');

####查询
select * from test;

• 查看数据库编码

SELECT datname, pg_encoding_to_char(encoding) FROM pg_database;

2.创建用户和数据库

未登录终端时：

# 通过 createuser 命令创建一个新的数据库用户。你可以使用以下命令创建一个名为 testuser 的用户
createuser testuser

# 如果希望testuser要密码登录，先登录终端，执行更新密码操作，再退出就需要密码登录了
ALTER USER username PASSWORD 'new_password';

# 如果你想要创建一个需要密码登录的用户
# 你可以使用 createuser 命令的 -P 或 --pwprompt 选项。
# 这样会在创建用户时提示你输入密码，并将密码加密后存储在系统中。创建的用户将需要提供正确的密码才能登录
createuser -P testuser
createuser --pwprompt testuser

# 创建数据库：使用 createdb 命令创建一个新的数据库。
# 假设你想要创建一个名为 testdb 的数据库，并且将其所有者设置为 testuser，你可以使用以下命令
createdb -O testuser testdb

已登录时：

create database liutest;

3.命令行进入数据库

cd /home/liutest/pgsql/bin
./psql -h 127.0.0.1 -U liutest -d liutestdb

其中 -d后面的是数据库名，-U后面的为用户名，-p 指定端口号
输入密码进入

其他

psql: FATAL: Ident authentication failed for user

#
vi /var/lib/pgsql/10/data/pg_hba.conf 

将localhost和127.0.0.1的两行的ident修改成trust，然后重启服务

postgresql限制所有用户都需要登录才成访问

要限制所有用户都需要登录才能访问 PostgreSQL 数据库，你可以通过修改 PostgreSQL 的 pg_hba.conf 文件来实现。pg_hba.conf 文件用于定义 PostgreSQL 的客户端身份验证规则。
下面是具体的步骤

• 找到 pg_hba.conf 文件,并找到以下类似的条目

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident

这些条目定义了客户端对 PostgreSQL 数据库的访问规则。在默认情况下，local 条目使用 peer 方法，这意味着允许用户通过 Unix 套接字连接到数据库，并且使用操作系统用户进行身份验证。host 条目使用 ident 方法，这意味着允许通过 IPv4 地址连接到数据库，并且使用操作系统用户进行身份验证。

• 修改访问规则

将 local 和 host 条目中的 peer 和 ident 方法修改为 md5，表示使用 md5 密码进行身份验证。修改后的条目应该类似于：

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

• 重新加载 PostgreSQL 配置

pg_ctl reload -D /path/to/your/data/directory

注意将 /path/to/your/data/directory 替换为你的 PostgreSQL 数据目录的实际路径。

最大连接数修改

查看

SHOW max_connections;

编辑 postgresql.conf 文件

max_connections = 2000

允许远程服务器连接

修改postgresql.conf

#listen_addresses = 'localhost'

修改为 * 表示允许所有 IP 地址连接，或者指定具体的 IP 地址
listen_addresses = '*'
listen_addresses = '192.168.1.100'

pg_hba.conf

在 pg_hba.conf 文件中添加一条规则，允许指定的远程 IP 地址范围访问数据库。根据你的需求，选择合适的认证方法（如 md5 密码认证）。
例如，允许 IP 地址为 192.168.1.100 的主机访问数据库：

host    all             all             192.168.1.100/32    md5

host    all             all             0.0.0.0/0    md5

这条规则的含义是，允许 IP 地址为 192.168.1.100 的主机使用密码（md5 方法）认证访问所有数据库的所有用户。将 /32 替换为具体的子网掩码。

类似mysql中的\G效果

使用 \x 命令来切换展示模式，将查询结果以竖直方式显示，想回退用横模式，再执行一次\x即可

\x

查看 PostgreSQL 当前时区

SHOW timezone;

设置 PostgreSQL 时区

• 方法 1: 在启动容器时设置时区

docker run -itd --name postgres \
  -v /home/liutest/postgresqldata:/var/lib/postgresql/data \
  -p 5432:5432 \
  -e TZ=Asia/Shanghai \
  --restart=always \
  postgres:15.8

• 方法 2: 修改 PostgreSQL 配置文件

# 你可以通过修改 postgresql.conf 文件中的 timezone 参数来设置时区。

然后，找到 postgresql.conf 文件（通常在 /var/lib/postgresql/data 目录下）并编辑它：
vi /var/lib/postgresql/data/postgresql.conf

找到或添加以下行，设置时区为 Asia/Shanghai：
timezone = 'Asia/Shanghai'

保存并退出编辑器，然后重启 PostgreSQL 以使更改生效：

• 方法 3: 使用 SQL 命令设置时区

# 如果不想修改配置文件，你也可以通过 SQL 命令在运行时设置时区：
SET TIMEZONE='Asia/Shanghai';

# 要将其永久设置为默认时区，可以使用：
ALTER DATABASE your_database_name SET timezone TO 'Asia/Shanghai';

或者
ALTER USER your_username SET timezone TO 'Asia/Shanghai';