pg小工：pgsql的安装

pg小工：pgsql的安装

 

 

 

安装系统：Centos7 release 7.5 (Final)

 

三种安装方式

1. 二进制包安装，EnterpriseDB提供了两种PostgreSQL的二进制包，两种都无须编译安装，并且只提供到PostgreSQL 10.X版本

第一种：傻瓜向导二进制包

下载地址：https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

下载下来之后是一个二进制可执行文件

postgresql-10.21-1-linux-x64.run

[root@VM-0-12-centos tmp]# file -i postgresql-10.21-1-linux-x64.run       #postgresql-10.21-1-linux-x64.run 是一个二进制可执行文件
postgresql-10.21-1-linux-x64.run: application/x-executable; charset=binary[root@VM-0-12-centos tmp]# chmod +x  postgresql-10.21-1-linux-x64.run 
[root@VM-0-12-centos tmp]# ./postgresql-10.21-1-linux-x64.run   #全部默认下一步
----------------------------------------------------------------------------
Welcome to the PostgreSQL Setup Wizard.----------------------------------------------------------------------------
Please specify the directory where PostgreSQL will be installed.Installation Directory [/opt/PostgreSQL/10]: ----------------------------------------------------------------------------
Select the components you want to install; clear the components you do not want 
to install. Click Next when you are ready to continue.PostgreSQL Server [Y/n] :pgAdmin 4 [Y/n] :Stack Builder [Y/n] :Command Line Tools [Y/n] :Is the selection above correct? [Y/n]: ----------------------------------------------------------------------------
Please select a directory under which to store your data.Data Directory [/opt/PostgreSQL/10/data]: ----------------------------------------------------------------------------
Please provide a password for the database superuser (postgres). A locked Unix 
user account (postgres) will be created if not present.Password :
Retype password :
----------------------------------------------------------------------------
Please select the port number the server should listen on.Port [5432]: ----------------------------------------------------------------------------
Advanced OptionsSelect the locale to be used by the new database cluster.Locale[1] [Default locale]
[2] aa_DJ
[3] aa_DJ.iso88591
[4] aa_DJ.utf8
[5] aa_ER
[6] aa_ER@saaho
[7] aa_ER.utf8
[8] aa_ER.utf8@saaho
[9] aa_ET
[10] aa_ET.utf8
其他省略
Please choose an option [1] : 1----------------------------------------------------------------------------
Pre Installation SummaryThe following settings will be used for the installation::Installation Directory: /opt/PostgreSQL/10
Server Installation Directory: /opt/PostgreSQL/10
Data Directory: /opt/PostgreSQL/10/data
Database Port: 5432
Database Superuser: postgres
Operating System Account: postgres
Database Service: postgresql-10
Command Line Tools Installation Directory: /opt/PostgreSQL/10
pgAdmin4 Installation Directory: /opt/PostgreSQL/10/pgAdmin 4
Stack Builder Installation Directory: /opt/PostgreSQL/10
Installation Log: /tmp/install-postgresql.log
Press [Enter] to continue:----------------------------------------------------------------------------
Setup is now ready to begin installing PostgreSQL on your computer.Do you want to continue? [Y/n]: y----------------------------------------------------------------------------
Please wait while Setup installs PostgreSQL on your computer.Installing0% ______________ 50% ______________ 100%#########################################----------------------------------------------------------------------------
Setup has finished installing PostgreSQL on your computer.

安装完后，会在/opt/PostgreSQL/10/目录下存放安装文件

在/opt/PostgreSQL/10/data/目录下存放数据库数据文件

 

第二种：普通二进制包，需要解压安装

下载地址：https://get.enterprisedb.com/postgresql/ 

wget -c https://get.enterprisedb.com/postgresql/postgresql-10.1-1-linux-x64-binaries.tar.gz
tar zxvf  postgresql-10.1-1-linux-x64-binaries.tar.gz  -C /usr/local/  #解压二进制包关闭SELinux
setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
cat /etc/selinux/config关闭防火墙
systemctl stop firewalld.service
systemctl disable firewalld.service创建用户和组
groupadd postgres
useradd -g postgres postgres初始化到不同的数据库目录并做相应授权
mkdir -p /data/pgsql/pgsql5432/data/
chown -R postgres.postgres /data/pgsql/pgsql5432初始化和启动
su - postgres
/usr/local/pgsql/bin/initdb -D /data/pgsql/pgsql5432/data/   -E UTF8 --locale=C  --lc-collate=C  --lc-ctype=en_US.UTF8  -U postgres -k -W  
/usr/local/pgsql/bin/pg_ctl -D /data/pgsql/pgsql5432/data/ -l logfile start为用户设置环境变量
su - postgres
cat >>~/.bash_profile <<'EOT'
#PG10 configs
export PGHOME=/home/postgres
export PG_BIN=/usr/local/pgsql/bin/
export PATH=$PATH:$PG_BIN
export PGDATA=/data/pgsql/pgsql5432/data/
cd $PGDATA
EOTsource /home/postgres/.bash_profile
env |grep PGDATA启用远程访问/远程登录
vi $PGDATA/postgresql.conf
修改成如下，表示监听所有的IP：
listen_addresses = '*'vi $PGDATA/pg_hba.conf
添加下面一行，测试用途允许所有访问通过
host all all 0.0.0.0/0 trust重新加载配置
pg_ctl reload -D $PGDATApsql
psql.bin (10.1)
Type "help" for help.
postgres=# 
postgres=# 
postgres=# create table tt(id int);
CREATE TABLE

 

 

2. rpm/yum安装
percona也发行了rpm包，percona的包还包括了pgsql的其他周边工具，并且只提供到PostgreSQL 11.X版本
https://www.percona.com/downloads/percona-postgresql-11/LATEST/

可以自己下载rpm包，然后用rpm安装

 

yum安装

yum安装可以安装到最新版本PostgreSQL 14.X版本，下面是yum各种包用途：
postgresql14 - PostgreSQL client programs and libraries
postgresql14-libs - The shared libraries required for any PostgreSQL clients
postgresql14-contrib - Contributed source and binaries distributed with PostgreSQL
postgresql14-server - The programs needed to create and run a PostgreSQL server

关闭SELinux

setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
cat /etc/selinux/config

关闭防火墙

systemctl stop firewalld.service
systemctl disable firewalld.service

安装

安装yum源和依赖
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y gcc gcc-c++ perl-ExtUtils-Embed gmake zlib zlib-devel centos-release-scl-rh
yum install -y libaio-devel bison-devel tcl tcl-devel pam pam-devel libicu
yum install -y libxml2 libxml2-devel openssl openssl-devel readline readline-devel
yum install -y libxslt libxslt-devel openldap openldap-devel python python-devel
安装客户端和服务端
yum install -y postgresql14*
yum install -y postgresql14-server
安装开发包
yum install -y postgresql14-contrib.x86_64
yum install -y postgresql14-devel.x86_64
或
rpm安装
wget --no-check-certificate -c https://yum.postgresql.org/14/redhat/rhel-7-x86_64/postgresql14-14.2-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql14-14.2-1PGDG.rhel7.x86_64.rpm

创建用户和组

#yum安装时候会自动创建postgres用户和postgres用户组，要先把它删除
userdel -r postgres
groupdel postgres
#建postgres用户和组
groupadd postgres
useradd -g postgres postgres
passwd postgres

初始化到不同的数据库目录并做相应授权，然后启动

mkdir -p /data/pgsql/pgsql5432/data/
chown -R postgres.postgres /data/pgsql/pgsql5432/
chown -R postgres.postgres /var/run/postgresql/
su - postgres
/usr/pgsql-14/bin/initdb -D /data/pgsql/pgsql5432/data/
/usr/pgsql-14/bin/pg_ctl -D /data/pgsql/pgsql5432/data/ -l logfile start

查看数据库是否正在运行

/usr/pgsql-14/bin/pg_ctl -D /data/pgsql/pgsql5432/data/ status
pg_ctl: server is running (PID: 7284)
/usr/pgsql-14/bin/postgres "-D" "/data/pgsql/pgsql5432/data"

为用户设置环境变量，yum安装会自动设置一些环境变量，但最好还是手工设置环境变量

su - postgrescat >>~/.bash_profile <<'EOT'
#PG14 configs
export PGHOME=/home/postgres
export PG_BIN=/usr/pgsql-14/bin
export PATH=$PATH:$PG_BIN
export PGDATA=/data/pgsql/pgsql5432/data/
cd $PGDATA
EOTsource /home/postgres/.bash_profile
env |grep PGDATA

启用远程访问/远程登录

vi $PGDATA/postgresql.conf
修改成如下：
listen_addresses = '*'
表示监听所有的IPvi $PGDATA/pg_hba.conf
添加下面一行，测试用途允许所有访问通过
host all all 0.0.0.0/0 trust重新加载配置
pg_ctl reload -D $PGDATA

登录数据库，创建用户和数据库表

登录数据库
psql -d postgres创建用户，并进行授权
create database zdb;
create user zdb password 'zdb';
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA zdb TO zdb;
ALTER ROLE zdb WITH Superuser;创建表
\c zdb
CREATE TABLE weather (
city varchar(80),
temp_lo int, -- low temperature
temp_hi int, -- high temperature
prcp real, -- precipitation
date date
);

利用新建的用户登录测试

psql -h 127.0.0.1 -p 5432 -d zdb -U zdb -W
select * from weather;

使用systemd服务方式启动pgsql，yum/rpm安装完毕后默认自带systemd配置文件，文件路径如下

/usr/lib/systemd/system/postgresql-14.service

配置文件会关联服务文件

/etc/systemd/system/postgresql-14.service.d

文件内容如下，默认加了超时值和OOM调整值

# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades. It is recommended to use systemd
# "dropin" feature; i.e. create file with suffix .conf under
# /etc/systemd/system/postgresql-14.service.d directory overriding the
# unit's defaults. You can also use "systemctl edit postgresql-14"
# Look at systemd.unit(5) manual page for more info.# Note: changing PGDATA will typically require adjusting SELinux
# configuration as well.# Note: do not use a PGDATA pathname containing spaces, or you will
# break postgresql-14-setup.
[Unit]
Description=PostgreSQL 14 database server
Documentation=https://www.postgresql.org/docs/14/static/
After=syslog.target
After=network.target[Service]
Type=notifyUser=postgres
Group=postgres# Note: avoid inserting whitespace in these Environment= lines, or you may
# break postgresql-setup.# Location of database directory
Environment=PGDATA=/var/lib/pgsql/14/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
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj #环境变量
Environment=PG_OOM_ADJUST_VALUE=0ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-14/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed #主进程将收到 SIGTERM 信号，子进程收到 SIGKILL 信号，，强杀子进程，主进程慢慢退出
KillSignal=SIGINT #程序终止(interrupt)信号，针对前台进程# Do not set any timeout value, so that systemd will not kill postmaster
# during crash recovery.
TimeoutSec=0 # 同时设置TimeoutStartSec和TimeoutStopSec为相同的值# 0 is the same as infinity, but "infinity" needs systemd 229
TimeoutStartSec=0TimeoutStopSec=1h[Install]
WantedBy=multi-user.target

初始化数据库

/usr/pgsql-14/bin/postgresql-14-setup initdb

启动数据库

systemctl start postgresql-14

默认的数据库文件路径

/var/lib/pgsql/14/data/
/var/lib/pgsql/14/backups/

 

 

 

3. 源码编译安装

#安装pg14.3 （以硬盘为SSD来设置配置文件）1.建数据库目录
useradd   postgres 
pgport=5432
mkdir -p /usr/local/pgsql
mkdir -p /data/pgsql/pgsql$pgport/{data,tmp,logs}
mkdir -p /data/pgsql/pgsql$pgport/logs/archive_wals/chown -R postgres:postgres /data/pgsql/pgsql$pgport/
chown -R postgres:postgres /usr/local/pgsql/2.下载pg源码包cd /tmp
wget -c  --tries=0   --no-check-certificate   https://ftp.postgresql.org/pub/source/v14.3/postgresql-14.3.tar.gz3.安装pgsql依赖库
yum install -y  gcc gcc-c++ perl-ExtUtils-Embed gmake  zlib zlib-devel  centos-release-scl-rh
yum install -y  libaio-devel  bison-devel  tcl tcl-devel  pam pam-devel  libicu  libicu-devel
yum install -y  libxml2 libxml2-devel openssl openssl-devel readline readline-devel
yum install -y  libxslt libxslt-devel openldap openldap-devel python python-devel
yum install -y  llvm5.0  llvm5.0-devel  clang  systemd-devel  4.编译和安装pgsql，#确认安装结果echo $?
以root执行下面步骤
cd /tmp
tar xvf  postgresql-14.3.tar.gzcd  postgresql-14.3
chmod +x configure
#数据文件按5G一个切分(--with-segsize,单位为G,默认为1G)
./configure --prefix=/usr/local/pgsql/  --with-tcl --with-openssl --with-libxml --with-libxslt --enable-thread-safety  --with-segsize=5 
echo $?
gmake world -j 32 
echo $?
##All of PostgreSQL successfully made. Ready to install.（成功执行的输出）gmake install-world 
echo $?
##PostgreSQL installation complete.（成功执行的输出）##安装pgsql扩展
cd contrib/
make && make install 5.去软件安装目录，查看安装是否成功
ll  /usr/local/pgsql/bin6.编辑 .base_profile修改用户环境变量
su - postgrescat >> .bash_profile<< EOF
export PS1="\$USER@\`/bin/hostname -s\`-> "
export PGPORT=5432
export PGDATA=/data/pgsql/pgsql\$PGPORT/data
export LANG=en_US.utf8
export PGHOME=/usr/local/pgsql
export LD_LIBRARY_PATH=\$PGHOME/lib:/lib64:/usr/local/lib64:/usr/local/lib:\$LD_LIBRARY_PATH
export DATE="\`/bin/date +"%Y%m%d%H%M"\`"
export PATH=\$PGHOME/bin:\$PATH:.
export MANPATH=\$PGHOME/share/man:\$MANPATH
export PGHOST='127.0.0.1'
export PGDATABASE=postgres
export PGUSER=postgres
alias rm='rm -i'
alias ll='ls -lh'
alias startpg='/usr/local/pgsql/bin/pg_ctl -w -D \$PGDATA start'
alias stoppg='/usr/local/pgsql/bin/pg_ctl -D \$PGDATA -m fast stop'
EOFcat > .psqlrc<< EOF
\set PROMPT1  '%n@%M:%>[%/](%p)%R%#'
\set ECHO_HIDDEN
\x
\timing
EOF7.初始化数据库
su - postgres
pgport=5432
/usr/local/pgsql/bin/initdb -D /data/pgsql/pgsql$pgport/data -E UTF8 --locale=C  --lc-collate=C  --lc-ctype=en_US.UTF8 --wal-segsize=1024 -U postgres -k -W  
#WAL段尺寸按16MB划分(--with-wal-segsize,单位为M,默认为16MB)，可以设置为最大1G，--wal-segsize=1024也就是1GB一个wal文件8.编辑 postgresql.conf 
#替换include参数
pgport=5432
if  grep -q  "#include = ''"  /data/pgsql/pgsql$pgport/data/postgresql.conf
thensed -i "/^#include = ''/a\include = 'extrapostgresql.conf'\n"  /data/pgsql/pgsql$pgport/data/postgresql.conf
elsesed -i "/^#include = '...'/a\include = 'extrapostgresql.conf'\n"  /data/pgsql/pgsql$pgport/data/postgresql.conf
ficd /tmp/
vi  modpostgresqlconf.sh
#############################################################################################
#!/bin/bash
# Written by steven
# Name:        modpostgresqlconf.sh
# Version:      v1.0
# Function:     创建postgresql.conf
# Create Date:  2018-08-27port=5432   #设置端口cpucore=$(grep -c  processor  /proc/cpuinfo)  #cpu核心
memorytrue=$(free -m |grep Mem|awk '{ print $2}')   #内存if [ "$cpucore" -le 4 -a "$memorytrue" -le 7820 ];  #要大于4核8g内存才能跑pgsqlthenecho "Not meeting the requirements"exit 1
fi#根据当前环境计算某些参数的具体值
sharedbuffers=$(printf "%1.f\n" $(echo "$memorytrue*0.5"|bc))MB  #数据库最大内存
cpucoreprocess=$(echo "$cpucore-4"|bc)
cpucoreprocessdividedtwo=$(echo "$cpucoreprocess/2"|bc)
sharedbuffersdividedtwo=$(printf "%1.f\n" $(echo "$memorytrue*0.5*0.5"|bc))MBcat > /data/pgsql/pgsql$port/data/extrapostgresql.conf <<EOF
listen_addresses = '*'
port = $port
max_connections = 1000
superuser_reserved_connections = 3 
unix_socket_directories = '/data/pgsql/pgsql$port/tmp'
unix_socket_permissions = 0700
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
password_encryption = md5
shared_buffers = $sharedbuffers   
work_mem = 8MB
maintenance_work_mem = 1GB
autovacuum_work_mem = 1GB
max_files_per_process = 65536
vacuum_cost_delay = 0ms
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 8.0
bgwriter_flush_after = 512kB
effective_io_concurrency = 0
max_worker_processes = 512
max_parallel_maintenance_workers = $cpucoreprocessdividedtwo
max_parallel_workers_per_gather = $cpucoreprocessdividedtwo 
parallel_leader_participation = on
max_parallel_workers = $cpucoreprocess
old_snapshot_threshold = 6h
wal_level = replica
synchronous_commit = on
full_page_writes = on
wal_compression = on
wal_buffers = 16MB
wal_log_hints = on
checkpoint_timeout = 5min 
checkpoint_warning = 2min 
max_wal_size = 10GB
min_wal_size = 6GB
checkpoint_completion_target = 0.6
archive_mode = always
archive_command = 'if [ -f "/data/pgsql/pgsql$port/logs/archive_active" ]; then python /data/script/backupwalpgsql.py  -s %p -d %f; fi'max_wal_senders = 32
wal_keep_size = 5000
max_replication_slots = 32
synchronous_standby_names = ''
hot_standby = on
max_standby_archive_delay = 120s 
max_standby_streaming_delay = 0s  
wal_receiver_status_interval = 1s  
hot_standby_feedback = off
enable_partitionwise_join = on
enable_partitionwise_aggregate = on 
random_page_cost = 1.1
effective_cache_size = $sharedbuffers 
jit = on
jit_provider = 'llvmjit'
log_destination = 'csvlog'
logging_collector = on
log_directory = '/data/pgsql/pgsql$port/logs'
log_filename = 'pgsql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 0min
log_rotation_size = 0MB
log_min_duration_statement = 1s 
log_lock_waits = on
log_statement = 'ddl'
log_temp_files = 256MB
autovacuum = on
autovacuum_max_workers = 6
autovacuum_vacuum_scale_factor = 0.02 
autovacuum_analyze_scale_factor = 0.02 
autovacuum_freeze_max_age = 1200000000 
autovacuum_multixact_freeze_max_age = 1250000000
autovacuum_vacuum_cost_delay = 0ms
autovacuum_vacuum_threshold = 200
autovacuum_analyze_threshold = 200
idle_in_transaction_session_timeout = 6h
vacuum_freeze_table_age = 200000000
vacuum_multixact_freeze_table_age = 200000000
statement_timeout = 5min
lock_timeout = 5min
deadlock_timeout = 5min
row_security = off
shared_preload_libraries = 'pg_stat_statements,dblink'# Add settings for extensions here
pg_stat_statements.max = 1000
pg_stat_statements.track = top
pg_stat_statements.track_utility = on
pg_stat_statements.save = on
EOFif [  -e /data/pgsql/pgsql$port/data/extrapostgresql.conf   ]; then echo  "Configuration complete"; fi10.pgsql自启动
cat >> /etc/rc.local<< EOF
sleep 10
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
su  - postgres  -c "/usr/local/pgsql/bin/pg_ctl -w -D \$PGDATA start"
exit 0
EOF
chmod +x /etc/rc.d/rc.local 11.启动pgsql
pgport=5432
chown -R postgres:postgres   /data/pgsql/pgsql$pgport/
su  - postgres
/usr/local/pgsql/bin/pg_ctl -w -D /data/pgsql/pgsql5432/data/ start12.开启归档wal
mkdir -p /data/backup/pgsql/
chown -R postgres:postgres /data/backup/pgsql/
chown  postgres:postgres  /data/script/backupwalpgsql.py
touch  /data/pgsql/pgsql5432/logs/archive_active登录pg
/usr/local/pgsql/bin/psql  -h 127.0.0.1 -p 5432 -d postgres -U postgres##最重要一点，查看系统视图，检查pg是否读取到extrapostgresql.conf文件
select * from pg_settings;