一、下载安装
1.1 解压
https://www.postgresql.org/ftp/source/v10.3/
tar ‐zxvf postgresql‐10.3.tar.gz
1.2 安装依赖
# centos
yum ‐y install zlib‐devel readline‐devel
# ubuntu
sudo apt-cache search readline
sudo apt install lib64readline8 lib64readline-dev
sudo apt install zlib1g-dev
sudo apt install gcc
sudo apt install libreadline6-dev
sudo apt install make
1.3、编译安装
./configure ‐‐prefix=/usr/local/postgresql
make && make install
1.4 添加用户和用户组
useradd postgres
groupadd postgres
1.5 生成数据库文件目录
mkdir /usr/local/postgresql/data
1.6 修改用户文件访问权限
chown postgres /usr/local/postgresql/data
chgrp postgres /usr/local/postgresql/data
1.7 初始化数据库
# 用户切换
su ‐ postgres
/usr/local/postgresql/bin/initdb ‐D /usr/local/postgresql/data
二、修改配置
2.1 配置数据库允许访问的IP
vi /usr/local/postgresql/data/pg_hba.conf
配置IP都可以连接数据库(如需要所有IP都可以访问则对应为0.0.0.0/0),此处认证类型选择的是
MD5,可参考官方文档认证类型,选择适合的认证方式
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
#host all 10.130.212.158/24 md5
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
host all all 0.0.0.0/0 md5 #添加这一行
2.2 配置监听地址,链接端口等
vi /usr/local/postgresql/data/postgresql.conf
listen_addresses配置监听地址范围,改为*则为所有
port 默认为5432
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
#reserved_connections = 0 # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directories = '/tmp' # comma-separated list of directories
2.3 启动数据库服务
/usr/local/postgresql/bin/pg_ctl ‐D /usr/local/postgresql/data ‐l
/usr/local/postgresql/data/logfile start
三、创建用户名密码数据库
3.1 修改postgres密码
su ‐ postgres
/usr/local/postgresql/bin/psql
# \password postgres
Enter new password
3.2 创建数据库用户kong
CREATE USER kong WITH PASSWORD 'kong';
3.3 创建用户数据库,这里为kong,并指定所有者为kong
CREATE DATABASE kong OWNER kong;
3.4 将kong数据库的所有权限都赋予kong用户,否则kong只能登录控制台,没有任何数据库操作权
限
GRANT ALL PRIVILEGES ON DATABASE kong to kong;
3.5 退出控制台并验证
[root@k8s‐node01 ~]# psql ‐U kong ‐d kong ‐h 10.131.100.95 ‐p 5432
Password for user kong:
psql (10.3)
Type "help" for help.
kong=>
四、常见错误
4.1 psql: symbol lookup error: /usr/local/postgresql/bin/psql: undefined symbol: PQhostaddr
解决方法添加如下环境变量
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/postgresql/lib
4.2 Navicat连接postgresql时出现“ERROR: column datlastsysoid“ does not exist LINE 1: SELECT DISTINCT datlas“报错
原因:Postgres 15 从表中删除了 datlastsysoid 字段pg_database,因此 Navicat 15.0.29 或 16.1 之前的任何版本在查找此已弃用字段时都会引发此错误
解决(navicat15版本为例):
方法一:安装16.1及以上版本
方法二:关闭navicat,打开navicat安装目录,找到libcc.dll文件(先备份一份防止后面出问题方便还原),使用十六进制编辑器打开libcc.dll文件,推荐使用在线编辑器https://hexed.it/,打开编辑器后导入libcc.dll文件,ctrl+f 搜索"SELECT DISTINCT datlastsysoid",找到后将"datlastsysoid" 替换为"dattablespace",将文件另存为到初始位置,打开navicat,测试连接打开数据库,可以正常使用。