POSTGre 数据库安装操作

## **第一步、下载与安装**

postgre 支持 windows 和 linux 两种系统，本次在linux环境安装。  
下载地址：[postgreSQL官网](https://www.postgresql.org/download/linux/redhat/)

ubuntu安装postgre

Ubuntu安装PostgreSQL的步骤如下：

更新包列表：

sudo apt update

Err:1 http://mirrors.cloud.aliyuncs.com/ubuntu focal InRelease

Could not resolve 'mirrors.cloud.aliyuncs.com'

Err:2 http://mirrors.cloud.aliyuncs.com/ubuntu focal-updates InRelease

Could not resolve 'mirrors.cloud.aliyuncs.com'

Err:3 http://mirrors.cloud.aliyuncs.com/ubuntu focal-security InRelease

Could not resolve 'mirrors.cloud.aliyuncs.com'

Hit:4 https://download.docker.com/linux/ubuntu focal InRelease

Hit:5 http://repo.mysql.com/apt/ubuntu bionic InRelease

Hit:6 https://mirrors.aliyun.com/kubernetes/apt kubernetes-xenial InRelease

Reading package lists... Done

W: Failed to fetch http://mirrors.cloud.aliyuncs.com/ubuntu/dists/focal/InRelease Could not resolve 'mirrors.cloud.aliyuncs.com'

W: Failed to fetch http://mirrors.cloud.aliyuncs.com/ubuntu/dists/focal-updates/InRelease Could not resolve 'mirrors.cloud.aliyuncs.com'

W: Failed to fetch http://mirrors.cloud.aliyuncs.com/ubuntu/dists/focal-security/InRelease Could not resolve 'mirrors.cloud.aliyuncs.com'

W: Some index files failed to download. They have been ignored, or old ones used instead.

；

解决办法：ubuntu换源  
1.备份原来的更新源

cp /etc/apt/sources.list /etc/apt/sources.list.backup

2.修改更新源

vim /etc/apt/sources.list

1. 更改为如下内容：

#添加阿里源

deb http://mirrors.aliyun.com/ubuntu/ focal main restricted universe multiverse

deb-src http://mirrors.aliyun.com/ubuntu/ focal main restricted universe multiverse

deb http://mirrors.aliyun.com/ubuntu/ focal-security main restricted universe multiverse

deb-src http://mirrors.aliyun.com/ubuntu/ focal-security main restricted universe multiverse

deb http://mirrors.aliyun.com/ubuntu/ focal-updates main restricted universe multiverse

deb-src http://mirrors.aliyun.com/ubuntu/ focal-updates main restricted universe multiverse

deb http://mirrors.aliyun.com/ubuntu/ focal-proposed main restricted universe multiverse

deb-src http://mirrors.aliyun.com/ubuntu/ focal-proposed main restricted universe multiverse

deb http://mirrors.aliyun.com/ubuntu/ focal-backports main restricted universe multiverse

deb-src http://mirrors.aliyun.com/ubuntu/ focal-backports main restricted universe multiverse

#添加清华源

deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ focal main restricted universe multiverse

# deb-src https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ focal main restricted universe multiverse

deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ focal-updates main restricted universe multiverse

# deb-src https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ focal-updates main restricted universe multiverse

deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ focal-backports main restricted universe multiverse

# deb-src https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ focal-backports main restricted universe multiverse

deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ focal-security main restricted universe multiverse

# deb-src https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ focal-security main restricted universe multiverse multiverse

3.更新源 sudo apt-get update，问题解决

![](/media/202407//1722310426.749348.png)

安装POSTGRESQL

# 创建软件源  
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb\_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'  
# 添加key  
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -  
# 安装  
sudo apt update  
sudo apt install postgresql-14 postgresql-client-14

![](/media/202407//1722310426.7589092.png)  
# 启动命令  
sudo systemctl status postgresql

sudo systemctl start postgresql

sudo systemctl stop postgresql

![](/media/202407//1722310426.7666037.png)

![](/media/202407//1722310426.7740712.png)

sudo mkdir -p /tqls\_system/postgresql/data

sudo mkdir -p /tqls\_system/postgresql/log

chown postgres:postgres /tqls\_system/postgresql/data /tqls\_system/postgresql/log

初始化数据库：

sudo -u postgres /usr/lib/postgresql/14/bin/initdb -D /tqls\_system/postgresql/data --locale=C -E UTF-8 --pgdata=/tqls\_system/postgresql/log

![](/media/202407//1722310426.7825208.png)

/usr/lib/postgresql/14/bin/pg\_ctl -D /tqls\_system/postgresql/log -l logfile start

修改PostgreSQL配置文件postgresql.conf和pg\_hba.conf，设置数据目录和日志目录：

编辑postgresql.conf：

修改参数文件

tcp\_keepalives\_interval 5

tcp\_keepalives\_idle 30

max\_connections 8000

SHOW tcp\_keepalives\_idle;

show max\_connections;

show tcp\_keepalives\_interval;

已完成

切换到PostgreSQL用户（默认为postgres）：

sudo -i -u postgres

创建一个新的角色（可选）：

createuser --interactive

创建一个新数据库（可选）：

createdb <your\_database\_name>

登录到PostgreSQL命令行界面：

psql

退出psql命令行：

\q

## **第二步、配置数据库信息**

### **1）修改密码**

// 切换到postgres用户

su postgres

// 切换SQL模式

psql

// 修改密码

alter user postgres with password 'postgres123';

// 创建test用户

create user test with password 'test';

// 授权

grant all privileges on database mydb to test;

// 退出

\q

为了可以通过Navicat等工具连接，可进行以下设置

### **2）配置远程访问**

将5432端口添加到防火墙列表

// 打开防火墙端口

sudo firewall-cmd --add-port=5432/tcp --permanent

// 刷新防火墙

sudo firewall-cmd --reload

### **3）修改配置文件**

修改监听地址为任意地址，即修改postgresql.conf文件。

// 打开配置文件

vi /var/lib/pgsql/15/data/postgresql.conf

// 打开监听注释，监听地址改为*

listen\_addresses = '*'

// 保存退出 esc

:wq

修改配置文件如下图所示。

![](/media/202407//1722310426.795316.png)

允许所有IP访问，即修改 pg\_hba.conf 文件

// 打开配置文件

vi /var/lib/pgsql/14/data/pg\_hba.conf

// 新增一行,若连接不上则将 scram-sha-256修改为trust 信任模式

host all all 0.0.0.0/0 scram-sha-256

// 重启服务

sudo systemctl restart postgresql-14

配置文件新增如下图所示。

![](/media/202407//1722310426.805593.png)

用Navicat连接数据库，连接信息如下图所示。

![](/media/202407//1722310426.816486.png)

## **第三步、常用操作命令**

****数据库****，即创建数据库，切换数据库等

// 切换到postgres用户

su postgres

// 创建数据库 mydb

create database mydb;

// 查看所有数据库

\l

// 切换到mydb数据库

\c mydb

// 删除

drop database mydb;

操作结果如下图所示。

![](/media/202407//1722310426.826789.png)

****数据表****，即创建表，插入数据等

// 创建表

create table business\_order(id serial primary key,order\_no varchar(255),sku\_name varchar(255));

// 插入数据

insert into business\_order(order\_no,sku\_name)values('20221126000001','iphone14 plus');

// 查看表内容

select * from business\_order;

// 查看表结构

\d business\_order

// 查看所有表，序列等

\d

创建表语句与其他数据库语言类似，注意的是主键递增序列用 serial，mysql 使用的是 auto\_increment。

![](/media/202407//1722310426.841998.png)

查看表结构

![](/media/202407//1722310426.84964.png)

## **第四步、数据库备份与还原**

实际工作中会对数据库进行备份和还原，备份主要有三种格式  
.bak 即压缩的二进制  
.sql 即明文存储  
.tar 即tarball压缩格式  
数据库备份分****单数据库备份****，使用 pg\_dump 命令；****所有数据库备份****，使用 pg\_dumpall 命令。

// 切换到postgres用户

su postgres

// 单数据库备份-导出到当前目录

pg\_dump mydb > mydb.bak

// 所有数据库备份,

pg\_dumpall > backup.bak

// 整个数据库备份到指定位置

pg\_dump -f /tmp/mydb.bak mydb

// 备份postgres数据库中business\_order表

pg\_dump -U postgres -f /tmp/mydb.sql -t business\_order postgres

// 数据库恢复-直接恢复，注意先新增空数据库

psql -f /tmp/mydb.bak mydb

// 数据库恢复

pg\_restore -U postgres -d business\_order /temp/mydb.bak

// 查看目录

ls -l

执行结果如下图所示。

![](/media/202407//1722310426.8624065.png)

## **异常处理**

1. ****Navicat连接报错****  
错误代码：authentication method 10 not supported  
处理方案：在 ****pg\_hba.conf**** 文件中可将新增的 0.0.0.0/0 的模式改为 trust

卸载postgresql

先停止postgresql

sudo systemctl stop postgresql

删除postgresql

sudo apt-get --purge remove postgresql-*

手动删除组件

sudo rm -rf /etc/postgresql/

sudo apt-get autoremove

Sudo rm -rf /etc/postgresql-common/

Sudo rm -rf /var/lib/postgresql/

Sudo userdel -r postgres

源码安装手册

sudo apt-get install build-essential zlib1g-dev libreadline-dev libssl-dev libpq-dev libyaml-dev libsqlite3-dev sqlite3 libxml2-dev libxslt1-dev libcurl4-openssl-dev python-minimal libc6 libpcre3 libpcre3-dev

python-minimal安装失败

![](/media/202407//1722310426.8729024.png)

下载软件

1. root@postgre:/tqls\_system/soft# chmod 755 postgresql-14.12.tar.gz 
2. root@postgre:/tqls\_system/soft# ll
3. total 28592
4. drwxr-xr-x 2 root root 4096 Jul 24 15:20 ./
5. drwxr-xr-x 5 root root 4096 Jul 24 14:40 ../
6. -rwxr-xr-x 1 root root 29266783 Jul 24 15:16 postgresql-14.12.tar.gz*

解压源码包

tar -zxvf postgresql-14.12.tar.gz

root@postgre:/tqls\_system# mkdir /tqls\_system/pgsql

root@postgre:/tqls\_system# mkdir /tqls\_system/pgsql/data

root@postgre:/tqls\_system# mkdir /tqls\_system/pgsql/log

root@postgre:/tqls\_system# cd postgresql-14.12/

./configure --prefix=/tqls\_system/pgsql

编译和安装

./configure --prefix=/tqls\_system/pgsql

![](/media/202407//1722310426.8825066.png)

make

![](/media/202407//1722310426.8899698.png)

make install

![](/media/202407//1722310426.8979144.png)

sudo groupadd postgres

sudo useradd -g postgres postgres

root@postgre:/tqls\_system# mkdir /tqls\_system/pgsql/data

root@postgre:/tqls\_system# mkdir /tqls\_system/pgsql/log

sudo mkdir /usr/local/pgsql/data

sudo chown postgres:postgres /tqls\_system/pgsql/data /tqls\_system/pgsql/log

初始化数据库

sudo -i -u postgres /tqls\_system/pgsql/bin/initdb -D /tqls\_system/pgsql/data

root@postgre:/tqls\_system/pgsql/log# sudo -i -u postgres /tqls\_system/pgsql/bin/initdb -D /tqls\_system/pgsql/data

sudo: unable to change directory to /home/postgres: No such file or directory

sudo: unable to execute /bin/sh: No such file or directory

报错需要创建目录

root@postgre:/tqls\_system/pgsql# sudo mkdir -p /home/postgres

![](/media/202407//1722310426.906946.png)

sudo useradd -m newuser

sudo passwd newuser

sudo groupadd newgroup

sudo usermod -a -G newgroup newuser

启动数据库

/tqls\_system/pgsql/bin/pg\_ctl -D /tqls\_system/pgsql/data -l /tqls\_system/pgsql/log/logfile

start

root@postgre:/tqls\_system/pgsql# cd log/

root@postgre:/tqls\_system/pgsql/log# chown pg\_user:pg\_user logfile

root@postgre:/tqls\_system/pgsql/log# /tqls\_system/pgsql/bin/pg\_ctl -D /tqls\_system/pgsql/data -l /tqls\_system/pgsql/log/ start

pg\_ctl: cannot be run as root

Please log in (using, e.g., "su") as the (unprivileged) user that will

own the server process.

root@postgre:/tqls\_system/pgsql/log# /tqls\_system/pgsql/bin/pg\_ctl -D /tqls\_system/pgsql/data -l /tqls\_system/pgsql/log/logfile start

pg\_ctl: cannot be run as root

Please log in (using, e.g., "su") as the (unprivileged) user that will

own the server process.

root@postgre:/tqls\_system/pgsql/log# su - pg\_user

/tqls\_system/pgsql/bin/pg\_ctl -D /tqls\_system/pgsql/data -l /tqls\_system/pgsql/log/logfile start

/tqls\_system/pgsql/bin/pg\_ctl -D /tqls\_system/pgsql/data -l /tqls\_system/pgsql/log/logfile stop

waiting for server to start.... done

server started

$

root@postgre:/tqls\_system/pgsql/log# /tqls\_system/pgsql/bin/psql -U postgres

修改参数文件

tcp\_keepalives\_interval 5

tcp\_keepalives\_idle 30

max\_connections 8000

SHOW tcp\_keepalives\_idle;

show max\_connections;

show tcp\_keepalives\_interval;

已完成

root@postgre:/tqls\_system/pgsql/data# /tqls\_system/pgsql/bin/psql -U postgres

psql (14.12)

Type "help" for help.

postgres=#

不能翻页

![](/media/202407//1722310426.9172106.png)

因默认ubuntu创建的普通帐号，默认shell为/bin/sh，不能使用TAB键、上下键，命令行不显示当前路径，所以将「指定用户」帐号的shell改为/bin/bash就可以了。

![](/media/202407//1722310426.9236565.png)

### **九、设置PostgreSQL开机自启动**

找到postgresql-14.12

root@postgre:/tqls\_system/postgresql-14.12/# cd /tqls\_system/postgresql-14.12/contrib/start-scripts/

root@postgre:/tqls\_system/postgresql-14.12/contrib/start-scripts# ls

freebsd linux macos

切换为root用户

[root@localhost start-scripts]# chmod a+x linux

[root@localhost start-scripts]# cp linux /etc/init.d/postgresql

修改/etc/init.d/postgresql文件的两个变量

# Installation prefix

prefix=/tqls\_system/pgsql

# Data directory

PGDATA="/tqls\_system/pgsql/data"

设置postgresql服务开机自启动

然后将5432端口开启（或直接把防火墙给关了）

sudo ufw allow 5432/tcp

执行service postgresql start，启动PostgreSQL服务

如果这里执行命令后出现Failed to start postgresql.service: Unit postgresql.service not found.错误  
请检查配置postgres用户的环境变量的时候是否将PATH给export了

![](/media/202407//1722310426.9338946.png)

![](/media/202407//1722310426.9397347.png)

export PGHOME=/tqls\_system/pgsql

export PGDATA=/tqls\_system/pgsql/data

export PGHOST=localhost

export PATH="$HOME/bin:$HOME/.local/bin:$PATH:$PGHOME/bin"

export MANPATH=$PGHOME/share/man:$MANPATH

export LANG=en\_US.utf8

export DATE=`date +"%Y-%m-%d %H:%M:%S"`

export LD\_LIBRARY\_PATH=$PGHOME/lib:$LD\_LIBRARY\_PATH

#下面只是启动服务的快捷配置，可不用配置

alias pg\_start='pg\_ctl start -D $PGDATA'

alias pg\_stop='pg\_ctl stop -D $PGDATA -m fast'

启动数据库

#初始化数据库

initdb -D /var/postgresql/data

#启动服务

pg\_ctl -D /var/postgresql/data -l /var/postgresql/logs/logfile start

#连接数据库

psql

#创建数据库

create database test;

#创建表

create table t\_user (id integer, name text);

#插入测试数据

insert into t\_user values (1,'joke');

#查询数据

select * from t\_user ;

#退出psql窗口

\q

十、开始测试

默认的用户是postgres，密码和linux系统中所设的postgres用户的密码一样

[root@localhost init.d]# su - postgres

Last login: Thu Aug 4 23:08:40 PDT 2022 on pts/0

[pg\_user@localhost ~]$ psql -U postgres

psql (14.12)

Type "help" for help.

postgres=#

root@postgre:/tqls\_system/pgsql/log# su - pg\_user

/tqls\_system/pgsql/bin/pg\_ctl -D /tqls\_system/pgsql/data -l /tqls\_system/pgsql/log/logfile start

/tqls\_system/pgsql/bin/pg\_ctl -D /tqls\_system/pgsql/data -l /tqls\_system/pgsql/log/logfile stop

启停数据库

pg\_user@postgre:~$ pg\_stop

waiting for server to shut down.... done

server stopped

pg\_user@postgre:~$ ps -ef|grep post

pg\_user 27433 27346 0 09:56 pts/0 00:00:00 grep --color=auto post

pg\_user@postgre:~$ pg\_start

![](/media/202407//1722310426.9519017.png)

pg\_user@postgre:~$ psql -U postgres

psql (14.12)

Type "help" for help.

postgres=#

账号和初始密码：postgres/pstgres123456

备份和恢复

pg\_user@postgre:/tqls\_system/pgsql$

#pg\_dump -U postgres -f /tqls\_system/pgsql/backup2024/mydb.bak mydb

实际工作中会对数据库进行备份和还原，备份主要有三种格式  
.bak 即压缩的二进制  
.sql 即明文存储  
.tar 即tarball压缩格式  
数据库备份分****单数据库备份****，使用 pg\_dump 命令；****所有数据库备份****，使用 pg\_dumpall 命令。

// 切换到postgres用户（我们是pg\_user）

su pg\_user #(我们是pg\_user)

// 单数据库备份-导出到当前目录

pg\_dump mydb > mydb.bak

// 所有数据库备份,

pg\_dumpall > backup.bak

// 整个数据库备份到指定位置

pg\_dump -U postgres -f /tmp/mydb.bak mydb

// 备份postgres数据库中business\_order表

pg\_dump -U postgres -f /tmp/mydb.sql -t business\_order postgres

// 数据库恢复-直接恢复，注意先新增空数据库

psql -f /tmp/mydb.bak mydb

// 数据库恢复

pg\_restore -U postgres -d business\_order /temp/mydb.bak

pg\_user@postgre:~$ cat /tqls\_system/backup\_pg.sh

#!/bin/bash

backupdir=//tqls\_system/pgsql/backup2024/test

/tqls\_system/pgsql/bin/pg\_dump -U postgres -F t -f $backupdir/test\_$(date +%Y%m%d%H).bak test

echo "backup finished"

find $backupdir -name "test\_*.bak" -type f -mtime +30 -exec rm {} \; > /dev/null 2>&1

![](/media/202407//1722310426.9614358.png)

root@postgre:/tqls\_system/pgsql/backup2024# mkdir /tqls\_system/pgsql/backup2024/test

root@postgre:/tqls\_system/pgsql/backup2024# sh /tqls\_system/backup\_pg.sh

backup finished

root@postgre:/tqls\_system/pgsql/backup2024# ls

mydb.bak test

root@postgre:/tqls\_system/pgsql/backup2024# cd test/

root@postgre:/tqls\_system/pgsql/backup2024/test# ls

test\_2024072513.bak

备份已完成

// 数据库恢复

新建库名恢复

create database test2;

备份文件/tqls\_system/pgsql/backup2024/test/test\_2024072514.bak

恢复操作

root@postgre:~# /tqls\_system/pgsql/bin/pg\_restore -U postgres -d test2 /tqls\_system/pgsql/backup2024/test/test\_2024072514.bak