简介

PostgreSQL 是一种开源的关系型数据库管理系统，它具有强大的功能和可扩展性，被广泛应用于各种领域，如Web开发、科学计算、数据分析等。

它具有以下特点：

支持 SQL 标准：PostgreSQL 是一个符合 SQL 标准的关系型数据库，这意味着你可以使用 SQL 语言来操作数据，查询、插入、更新、删除等操作都可以通过 SQL 语句来实现。
数据可靠性高：PostgreSQL 通过事务、ACID 兼容性等技术来保证数据的一致性和可靠性，确保数据在操作过程中不会出现丢失或损坏的情况。
可扩展性强：PostgreSQL 支持多种扩展方式，例如插件扩展、C扩展等，可以轻松地增加新的功能和性能。
支持多种数据类型：PostgreSQL 支持多种数据类型，包括整数、浮点数、日期、时间等，还支持自定义数据类型，可以满足不同场景下的需求。
高性能：PostgreSQL 通过多种优化技术来提高性能，例如索引、缓存、查询优化等，可以应对大规模数据的处理和高并发访问。
开源免费：PostgreSQL 是开源的数据库管理系统，可以免费使用和修改，并且有一个活跃的社区来提供支持和维护。

总的来说，PostgreSQL 是一个功能强大、可靠、可扩展、高性能的关系型数据库管理系统，适用于各种场景下的数据管理和处理需求。

主要还是开源免费吧。

PostgreSQL 和 MySQL 是两种流行的关系型数据库管理系统，它们都有广泛的应用和强大的功能。虽然它们有一些共同之处，但它们之间也存在一些显著的区别。

以下是一些 PostgreSQL 和 MySQL 的主要差别：

数据完整性：PostgreSQL 在数据完整性方面更严格，它支持更高级别的完整性约束，如外键约束、唯一性约束等。而 MySQL 在此方面的支持相对较弱。
数据类型：PostgreSQL 支持更多的数据类型，如数组、范围、全文搜索等，这使得它在处理复杂数据类型时更为灵活。而 MySQL 的数据类型相对较少。
事务支持：PostgreSQL 支持完整的事务模型，包括 ACID 兼容性和隔离级别，这使得它在需要高可靠性和数据一致性的场景下更为适用。而 MySQL 的事务支持相对较弱。
扩展性：虽然两者都具有一定程度的可扩展性，但 PostgreSQL 更具优势。它支持更多的扩展功能，如物化视图、触发器、规则等，并且通常不需要修改源代码即可实现扩展。而 MySQL 的扩展性相对较差。
查询优化器：PostgreSQL 的查询优化器更为强大和灵活，能够更好地处理复杂查询和数据索引。而 MySQL 的查询优化器相对较简单。
社区和支持：MySQL 是由甲骨文公司开发和维护的商业软件，而 PostgreSQL 则是一个开源项目，拥有一个广泛的社区支持和丰富的开发资源。

总的来说，PostgreSQL 和 MySQL 都是优秀的数据库管理系统，具有各自的优势和适用场景。选择哪种数据库取决于你的具体需求和项目要求。如果你需要更严格的数据完整性、更丰富的数据类型和更好的扩展性，那么 PostgreSQL 可能更适合你；而如果你更关注查询性能和简洁的软件架构，MySQL 可能更适合你。

字段的自增逻辑

表里面的自增逻辑是通过定义了一个sequence。

nextval('user_uid_seq'::regclass)

ALTER SEQUENCE public.user_uid_seq
	INCREMENT BY 1
	RESTART 1;

在 Docker 中安装和运行 PostgreSQL 非常简单。你可以通过以下步骤来完成：

1. 确保已经安装 Docker

首先，请确保你的系统上已经安装了 Docker。如果还没有安装，可以从 Docker 官方网站下载并安装。

2. 拉取 PostgreSQL 镜像

在终端中运行以下命令来拉取官方的 PostgreSQL Docker 镜像：

1	docker pull postgres

3. 运行 PostgreSQL 容器

使用以下命令启动一个 PostgreSQL 容器。你可以指定数据库名称、用户名和密码等配置。

1	docker run --name my-postgres-container -e POSTGRES_PASSWORD=mysecretpassword -e POSTGRES_USER=myuser -e POSTGRES_DB=mydatabase -p 5432:5432 -d postgres

参数说明：

--name my-postgres-container：为你的 PostgreSQL 容器命名。
-e POSTGRES_PASSWORD=mysecretpassword：设置 PostgreSQL 用户的密码。
-e POSTGRES_USER=myuser：设置 PostgreSQL 用户名（可选，默认为 postgres）。
-e POSTGRES_DB=mydatabase：设置数据库名称（可选，默认与用户名相同）。
-p 5432:5432：将容器的 5432 端口映射到主机的 5432 端口，以便你可以从主机上访问数据库。
-d postgres：在后台运行容器，并使用 postgres 镜像。

4. 连接到 PostgreSQL 数据库

启动容器后，你可以通过以下方式连接到 PostgreSQL 数据库：

使用 `psql` 客户端

如果你有 psql 客户端工具，可以在终端中运行以下命令进行连接：

1	psql -h localhost -U myuser -d mydatabase

系统将提示你输入密码，然后你就可以开始操作数据库了。

使用 Docker 容器内的 `psql`

你也可以进入容器内部并使用容器内的 psql 工具：

1	docker exec -it my-postgres-container psql -U myuser -d mydatabase

5. 停止和移除 PostgreSQL 容器

如果你想停止运行的 PostgreSQL 容器，可以使用以下命令：

1	docker stop my-postgres-container

要删除这个容器，可以运行：

1	docker rm my-postgres-container

这就是如何使用 Docker 安装并运行 PostgreSQL 数据库的全部步骤。

在 Docker 中安装和运行 PostgreSQL 非常简单。你可以通过以下步骤来完成：

1. 确保已经安装 Docker

首先，请确保你的系统上已经安装了 Docker。如果还没有安装，可以从 Docker 官方网站下载并安装。

2. 拉取 PostgreSQL 镜像

在终端中运行以下命令来拉取官方的 PostgreSQL Docker 镜像：

1	docker pull postgres

3. 运行 PostgreSQL 容器

使用以下命令启动一个 PostgreSQL 容器。你可以指定数据库名称、用户名和密码等配置。

1	docker run --name my-postgres-container -e POSTGRES_PASSWORD=mysecretpassword -e POSTGRES_USER=myuser -e POSTGRES_DB=mydatabase -p 5432:5432 -d postgres

参数说明：

--name my-postgres-container：为你的 PostgreSQL 容器命名。
-e POSTGRES_PASSWORD=mysecretpassword：设置 PostgreSQL 用户的密码。
-e POSTGRES_USER=myuser：设置 PostgreSQL 用户名（可选，默认为 postgres）。
-e POSTGRES_DB=mydatabase：设置数据库名称（可选，默认与用户名相同）。
-p 5432:5432：将容器的 5432 端口映射到主机的 5432 端口，以便你可以从主机上访问数据库。
-d postgres：在后台运行容器，并使用 postgres 镜像。

4. 连接到 PostgreSQL 数据库

启动容器后，你可以通过以下方式连接到 PostgreSQL 数据库：

使用 `psql` 客户端

如果你有 psql 客户端工具，可以在终端中运行以下命令进行连接：

1	psql -h localhost -U myuser -d mydatabase

系统将提示你输入密码，然后你就可以开始操作数据库了。

使用 Docker 容器内的 `psql`

你也可以进入容器内部并使用容器内的 psql 工具：

1	docker exec -it my-postgres-container psql -U myuser -d mydatabase

5. 停止和移除 PostgreSQL 容器

如果你想停止运行的 PostgreSQL 容器，可以使用以下命令：

1	docker stop my-postgres-container

要删除这个容器，可以运行：

1	docker rm my-postgres-container

这就是如何使用 Docker 安装并运行 PostgreSQL 数据库的全部步骤。

这个错误消息表明你试图连接到 PostgreSQL 服务器时，服务器要求使用 SCRAM（Salted Challenge Response Authentication Mechanism）认证方式进行身份验证，但你没有提供密码。

可能的原因

客户端没有提供密码：连接时，你可能没有正确配置密码参数，或者使用的连接工具没有提示你输入密码。
pg_hba.conf 文件配置：服务器配置文件 pg_hba.conf 要求使用 SCRAM 认证，但客户端连接时没有提供密码。

解决方法

1. 提供密码

确保你在连接时提供了密码。可以通过以下方式提供密码：

使用 psql 命令行工具：

当使用 psql 工具时，你可以直接通过命令提供密码：
1
psql -h localhost -U postgres -d your_database
系统会提示你输入密码。如果你希望在命令中直接提供密码，可以使用以下语法，但要注意安全性：
1
PGPASSWORD='your_password' psql -h localhost -U postgres -d your_database
通过连接字符串：

你也可以通过完整的连接字符串来提供密码：
1
psql "host=localhost dbname=your_database user=postgres password=your_password"

2. 确认 `pg_hba.conf` 配置

检查 PostgreSQL 的 pg_hba.conf 文件，确保你使用的认证方式是符合你预期的。以下是配置示例：

1 2	# TYPE DATABASE USER ADDRESS METHOD local all all scram-sha-256

METHOD 列指定了认证方式。为了简化测试，你可以将其修改为 trust（临时禁用密码认证），然后重启 PostgreSQL 服务进行测试：

1	local all all trust

重启 PostgreSQL 服务：

1	sudo systemctl restart postgresql

修改密码或完成其他设置后，再将 trust 改回为 scram-sha-256 或其他更安全的认证方式。

3. 设置或修改用户密码

如果你还没有为 PostgreSQL 用户设置密码，可以通过以下命令进行设置：

切换到 postgres 用户：
1
sudo -i -u postgres
进入 psql 命令行工具：
1
psql

设置密码：

1	ALTER USER postgres PASSWORD 'your_password';

退出 psql：
1
\q
退出 postgres 用户：
1
exit

这样，用户密码就会被正确设置，连接时不会再出现缺少密码的错误。

要在 Ubuntu 中卸载 PostgreSQL，可以按照以下步骤操作：

1. 停止 PostgreSQL 服务

在卸载之前，最好先停止 PostgreSQL 服务：

sudo systemctl stop postgresql
sudo systemctl start postgresql
sudo systemctl status postgresql
sudo systemctl enable postgresql
sudo systemctl cat postgresql

2. 卸载 PostgreSQL 包

使用 apt 命令卸载 PostgreSQL 及其相关的包：

1	sudo apt-get --purge remove postgresql postgresql-client postgresql-contrib

这将卸载 PostgreSQL 服务器、客户端和扩展工具。

3. 删除数据目录和配置文件

即使卸载了 PostgreSQL，数据目录和配置文件可能仍然存在。如果你希望完全清除 PostgreSQL，可以删除这些目录：

1	sudo rm -rf /etc/postgresql /var/lib/postgresql /var/log/postgresql

/etc/postgresql：存放 PostgreSQL 配置文件。
/var/lib/postgresql：存放 PostgreSQL 数据库数据。
/var/log/postgresql：存放 PostgreSQL 日志文件。

4. 删除用户和组（可选）

如果你不再需要 postgres 用户和组，可以选择删除它们：

1 2	sudo deluser postgres sudo delgroup postgres

5. 清理无用的依赖包

为了确保系统干净，可以使用以下命令清理系统中未使用的依赖包：

1 2	sudo apt-get autoremove sudo apt-get autoclean

6. 检查是否完全卸载

你可以通过以下命令检查是否完全卸载 PostgreSQL：

1	psql --version

如果出现 command not found 错误，说明 PostgreSQL 已被完全卸载。

通过以上步骤，你可以在 Ubuntu 系统中完全卸载 PostgreSQL。

在 macOS 上安装 PostgreSQL 有几种方法，最常见的是通过 Homebrew 来安装。以下是通过 Homebrew 安装 PostgreSQL 的步骤：

1. 安装 Homebrew

如果你还没有安装 Homebrew，可以使用以下命令来安装：

1	/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

安装完成后，确保将 Homebrew 添加到你的路径中（系统会提示你执行相关命令）。

2. 更新 Homebrew

安装完 Homebrew 后，先更新它的包列表：

1	brew update

3. 安装 PostgreSQL

使用 Homebrew 安装 PostgreSQL：

1	brew install postgresql

4. 启动 PostgreSQL 服务

安装完成后，可以启动 PostgreSQL 服务：

1	brew services start postgresql

这将使 PostgreSQL 作为后台服务运行，并在系统重启后自动启动。

如果你只想手动启动 PostgreSQL，可以运行以下命令：

1	pg_ctl -D /usr/local/var/postgres start

5. 初始化数据库（如果必要）

在某些情况下，Homebrew 会自动初始化数据库。如果没有自动完成，你可以手动执行初始化：

1	initdb /usr/local/var/postgres

6. 连接到 PostgreSQL

安装和启动 PostgreSQL 后，可以使用 psql 命令行工具连接到数据库：

1	psql postgres

这将连接到默认的 postgres 数据库，并进入 PostgreSQL 的命令行模式。

7. 创建一个新用户和数据库

如果你想创建一个新用户和数据库，可以在 psql 中执行以下命令：

1
2
3

CREATE USER myuser WITH PASSWORD 'mypassword';
CREATE DATABASE mydb;
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

8. 停止 PostgreSQL 服务

如果需要停止 PostgreSQL 服务，可以使用以下命令：

1	brew services stop postgresql

手动启动

1	/opt/homebrew/opt/postgresql@14/bin/postgres -D /opt/homebrew/var/postgresql@14

总结

通过 Homebrew 安装 PostgreSQL 是 macOS 上最简单和推荐的方法。安装后，你可以通过 Homebrew 管理 PostgreSQL 服务的启动和停止。

手动启动一个postgresql服务器。

如何做定期备份

设置一个密码

1
2
3

~/.pgpass
127.0.0.1:5432:数据库名:用户名:密码

编写脚本：

#!/bin/bash
# 手动备份 PostgreSQL xdex_db 并压缩，只保留最近3个备份

# 配置
HOST="127.0.0.1"
PORT="5432"
USER="usr"
DB="db"
BACKUP_DIR="/root/db_files"  # 备份保存路径
DATE=$(date +%F_%H-%M-%S)
FILENAME="dump_${DB}_${DATE}.sql"
ARCHIVE="${FILENAME}.gz"

# 确保备份目录存在
mkdir -p "$BACKUP_DIR"

# 备份并压缩
pg_dump -h "$HOST" -p "$PORT" -U "$USER" "$DB" | gzip > "${BACKUP_DIR}/${ARCHIVE}"

if [ $? -eq 0 ]; then
    echo "✅ 备份完成: ${BACKUP_DIR}/${ARCHIVE}"

    # 清理，只保留最新的 3 个备份
    ls -1t "${BACKUP_DIR}"/dump_${DB}_*.sql.gz | tail -n +4 | xargs -r rm -f
else
    echo "❌ 备份失败"
    exit 1
fi

每小时备份一次：

1	0 * * * * /bin/bash /root/db_files/pg_backup.sh >> /root/db_files/pg_backup.log 2>&1

在 PostgreSQL 里，JSON 类型家族确实有几个，但你提到的 jsonl 和 jsonp 并不是 PostgreSQL 内置的数据类型。我们可以理清一下：

⸻

1️⃣ json 和 jsonb（官方支持）

类型描述优缺点
json 原始 JSON 文本，存储时保持输入格式（空格、顺序等）读取时需要解析，存储保留原样，写入速度快，但查询和索引慢
jsonb 二进制 JSON，内部以二进制形式存储，字段顺序不保留支持索引和高效查询，存储略慢，但查询和操作效率高

示例：

CREATE TABLE example (
data_json json,
data_jsonb jsonb
);

INSERT INTO example VALUES (
‘{“name”:”Alice”,”age”:25}’,
‘{“name”:”Alice”,”age”:25}’
);

查询：

– json 查询需要用 ->> 获取文本
SELECT data_json->>’name’ FROM example;

– jsonb 查询可以用索引和 @> 操作符
SELECT * FROM example WHERE data_jsonb @> ‘{“age”:25}’;

⸻

2️⃣ jsonl 或 JSON Lines
• 不是 PostgreSQL 内置类型，而是一种文件/文本格式
• 每行是一条完整的 JSON 对象，通常用作日志或批量导入/导出
• PostgreSQL 中可以用 text 或 jsonb 存储每行数据

示例（文件内容）：

{“name”:”Alice”,”age”:25}
{“name”:”Bob”,”age”:30}

⸻

3️⃣ jsonp
• JSONP 是 JavaScript JSON with Padding 的缩写
• 用于浏览器跨域请求，将 JSON 包装在回调函数里返回
• 这不是 PostgreSQL 类型，通常在 Web 接口层处理

示例：

callback({“name”:”Alice”,”age”:25});

⸻

总结
• PostgreSQL 内置 JSON 类型： json / jsonb
• jsonl: 一行一条 JSON 数据的文件格式，可用 text 或 jsonb 存储
• jsonp: 前端跨域用的 JSON 包装格式，不是数据库类型

⸻

无法使用事件方式

pq 无法解析 → 就报 unknown authentication response: 10

将版本升级了就对了；

go: upgraded github.com/lib/pq v1.0.0 => v1.10.9

核心就是：lib/pq 不支持 SCRAM-SHA-256 认证，无论版本，必须使用支持 SCRAM 的驱动（如 pgx）或者把 PostgreSQL 用户改回 md5。

  
reportProblem := func(ev pq.ListenerEventType, err error) {  
    if err != nil {  
       myLog.Errorf("[StartChainEventListener] [Listener Error] Type: %v, Err: %v", ev, err)  
    }  
}  
  
listener := pq.NewListener(w.sqlDSN(), 10*time.Second, time.Minute, reportProblem)  
myLog.Infof("listenPgNotify start listener")  
if err := listener.Listen("order_finished"); err != nil {  
    myLog.Errorln("Listen error:", err)  
    return  
}  
  
myLog.Infof("listenPgNotify start for")  
  
for {  
    select {  
    case <-w.ctx.Done():  
       _ = listener.Close()  
       myLog.Infof("listenPgNotify listener is close")  
       return  
    case n := <-listener.Notify:  
       myLog.Infoln("listenPgNotify <-listener.Notify", n)  
       if n == nil || n.Extra == "" {  
          myLog.Infoln("listenPgNotify n is nil or n.Extra is empty")  
          continue  
       }  
  
       // 解析 JSON 到实体  
       var order entities.Player  
       if err := json.Unmarshal([]byte(n.Extra), &order); err != nil {  
          myLog.Infoln("listenPgNotify json unmarshal error:", err)  
          continue  
       }  
  
       myLog.Infoln("listenPgNotify start handleNewOrder")  
  
       go w.handleNewPlayer(order)  
    }  
}

  
func sqlDSN() string {  
    cfg := cfgModule.Cfg  
  
    if len(cfg.Master) == 0 {  
       return ""  
    }  
  
    host := cfg.host
    user := cfg.Username  
    pass := cfg.Password  
  
    ret := fmt.Sprintf(  
       "postgres://%s:%s@%s:%d/%s?sslmode=disable",  
       user,  
       pass,  
       host,  
       cfg.Port,  
       cfg.DBName,  
    )  
  
    fmt.Println(ret)  
  
    return ret  
}

检查服务器卡住的问题：

先看数据库现在在“干嘛”（最重要）

SELECT pid,
       now() - query_start AS duration,
       state,
       wait_event_type,
       wait_event,
       query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC
LIMIT 20;

判断是不是“锁”导致卡死

SELECT blocked.pid     AS blocked_pid,
       blocked.query   AS blocked_query,
       blocking.pid    AS blocking_pid,
       blocking.query  AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks kl ON kl.locktype = bl.locktype
                AND kl.database IS NOT DISTINCT FROM bl.database
                AND kl.relation IS NOT DISTINCT FROM bl.relation
                AND kl.page IS NOT DISTINCT FROM bl.page
                AND kl.tuple IS NOT DISTINCT FROM bl.tuple
                AND kl.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = kl.pid;

SELECT pg_terminate_backend(<blocking_pid>);

看数据库整体瓶颈类型

1
2
3

SELECT wait_event_type, count(*)
FROM pg_stat_activity
GROUP BY wait_event_type;

找“历史最慢 SQL”（无需 slow log）

SELECT query,
       calls,
       total_time,
       mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

查 IO / 表热点

SELECT relname,
       seq_scan,
       idx_scan,
       n_tup_ins,
       n_tup_upd,
       n_tup_del
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 10;

查缓存命中率（判断是否 IO 瓶颈）

1
2
3

SELECT
  sum(blks_hit) / (sum(blks_hit) + sum(blks_read)) AS cache_hit_ratio
FROM pg_stat_database;

查连接数是否打爆

1 2	SELECT count(*) FROM pg_stat_activity; SHOW max_connections;

简介

字段的自增逻辑

1. 确保已经安装 Docker

2. 拉取 PostgreSQL 镜像

3. 运行 PostgreSQL 容器

4. 连接到 PostgreSQL 数据库

使用 psql 客户端

使用 Docker 容器内的 psql

5. 停止和移除 PostgreSQL 容器

1. 确保已经安装 Docker

2. 拉取 PostgreSQL 镜像

3. 运行 PostgreSQL 容器

4. 连接到 PostgreSQL 数据库

使用 psql 客户端

使用 Docker 容器内的 psql

5. 停止和移除 PostgreSQL 容器

可能的原因

解决方法

1. 提供密码

2. 确认 pg_hba.conf 配置

3. 设置或修改用户密码

1. 停止 PostgreSQL 服务

2. 卸载 PostgreSQL 包

3. 删除数据目录和配置文件

4. 删除用户和组（可选）

5. 清理无用的依赖包

6. 检查是否完全卸载

1. 安装 Homebrew

2. 更新 Homebrew

3. 安装 PostgreSQL

4. 启动 PostgreSQL 服务

5. 初始化数据库（如果必要）

6. 连接到 PostgreSQL

7. 创建一个新用户和数据库

8. 停止 PostgreSQL 服务

手动启动

总结

如何做定期备份

无法使用事件方式

先看数据库现在在“干嘛”（最重要）

判断是不是“锁”导致卡死

看数据库整体瓶颈类型

找“历史最慢 SQL”（无需 slow log）

查 IO / 表热点

查缓存命中率（判断是否 IO 瓶颈）

查连接数是否打爆

使用 `psql` 客户端

使用 Docker 容器内的 `psql`

使用 `psql` 客户端

使用 Docker 容器内的 `psql`

2. 确认 `pg_hba.conf` 配置