资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

立即前往
首页/文章专栏/postgresql10主备流复制测试

postgresql10主备流复制测试

IT那活儿 / 784人阅读
postgresql10主备流复制测试

点击上方“IT那活儿”，关注后了解更多精彩内容！！！


 环  境 



主机操作系统IP数据库
postgresql1Red Hat Enterprise 6.5192.168.20.101PostgreSQL10.0
postgresql2Red Hat Enterprise 6.5192.168.20.102PostgreSQL10.0



 安装PostgreSQL 


1. 检查依赖包

</>复制代码 
  1. rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})
  2. " | grep -E "perl-ExtUtils-Embed|readline-
    devel|zlib-devel|pam-devel|libxml2-devel|libxslt-devel|openldap-devel|
    python-devel|gcc-c++|openssl-devel|cmake|readline|openssl|zlib"
2. yum源配置
</>复制代码 
  1. vi source.repo
    [rhel-source-beta]
    name=Source
    baseurl=file:///media
    enabled=1
    gpgcheck=0
3. 安装依赖包
</>复制代码 
  1. yum install -y readline-devel zlib-devel pam-devel libxml2-
    devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake
4. 创建用户及目录
</>复制代码 
  1. groupadd -g 1000 postgres
    useradd -u 1100 -g postgres -G postgres -d /home/postgres postgres

    mkdir -p /pgadmin/pg_data
    mkdir -p /pgadmin/postgres10

    chown -R postgres:postgres /pgadmin/pg_data
    chown -R postgres:postgres /pgadmin/postgres10
5. 编译安装
--下载源码包postgresql-10.0.tar.gz上传到服务器：
</>复制代码 
  1. cp postgresql-10.0.tar.gz /tmp/soft
    tar -xvf postgresql-10.0.tar.gz
--编译安装：
</>复制代码 
  1. cd postgresql-10.0
    ./configure --prefix=/pgadmin/postgres10

    make world
    make install-world
--日志：
./configure --prefix=/pgadmin/postgres10
make word
make install-world

6. 初始化数据库

--初始化（密码：postgres）：

</>复制代码 
  1. mkdir -p /pgadmin/pg_data/10.0/{data,backups,scripts,archive_wals}
    /pgadmin/pgsql/bin/initdb -D /pgadmin/pg_data/10.0/data/ -W
--日志：
</>复制代码 
  1. [postgres@postgresql2 ~]$ /pgadmin/pgsql/bin/initdb -D /pgadmin/pg_data/10.0/data/ -W
    The files belonging to this database system will be owned by user "postgres".
    This user must also own the server process.

    The database cluster will be initialized with locale "en_US.UTF-8".
    The default database encoding has accordingly been set to "UTF8".
    The default text search configuration will be set to "english".

    Data page checksums are disabled.

    Enter new superuser password:
    Enter it again:

    fixing permissions on existing directory /pgadmin/pg_data/10.0/data ... ok
    creating subdirectories ... ok
    selecting default max_connections ... 100
    selecting default shared_buffers ... 128MB
    selecting dynamic shared memory implementation ... posix
    creating configuration files ... ok
    running bootstrap script ... ok
    performing post-bootstrap initialization ... ok
    syncing data to disk ... ok

    WARNING: enabling "trust" authentication for local connections
    You can change this by editing pg_hba.conf or using the option -A, or
    --auth-local and --auth-host, the next time you run initdb.

    Success. You can now start the database server using:

    /pgadmin/pgsql/bin/pg_ctl -D /pgadmin/pg_data/10.0/data/ -l logfile start

    [postgres@postgresql2 ~]$

7. 创建软连接

ln -s /pgadmin/postgres10 /pgadmin/pgsql

8. 配置环境变量

如果不配置PGHOST，后面通过psql登陆会报错。
</>复制代码 
  1. export PGSQL_HOME=/pgadmin/pgsql
    export PGHOST=/pgadmin/pg_data/10.0/data
    export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PGSQL_HOME/lib
    export PATH=$PGSQL_HOME/bin:$PATH
    export PGDATA=/pgadmin/pg_data/10.0/data

9. 配置互信

--在两个节点执行：

</>复制代码 
  1. mkdir /home/postgres/.ssh
    chmod 600 /home/postgres/.ssh
    ssh-keygen -t dsa
    ssh-keygen -t rsa
    cd /home/postgres/.ssh
    cat id_dsa.pub > authorized_keys
    cat id_rsa.pub >> authorized_keys

--在node1执行：

</>复制代码 
  1. ssh postgresql2 cat /home/postgres/.ssh/authorized_keys >>authorized_keys
    scp authorized_keys postgresql2:/home/postgres/.ssh/

10. 修改配置文件

</>复制代码 
  1. cd /pgadmin/pg_data/10.0/data
    cp   pg_hba.conf  pg_hba.conf20200904
    vi pg_hba.conf（增加如下信息）
    host all all  0.0.0.0/0  md5


</>复制代码 
  1. vi postgresql.conf
    #添加如下内容
    listen_addresses = *
    port = 5432
    superuser_reserved_connections = 20
    unix_socket_directories = /pgadmin/pg_data/10.0/data
    unix_socket_permissions = 0700
    tcp_keepalives_idle = 60
    tcp_keepalives_interval = 10
    tcp_keepalives_count = 10
    vacuum_cost_delay = 10
    bgwriter_delay = 10ms
    synchronous_commit = off
    #checkpoint_segments = 8
    wal_writer_delay = 10ms
    log_destination = csvlog
    logging_collector = on
    log_directory = pg_log
    log_filename =postgresql-%Y-%m-%d_%H%M%S.log
    log_file_mode = 0600
    log_truncate_on_rotation = on
    log_rotation_age = 1d
    log_rotation_size = 10MB


    pg_ctl start 启动数据库
    pg_ctl -m fast stop 停止数据库
    pg_ctl restart 重启数据库

    ps -ef | grep postgres 数据库服务是否正常启动
    psql -h 127.0.0.1 -d postgres -U postgres 登录数据库测试
</>复制代码 
  1. [postgres@postgresql1 data]$ pg_ctl start
    pg_ctl: another server might be running; trying to start server anyway
    waiting for server to start....2020-09-04 02:04:48.182 GMT [3524] LOG: listening on IPv4 address "0.0.0.0", port 5432
    2020-09-04 02:04:48.182 GMT [3524] LOG: listening on IPv6 address "::", port 5432
    2020-09-04 02:04:48.186 GMT [3524] LOG: listening on Unix socket "/pgadmin/pg_data/10.0/data/.s.PGSQL.5432"
    2020-09-04 02:04:48.222 GMT [3524] LOG: redirecting log output to logging collector process
    2020-09-04 02:04:48.222 GMT [3524] HINT: Future log output will appear in directory "pg_log".
    done
    server started
    [postgres@postgresql1 data]$
    [postgres@postgresql1 data]$ ps -ef|grep postgres
    root 3465  2596  0 09:59 pts/0    00:00:00 su - postgres
    postgres 3466  3465  0 09:59 pts/0    00:00:00 -bash
    postgres 3524     1  0 10:04 pts/0    00:00:00 /pgadmin/postgres10/bin/postgres
    postgres 3525  3524  0 10:04 ? 00:00:00 postgres: logger process
    postgres 3527  3524  0 10:04 ? 00:00:00 postgres: checkpointer process
    postgres 3528  3524  0 10:04 ? 00:00:00 postgres: writer process
    postgres 3529  3524  0 10:04 ? 00:00:00 postgres: wal writer process
    postgres 3530  3524  0 10:04 ? 00:00:00 postgres: autovacuum launcher process
    postgres 3531  3524  0 10:04 ? 00:00:00 postgres: stats collector process
    postgres 3532  3524  0 10:04 ? 00:00:00 postgres: bgworker: logical replication launcher
    postgres 3533  3466  7 10:04 pts/0    00:00:00 ps -ef
    postgres 3534  3466  0 10:04 pts/0    00:00:00 grep postgres
    [postgres@postgresql1 data]$
    [postgres@postgresql1 data]$ pg_ctl -m fast stop
    waiting for server to shut down.... done
    server stopped
    [postgres@postgresql1 data]$ pg_ctl restart
    pg_ctl: PID file "/pgadmin/pg_data/10.0/data/postmaster.pid" does not exist
    Is server running?
    starting server anyway
    waiting for server to start....2020-09-04 02:05:25.327 GMT [3538] LOG: listening on IPv4 address "0.0.0.0", port 5432
    2020-09-04 02:05:25.327 GMT [3538] LOG: listening on IPv6 address "::", port 5432
    2020-09-04 02:05:25.330 GMT [3538] LOG: listening on Unix socket "/pgadmin/pg_data/10.0/data/.s.PGSQL.5432"
    2020-09-04 02:05:25.336 GMT [3538] LOG: redirecting log output to logging collector process
    2020-09-04 02:05:25.336 GMT [3538] HINT: Future log output will appear in directory "pg_log".
    done
    server started
    [postgres@postgresql1 data]$ ps -ef | grep postgres
    root 3465  2596  0 09:59 pts/0    00:00:00 su - postgres
    postgres 3466  3465  0 09:59 pts/0    00:00:00 -bash
    postgres 3538     1  0 10:05 pts/0    00:00:00 /pgadmin/postgres10/bin/postgres
    postgres 3539  3538  0 10:05 ? 00:00:00 postgres: logger process
    postgres 3541  3538  0 10:05 ? 00:00:00 postgres: checkpointer process
    postgres 3542  3538  0 10:05 ? 00:00:00 postgres: writer process
    postgres 3543  3538  0 10:05 ? 00:00:00 postgres: wal writer process
    postgres 3544  3538  0 10:05 ? 00:00:00 postgres: autovacuum launcher process
    postgres 3545  3538  0 10:05 ? 00:00:00 postgres: stats collector process
    postgres 3546  3538  0 10:05 ? 00:00:00 postgres: bgworker: logical replication launcher
    postgres 3547  3466  2 10:05 pts/0    00:00:00 ps -ef
    postgres 3548  3466  0 10:05 pts/0    00:00:00 grep postgres
    [postgres@postgresql1 data]$ psql -h 127.0.0.1 -d postgres -U postgres
    psql (10.0)
    Type "help" for help.

    postgres=#

11. 新创建postgresql用户

</>复制代码 
  1. create role cyl superuser;
    alter role cyl password cyl;
    alter role cyl login;

--创建。



 postgressql流复制配置 


1. 主节点配置准备工作

cd $PGDATA
vi pg_hba.conf

--添加如下内容：

</>复制代码 
  1. host replication replica 0.0.0.0/0               md5
    vi postgresql.conf
--添加如下内容：
</>复制代码 
  1. listen_addresses = *
    port = 5432
    max_connections = 500
--然后重启一下数据库就可以正常使用了，至此前期准备工作已经完成。
pg_ctl start 启动数据库。

2. 主库配置

2.1 修改postgresql.conf，在文件末尾增加以下属性：

vi postgresql.conf

--接着在文件末尾添加如下：

</>复制代码 
  1. wal_level = hot_standby
    checkpoint_segments = 16
    checkpoint_timeout = 5min
    archive_mode = on
    max_wal_senders = 3
    wal_keep_segments = 16

    wal_level = hot_standby
--这个是设置主为wal的主机。
max_wal_senders = 3
--这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个。
wal_keep_segments = 16
--设置流复制保留的最多的xlog数目。
checkpoint_segments = 16
--WAL log的最大数量，系统默认值是3。超过该数量的WAL日志，会自动触发checkpoint。
checkpoint_timeout=5min
--系统自动执行checkpoint之间的最大时间间隔。系统默认值是5分钟。
--重启报错，将刚才的文件postgresql.conf中的checkpoint_segments这行注释掉。

2.2 创建具有replication的权限用户

暂时还没研究透这个用户的作用
</>复制代码 
  1. su - postgresql
    psql -h 127.0.0.1 -d postgres -U postgres 登录数据库
    create role replica login replication encrypted password replica; 创建用户
2.3 配置密码文件
</>复制代码 
  1. cd /home/postgresql
    vi .pgpass
    #主数据库IP:主数据库端口号:replication:replica:replica
    192.168.20.101:5432:replica:replica
    chmod 400 .pgpass
    #重启数据库
    pg_ctl restart

3. 备库配置

3.1 停止备库的PostgreSQL服务

pg_ctl stop

3.2 mv备库之前的数据文件
</>复制代码 
  1. mv /pgadmin/pg_data/10.0/data /pgadmin/pg_data/10.0/data_bak
3.3 从主库上恢复数据
</>复制代码 
  1. pg_basebackup -D $PGDATA -F p -X stream -v -P -h 192.168.20.101 -U cyl
--报错：
--在主节点的pg_hba.conf中加入如下内容：
</>复制代码 
  1. host   replication cyl 0.0.0.0/0               md5
--重新执行pg_basebackup，成功。
</>复制代码 
  1. [postgres@postgresql2 10.0]$ pg_basebackup -D $PGDATA -F p -X stream -v -P -h 192.168.20.101 -U cyl
    pg_basebackup: could not connect to server: FATAL: no pg_hba.conf entry for replication connection from host "192.168.20.102", user "cyl"
    pg_basebackup: removing contents of data directory "/pgadmin/pg_data/10.0/data"
    [postgres@postgresql2 10.0]$ pg_basebackup -D $PGDATA -F p -X stream -v -P -h 192.168.20.101 -U cyl
    Password:
    pg_basebackup: initiating base backup, waiting for checkpoint to complete
    WARNING: skipping special file "./.s.PGSQL.5432"
    pg_basebackup: checkpoint completed
    pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
    pg_basebackup: starting background WAL receiver
    WARNING: skipping special file "./.s.PGSQL.5432"/10.0/data/backup_label)
    31296/31296 kB (100%), 1/1 tablespace
    pg_basebackup: write-ahead log end point: 0/20000F8
    pg_basebackup: waiting for background process to finish streaming ...
    pg_basebackup: base backup completed
    [postgres@postgresql2 10.0]$
3.4 修改postgresql.conf
</>复制代码 
  1. #添加
    cd $PGDATA
    vi postgresql.conf
    hot_standby = on
3.5 增加recovery.conf文件
</>复制代码 
  1. vi recovery.conf
    standby_mode = on
    recovery_target_timeline = latest
    primary_conninfo = host=192.168.20.101 port=5432 user=cyl password=cyl

    赋予权限
    chmod 755 recovery.conf
3.6 启动数据库
</>复制代码 
  1. [postgres@postgresql2 data]$ pg_ctl start
    waiting for server to start....2020-09-04 03:11:25.151 GMT [11232] FATAL: data directory "/pgadmin/pg_data/10.0/data" has group or world access
    2020-09-04 03:11:25.151 GMT [11232] DETAIL: Permissions should be u=rwx (0700).
    stopped waiting
    pg_ctl: could not start server
    Examine the log output.
    [postgres@postgresql2 data]$
--启动时出现报错，data目录权限错误，需要修改成700。

</>复制代码 
  1. [postgres@postgresql2 10.0]$ ls -trl
    total 20
    drwxrwxr-x. 2 postgres postgres 4096 Sep 4 10:12 scripts
    drwxrwxr-x. 2 postgres postgres 4096 Sep 4 10:12 backups
    drwxrwxr-x. 2 postgres postgres 4096 Sep 4 10:12 archive_wals
    drwx------. 20 postgres postgres 4096 Sep 4 10:37 data_bak
    drwxrwxr-x. 20 postgres postgres 4096 Sep 4 11:08 data
    [postgres@postgresql2 10.0]$ chmod 700 data
    [postgres@postgresql2 10.0]$ ls -trl
    total 20
    drwxrwxr-x. 2 postgres postgres 4096 Sep 4 10:12 scripts
    drwxrwxr-x. 2 postgres postgres 4096 Sep 4 10:12 backups
    drwxrwxr-x. 2 postgres postgres 4096 Sep 4 10:12 archive_wals
    drwx------. 20 postgres postgres 4096 Sep 4 10:37 data_bak
    drwx------. 20 postgres postgres 4096 Sep 4 11:08 data
    [postgres@postgresql2 10.0]$


--修改权限后正常拉起数据库。


</>复制代码 
  1. [postgres@postgresql2 data]$ pg_ctl start
    waiting for server to start....2020-09-04 03:12:29.191 GMT [11242] LOG: listening on IPv4 address "0.0.0.0", port 5432
    2020-09-04 03:12:29.191 GMT [11242] LOG: listening on IPv6 address "::", port 5432
    2020-09-04 03:12:29.195 GMT [11242] LOG: listening on Unix socket "/pgadmin/pg_data/10.0/data/.s.PGSQL.5432"
    2020-09-04 03:12:29.221 GMT [11242] LOG: redirecting log output to logging collector process
    2020-09-04 03:12:29.221 GMT [11242] HINT: Future log output will appear in directory "pg_log".
    done
    server started
    [postgres@postgresql2 data]$ ps -aux | grep postgres
    Warning: bad syntax, perhaps a bogus -? See /usr/share/doc/procps-3.2.8/FAQ
    root 10971  0.0  0.1 163748  2012 pts/0    S 10:22   0:00 su - postgres
    postgres 10972  0.0  0.1 108472  1972 pts/0    S 10:22   0:00 -bash
    root 11148  0.0  0.1 163748  2008 pts/1    S 10:59   0:00 su - postgres
    postgres 11149  0.0  0.0 108340  1824 pts/1    S+ 10:59   0:00 -bash
    postgres 11242  0.0  0.9 152440 18452 pts/0    S 11:12   0:00 /pgadmin/postgres10/bin/postgres
    postgres 11243  0.0  0.0 117072   884 ? Ss 11:12   0:00 postgres: logger process
    postgres 11244  0.0  0.0 152560  1692 ? Ss 11:12   0:00 postgres: startup process waiting for 000000010000000000000003
    postgres 11245  0.0  0.0 152440  1108 ? Ss 11:12   0:00 postgres: checkpointer process
    postgres 11246  0.2  0.0 152440  1092 ? Ss 11:12   0:00 postgres: writer process
    postgres 11247  0.0  0.0 119168   836 ? Ss 11:12   0:00 postgres: stats collector process
    postgres 11279 10.0  0.0 110236  1148 pts/0    R+ 11:14   0:00 ps -aux
    postgres 11280  0.0  0.0 103252   836 pts/0    S+ 11:14   0:00 grep postgres
    [postgres@postgresql2 data]$

4. 主备验证

4.1 进程验证

--在主库的服务器上输入命令，会看到多出一个wal sender process的进程：
＃ps -aux | grep postgres
</>复制代码 
  1. ＃ps -aux | grep postgres
    [postgres@postgresql1 data]$ ps -aux | grep postgres
    Warning: bad syntax, perhaps a bogus -? See /usr/share/doc/procps-3.2.8/FAQ
    root 3614  0.0  0.0 163748  2004 pts/0    S 10:21   0:00 su - postgres
    postgres 3615  0.0  0.0 108440  1892 pts/0    S 10:21   0:00 -bash
    postgres 3870  0.0  0.4 152440 18468 pts/0    S 11:03   0:00 /pgadmin/postgres10/bin/postgres
    postgres 3871  0.0  0.0 117072   884 ? Ss 11:03   0:00 postgres: logger process
    postgres 3873  0.0  0.0 152708  3132 ? Ss 11:03   0:00 postgres: checkpointer process
    postgres 3874  0.3  0.0 152572  1588 ? Ss 11:03   1:07 postgres: writer process
    postgres 3875  0.0  0.0 152440  1496 ? Ss 11:03   0:02 postgres: wal writer process
    postgres 3876  0.0  0.0 153204  2028 ? Ss 11:03   0:00 postgres: autovacuum launcher process
    postgres 3877  0.0  0.0 119168   956 ? Ss 11:03   0:00 postgres: archiver process
    postgres 3878  0.0  0.0 119300  1100 ? Ss 11:03   0:00 postgres: stats collector process
    postgres 3879  0.0  0.0 153044  1680 ? Ss 11:03   0:00 postgres: bgworker: logical replication launcher
    root 4009  0.0  0.0 163748  2008 pts/1    S 11:23   0:00 su - postgres
    postgres 4010  0.0  0.0 108340  1892 pts/1    S 11:23   0:00 -bash
    postgres 4050  0.0  0.0 153724  2904 ? Ss 11:29   0:00 postgres: wal sender process cyl 192.168.20.102(34150) streaming 0/3104F00
    postgres 5058  0.0  0.0 117304  1756 pts/0    S+ 15:46   0:00 psql
    postgres 5059  0.0  0.0 153732  2936 ? Ss 15:46   0:00 postgres: postgres postgres [local] idle
    postgres 5510  7.0  0.0 110240  1148 pts/1    R+ 17:11   0:00 ps -aux
    postgres 5511  0.0  0.0 103252   836 pts/1    S+ 17:11   0:00 grep postgres
--在备库的服务器上输入命令，会看到多出一个 wal receiver process 的进程：
＃ps -aux | grep postgres

</>复制代码 
  1. ＃ps -aux | grep postgres
    [postgres@postgresql2 data]$ ps -aux | grep postgres
    Warning: bad syntax, perhaps a bogus -? See /usr/share/doc/procps-3.2.8/FAQ
    root 10971  0.0  0.1 163748  2012 pts/0    S 10:22   0:00 su - postgres
    postgres 10972  0.0  0.1 108472  1972 pts/0    S 10:22   0:00 -bash
    root 11148  0.0  0.1 163748  2008 pts/1    S 10:59   0:00 su - postgres
    postgres 11149  0.0  0.0 108340  1824 pts/1    S+ 10:59   0:00 -bash
    postgres 11242  0.0  0.9 152440 18452 pts/0    S 11:12   0:00 /pgadmin/postgres10/bin/postgres
    postgres 11243  0.0  0.0 117072   884 ? Ss 11:12   0:00 postgres: logger process
    postgres 11244  0.0  0.0 152560  1692 ? Ss 11:12   0:00 postgres: startup process waiting for 000000010000000000000003
    postgres 11245  0.0  0.0 152440  1108 ? Ss 11:12   0:00 postgres: checkpointer process
    postgres 11246  0.2  0.0 152440  1092 ? Ss 11:12   0:00 postgres: writer process
    postgres 11247  0.0  0.0 119168   836 ? Ss 11:12   0:00 postgres: stats collector process
    postgres 11279 10.0  0.0 110236  1148 pts/0    R+ 11:14   0:00 ps -aux
    postgres 11280  0.0  0.0 103252   836 pts/0    S+ 11:14   0:00 grep postgres
    [postgres@postgresql2 data]$

--无wal receiver process 进程，经核查recovery.conf用户名写错，修改recovery.conf后，重启数据库。

</>复制代码 
  1. [postgres@postgresql2 data]$ cat recovery.conf
    standby_mode = on
    recovery_target_timeline = latest
    primary_conninfo = host=192.168.20.101 port=5432 user=cyl password=cyl
    [postgres@postgresql2 data]
    [postgres@postgresql2 data]$ pg_ctl restart
    waiting for server to shut down.... done
    server stopped
    waiting for server to start....2020-09-04 03:17:00.052 GMT [11319] LOG: listening on IPv4 address "0.0.0.0", port 5432
    2020-09-04 03:17:00.052 GMT [11319] LOG: listening on IPv6 address "::", port 5432
    2020-09-04 03:17:00.055 GMT [11319] LOG: listening on Unix socket "/pgadmin/pg_data/10.0/data/.s.PGSQL.5432"
    2020-09-04 03:17:00.070 GMT [11319] LOG: redirecting log output to logging collector process
    2020-09-04 03:17:00.070 GMT [11319] HINT: Future log output will appear in directory "pg_log".
    done
    server started
    [postgres@postgresql2 data]$
    [postgres@postgresql2 data]$
    [postgres@postgresql2 data]$ ps -aux | grep postgres
    Warning: bad syntax, perhaps a bogus -? See /usr/share/doc/procps-3.2.8/FAQ
    root 10971  0.0  0.1 163748  2012 pts/0    S 10:22   0:00 su - postgres
    postgres 10972  0.0  0.1 108472  1972 pts/0    S 10:22   0:00 -bash
    root 11148  0.0  0.1 163748  2008 pts/1    S 10:59   0:00 su - postgres
    postgres 11149  0.0  0.0 108340  1824 pts/1    S+ 10:59   0:00 -bash
    postgres 11319  0.0  0.9 152440 18456 pts/0    S 11:16   0:00 /pgadmin/postgres10/bin/postgres
    postgres 11320  0.0  0.0 117072   888 ? Ss 11:16   0:00 postgres: logger process
    postgres 11321  0.0  0.0 152560  1648 ? Ss 11:16   0:00 postgres: startup process recovering 000000010000000000000003
    postgres 11322  0.0  0.0 152440  1116 ? Ss 11:16   0:00 postgres: checkpointer process
    postgres 11323  0.2  0.0 152440  1100 ? Ss 11:16   0:00 postgres: writer process
    postgres 11324  0.0  0.0 119168   844 ? Ss 11:16   0:00 postgres: stats collector process
    postgres 11325  0.1  0.1 156820  1948 ? Ss 11:16   0:00 postgres: wal receiver process streaming 0/3000140
    postgres 11326  0.0  0.0 110236  1148 pts/0    R+ 11:17   0:00 ps -aux
    postgres 11327  0.0  0.0 103252   836 pts/0    S+ 11:17   0:00 grep postgres
    [postgres@postgresql2 data]$

4.2 主库上创建一个用户和数据库验证

--备库核查：
核查发现备库已经crash，重启报错。
根据报错日志查看是因为备库主机没有/pgadmin/pg_data/10.0/data/tbs_pgtest目录导致，备库手动创建目录，重新拉起数据库。
--拉起数据库后可以看到，mytestdb1已经同步到备库。
</>复制代码 
  1. [postgres@postgresql2 pg_log]$ more postgresql-2020-09-04_032720.csv
    2020-09-04 03:27:20.478 GMT,,,11358,,5f51b418.2c5e,1,,2020-09-04 03:27:20 GMT,,0,LOG,00000,"ending log output to stderr",,"Future log output will go to log destination ""csvlog"".",,,,,,,""
    2020-09-04 03:27:20.554 GMT,,,11360,,5f51b418.2c60,1,,2020-09-04 03:27:20 GMT,,0,LOG,00000,"database system was interrupted while in recovery at log time 2020-09-04 03:08:46 GMT",,"If this has occurred more t
    han once some data might be corrupted and you might need to choose an earlier recovery target.",,,,,,,""
    2020-09-04 03:27:20.579 GMT,,,11360,,5f51b418.2c60,2,,2020-09-04 03:27:20 GMT,,0,LOG,00000,"entering standby mode",,,,,,,,,""
    2020-09-04 03:27:20.581 GMT,,,11360,,5f51b418.2c60,3,,2020-09-04 03:27:20 GMT,1/0,0,LOG,00000,"redo starts at 0/3000060",,,,,,,,,""
    2020-09-04 03:27:20.581 GMT,,,11360,,5f51b418.2c60,4,,2020-09-04 03:27:20 GMT,1/0,0,LOG,00000,"consistent recovery state reached at 0/3000108",,,,,,,,,""
    2020-09-04 03:27:20.581 GMT,,,11360,,5f51b418.2c60,5,,2020-09-04 03:27:20 GMT,1/0,0,FATAL,58P01,"directory ""/pgadmin/pg_data/10.0/data/tbs_pgtest"" does not exist",,"Create this directory for the tablespace
    before restarting the server.",,,"WAL redo at 0/30012D8 for Tablespace/CREATE: 32784 ""/pgadmin/pg_data/10.0/data/tbs_pgtest""",,,,""
    2020-09-04 03:27:20.585 GMT,,,11358,,5f51b418.2c5e,2,,2020-09-04 03:27:20 GMT,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,,""
    2020-09-04 03:27:20.587 GMT,,,11358,,5f51b418.2c5e,3,,2020-09-04 03:27:20 GMT,,0,LOG,00000,"startup process (PID 11360) exited with exit code 1",,,,,,,,,""
    2020-09-04 03:27:20.587 GMT,,,11358,,5f51b418.2c5e,4,,2020-09-04 03:27:20 GMT,,0,LOG,00000,"terminating any other active server processes",,,,,,,,,""
    2020-09-04 03:27:20.589 GMT,,,11358,,5f51b418.2c5e,5,,2020-09-04 03:27:20 GMT,,0,LOG,00000,"database system is shut down",,,,,,,,,""
    [postgres@postgresql2 pg_log]$ mkdir -p /pgadmin/pg_data/10.0/data/tbs_pgtest
    [postgres@postgresql2 pg_log]$

4.3 主库上创建表录入数据验证

--主库操作：
</>复制代码 
  1. create table test_1(id int4,name text,create_time timestamp without time zone default clock_timestamp());
    alter table test_1 add primary key (id);
    insert into test_1(id,name)
    select n,n ||_francs
    from generate_series(1,5000) n;
    select * from test_1 limit 10;

--备库查看：

4.4 备库上删除表操作验证

可以看到备库无法进行写操作。
PostgreSQL在9.0之后引入了主备流复制机制，通过流复制，备库不断的从主库同步相应的数据，并在备库apply每个WAL record，这里的流复制每次传输单位是WAL日志的record。
而PostgreSQL9.0之前提供的方法是主库写完一个WAL日志文件后，才把WAL日志文件传送到备库，这样的方式导致主备延迟特别大。同时PostgreSQL9.0之后提供了Hot Standby，备库在应用WAL record的同时也能够提供只读服务。



本文作者：汤杰

本文来源：IT那活儿（上海新炬王翦团队）


分享

收藏

点赞

在看

GPU云服务器 云服务器 postgresql主从复制 postgresql 性能测试 postgresql性能测试 10gbiz测试IP

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129666.html

相关文章

  • postgresql 10主备流复制测试

    postgresql 10主备流复制测试 img{ display:block; margin:0 auto !important; width:100%; } body{ width:75%; ...

    IT那活儿 评论0 收藏2959

  • 新书推荐 |《PostgreSQL实战》出版(提供样章下载)

    摘要：作者谭峰张文升出版日期年月页数页定价元本书特色中国开源软件推进联盟分会特聘专家撰写，国内多位开源数据库专家鼎力推荐。张文升中国开源软件推进联盟分会核心成员之一。 很高兴《PostgreSQL实战》一书终于出版，本书大体上系统总结了笔者 PostgreSQL DBA 职业生涯的经验总结，本书的另一位作者张文升拥有丰富的PostgreSQL运维经验，目前就职于探探科技任首席PostgreS...

    Martin91 评论0 收藏0

  • PostgreSQL9.5：pg_rewind 快速恢复备节点

    摘要：上操作备注执行抛出以上错误，错误内容很明显。再次上操作备注成功。启动原主库，上操作数据验证上操作备注成功，原主库现在是以备库角色启动，而且数据表也同步过来了。三原理四参考的主备切换使用搭建流复制环境 了解 PG 的朋友应该知道 PG 的主备切换并不容易，步骤较严谨，在激活备节点前需主动关闭主节点，否则再想以备节点角色拉起主节点会比较困难，之前博客介绍过主备切换，PostgreSQL H...

    hersion 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师
我要私信

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<