资讯专栏INFORMATION COLUMN

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

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

postgresql10主备流复制测试

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

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


 环  境 



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



 安装PostgreSQL 


1. 检查依赖包

rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})
" | 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源配置
vi source.repo
[rhel-source-beta]
name=Source
baseurl=file:///media
enabled=1
gpgcheck=0
3. 安装依赖包
yum install -y readline-devel zlib-devel pam-devel libxml2-
devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake
4. 创建用户及目录
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上传到服务器：
cp postgresql-10.0.tar.gz /tmp/soft
tar -xvf postgresql-10.0.tar.gz
--编译安装：
cd postgresql-10.0
./configure --prefix=/pgadmin/postgres10

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

6. 初始化数据库

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

mkdir -p /pgadmin/pg_data/10.0/{data,backups,scripts,archive_wals}
/pgadmin/pgsql/bin/initdb -D /pgadmin/pg_data/10.0/data/ -W
--日志：
[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登陆会报错。
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. 配置互信

--在两个节点执行：

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执行：

ssh postgresql2 cat /home/postgres/.ssh/authorized_keys >>authorized_keys
scp authorized_keys postgresql2:/home/postgres/.ssh/

10. 修改配置文件

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


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 登录数据库测试
[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用户

create role cyl superuser;
alter role cyl password cyl;
alter role cyl login;

--创建。



 postgressql流复制配置 


1. 主节点配置准备工作

cd $PGDATA
vi pg_hba.conf

--添加如下内容：

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

2. 主库配置

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

vi postgresql.conf

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

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的权限用户

暂时还没研究透这个用户的作用
su - postgresql
psql -h 127.0.0.1 -d postgres -U postgres 登录数据库
create role replica login replication encrypted password replica; 创建用户
2.3 配置密码文件
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备库之前的数据文件
mv /pgadmin/pg_data/10.0/data /pgadmin/pg_data/10.0/data_bak
3.3 从主库上恢复数据
pg_basebackup -D $PGDATA -F p -X stream -v -P -h 192.168.20.101 -U cyl
--报错：
--在主节点的pg_hba.conf中加入如下内容：
host   replication cyl 0.0.0.0/0               md5
--重新执行pg_basebackup，成功。
[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
#添加
cd $PGDATA
vi postgresql.conf
hot_standby = on
3.5 增加recovery.conf文件
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 启动数据库
[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。

[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]$


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


[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
＃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

＃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后，重启数据库。

[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已经同步到备库。
[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 主库上创建表录入数据验证

--主库操作：
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元查看
<