深圳网站制作哪家价格便宜,手机怎样创建网站,中山vi设计公司,建一个国外网站多少钱实验目的
配置一个最简的主从环境#xff0c;了解基本的主从配置。
环境参数
操作系统CentOS Linux release 7.9.2009 (Core)数据库版本PostgreSQL 10.23主库端口15431备库端口15432
因为只是做实验#xff0c;所以主备库放在同一台机器上#xff0c;仅通过端口区分主备…实验目的
配置一个最简的主从环境了解基本的主从配置。
环境参数
操作系统CentOS Linux release 7.9.2009 (Core)数据库版本PostgreSQL 10.23主库端口15431备库端口15432
因为只是做实验所以主备库放在同一台机器上仅通过端口区分主备操作的系统用户为pg没有特殊说明的情况下操作命令都由pg用户执行
搭个环境
初始化主库
[pglocalhost ~]$ pg_ctl -D /data/db1 init
The files belonging to this database system will be owned by user pg.
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.creating directory /data/db1 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Asia/Shanghai
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... okWARNING: 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:/usr/pgsql-10/bin/pg_ctl -D /data/db1 -l logfile start
配置主库参数
[pglocalhost ~]$ vi /data/db1/postgresql.conf
修改如下参数
---------------------------------------
port 15431 #主库端口
unix_socket_directories /tmp #本地连接socket文件目录pg访问/var/run/postgresql会有权限制问题反正不打算用本地socket方式连接所以直接改成/tmp[pglocalhost ~]$ vi /data/db1/pg_hba.conf
确认已开启本地复制的权限本地连接的所有用户都有备机的复制权限
---------------------------------------
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust初始化备库
备库的基准文件必须来自于主库直接复制主库文件到备库位置即可。
[pglocalhost ~]$ rsync -avz /data/db1/* /data/db2#修改主备库目录的权限为0700否则会导致数据库启动异常
[pglocalhost ~]$ chmod 0700 /data/db[1,2]配置备库参数
备库需求配置一个特定的参数文件recovery.conf可以从安装目录中复制文件模板过来修改。
[pglocalhost ~]$ cp /usr/pgsql-10/share/recovery.conf.sample /data/db2/recovery.conf[pglocalhost ~]$ vi /data/db2/recovery.conf
开启备库模式
---------------------------------------
standby_mode on[pglocalhost ~]$ vi /data/db2/postgresql.conf
修改如下参数
---------------------------------------
port 15432
unix_socket_directories /tmp验证主备功能
#启动主库
[pglocalhost ~]$ pg_ctl -D /data/db1/ -l /data/db1/server.log start
waiting for server to start.... done
server started#启动备库
[pglocalhost ~]$ pg_ctl -D /data/db2/ -l /data/db2/server.log start
waiting for server to start.... done
server started#查看可用账号和库
[pglocalhost ~]$ psql -l -h localhost -p 15431List of databasesName | Owner | Encoding | Collate | Ctype | Access privileges
-------------------------------------------------------------------------postgres | pg | UTF8 | en_US.UTF-8 | en_US.UTF-8 |template0 | pg | UTF8 | en_US.UTF-8 | en_US.UTF-8 | c/pg | | | | | pgCTc/pgtemplate1 | pg | UTF8 | en_US.UTF-8 | en_US.UTF-8 | c/pg | | | | | pgCTc/pg
(3 rows)[pglocalhost ~]$ psql -l -h localhost -p 15432List of databasesName | Owner | Encoding | Collate | Ctype | Access privileges
-------------------------------------------------------------------------postgres | pg | UTF8 | en_US.UTF-8 | en_US.UTF-8 |template0 | pg | UTF8 | en_US.UTF-8 | en_US.UTF-8 | c/pg | | | | | pgCTc/pgtemplate1 | pg | UTF8 | en_US.UTF-8 | en_US.UTF-8 | c/pg | | | | | pgCTc/pg
(3 rows)#连接主库建表
[pglocalhost ~]$ psql -h localhost -p 15431 -U pg postgres
psql (10.23)
Type help for help.postgres# create table t3(a int);
CREATE TABLE
postgres# insert into t3(a) values (1),(2),(3);
INSERT 0 3#连接备库查看看同步情况
[pglocalhost ~]$ psql -h localhost -p 15432 -U pg postgres
psql (10.23)
Type help for help.postgres# \dtList of relationsSchema | Name | Type | Owner
----------------------------public | t3 | table | pg
(1 row)postgres# select * from t3;a
---123
(3 rows)postgres#
