主机 | 操作系统 | IP | 数据库 |
postgresql1 | Red Hat Enterprise 6.5 | 192.168.20.101 | PostgreSQL10.0 |
postgresql2 | Red Hat Enterprise 6.5 | 192.168.20.102 | PostgreSQL10.0 |
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" |
vi source.repo [rhel-source-beta] name=Source baseurl=file:///media enabled=1 gpgcheck=0 |
yum install -y readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake |
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 |
下载源码包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
makeword
makeinstall-world
初始化（密码：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 ~]$ |
ln -s /pgadmin/postgres10 /pgadmin/pgsql |
如果不配置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 |
在两个节点执行 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/ |
2.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=# |
create role cyl superuser; alter role cyl password cyl; alter role cyl login; |
创建
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 启动数据库 |
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这行注释掉 |
暂时还没研究透这个用户的作用
su - postgresql psql -h 127.0.0.1 -d postgres -U postgres 登录数据库 create role replica login replication encrypted password replica; 创建用户 |
cd /home/postgresql vi .pgpass #主数据库IP:主数据库端口号:replication:replica:replica 192.168.20.101:5432:replica:replica chmod 400 .pgpass #重启数据库 pg_ctl restart |
pg_ctl stop |
mv /pgadmin/pg_data/10.0/data /pgadmin/pg_data/10.0/data_bak |
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]$ |
#添加 cd $PGDATA vi postgresql.conf hot_standby = on |
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 |
[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
修改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]$ |
1、在主库的服务器上输入命令，会看到多出一个walsender process的进程：＃ps -aux | greppostgres
2、在备库的服务器上输入命令，会看到多出一个wal receiver process的进程：＃ps -aux | greppostgres
在主库的服务器上输入命令，会看到多出一个wal sender process的进程： ＃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 [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]$ |
备库核查：
核查发现备库已经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]$ |
主库操作：
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; |
备库查看：
可以看到备库无法进行写操作
PostgreSQL在9.0之后引入了主备流复制机制，通过流复制，备库不断的从主库同步相应的数据，并在备库apply每个WALrecord，这里的流复制每次传输单位是WAL日志的record。而PostgreSQL9.0之前提供的方法是主库写完一个WAL日志文件后，才把WAL日志文件传送到备库，这样的方式导致主备延迟特别大。同时PostgreSQL9.0之后提供了HotStandby，备库在应用WALrecord的同时也能够提供只读服务。
文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。
转载请注明本文地址：https://www.ucloud.cn/yun/130122.html
postgresql10主备流复制测试 img{ display:block; margin:0 auto !important; width:100%; } body{ width:75%; ...
摘要：作者谭峰张文升出版日期年月页数页定价元本书特色中国开源软件推进联盟分会特聘专家撰写，国内多位开源数据库专家鼎力推荐。张文升中国开源软件推进联盟分会核心成员之一。 很高兴《PostgreSQL实战》一书终于出版，本书大体上系统总结了笔者 PostgreSQL DBA 职业生涯的经验总结，本书的另一位作者张文升拥有丰富的PostgreSQL运维经验，目前就职于探探科技任首席PostgreS...
摘要：上操作备注执行抛出以上错误，错误内容很明显。再次上操作备注成功。启动原主库，上操作数据验证上操作备注成功，原主库现在是以备库角色启动，而且数据表也同步过来了。三原理四参考的主备切换使用搭建流复制环境 了解 PG 的朋友应该知道 PG 的主备切换并不容易，步骤较严谨，在激活备节点前需主动关闭主节点，否则再想以备节点角色拉起主节点会比较困难，之前博客介绍过主备切换，PostgreSQL H...
阅读 1347·2023-01-11 13:20
阅读 1686·2023-01-11 13:20
阅读 1134·2023-01-11 13:20
阅读 1861·2023-01-11 13:20
阅读 4104·2023-01-11 13:20
阅读 2708·2023-01-11 13:20
阅读 1386·2023-01-11 13:20
阅读 3599·2023-01-11 13:20
