PostgreSQL 安装、常用命令及优化

06-01 1744阅读

一、安装 PostgreSQL

（一）二进制安装方式

1. 下载二进制包

首先，访问 PostgreSQL 官方下载页面（PostgreSQL: File Browser），选择合适的版本。以 PostgreSQL 14.8 为例，在 Linux 系统上可以使用以下命令下载：

bash

wget https://ftp.postgresql.org/pub/source/v14.8/postgresql-14.8.tar.gz

2. 解压文件

使用以下命令解压下载的压缩包：

bash

tar -zxvf postgresql-14.8.tar.gz
cd postgresql-14.8

3. 配置编译选项

在编译之前，需要配置一些编译选项。可以使用 ./configure 命令进行配置，以下是一个基本的配置示例：

bash

./configure --prefix=/usr/local/pgsql

--prefix：指定 PostgreSQL 的安装路径，这里设置为 /usr/local/pgsql，你可以根据需要修改。
4. 编译和安装

配置完成后，执行以下命令进行编译和安装：

bash
```
make
sudo make install
```
编译和安装过程可能需要一些时间，取决于系统性能。

5. 创建用户和组

为 PostgreSQL 创建一个专用的用户和组，以提高安全性：

bash
```
sudo groupadd postgres
sudo useradd -g postgres postgres
```
6. 初始化数据库

切换到 postgres 用户，并初始化数据库：

bash
```
sudo -u postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
```
- -D：指定数据库的数据目录，这里设置为 /usr/local/pgsql/data。
  7. 启动服务
  
  创建一个启动脚本，方便后续启动和管理 PostgreSQL 服务。在 /etc/systemd/system 目录下创建 postgresql.service 文件：
  
  bash
  （图片来源网络，侵删）
```
sudo vim /etc/systemd/system/postgresql.service
```
  添加以下内容：
  
  plaintext
  （图片来源网络，侵删）
```
[Unit]
Description=PostgreSQL 14 database server
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGDATA=/usr/local/pgsql/data
ExecStart=/usr/local/pgsql/bin/pg_ctl start -D ${PGDATA} -s -o "-p 5432"
ExecStop=/usr/local/pgsql/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/usr/local/pgsql/bin/pg_ctl reload -D ${PGDATA} -s
KillMode=mixed
TimeoutSec=0
[Install]
WantedBy=multi-user.target
```
  保存并退出文件，然后重新加载 systemd 配置：
  
  bash
  （图片来源网络，侵删）
```
sudo systemctl daemon-reload
```
  启动 PostgreSQL 服务并设置开机自启：
  
  bash
```
sudo systemctl start postgresql
sudo systemctl enable postgresql
```
  （二）在 CentOS/RHEL 系统上使用包管理器安装
  - 添加 PostgreSQL 仓库
    bash
```
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
```
    将 EL - 7 替换为你实际的 CentOS/RHEL 版本（如 EL - 8）。
    - 安装 PostgreSQL
      以 PostgreSQL 14 为例：
      bash
```
sudo yum install -y postgresql14-server
```
      - 初始化数据库
        bash
        
        sudo /usr/pgsql - 14/bin/postgresql - 14 - setup initdb
        
        启动并设置开机自启
        bash
        
        sudo systemctl start postgresql - 14 sudo systemctl enable postgresql - 14
        
        （三）在 Ubuntu/Debian 系统上使用包管理器安装
        
        添加 PostgreSQL 仓库
        bash
        
        sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
        
        安装 PostgreSQL
        以 PostgreSQL 14 为例：
        bash
        
        sudo apt update sudo apt install -y postgresql - 14
        
        启动并设置开机自启
        bash
        
        sudo systemctl start postgresql sudo systemctl enable postgresql
        
        （四）在 macOS 系统上安装
        
        使用 Homebrew 安装
        bash
        
        brew install postgresql
        
        启动 PostgreSQL 服务
        bash
        
        brew services start postgresql
        
        二、配置 PostgreSQL
        
        切换到 postgres 用户
        bash
        
        sudo -u postgres psql
        
        创建新用户和数据库
        sql
        
        CREATE USER your_user WITH PASSWORD 'your_password'; CREATE DATABASE your_database OWNER your_user;
        
        退出 psql
        sql
        
        \q
        
        三、PostgreSQL 常用命令及使用方式
        
        （一）服务管理命令
        
        1. 启动服务
        
        Linux（systemd）：
        bash
        
        sudo systemctl start postgresql
        
        macOS（Homebrew）：
        bash
        
        brew services start postgresql
        
        2. 停止服务
        
        Linux（systemd）：
        bash
        
        sudo systemctl stop postgresql
        
        macOS（Homebrew）
        bash
        
        brew services stop postgresql
        
        3. 重启服务
        
        Linux（systemd）：
        bash
        
        sudo systemctl restart postgresql
        
        macOS（Homebrew）：
        bash
        
        brew services restart postgresql
        
        4. 查看服务状态
        
        Linux（systemd）：
        bash
        
        sudo systemctl status postgresql
        
        macOS（Homebrew）：
        bash
        
        brew services info postgresql
        
        （二）psql 客户端命令
        
        1. 连接数据库
        
        以 postgres 用户连接到默认数据库：
        bash
        
        sudo -u postgres psql
        
        以指定用户连接到指定数据库：
        bash
        
        psql -U your_user -d your_database
        
        连接到远程数据库：
        bash
        
        psql -h remote_host -p 5432 -U your_user -d your_database
        
        2. 数据库操作
        
        创建数据库：
        sql
        
        CREATE DATABASE new_database;
        
        删除数据库：
        sql
        
        DROP DATABASE existing_database;
        
        查看所有数据库：
        sql
        
        \l
        
        3. 表操作
        
        创建表：
        sql
        
        CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100), age INT );
        
        删除表：
        sql
        
        DROP TABLE users;
        
        查看所有表：
        sql
        
        \dt
        
        查看表结构：
        sql
        
        \d users
        
        4. 用户和权限操作
        
        创建用户：
        sql
        
        CREATE USER new_user WITH PASSWORD 'new_password';
        
        删除用户：
        sql
        
        DROP USER existing_user;
        
        授予权限：
        sql
        
        GRANT ALL PRIVILEGES ON DATABASE your_database TO your_user;
        
        撤销权限：
        sql
        
        REVOKE ALL PRIVILEGES ON DATABASE your_database FROM your_user;
        
        5. 数据操作
        
        插入数据：
        sql
        
        INSERT INTO users (name, age) VALUES ('John', 30);
        
        查询数据：
        sql
        
        SELECT * FROM users;
        
        更新数据：
        sql
        
        UPDATE users SET age = 31 WHERE name = 'John';
        
        删除数据：
        sql
        
        DELETE FROM users WHERE name = 'John';
        
        6. 其他 psql 命令
        
        退出 psql：
        sql
        
        \q
        
        查看帮助信息：
        sql
        
        \?
        
        （三）备份与恢复命令
        
        1. 备份数据库
        
        使用 pg_dump 备份单个数据库：
        bash
        
        pg_dump -U your_user -d your_database > backup.sql
        
        备份所有数据库：
        bash
        
        pg_dumpall -U your_user > all_backup.sql
        
        2. 恢复数据库
        
        恢复单个数据库：
        bash
        
        psql -U your_user -d your_database
        恢复所有数据库：
        bash
        
        psql -U your_user -f all_backup.sql postgres
        
        四、优化 PostgreSQL
        
        （一）内存优化
        
        修改 postgresql.conf 文件
        找到 shared_buffers 参数，它表示 PostgreSQL 用于缓存数据的共享内存大小。一般可以设置为系统内存的 25% - 50%。例如，系统有 8GB 内存，可以设置为：
        plaintext
        
        shared_buffers = '2GB'
        
        调整 work_mem
        work_mem 控制每个排序操作和哈希表使用的内存量。根据实际情况适当增加，例如：
        plaintext
        
        work_mem = '64MB'
        
        调整 maintenance_work_mem
        maintenance_work_mem 用于 VACUUM、CREATE INDEX 等维护操作。可以设置为几百兆到几 GB，例如：
        plaintext
        
        maintenance_work_mem = '512MB'
        
        （二）磁盘 I/O 优化
        
        使用快速存储设备
        尽量使用 SSD 磁盘，其读写速度远高于传统 HDD 磁盘，可以显著提高 PostgreSQL 的性能。
        优化磁盘调度算法
        对于 Linux 系统，可以为 PostgreSQL 数据目录所在的磁盘选择合适的 I/O 调度算法。例如，对于 SSD 磁盘，可以使用 noop 调度算法：
        bash
        
        echo noop > /sys/block/sda/queue/scheduler
        
        将 sda 替换为实际的磁盘设备名。
        
        调整 fsync 参数
        fsync 参数控制 PostgreSQL 是否将数据同步写入磁盘。如果对数据安全性要求不是特别高，可以将其设置为 off 以提高性能，但这样在系统崩溃时可能会丢失部分数据：
        plaintext
        
        fsync = off
        
        （三）网络优化
        
        调整 listen_addresses
        在 postgresql.conf 中设置 listen_addresses 参数，指定 PostgreSQL 监听的 IP 地址。如果需要远程访问，可以设置为 '*'：
        plaintext
        
        listen_addresses = '*'
        
        修改 pg_hba.conf
        在 pg_hba.conf 文件中添加远程访问规则，例如允许所有 IP 地址以密码认证方式访问：
        plaintext
        
        host all all 0.0.0.0/0 md5
        
        修改后需要重新加载配置：
        
        bash
        
        sudo systemctl reload postgresql - 14
        
        （四）查询优化
        
        创建合适的索引
        分析查询语句，为经常用于 WHERE 子句、JOIN 条件和 ORDER BY 子句的列创建索引。例如：
        sql
        
        CREATE INDEX idx_column_name ON your_table (column_name);
        
        使用 EXPLAIN 分析查询
        使用 EXPLAIN 关键字分析查询语句的执行计划，找出可能的性能瓶颈：
        sql
        
        EXPLAIN SELECT * FROM your_table WHERE column_name = 'value';
        
        定期进行 VACUUM 和 ANALYZE
        VACUUM 用于回收已删除行占用的空间，ANALYZE 用于更新表的统计信息，以帮助查询优化器生成更优的执行计划：
        sql
        
        VACUUM ANALYZE your_table;
        
        （五）日志优化
        
        调整日志级别
        在 postgresql.conf 中设置 log_statement 参数，控制日志记录的详细程度。例如，设置为 'ddl' 只记录数据定义语言（DDL）语句：
        plaintext
        
        log_statement = 'ddl'
        
        定期清理日志文件
        可以编写脚本定期清理 PostgreSQL 的日志文件，避免占用过多磁盘空间。例如：
        bash
        
        find /var/log/postgresql -name "postgresql-*.log" -mtime +7 -delete
        
        根据实际业务需求和系统资源情况，还可以进一步调整优化参数。

免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们。

一	二	三	四	五	六	日
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

一、安装 PostgreSQL

（一）二进制安装方式

1. 下载二进制包

2. 解压文件

3. 配置编译选项

4. 编译和安装

5. 创建用户和组

6. 初始化数据库

7. 启动服务

（二）在 CentOS/RHEL 系统上使用包管理器安装

（三）在 Ubuntu/Debian 系统上使用包管理器安装

（四）在 macOS 系统上安装

二、配置 PostgreSQL

三、PostgreSQL 常用命令及使用方式

（一）服务管理命令

1. 启动服务

2. 停止服务

3. 重启服务

4. 查看服务状态

（二）psql 客户端命令

1. 连接数据库

2. 数据库操作

3. 表操作

4. 用户和权限操作

5. 数据操作

6. 其他 psql 命令

（三）备份与恢复命令

1. 备份数据库

2. 恢复数据库

四、优化 PostgreSQL

（一）内存优化

（二）磁盘 I/O 优化

（三）网络优化

（四）查询优化

（五）日志优化

相关阅读

Linux Shell异常处理，常见问题与解决方案？Shell脚本报错怎么解决？Shell脚本报错如何快速解决？

Linux中高效复制多级目录与文件的技巧与方法？如何快速复制Linux多级目录？Linux多级目录如何快速复制？

IDA Pro监听Linux进程，逆向工程中的动态调试技术？IDA如何动态调试Linux进程？IDA如何逆向调试Linux进程？

Linux下按pip安装与管理Python包指南？Linux如何用pip管理Python包？Linux下pip怎么管理Python包？

目录[+]