Linux 上安装 PostgreSQL

PostGresql是什么？

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

Linux 上安装 PostgreSQL

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

1. 环境准备

# 检查PostgreSQL是否已经安装
rpm -qa | grep postgres 
# 检查PostgreSQL安装位置
rpm -qal | grep postgres
# 卸载已安装PostgreSQL
rpm –e PostgreSQL版本
# 查看卸载是否已完成
rpm -qa | grep postgres

2. 二进制已编译安装包

2.1 下载二进制包

官网下载地址：https://www.enterprisedb.com/download-postgresql-binaries

下载地址：https://get.enterprisedb.com/postgresql/postgresql-10.23-1-linux-x64-binaries.tar.gz

2.2 创建postgres用户

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

2.3 解压安装包

安装包放到 /home/postgres 下

# 创建目录
mkdir -p /home/postgres
# 解压安装包
tar -xvf postgresql-10.23-1-linux-x64-binaries.tar.gz -C /home/postgres/
# yum依赖
yum install -y gcc gcc-c++
yum install -y readline-devel
yum install -y zlib-devel

2.4 创建data目录

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

2.5 启动登录PostgreSQL

初始化

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

启动

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

关闭

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

登录PostgreSQL数据库

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

开启远程访问

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

修改pg_hba.conf文件，增加下图红框部分内容

host    all             all             0.0.0.0/0            md5

3. yum安装PostgreSQL

安装过程官网参考：https://www.postgresql.org/download/linux/redhat/

3.1 安装

# 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 postgresql13-server

3.2 指定数据目录

chown postgres:postgres /data/pgdata
chmod 750 /data/pgdata

3.3 修改启动参数

vi /usr/lib/systemd/system/postgresql-13.service

修改PGDATA参数，该参数会在 postgresql-13-setup initdb 中使用

Environment=PGDATA=/data/pgdata

3.4 初始化数据库

# 切换用户
su - postgres
# 初始化数据库
sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
# 设置开机自动启动
sudo systemctl enable postgresql-13

4. 源码方式安装PostgreSQL

4.1 下载源码

从官网下载，地址：https://www.postgresql.org/download/

4.2 创建postgres用户

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

4.3 进行源码安装

# 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.4 修改配置文件并重新加载

vi /opt/postgresql/pgsqldata/pg_hba.conf
plaintext

host    all             all             0.0.0.0/0            md5


vi /opt/postgresql/pgsqldata/postgresql.conf
plaintext

listen_addresses = '*'

/opt/postgresql/bin/pg_ctl -D /opt/postgresql/pgsqlData reload

配置防火墙端口

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

4.5 设置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

5. 基本操作

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

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

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

# 查询
select * from test;

# 查看pgsql版本
SELECT version();

# 查看用户名和密码
SELECT * FROM pg_authid;

# 获取服务器上所有数据库信息
SELECT * FROM pg_database ORDER BY datname;

# 获取所有库表信息
select * from pg_tables ORDER BY schemaname;

# 获取当前
\d 库名 获取库中数据表名列表
\d 表名 获取表结构字段描述

# 备份与还原
/opt/postgresql/bin/pg_dump -h localhost -U postgres -p 5432 -d test -s -f /root/data.sql
/opt/postgresql/bin/psql -h localhost -p 5432 -U postgres -W -d test < /root/data.sql

原文