rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | 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=Sourcebaseurl=file:///mediaenabled=1gpgcheck=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 postgresuseradd -u 1100 -g postgres -G postgres -d /home/postgres postgresmkdir -p /pgadmin/pg_datamkdir -p /pgadmin/postgres10chown -R postgres:postgres /pgadmin/pg_datachown -R postgres:postgres /pgadmin/postgres10
复制

cp postgresql-10.0.tar.gz /tmp/softtar -xvf postgresql-10.0.tar.gz

复制

cd postgresql-10.0./configure --prefix=/pgadmin/postgres10make worldmake install-world
复制

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/ -WThe 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 ... okcreating subdirectories ... okselecting default max_connections ... 100selecting default shared_buffers ... 128MBselecting dynamic shared memory implementation ... posixcreating configuration files ... okrunning bootstrap script ... okperforming post-bootstrap initialization ... oksyncing data to disk ... okWARNING: enabling "trust" authentication for local connectionsYou 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 ~]$
复制

export PGSQL_HOME=/pgadmin/pgsqlexport PGHOST=/pgadmin/pg_data/10.0/dataexport LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PGSQL_HOME/libexport PATH=$PGSQL_HOME/bin:$PATHexport PGDATA=/pgadmin/pg_data/10.0/data
复制

mkdir /home/postgres/.sshchmod 600 /home/postgres/.sshssh-keygen -t dsassh-keygen -t rsacd /home/postgres/.sshcat id_dsa.pub > authorized_keyscat id_rsa.pub >> authorized_keys
复制

ssh postgresql2 cat /home/postgres/.ssh/authorized_keys >>authorized_keysscp authorized_keys postgresql2:/home/postgres/.ssh/
复制

cd /pgadmin/pg_data/10.0/datacp   pg_hba.conf  pg_hba.conf20200904vi pg_hba.conf（增加如下信息）host all all  0.0.0.0/0  md5
复制

vi postgresql.conf#添加如下内容listen_addresses = '*'port = 5432superuser_reserved_connections = 20unix_socket_directories = '/pgadmin/pg_data/10.0/data'unix_socket_permissions = 0700tcp_keepalives_idle = 60tcp_keepalives_interval = 10tcp_keepalives_count = 10vacuum_cost_delay = 10bgwriter_delay = 10mssynchronous_commit = off#checkpoint_segments = 8wal_writer_delay = 10mslog_destination = 'csvlog'logging_collector = onlog_directory = 'pg_log'log_filename ='postgresql-%Y-%m-%d_%H%M%S.log'log_file_mode = 0600log_truncate_on_rotation = onlog_rotation_age = 1dlog_rotation_size = 10MBpg_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 startpg_ctl: another server might be running; trying to start server anywaywaiting for server to start....2020-09-04 02:04:48.182 GMT [3524] LOG: listening on IPv4 address "0.0.0.0", port 54322020-09-04 02:04:48.182 GMT [3524] LOG: listening on IPv6 address "::", port 54322020-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 process2020-09-04 02:04:48.222 GMT [3524] HINT: Future log output will appear in directory "pg_log".doneserver started[postgres@postgresql1 data]$[postgres@postgresql1 data]$ ps -ef|grep postgresroot 3465  2596  0 09:59 pts/0    00:00:00 su - postgrespostgres 3466  3465  0 09:59 pts/0    00:00:00 -bashpostgres 3524     1  0 10:04 pts/0    00:00:00 /pgadmin/postgres10/bin/postgrespostgres 3525  3524  0 10:04 ? 00:00:00 postgres: logger processpostgres 3527  3524  0 10:04 ? 00:00:00 postgres: checkpointer processpostgres 3528  3524  0 10:04 ? 00:00:00 postgres: writer processpostgres 3529  3524  0 10:04 ? 00:00:00 postgres: wal writer processpostgres 3530  3524  0 10:04 ? 00:00:00 postgres: autovacuum launcher processpostgres 3531  3524  0 10:04 ? 00:00:00 postgres: stats collector processpostgres 3532  3524  0 10:04 ? 00:00:00 postgres: bgworker: logical replication launcherpostgres 3533  3466  7 10:04 pts/0    00:00:00 ps -efpostgres 3534  3466  0 10:04 pts/0    00:00:00 grep postgres[postgres@postgresql1 data]$[postgres@postgresql1 data]$ pg_ctl -m fast stopwaiting for server to shut down.... doneserver stopped[postgres@postgresql1 data]$ pg_ctl restartpg_ctl: PID file "/pgadmin/pg_data/10.0/data/postmaster.pid" does not existIs server running?starting server anywaywaiting for server to start....2020-09-04 02:05:25.327 GMT [3538] LOG: listening on IPv4 address "0.0.0.0", port 54322020-09-04 02:05:25.327 GMT [3538] LOG: listening on IPv6 address "::", port 54322020-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 process2020-09-04 02:05:25.336 GMT [3538] HINT: Future log output will appear in directory "pg_log".doneserver started[postgres@postgresql1 data]$ ps -ef | grep postgresroot 3465  2596  0 09:59 pts/0    00:00:00 su - postgrespostgres 3466  3465  0 09:59 pts/0    00:00:00 -bashpostgres 3538     1  0 10:05 pts/0    00:00:00 /pgadmin/postgres10/bin/postgrespostgres 3539  3538  0 10:05 ? 00:00:00 postgres: logger processpostgres 3541  3538  0 10:05 ? 00:00:00 postgres: checkpointer processpostgres 3542  3538  0 10:05 ? 00:00:00 postgres: writer processpostgres 3543  3538  0 10:05 ? 00:00:00 postgres: wal writer processpostgres 3544  3538  0 10:05 ? 00:00:00 postgres: autovacuum launcher processpostgres 3545  3538  0 10:05 ? 00:00:00 postgres: stats collector processpostgres 3546  3538  0 10:05 ? 00:00:00 postgres: bgworker: logical replication launcherpostgres 3547  3466  2 10:05 pts/0    00:00:00 ps -efpostgres 3548  3466  0 10:05 pts/0    00:00:00 grep postgres[postgres@postgresql1 data]$ psql -h 127.0.0.1 -d postgres -U postgrespsql (10.0)Type "help" for help.postgres=#
复制

create role cyl superuser;alter role cyl password 'cyl';alter role cyl login;
复制

host replication replica 0.0.0.0/0               md5vi postgresql.conf
复制

listen_addresses = '*'port = 5432max_connections = 500
复制

wal_level = hot_standbycheckpoint_segments = 16checkpoint_timeout = 5minarchive_mode = onmax_wal_senders = 3wal_keep_segments = 16wal_level = hot_standby
复制

su - postgresqlpsql -h 127.0.0.1 -d postgres -U postgres 登录数据库create role replica login replication encrypted password 'replica'; 创建用户
复制

cd /home/postgresqlvi .pgpass#主数据库IP:主数据库端口号:replication:replica:replica192.168.20.101:5432:replica:replicachmod 400 .pgpass#重启数据库pg_ctl restart
复制

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
复制

host   replication cyl 0.0.0.0/0               md5
复制

[postgres@postgresql2 10.0]$ pg_basebackup -D $PGDATA -F p -X stream -v -P -h 192.168.20.101 -U cylpg_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 cylPassword:pg_basebackup: initiating base backup, waiting for checkpoint to completeWARNING: skipping special file "./.s.PGSQL.5432"pg_basebackup: checkpoint completedpg_basebackup: write-ahead log start point: 0/2000028 on timeline 1pg_basebackup: starting background WAL receiverWARNING: skipping special file "./.s.PGSQL.5432"/10.0/data/backup_label)31296/31296 kB (100%), 1/1 tablespacepg_basebackup: write-ahead log end point: 0/20000F8pg_basebackup: waiting for background process to finish streaming ...pg_basebackup: base backup completed[postgres@postgresql2 10.0]$
复制

#添加cd $PGDATAvi postgresql.confhot_standby = on
复制

vi recovery.confstandby_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 startwaiting 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 access2020-09-04 03:11:25.151 GMT [11232] DETAIL: Permissions should be u=rwx (0700).stopped waitingpg_ctl: could not start serverExamine the log output.[postgres@postgresql2 data]$
复制

[postgres@postgresql2 10.0]$ ls -trltotal 20drwxrwxr-x. 2 postgres postgres 4096 Sep 4 10:12 scriptsdrwxrwxr-x. 2 postgres postgres 4096 Sep 4 10:12 backupsdrwxrwxr-x. 2 postgres postgres 4096 Sep 4 10:12 archive_walsdrwx------. 20 postgres postgres 4096 Sep 4 10:37 data_bakdrwxrwxr-x. 20 postgres postgres 4096 Sep 4 11:08 data[postgres@postgresql2 10.0]$ chmod 700 data[postgres@postgresql2 10.0]$ ls -trltotal 20drwxrwxr-x. 2 postgres postgres 4096 Sep 4 10:12 scriptsdrwxrwxr-x. 2 postgres postgres 4096 Sep 4 10:12 backupsdrwxrwxr-x. 2 postgres postgres 4096 Sep 4 10:12 archive_walsdrwx------. 20 postgres postgres 4096 Sep 4 10:37 data_bakdrwx------. 20 postgres postgres 4096 Sep 4 11:08 data[postgres@postgresql2 10.0]$
复制

[postgres@postgresql2 data]$ pg_ctl startwaiting for server to start....2020-09-04 03:12:29.191 GMT [11242] LOG: listening on IPv4 address "0.0.0.0", port 54322020-09-04 03:12:29.191 GMT [11242] LOG: listening on IPv6 address "::", port 54322020-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 process2020-09-04 03:12:29.221 GMT [11242] HINT: Future log output will appear in directory "pg_log".doneserver started[postgres@postgresql2 data]$ ps -aux | grep postgresWarning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQroot 10971  0.0  0.1 163748  2012 pts/0    S 10:22   0:00 su - postgrespostgres 10972  0.0  0.1 108472  1972 pts/0    S 10:22   0:00 -bashroot 11148  0.0  0.1 163748  2008 pts/1    S 10:59   0:00 su - postgrespostgres 11149  0.0  0.0 108340  1824 pts/1    S+ 10:59   0:00 -bashpostgres 11242  0.0  0.9 152440 18452 pts/0    S 11:12   0:00 /pgadmin/postgres10/bin/postgrespostgres 11243  0.0  0.0 117072   884 ? Ss 11:12   0:00 postgres: logger processpostgres 11244  0.0  0.0 152560  1692 ? Ss 11:12   0:00 postgres: startup process waiting for 000000010000000000000003postgres 11245  0.0  0.0 152440  1108 ? Ss 11:12   0:00 postgres: checkpointer processpostgres 11246  0.2  0.0 152440  1092 ? Ss 11:12   0:00 postgres: writer processpostgres 11247  0.0  0.0 119168   836 ? Ss 11:12   0:00 postgres: stats collector processpostgres 11279 10.0  0.0 110236  1148 pts/0    R+ 11:14   0:00 ps -auxpostgres 11280  0.0  0.0 103252   836 pts/0    S+ 11:14   0:00 grep postgres[postgres@postgresql2 data]$
复制

＃ps -aux | grep postgres[postgres@postgresql1 data]$ ps -aux | grep postgresWarning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQroot 3614  0.0  0.0 163748  2004 pts/0    S 10:21   0:00 su - postgrespostgres 3615  0.0  0.0 108440  1892 pts/0    S 10:21   0:00 -bashpostgres 3870  0.0  0.4 152440 18468 pts/0    S 11:03   0:00 /pgadmin/postgres10/bin/postgrespostgres 3871  0.0  0.0 117072   884 ? Ss 11:03   0:00 postgres: logger processpostgres 3873  0.0  0.0 152708  3132 ? Ss 11:03   0:00 postgres: checkpointer processpostgres 3874  0.3  0.0 152572  1588 ? Ss 11:03   1:07 postgres: writer processpostgres 3875  0.0  0.0 152440  1496 ? Ss 11:03   0:02 postgres: wal writer processpostgres 3876  0.0  0.0 153204  2028 ? Ss 11:03   0:00 postgres: autovacuum launcher processpostgres 3877  0.0  0.0 119168   956 ? Ss 11:03   0:00 postgres: archiver processpostgres 3878  0.0  0.0 119300  1100 ? Ss 11:03   0:00 postgres: stats collector processpostgres 3879  0.0  0.0 153044  1680 ? Ss 11:03   0:00 postgres: bgworker: logical replication launcherroot 4009  0.0  0.0 163748  2008 pts/1    S 11:23   0:00 su - postgrespostgres 4010  0.0  0.0 108340  1892 pts/1    S 11:23   0:00 -bashpostgres 4050  0.0  0.0 153724  2904 ? Ss 11:29   0:00 postgres: wal sender process cyl 192.168.20.102(34150) streaming 0/3104F00postgres 5058  0.0  0.0 117304  1756 pts/0    S+ 15:46   0:00 psqlpostgres 5059  0.0  0.0 153732  2936 ? Ss 15:46   0:00 postgres: postgres postgres [local] idlepostgres 5510  7.0  0.0 110240  1148 pts/1    R+ 17:11   0:00 ps -auxpostgres 5511  0.0  0.0 103252   836 pts/1    S+ 17:11   0:00 grep postgres
复制

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

[postgres@postgresql2 data]$ cat recovery.confstandby_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 restartwaiting for server to shut down.... doneserver stoppedwaiting for server to start....2020-09-04 03:17:00.052 GMT [11319] LOG: listening on IPv4 address "0.0.0.0", port 54322020-09-04 03:17:00.052 GMT [11319] LOG: listening on IPv6 address "::", port 54322020-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 process2020-09-04 03:17:00.070 GMT [11319] HINT: Future log output will appear in directory "pg_log".doneserver started[postgres@postgresql2 data]$[postgres@postgresql2 data]$[postgres@postgresql2 data]$ ps -aux | grep postgresWarning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQroot 10971  0.0  0.1 163748  2012 pts/0    S 10:22   0:00 su - postgrespostgres 10972  0.0  0.1 108472  1972 pts/0    S 10:22   0:00 -bashroot 11148  0.0  0.1 163748  2008 pts/1    S 10:59   0:00 su - postgrespostgres 11149  0.0  0.0 108340  1824 pts/1    S+ 10:59   0:00 -bashpostgres 11319  0.0  0.9 152440 18456 pts/0    S 11:16   0:00 /pgadmin/postgres10/bin/postgrespostgres 11320  0.0  0.0 117072   888 ? Ss 11:16   0:00 postgres: logger processpostgres 11321  0.0  0.0 152560  1648 ? Ss 11:16   0:00 postgres: startup process recovering 000000010000000000000003postgres 11322  0.0  0.0 152440  1116 ? Ss 11:16   0:00 postgres: checkpointer processpostgres 11323  0.2  0.0 152440  1100 ? Ss 11:16   0:00 postgres: writer processpostgres 11324  0.0  0.0 119168   844 ? Ss 11:16   0:00 postgres: stats collector processpostgres 11325  0.1  0.1 156820  1948 ? Ss 11:16   0:00 postgres: wal receiver process streaming 0/3000140postgres 11326  0.0  0.0 110236  1148 pts/0    R+ 11:17   0:00 ps -auxpostgres 11327  0.0  0.0 103252   836 pts/0    S+ 11:17   0:00 grep postgres[postgres@postgresql2 data]$
复制

[postgres@postgresql2 pg_log]$ more postgresql-2020-09-04_032720.csv2020-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 than 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 tablespacebefore 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;
复制