centos7安装pgsql

postgresql主从同步

目录

一、环境准备：

二、主库安装配置：

三、从库安装配置

四、 将服务设置为开机自启

 

一、环境准备：

主库IP：192.168.1.55

从库IP：192.168.1.56

安装介质准备

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

我这里下载的是11.6版本,介质如下:

postgresql-11.6.tar.gz

 

 

 贴个百度网盘分享

链接：https://pan.baidu.com/s/14biksO-jRu5EvLEhXuwHHw
提取码：vhji

 

二、主库安装配置：

1.安装依赖包

yum -y install readline  gcc  -y readline-devel zlib-devel

2.编译安装

tar -xvf postgresql-11.6.tar.gz

mkdir -p /opt/postgresql-11.6

 cd postgresql-11.6

./configure --prefix=/opt/postgresql-11.6 --with-blocksize=32  && make && make install

3.创建相应的用户

 groupadd postgres

 useradd -g postgres postgres

创建数据及日志目录，并做相应授权

mkdir -p /opt/postgresql-11.6/{data,log}

chown -R postgres:postgres /opt/postgresql-11.6

 

4.初始化数据库

su - postgres

[postgres@localhost bin]$ cd /opt/postgresql-11.6/bin

[postgres@localhost bin]$ ./initdb -D /opt/postgresql-11.6/data/

 

 

 

5.启动数据库

[postgres@localhost bin]$ cd /opt/postgresql-11.6/bin

[postgres@localhost bin]$./pg_ctl -D /opt/postgresql-11.6/data/ -l /opt/postgresql-11.6/log/postgres.log start

 

 

 

6.修改环境变量

[postgres@localhost ~]$ vim .bash_profile

# .bash_profile

 

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi

 

# User specific environment and startup programs

 

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/opt/postgresql-11.6/bin

 

export PATH

 

 

 

source .bash_profile

7. 登陆使用

[postgres@localhost bin]$cd /opt/postgresql-11.6/bin

[postgres@localhost bin]$ ./psql

 

 

 

8.修改postgres用户的访问密码并测试建库建表

9.PostgreSQL 数据库默认会创建一个postgres的数据库用户作为数据库的管理员，默认密码为空，我们需要修改为指定的密码，这里设定为postgres.

su - postgres

psql

# ALTER USER postgres WITH PASSWORD 'postgres';

# select * from pg_shadow ;

# create database hxl;

# \c hxl

 

project=# create table person(id integer, name text);

project=# insert into person values (1, 'hxl');

project=# select * from person;

 

 

 

10.配置postgresql允许远程访问

只需要修改data目录下的pg_hba.conf和postgresql.conf这两个文件:

pg_hba.conf：配置对数据库的访问权限;

postgresql.conf：配置PostgreSQL数据库服务器的相应的参数

 

11.vim /opt/postgresql-11.6/data/pg_hba.conf

 

 

 

 

12.重新加载配置文件

su - postgres

pg_ctl -D /opt/postgresql-11.6/data reload

 

13.修改postgresql.conf

vim /opt/postgresql-11.6/data/postgresql.conf

 

listen_addresses = '*'   # what IP address(es) to listen on;

14.修改该改参数需要重启动

 

pg_ctl -D /opt/postgresql-11.6/data -l /opt/postgresql-11.6/log/postgres.log stop

pg_ctl -D /opt/postgresql-11.6/data -l /opt/postgresql-11.6/log/postgres.log start

 

到这里主库已经按照好了,下面进行主库的配置

15.创建同步账号

[postgres@localhost data]$ psql

psql (11.6)

Type "help" for help.

 

postgres=# CREATE ROLE repl login replication encrypted password 'repl';

CREATE ROLE

 

 

 

 

16.修改配置文件(pg_hba.conf)

vim /opt/postgresql-11.6/data/pg_hba.conf

在该文件最后添加如下两行:

host    replication     repl            192.168.1.0/24          md5

host    all             repl            192.168.1.0/24          trust

 

 

 

 

17. 修改配置文件(postgresql.conf)

vim /opt/postgresql-11.6/data/postgresql.conf

找到相应的参数进行如下配置修改

wal_level = replica

archive_mode = on

archive_command = 'cp %p /opt/postgresql-11.6/data/pg_archive/%f'

##%p = path of file to archive

##%f = file name only

max_wal_senders = 6

wal_keep_segments = 10240

wal_sender_timeout = 60s

 

 

18.创建归档日志目录

mkdir -p /opt/postgresql-11.6/data/pg_archive

重启主库

pg_ctl -D /opt/postgresql-11.6/data/ -l /opt/postgresql-11.6/log/postgres.log restart

 

三、从库安装配置

1.从库的安装跟主库安装步骤一致,需要启动数据库

停掉从库

若从库的数据库已经在运行的话,事先将其停掉

[postgres@localhost data]$ pg_ctl -D /opt/postgresql-11.6/data/ -l /opt/postgresql-11.6/log/postgres.log stop

waiting for server to shut down.... done

server stopped

2.准备data目录

从库安装完成后，不初始化，若已经初始化，删除其data目录

若之前安装的pg有data目录的话需要将其删除掉,并创建一个空的相同的目录

su - postgres

[postgres@localhost postgresql-11.6]$ cd /opt/postgresql-11.6

[postgres@localhost postgresql-11.6]$ mv data bakdata

[postgres@localhost postgresql-11.6]$ mkdir data

root用户下修改权限

chown -R postgres:postgres /opt/postgresql-11.6

chmod 0700 /opt/postgresql-11.6/data

 

 

 

3.同步主库的数据文件

[postgres@localhost postgresql-11.6]$ pg_basebackup -Fp --progress -D /opt/postgresql-11.6/data -h 192.168.1.55 -p 5432 -U repl --password

输入密码 repl

 

 

 

查看

 

 

 

4.创建recovery.conf文件

从模板文件拷贝到data目录

[postgres@localhost share]$ cp /opt/postgresql-11.6/share/recovery.conf.sample /opt/postgresql-11.6/data/recovery.conf

 

对其进行修改，参数如下:

 

在data目录下创建recovery.conf文件,内容如下

standby_mode = on  # 这个说明这台机器为从库

primary_conninfo = 'host=192.168.1.55 port=5432 user=repl password=repl'  # 这个说明这台机器对应主库的信息

recovery_target_timeline = 'latest' # 这个说明这个流复制同步到最新的数据

 

 

 

5.修改从库postgresql.conf文件

修改如下内容项:

max_connections = 1000 #一般查多于写的应用从库的最大连接数要比较大

hot_standby = on       #说明这台机器不仅仅是用于数据归档，也用于数据查询

max_standby_streaming_delay = 30s  #数据流备份的最大延迟时间

wal_receiver_status_interval = 1s  #多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间

hot_standby_feedback = on          #如果有错误的数据复制，是否向主进行反馈

6.启动从库

pg_ctl -D /opt/postgresql-11.6/data/ -l /opt/postgresql-11.6/log/postgres.log start

7.主库上查看

 

 

 

对于如何主从切换具体看博客链接：

https://www.cnblogs.com/hxlasky/p/12192612.html

四、将服务设置为开机自启

1.修改postgresql文件配置

vim /etc/init.d/postgresql

#prefix设置为你的安装路径

#PGUSER设置为操作postgreSQL的用户（默认为postgres）

#PGLOG是日志路径

#注：如果设置的日志路径没有server.log,需要手动创建此文档

cd /opt/postgresql-11.6/data

touch server.log

以下为脚本内容：

#! /bin/sh

 

# chkconfig: 2345 98 02

# description: PostgreSQL RDBMS

 

# This is an example of a start/stop script for SysV-style init, such

# as is used on Linux systems.  You should edit some of the variables

# and maybe the 'echo' commands.

#

# Place this file at /etc/init.d/postgresql (or

# /etc/rc.d/init.d/postgresql) and make symlinks to

#   /etc/rc.d/rc0.d/K02postgresql

#   /etc/rc.d/rc1.d/K02postgresql

#   /etc/rc.d/rc2.d/K02postgresql

#   /etc/rc.d/rc3.d/S98postgresql

#   /etc/rc.d/rc4.d/S98postgresql

#   /etc/rc.d/rc5.d/S98postgresql

# Or, if you have chkconfig, simply:

# chkconfig --add postgresql

#

# Proper init scripts on Linux systems normally require setting lock

# and pid files under /var/run as well as reacting to network

# settings, so you should treat this with care.

 

# Original author:  Ryan Kirkpatrick <pgsql@rkirkpat.net>

 

# contrib/start-scripts/linux

 

## EDIT FROM HERE

 

# Installation prefix

prefix=/opt/postgresql-11.6

 

# Data directory

PGDATA="/opt/postgresql-11.6/data"

 

# Who to run the postmaster as, usually "postgres".  (NOT "root")

PGUSER=postgres

 

# Where to keep a log file

PGLOG="$PGDATA/serverlog"

 

# It's often a good idea to protect the postmaster from being killed by the

# OOM killer (which will tend to preferentially kill the postmaster because

# of the way it accounts for shared memory).  To do that, uncomment these

# three lines:

#PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj

#PG_MASTER_OOM_SCORE_ADJ=-1000

#PG_CHILD_OOM_SCORE_ADJ=0

# Older Linux kernels may not have /proc/self/oom_score_adj, but instead

# /proc/self/oom_adj, which works similarly except for having a different

# range of scores.  For such a system, uncomment these three lines instead:

#PG_OOM_ADJUST_FILE=/proc/self/oom_adj

#PG_MASTER_OOM_SCORE_ADJ=-17

#PG_CHILD_OOM_SCORE_ADJ=0

 

## STOP EDITING HERE

 

# The path that is to be used for the script

PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

 

# What to use to start up the postmaster.  (If you want the script to wait

# until the server has started, you could use "pg_ctl start" here.)

DAEMON="$prefix/bin/postmaster"

 

# What to use to shut down the postmaster

PGCTL="$prefix/bin/pg_ctl"

 

set -e

 

# Only start if we can find the postmaster.

test -x $DAEMON ||

{

echo "$DAEMON not found"

if [ "$1" = "stop" ]

then exit 0

else exit 5

fi

}

 

# If we want to tell child processes to adjust their OOM scores, set up the

# necessary environment variables.  Can't just export them through the "su".

if [ -e "$PG_OOM_ADJUST_FILE" -a -n "$PG_CHILD_OOM_SCORE_ADJ" ]

then

DAEMON_ENV="PG_OOM_ADJUST_FILE=$PG_OOM_ADJUST_FILE PG_OOM_ADJUST_VALUE=$PG_CHILD_OOM_SCORE_ADJ"

fi

 

 

# Parse command line parameters.

case $1 in

  start)

echo -n "Starting PostgreSQL: "

test -e "$PG_OOM_ADJUST_FILE" && echo "$PG_MASTER_OOM_SCORE_ADJ" > "$PG_OOM_ADJUST_FILE"

su - $PGUSER -c "$DAEMON_ENV $DAEMON -D '$PGDATA' >>$PGLOG 2>&1 &"

echo "ok"

;;

  stop)

echo -n "Stopping PostgreSQL: "

su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s"

echo "ok"

;;

  restart)

echo -n "Restarting PostgreSQL: "

su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s"

test -e "$PG_OOM_ADJUST_FILE" && echo "$PG_MASTER_OOM_SCORE_ADJ" > "$PG_OOM_ADJUST_FILE"

su - $PGUSER -c "$DAEMON_ENV $DAEMON -D '$PGDATA' >>$PGLOG 2>&1 &"

echo "ok"

;;

  reload)

echo -n "Reload PostgreSQL: "

su - $PGUSER -c "$PGCTL reload -D '$PGDATA' -s"

echo "ok"

;;

  status)

su - $PGUSER -c "$PGCTL status -D '$PGDATA'"

;;

  *)

# Print help

echo "Usage: $0 {start|stop|restart|reload|status}" 1>&2

exit 1

;;

esac

 

exit 0

 

2.给postgresql分配执行权限

chmod a+x /etc/init.d/postgresql

3.添加开机自启

chkconfig --add postgresql 

4.重启服务器验证设置是否成功（可不做）

reboot

5.查询服务启动是否正常

systemctl status postgresql

 

 

 

嘻嘻 好久没有更新了呢

因为最近一直都和L待在一起

嘿嘿嘿

补个情话叭

想你的夜晚 数羊没用
数星星没用 喝酒没用
没用 没用 没用
除了想你 什么都没用