安装准备

1、根据需要的版本下载对应的安装包

下载地址 https://www.postgresql.org/ftp/source/

2、准备数据库连接工具

1、pgAdmin
下载地址https://www.pgadmin.org/
2、DBeaver
下载和使用参考地址 https://blog.csdn.net/sunny_day_day/article/details/107065672
3、Navicat
【正版需购买授权】

3、创建安装目录

mkdir /usr/local/pgsql/

数据库使用官方帮助文档

postgres9系列

http://postgres.cn/docs/9.3/index.html
http://postgres.cn/docs/9.4/index.html
http://postgres.cn/docs/9.5/index.html
http://postgres.cn/docs/9.6/index.html

postgres10

http://postgres.cn/docs/10/index.html

postgres11

http://postgres.cn/docs/11/index.html

postgres数据库安装配置

解压安装文件

gunzip postgresql-10.1.tar.gz

tar xf postgresql-10.1.tar

安装必要的插件工具

yum install gcc readline-devel zlib-devel 

编译安装

cd 到安装文件解压目录

./configure

make

su

make install

postgres数据库启动运行

初始化运行

adduser postgres

mkdir /usr/local/pgsql/data

chown postgres /usr/local/pgsql/data

su - postgres

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

/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &

/usr/local/pgsql/bin/createdb test

/usr/local/pgsql/bin/psql test

初始化命令介绍

#新增user
adduser postgres
#修改密码
passwd postgres
#目录赋权给postgres
chown -R postgres:postgres pgsql
#切换到postgres
su postgres
#切换目录
cd pgsql
#创建数据存储的目录
mkdir data
chmod 700 data
#初始化数据库
cd bin
./pg_ctl initdb -D ../data
#启动数据
./pg_ctl -D ../data -l logfile start 

postgres数据库配置文件修改

启动数据库后修改postgres默认的账号密码

#进入数据库
./psql
alter USER postgres with PASSWORD 'postgres@pw654321';

修改postgresql.conf和pghba.conf配置文件

postgresql.conf的参数修改

listen_address = '*'
port = 5432
max_connections = 1000

pg_hba.conf 的配置修改

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
host    all             all             10.20.0.1/16            md5
# 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    replication     replica         10.20.0.17/32           md5

postgres恢复数据库

#创建待恢复的数据库
create database platform with encoding='utf8';

#恢复数据库
./pg_restore -U postgres -d platform -v  "/usr/local/pgsql/database_backup/platform2020071.backup"

创建postgresql.service文件，设置开机自启动

设置开机自启动，在/lib/systemd/system/下创建postgresql.service文件，文件内容：

【需根据实际安装情况修改】

[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
Type=forking
 
User=postgres
Group=postgres
 
# Port number for server to listen on
#Environment=PGPORT=5432
 
# Location of database directory
Environment=PGDATA=/home/pgsql/data
 
# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog
 
# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
 
#ExecStartPre=/home/pgsql/bin/postgresql-check-db-dir ${PGDATA}
ExecStart=/home/pgsql/bin/pg_ctl start -D ${PGDATA} 
ExecStop=/home/pgsql/bin/pg_ctl stop -D ${PGDATA} 
ExecReload=/home/pgsql/bin/pg_ctl reload -D ${PGDATA}
 
# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300
 
[Install]
WantedBy=multi-user.target

systemctl 方式启动 postgresql

#设置开启自启动
systemctl enable postgresql

#启动postgres
systemctl start postgresql

#查看状态
systemctl status postgresql