介绍

PostgreSQL 是一个功能强大的开源对象关系数据库系统，它使用并扩展了 SQL 语言，并结合了许多功能，可以安全地存储和扩展最复杂的数据工作负载。

来自gpt-chat描述

PostgreSQL 是一个功能强大的开源关系数据库系统，它提供了许多优点，使其在企业和开发者社区中非常受欢迎：

  1. 高度兼容 SQL 标准：PostgreSQL 遵循 SQL 标准非常严格，提供了广泛的 SQL 数据类型和操作，包括复杂的查询、事务完整性和多版本并发控制（MVCC）。
  2. 扩展性和灵活性：它支持大型数据库和复杂的查询操作，同时提供了高度的可扩展性和定制性。用户可以通过添加自定义函数、数据类型、索引类型等来扩展其功能。
  3. 强大的数据完整性：PostgreSQL 支持主键、外键、唯一约束和检查约束等，这些都是确保数据完整性的重要工具。
  4. 支持多种编程语言：PostgreSQL 可以与多种编程语言集成，包括 Python、Java、C++、Ruby、Perl 等，这使得它在不同的应用程序和开发环境中都能很好地工作。
  5. 高级功能：它提供了许多高级数据库功能，如存储过程、触发器、视图、事务、并行查询等。
  6. 开源和社区支持：作为一个开源项目，PostgreSQL 有一个活跃的开发和用户社区，提供了丰富的文档、教程和第三方工具，以及及时的错误修复和功能更新。
  7. 安全性：PostgreSQL 提供了强大的安全机制，包括角色基的权限系统、SSL 支持和行级安全性。
  8. 成本效益：作为一个开源解决方案，使用 PostgreSQL 不需要支付高昂的许可费用，这对于预算有限的项目或希望降低成本的企业来说是一个很大的优势。

这些特点使得 PostgreSQL 成为企业级应用、数据仓库、数据分析和在线事务处理等多种场景的理想选择。

环境

操作系统：conetOS 7

软件版本：postgresql-16.3

步骤

官方网站

PostgreSQL: The world's most advanced open source database

-官方安装文档

PostgreSQL: Downloads

国内中文文档

文档目录/Document Index: 世界上功能最强大的开源数据库...

runoob教程

PostgreSQL 语法 | 菜鸟教程

官方下载地址

PostgreSQL: Linux downloads (Red Hat family)

安装方法一

通过官方RPM包安装

# 安装 RPM 存储库：
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 安装 PostgreSQL：
sudo yum install -y postgresql16-server

# 初始化数据库并启用自动启动：
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16

安装方法二

通过Red Hat系统发行版存储库安装

要从这些存储库安装 PostgreSQL，请在 RHEL 6 和 7 上 使用yum命令：

yum install postgresql-server

或者在 RHEL 8 和 Fedora 上使用 dnf命令：

dnf install postgresql-server

安装方法三（推荐）

源码编译安装

# 下载
wget https://ftp.postgresql.org/pub/source/v16.3/postgresql-16.3.tar.gz

# 解压到指定目录
tar -zxvf postgresql-16.3.tar.gz -C /opt/postgresql-16.3

编译安装

参见：http://www.postgres.cn/docs/12/install-short.html

# 检查系统环境与扫描依赖包
./configure

# 编译
make
su

# 自动构建并安装
make install

# 添加postgres账户（注意postgres账户未设置密码，生产请设置）
adduser postgres

# 创建data目录
mkdir /usr/local/pgsql/data

# 涉权data目录给postgres账户
chown postgres /usr/local/pgsql/data

# 切换到postgres账户
su - postgres

初始化安装与生成数据目录

/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

安装日志

[postgres@centos72 ~]$ /usr/local/pgsql/bin/initdb -D /usr/local/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 database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: 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:

    /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

安装成功后启动pgsql

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

[postgres@centos72 ~]$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
waiting for server to start.... done
server started

创建测试DB实例

/usr/local/pgsql/bin/createdb test

进入pgsql并定位到命名为test的DB实例

[postgres@centos72 ~]$ /usr/local/pgsql/bin/psql test
psql (16.3)
Type "help" for help.

test=# 
test=# help 
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
test=# select version();
                                                version                                                 
--------------------------------------------------------------------------------------------------------
 PostgreSQL 16.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190311 (Red Hat 8.3.1-3), 64-bit
(1 row)

通过\h可以查看SQL帮助，\q退出当前SQL窗口，\c 连接其它db实例，select version(); 查看安装版本信息；

SQL快速入门

支持主流sql语法，因此只要熟悉oracle、sql server、mysql等主流数据库SQL用法，PostgreSQL本质上使用一样（除特定专用语法外），无重新学习成本；

# 登录到PostgreSQL命令行（注意：如果psql未生效，则进入到安装目录执行：/usr/local/pgsql/bin/）
psql -U postgres
 
-- 创建新用户(psql默认不提供root账户，超级账户为安装账户，通过sudo postgres切换系统用户登录执行)
CREATE USER myuser WITH PASSWORD 'mypassword';
# 创建最高权限用户（SUPERUSER），生产建议只创建特定数据库权限的用户
#CREATE ROLE myuser WITH LOGIN SUPERUSER PASSWORD 'password';
 
-- 创建新数据库
CREATE DATABASE mydb OWNER myuser;
 
-- 给用户授权，相关权限：https://www.runoob.com/postgresql/postgresql-privileges.html
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

-- 连接到数据库
\c mydb
 
-- 创建表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(255)
);

-- 查询已建的表
SELECT * FROM pg_catalog.pg_tables WHERE schemaname='public';
 
-- 插入数据
INSERT INTO users (username, email) VALUES ('admin', 'admin@example.com');
 
-- 查询数据
SELECT * FROM users;
 
-- 更新数据
UPDATE users SET email = 'newemail@example.com' WHERE username = 'admin';
 
-- 删除数据
DELETE FROM users WHERE username = 'admin';

-- 退出命令行
\q

其它

修改端口

在安装目录下/usr/local/pgsql/data/postgresql.conf文件中修改port

# 默认为：5432
#port = 5432

启用远程链接

默认postgresql安装后只支持本地连接，需在/usr/local/pgsql/data/postgresql.conf文件中修改

# 默认为 listen_addresses = 'localhost' 
listen_addresses = '*'

还需要在/usr/local/pgsql/data/pg_hba.conf文件中最后一行，添加允许所有IP连接；

host    all             all             0.0.0.0/0               md5

上述配置修改完毕，重启postgresql服务

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data restart

重启后，通过命令行远程连接

psql -h <服务器IP> -U <用户名> -d <数据库名>

问题一

切换到psotgres账号，提示“-bash: warning: setlocale: LC_TIME: cannot change locale (en_US.UTF-8)”，并且后续命令也无法继续执行；

[root@centos72 postgresql-16.3]# su - postgres
Last login: Fri Jul 12 19:01:03 CST 2024 on pts/1
-bash: warning: setlocale: LC_TIME: cannot change locale (en_US.UTF-8)
[postgres@centos72 ~]$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

initdb: error: invalid locale settings; check LANG and LC_* environment variables
[postgres@centos72 ~]$ locale
locale: Cannot set LC_ALL to default locale: No such file or directory
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

locale指系统支持的语言与本地环境编码格式，如：语言、数字、时间、货币、地址、姓名等等，通过locale查看已LANG=en_US.UTF-8和LC_TIME="en_US.UTF-8"已设置，但提示LC_ALL默认locale找不到文件或目录，通过locale -a查看已支持的列表有en_US.UTF-8；

解决方案一：

  • 在命令中增加export设置到当前登录用户环境变量中 
    export LANG="en_US.UTF-8"
export LC_ALL="en_US.UTF-8"

  • 对于持久设置，你可以将上述 export 命令添加到你的 shell 配置文件中，如 .bashrc 或 .bash_profile

  • 解决方案二（推存）：

    将locale全部默认设置一遍：localedef -i en_US -f UTF-8 en_US.UTF-8

    完毕后，重新执行initdb，初始化pgsql正常：/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

[postgres@centos72 ~]$ /usr/local/pgsql/bin/initdb -D /usr/local/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 database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: 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:

    /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

问题二

通过客户端工具远程连接，查询users表，提示权限不足

ERROR:  permission denied for table users

在linux上先切换到postgres账户，再通过psql进入命令行客户端为访问用户进行授权

[postgres@centos72 ~]$./psql -U postgres
psql (16.3)
Type "help" for help.

postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".

查看对users表的用户权限，默认只有grantee列只有postgres超级账户权限

mydb=# select * from information_schema.table_privileges where table_name = 'users';
 grantor  | grantee  | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
----------+----------+---------------+--------------+------------+----------------+--------------+----------------
 postgres | postgres | mydb          | public       | users      | INSERT         | YES          | NO
 postgres | postgres | mydb          | public       | users      | SELECT         | YES          | YES
 postgres | postgres | mydb          | public       | users      | UPDATE         | YES          | NO
 postgres | postgres | mydb          | public       | users      | DELETE         | YES          | NO
 postgres | postgres | mydb          | public       | users      | TRUNCATE       | YES          | NO
 postgres | postgres | mydb          | public       | users      | REFERENCES     | YES          | NO
 postgres | postgres | mydb          | public       | users      | TRIGGER        | YES          | NO
(7 rows)

而我们创建的myuser并没有显示，说明，最初始创建的myuser对mydb授权有限，如下：

# 创建用户时已执行过
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

原因是我们创建的表没有指定模式的情况下，所建表都会在public模式下，需要对public模式下的表访问进行授权；（模式类似于一种命名空间，可将不同的表放在该模式下，进行独立管理，可通过 show search_path; 查看已创建的模式，public为内置默认模式）

重新执行myuser用户授权：GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser，授权对public所有表PRIVILEGES权限（INSERT、SELECT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER）

mydb=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;
GRANT
mydb=# select * from information_schema.table_privileges where  table_name = 'users';
 grantor  | grantee  | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
----------+----------+---------------+--------------+------------+----------------+--------------+----------------
 postgres | postgres | mydb          | public       | users      | INSERT         | YES          | NO
 postgres | postgres | mydb          | public       | users      | SELECT         | YES          | YES
 postgres | postgres | mydb          | public       | users      | UPDATE         | YES          | NO
 postgres | postgres | mydb          | public       | users      | DELETE         | YES          | NO
 postgres | postgres | mydb          | public       | users      | TRUNCATE       | YES          | NO
 postgres | postgres | mydb          | public       | users      | REFERENCES     | YES          | NO
 postgres | postgres | mydb          | public       | users      | TRIGGER        | YES          | NO
 postgres | myuser   | mydb          | public       | users      | INSERT         | NO           | NO
 postgres | myuser   | mydb          | public       | users      | SELECT         | NO           | YES
 postgres | myuser   | mydb          | public       | users      | UPDATE         | NO           | NO
 postgres | myuser   | mydb          | public       | users      | DELETE         | NO           | NO
 postgres | myuser   | mydb          | public       | users      | TRUNCATE       | NO           | NO
 postgres | myuser   | mydb          | public       | users      | REFERENCES     | NO           | NO
 postgres | myuser   | mydb          | public       | users      | TRIGGER        | NO           | NO
(14 rows)

再一次在客户端工具中执行查询，返回正常数据；

# linux # postgresql # 数据库 # sql
Logo
开放原子开发者工作坊

开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！

更多推荐

第二届开放原子大赛首批创新成果集结武汉，诚邀广大开发者共鉴开源技术盛宴

第二届开放原子大赛首批创新成果集结武汉，诚邀广大开发者共鉴开源技术盛宴

avatar 开放原子开发者工作坊

诚邀报名 | 开源基础设施能力建设分论坛：打造开源生态的“心脏”

诚邀报名 | 开源基础设施能力建设分论坛：打造开源生态的“心脏”

avatar 开放原子开发者工作坊

诚邀报名 | 编程语言分论坛：AI时代的技术革新与开源实践

诚邀报名 | 编程语言分论坛：AI时代的技术革新与开源实践

avatar 开放原子开发者工作坊