postgresql 12版本搭建及主备部署操作

# 网络检查

ping -c2 baidu.com

#关闭防火墙，selinux

systemctl stop firewalld && sudo systemctl disable firewalld

sed -ri s/SELINUX=enforcing/SELINUX=disabled/g /etc/selinux/config

setenforce 0

### 配置阿里云yum源

mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.bak

curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

#echo "isntalling tools"

yum -y install vim net-tools wget ntpdate

yum -y groupinstall "Development Tools"

# 时间同步

echo "*/10 * * * * /usr/sbin/ntpdate ntp.aliyun.com > /dev/null 2>&1" >>/etc/crontab

# 加快ssh连接

sed -ri s/ "#UseDNS yes" / "UseDNS no" /g /etc/ssh/sshd_config

systemctl restart sshd

## 环境准备，根据自身需要，减少或者增加

yum install -y wget gcc gcc-c++ epel-release llvm5.0 llvm5.0-devel clang libicu-devel perl-ExtUtils-Embed readline readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel openldap-devel systemd-devel tcl-devel python-devel

## 下载、解压，安装

cd /usr/ local /src

wget https://ftp.postgresql.org/pub/source/v12.0/postgresql-12.0.tar.gz

tar -xvzf postgresql-12.tar.gz

cd postgresql-12

./configure --prefix=/usr/local/pgsql/12 --enable-nls --with-python --with-tcl --with-gssapi --with-icu --with-openssl --with-pam --with-ldap --with-systemd --with-libxml --with-libxslt

make && make install

## 创建目录和用户,以及配置环境变量

groupadd postgres

useradd -g postgres postgres

## 密码设置在引号内输入自己的密码

echo "postgres" | passwd --stdin postgres

mkdir -p /data/pgdata/{data,archivedir}

chown -R postgres:postgres /usr/ local /pgsql -R

chown postgres:postgres /data/pgdata -R

echo "export PATH=/usr/local/pgsql/12/bin:$PATH" >/etc/profile.d/pgsql.sh

source /etc/profile.d/pgsql.sh

su - postgres

##初始化

initdb -D /data/pgdata/data -U postgres --locale=en_US.UTF8 -E UTF8

cp /data/pgdata/data/{pg_hba.conf,pg_hba.conf.bak}

## 允许repl用户进行流复制

cat <<-eof >>/data/pgdata/data/pg_hba.conf

host all     all     0.0.0.0/0    md5

host replication  repl   192.168.0.0/22   trust

eof

cp /data/pgdata/data/{postgresql.conf,postgresql.conf.bak}

##配置postgresql.conf

cat <<-eof >/data/pgdata/data/postgresql.conf

listen_addresses = '*'      

port = 5432

max_connections = 100

superuser_reserved_connections = 10

full_page_writes = on

wal_log_hints = off

max_wal_senders = 50

hot_standby = on

log_destination = 'csvlog'

logging_collector = on

log_directory = 'log'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S'

log_rotation_age = 1d

log_rotation_size = 10MB

log_statement = 'mod'

log_timezone = 'PRC'

timezone = 'PRC'

unix_socket_directories = '/tmp'

shared_buffers = 512MB

temp_buffers = 16MB

work_mem = 32MB

effective_cache_size = 2GB

maintenance_work_mem = 128MB

#max_stack_depth = 2MB

dynamic_shared_memory_type = posix

## PITR

full_page_writes = on

wal_buffers = 16MB

wal_writer_delay = 200ms

commit_delay = 0

commit_siblings = 5

wal_level = replica

archive_mode = on

archive_command = 'test ! -f /data/pgdata/archivedir/%f && cp %p /data/pgdata/archivedir/%f'

archive_timeout = 60s

eof

## 启动数据库

pg_ctl -D /data/pgdata/data -l /data/pgdata/data/serverlog start

## 创建用户进行流复制连接

psql -U postgres -c "ALTER USER postgres WITH PASSWORD 'postgres';"

psql -U postgres -c "CREATE USER repl WITH PASSWORD 'postgres' REPLICATION;"

## 备份复制，备库配置，

pg_basebackup -h 192.168.3.62 -p 5432 -U repl -w -Fp -Xs -Pv -R -D /data/pgdata/data

根据主库的postgresql.conf 配置文件 修改参数配置:

1, listen_addresses = '* '

2, wal_level = hot_standby

3, max_connections = xxx, # 一般比主库大一些.

4,hot_standby = on

5, max_standby_streaming_delay = 30s

6. wal_receiver_status_interval = 10s

7, hot_standby_feedback = on # 如果有错误的数据复制,是否向 主进行范例.

### 在postgre.auto.conf 添加 application_name =slave1,配置如下

primary_conninfo = 'application_name=slave1 user=repl passfile=' '/home/postgres/.pgpass' ' host=192.168.3.62 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'

## 启动备库

pg_ctl -D /data/pgdata/data start

##状态检查

### 回到主库查看状态

$ psql -c "select client_addr,usename,backend_start,application_name,sync_state,sync_priority FROM pg_stat_replication;"

  client_addr | usename |   backend_start   | application_name | sync_state | sync_priority

--------------+---------+-------------------------------+------------------+------------+---------------

  192.168.3.61 | repl | 2019-10-16 15:34:45.130732+08 | slave1   | async  |    0

(1 row)

$ psql -c "select client_addr, sync_state from pg_stat_replication;"

  client_addr | sync_state

--------------+------------

  192.168.3.61 | async

(1 row)

##备库只读, 所以任何操作, 都会告警失败:

postgres=# drop database pgtest;

ERROR: cannot execute DROP DATABASE in a read - only transaction

## 查看主备的状态

#主

ps -ef |grep sender

postgres 92996 51692 0 15:34 ?  0 postgres: walsender repl 192.168.3.61(53958) streaming 0/8000060

postgres 101845 20470 0 15:45 pts/2 0 grep --color=auto sender

#备

ps -ef |grep recover

postgres 98311 98309 0 15:34 ?  0 postgres: startup recovering 000000010000000000000008

postgres 108029 21054 0 15:46 pts/2 0 grep --color=auto recover

#通过此命令判断主备

pg_controldata -D /data/pgdata/data/ |grep state

in archive recovery ## 表示从库

in production  ## 表示主库

WITH AllUsers AS ( SELECT * FROM Users)

SELECT * FROM AllUsers WHERE Id = 100;

WITH UserRecord AS ( SELECT * FROM Users WHERE Id = 100)

SELECT * FROM UserRecord;

WITH AllUsers AS NOT MATERIALIZED ( SELECT * FROM Users)

SELECT * FROM AllUsers WHERE Id = 100;

CREATE TABLE Employee (

  ...,

  dob timestamp ,

  age integer GENERATED ALWAYS AS (date_part( 'year' , CURRENT_TIME - dob)) STORED

);

CREATE OR REPLACE FUNCTION calc_age() RETURNS TRIGGER AS $body$

BEGIN

  NEW.age := date_part( 'year' , CURRENT_TIME - NEW.dob:: timestamp );

  RETURN NEW;

END ;

$body$ LANGUAGE plpgsql;

-- SQL Server Syntax

CREATE TABLE Employee (

  ...,

  dob datetime,

  age AS DATEDIFF( year ,dob,GETDATE()) PERSISTED

);

CREATE TABLE test (x INTEGER );

INSERT INTO test SELECT generate_series(1, 1000000);

CREATE INDEX i_test ON test (x);

REINDEX INDEXCONCURRENTLY i_test;

CREATE STATISTICS stts3 (mcv) ON state, city FROM zipcodes;

ANALYZE zipcodes;