Postgresql10.14配置repmgr5.1.0执行步骤

2021-12-26 09:28:14

[root@data01 ~]# yum install -y openssl-devel zlib-devel readline-devel libevent-devel

Loaded plugins: fastestmirror, langpacks

Loading mirror speeds from cached hostfile

Package 1:openssl-devel-1.0.2k-19.el7.x86_64 already installed and latest version

Package zlib-devel-1.2.7-18.el7.x86_64 already installed and latest version

Package readline-devel-6.2-11.el7.x86_64 already installed and latest version

No package libevent-devel available.

Nothing to do

[root@data01 ~]# su - pg10

Last login: Wed Jan 6 06:45:35 CST 2021 on pts/0

[pg10@data01 ~]$ cd resource/

[pg10@data01 resource]$ tar -zxf postgresql-10.14.tar.gz

[pg10@data01 resource]$ cd postgresql-10.14/

[pg10@data01 postgresql-10.14]$ ./configure --prefix=/home/pg10/soft --with-openssl --with-pgport=5666

[pg10@data01 postgresql-10.14]$ make world -j24 && make install-world -j24

/bin/install -c -m 644 ./sslinfo--1.2.sql ./sslinfo--1.1--1.2.sql ./sslinfo--1.0--1.1.sql ./sslinfo--unpackaged--1.0.sql '/home/pg10/soft/share/postgresql/extension/'

make[2]: Leaving directory `/home/pg10/resource/postgresql-10.14/contrib/sslinfo'

/bin/install -c -m 644 ./unaccent.rules '/home/pg10/soft/share/postgresql/tsearch_data/'

make[2]: Leaving directory `/home/pg10/resource/postgresql-10.14/contrib/unaccent'

make[1]: Leaving directory `/home/pg10/resource/postgresql-10.14/contrib'

PostgreSQL, contrib, and documentation installation complete.

[pg10@data01 ~]$ echo "export PGHOME=/home/pg10/soft" >> /home/pg10/.bashrc

[pg10@data01 ~]$ echo "export PGDATA=/home/pg10/data" >> /home/pg10/.bashrc

[pg10@data01 ~]$ echo "export PATH=\${PGHOME}/bin:\${PATH}" >> /home/pg10/.bashrc

[pg10@data01 ~]$ source ~/.bashrc

[pg10@data01 ~]$ initdb -D $PGDATA -k

The files belonging to this database system will be owned by user "pg10".

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 enabled.

fixing permissions on existing directory /home/pg10/data ... 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 ... 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:

pg_ctl -D /home/pg10/data -l logfile start

[pg10@data01 ~]$ psql -d postgres

psql (10.14)

Type "help" for help.

postgres=# CREATE USER postgres WITH SUPERUSER PASSWORD '1qaz@WSX';

CREATE ROLE

postgres=# \q

[pg10@data01 ~]$ vim .pgpass

[pg10@data01 ~]$ chmod 0600 .pgpass

[pg10@data01 ~]$ psql -U postgres

psql (10.14)

Type "help" for help.

postgres=#

[pg10@data01 ~]$ psql -U postgres -d postgres -Aqt

postgres=# CREATE EXTENSION sslinfo ;

postgres=# \q

[pg10@data01 ~]$ mkdir ~/ssl

[pg10@data01 ~]$ cd ~/ssl/

[pg10@data01 ssl]$ openssl req -new -x509 -days 365 -nodes -text -out server.crt \

> -keyout server.key -subj "/CN=data01"

Generating a 2048 bit RSA private key

...............................................................+++

...+++

writing new private key to 'server.key'

-----

[pg10@data01 ssl]$ chmod og-rwx server.key

[pg10@data01 ssl]$ openssl req -new -nodes -text -out root.csr \

> -keyout root.key -subj "/CN=data01"

Generating a 2048 bit RSA private key

...........+++

............................................................................+++

writing new private key to 'root.key'

-----

[pg10@data01 ssl]$ chmod og-rwx root.key

[pg10@data01 ssl]$ openssl x509 -req -in root.csr -text -days 3650 \

> -extfile /etc/pki/tls/openssl.cnf -extensions v3_ca \

> -signkey root.key -out root.crt

Signature ok

subject=/CN=data01

Certificate:

Data:

Version: 1 (0x0)

Serial Number:

9c:da:ba:84:9c:d6:9c:0a

Signature Algorithm: NULL

Issuer: CN=data01

Validity

Not Before: Jan 5 23:20:02 2021 GMT

Not After : Jan 3 23:20:02 2031 GMT

Subject: CN=data01

Subject Public Key Info:

Public Key Algorithm: rsaEncryption

Public-Key: (2048 bit)

Modulus:

00:ac:54:78:10:2b:af:6d:12:3a:3e:6b:1c:55:d1:

c1:52:d1:bd:62:91:7e:e9:84:9f:b0:1e:15:aa:ea:

11:79:3e:8a:10:53:f4:f8:83:21:c0:a6:61:cd:12:

8e:6f:4d:54:19:fa:ac:29:93:9d:e8:0f:06:4e:cf:

b4:6b:2d:5d:a8:9b:fd:bc:b1:bd:08:06:06:c7:c2:

68:6e:01:01:22:f2:8f:46:e6:ae:e0:ee:5c:a7:fe:

33:90:3c:10:25:c5:4d:4f:ca:be:9f:2f:8d:a0:0d:

f5:8e:aa:d2:9d:a1:45:60:7d:a9:eb:6d:ec:3c:ab:

b6:d1:c1:04:ba:90:82:7a:bf:ed:82:81:23:06:49:

b9:52:ef:f4:02:81:ff:66:d2:c7:7b:cd:45:2a:9c:

f1:b3:0e:9a:f8:aa:fb:96:25:1c:bb:29:1d:8f:5c:

d5:af:da:4e:b0:f0:d4:5f:62:b2:10:b8:8c:fe:b8:

c4:9e:e8:63:4c:51:2e:95:de:38:87:3a:4b:8f:69:

fc:be:fa:bb:b6:ce:97:a7:64:36:e1:fd:e6:bf:e7:

c0:99:8b:d4:f6:d1:14:34:3c:16:ee:1d:a1:0f:8b:

88:18:0c:e5:85:15:05:c0:58:c6:14:b7:7d:77:b5:

75:90:63:1d:96:85:88:6e:ec:e6:79:22:c5:32:4a:

f6:01

Exponent: 65537 (0x10001)

Signature Algorithm: NULL

Getting Private key

[pg10@data01 ssl]$ openssl req -new -nodes -text -out server.csr \

> -keyout server.key -subj "/CN=data01"

Generating a 2048 bit RSA private key

.........+++

................................................................................................................+++

writing new private key to 'server.key'

-----

[pg10@data01 ssl]$ chmod og-rwx server.key

[pg10@data01 ssl]$ openssl x509 -req -in server.csr -text -days 365 \

> -CA root.crt -CAkey root.key -CAcreateserial \

> -out server.crt

Signature ok

subject=/CN=data01

Certificate:

Data:

Version: 1 (0x0)

Serial Number:

bd:b2:70:0e:bd:2b:ed:0c

Signature Algorithm: NULL

Issuer: CN=data01

Validity

Not Before: Jan 5 23:20:35 2021 GMT

Not After : Jan 5 23:20:35 2022 GMT

Subject: CN=data01

Subject Public Key Info:

Public Key Algorithm: rsaEncryption

Public-Key: (2048 bit)

Modulus:

00:ce:d6:d3:2e:3c:8e:ec:d3:63:2d:40:6a:33:37:

ee:5e:82:ba:b8:27:b0:d0:5d:e7:a7:e8:6f:ef:06:

af:e5:1a:cd:30:af:1f:e0:81:11:ad:ca:3f:a3:23:

d7:c1:8d:22:88:aa:80:ef:aa:e3:b4:7a:41:0e:f9:

e1:b6:5d:c1:5b:b1:4f:ef:c7:35:12:2b:19:72:2c:

74:e6:74:19:3b:80:e9:7e:16:ad:c1:16:c1:b4:55:

ec:c9:0e:31:74:d2:30:90:9b:fa:cf:ae:5b:3d:5c:

59:30:b8:32:7f:56:e3:98:ab:23:62:37:b4:0f:79:

94:42:96:1e:2d:4e:cc:01:4c:86:ac:37:31:55:e8:

7f:34:fd:ff:70:be:1f:63:07:09:d6:fe:01:ed:0b:

6e:39:ba:0b:af:b1:7d:57:5f:f9:f2:55:ab:1a:a5:

d0:7f:b5:0d:e6:1c:29:05:01:d4:18:b6:e0:59:48:

8d:6d:ee:f5:f1:ba:6e:29:9e:b9:f6:73:84:4d:43:

62:67:7a:a7:fe:6d:48:5b:7c:c5:e0:38:1c:27:de:

9d:ee:a8:ec:b5:e6:d2:9e:a2:ec:79:41:62:9a:cc:

cb:f9:3b:f7:fd:1a:1a:bd:b5:35:db:10:a3:f5:8b:

ae:13:f6:80:18:49:d8:5a:2d:11:1a:ca:b0:2a:65:

55:eb

Exponent: 65537 (0x10001)

Signature Algorithm: NULL

Getting CA Private Key

[pg10@data01 ssl]$

[pg10@data01 ssl]$ cp server.key server.crt $PGDATA

[pg10@data01 ssl]$ psql -U postgres -d postgres -Aqt

postgres=# ALTER SYSTEM SET ssl = on;

postgres=# \q

[pg10@data01 ssl]$ pg_ctl restart -D $PGDATA -l /tmp/logfile

waiting for server to shut down.... done

server stopped

waiting for server to start.... done

server started

[pg10@data01 ssl]$ psql -h localhost -U postgres -d postgres

psql (10.14)

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

Type "help" for help.

postgres=# SELECT ssl_is_used();

ssl_is_used

-------------

(1 row)

[pg10@data01 ssl]$ cd $PGDATA

[pg10@data01 data]$ vim postgresql.conf

[pg10@data01 data]$ pg_ctl restart -D $PGDATA -l /tmp/logfile

waiting for server to shut down.... done

server stopped

waiting for server to start.... done

server started

[pg10@data01 data]$

[pg10@data01 ~]$ psql -h localhost -U postgres -d postgres

psql (10.14)

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

Type "help" for help.

postgres=# CREATE TABLESPACE appdb OWNER appuser LOCATION '/data/appdb_tbs';

CREATE TABLESPACE

postgres=# CREATE DATABASE appdb WITH OWNER appuser TABLESPACE appdb;

CREATE DATABASE

postgres=# \c postgres appuser

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

You are now connected to database "postgres" as user "appuser".

postgres=> \c appdb postgres

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

You are now connected to database "appdb" as user "postgres".

appdb=# REVOKE CREATE ON SCHEMA public FROM public;

REVOKE

appdb=# \c appdb appuser

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

You are now connected to database "appdb" as user "appuser".

appdb=>

appdb=> CREATE SCHEMA appuser;

CREATE SCHEMA

appdb=> CREATE TABLE appuser.app(id int);

CREATE TABLE

appdb=> \c appdb postgres

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

You are now connected to database "appdb" as user "postgres".

appdb=# REVOKE CONNECT ON DATABASE appdb FROM public;

REVOKE

appdb=# CREATE USER readonlyuser WITH PASSWORD '1qaz@WSZ';

CREATE ROLE

appdb=# \c appdb appuser

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

You are now connected to database "appdb" as user "appuser".

appdb=> GRANT USAGE ON SCHEMA appuser TO readonlyuser;

GRANT

appdb=> GRANT SELECT ON ALL TABLES IN SCHEMA appuser TO readonlyuser;

GRANT

appdb=> ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO readonlyuser ;

ALTER DEFAULT PRIVILEGES

appdb=>

配置参数

[pg10@data01 ~]$ psql -U postgres -d postgres -Aqt

postgres=# ALTER SYSTEM SET logging_collector = on;

postgres=# show log_directory ;

log

postgres=# ALTER SYSTEM SET log_directory = '/data/pglog';

postgres=# show log_destination ;

stderr

postgres=# ALTER SYSTEM SET log_destination = csvlog;

postgres=# ALTER SYSTEM SET password_encryption = 'scram-sha-256';

postgres=# ALTER SYSTEM SET listen_addresses = '*';

postgres=# ALTER SYSTEM SET log_rotation_size = 0;

postgres=# ALTER SYSTEM SET log_truncate_on_rotation = on;

postgres=# ALTER SYSTEM SET log_line_prefix = '%m-%u-%d-%p';

postgres=# ALTER SYSTEM SET log_checkpoints = off;

postgres=# ALTER SYSTEM SET log_rotation_age = 1440;

postgres=# ALTER SYSTEM SET log_filename = 'postgresql.%d';

postgres=# ALTER SYSTEM SET full_page_writes = on;

postgres=# ALTER DATABASE postgres CONNECTION LIMIT 100;

postgres=# ALTER DATABASE appdb CONNECTION LIMIT 100;

postgres=# \q

[pg10@data01 ~]$ pg_ctl restart -D $PGDATA -l /tmp/logfile

waiting for server to shut down.... done

server stopped

waiting for server to start.... done

server started

修改密码后密码会变成SCRAM-SHA-256

[pg10@data01 ~]$ psql -U postgres -d postgres -Aqt

postgres=# ALTER USER postgres WITH PASSWORD '1qaz@WSX';

postgres=# ALTER USER appuser WITH PASSWORD '1qaz@WSX';

postgres=# ALTER USER readonlyuser WITH PASSWORD '1qaz@WSX';

postgres=# select * from pg_authid;

pg10|t|t|t|t|t|t|t|-1||

pg_monitor|f|t|f|f|f|f|f|-1||

pg_read_all_settings|f|t|f|f|f|f|f|-1||

pg_read_all_stats|f|t|f|f|f|f|f|-1||

pg_stat_scan_tables|f|t|f|f|f|f|f|-1||

pg_signal_backend|f|t|f|f|f|f|f|-1||

postgres|t|t|f|f|t|f|f|-1|SCRAM-SHA-256$4096:RYm9tWmRfCRrNa+PXqKHXA==$V/CqzIx+Y/YbOC5ceoKDI3Ab+DRW6qakx5BBoRgGyJA=:yXAlqmeotTfrsF/im81UgQ3l67oq4IYl1eDxh/mn7tk=|

appuser|f|t|f|t|t|f|f|-1|SCRAM-SHA-256$4096:1rvvRTKhSbc+xn+YETxv5g==$noQT8TXBd803Gg6b46kPhSRJVBNRNr3uyr7wYBgHd4E=:YVTc5nz7fFT+Z77C1ThrBkXnO+qRIYYf5XzsEJM4gSE=|2026-03-01 00:00:00+08

readonlyuser|f|t|f|f|t|f|f|-1|SCRAM-SHA-256$4096:OrFQMCKaJMap19bhlSnoMA==$hhl17/3x5bbAkhrSLTEa1R0yb5s9vYQ1TZL14MnONsA=:NSC08xAacvDsctbkoZAcW30cj1QaUzIvh13ehhrfQsw=|

[pg10@data01 ~]$ cd $PGDATA

[pg10@data01 data]$ ll

total 68

drwx------. 5 pg10 pg10 41 Jan 6 07:15 base

-rw-------. 1 pg10 pg10 37 Jan 6 07:43 current_logfiles

drwx------. 2 pg10 pg10 4096 Jan 6 07:44 global

drwx------. 2 pg10 pg10 6 Jan 6 07:15 pg_commit_ts

drwx------. 2 pg10 pg10 6 Jan 6 07:15 pg_dynshmem

-rw-------. 1 pg10 pg10 4513 Jan 6 07:15 pg_hba.conf

-rw-------. 1 pg10 pg10 1636 Jan 6 07:15 pg_ident.conf

drwx------. 4 pg10 pg10 68 Jan 6 07:43 pg_logical

drwx------. 4 pg10 pg10 36 Jan 6 07:15 pg_multixact

drwx------. 2 pg10 pg10 18 Jan 6 07:43 pg_notify

drwx------. 2 pg10 pg10 6 Jan 6 07:15 pg_replslot

drwx------. 2 pg10 pg10 6 Jan 6 07:15 pg_serial

drwx------. 2 pg10 pg10 6 Jan 6 07:15 pg_snapshots

drwx------. 2 pg10 pg10 6 Jan 6 07:43 pg_stat

drwx------. 2 pg10 pg10 84 Jan 6 07:45 pg_stat_tmp

drwx------. 2 pg10 pg10 18 Jan 6 07:15 pg_subtrans

drwx------. 2 pg10 pg10 19 Jan 6 07:34 pg_tblspc

drwx------. 2 pg10 pg10 6 Jan 6 07:15 pg_twophase

-rw-------. 1 pg10 pg10 3 Jan 6 07:15 PG_VERSION

drwx------. 3 pg10 pg10 60 Jan 6 07:15 pg_wal

drwx------. 2 pg10 pg10 18 Jan 6 07:15 pg_xact

-rw-------. 1 pg10 pg10 435 Jan 6 07:43 postgresql.auto.conf

-rw-------. 1 pg10 pg10 23032 Jan 6 07:23 postgresql.conf

-rw-------. 1 pg10 pg10 52 Jan 6 07:43 postmaster.opts

-rw-------. 1 pg10 pg10 74 Jan 6 07:43 postmaster.pid

-rw-rw-r--. 1 pg10 pg10 969 Jan 6 07:21 server.crt

-rw-------. 1 pg10 pg10 1704 Jan 6 07:21 server.key

[pg10@data01 data]$ vim pg_hba.conf

[pg10@data01 data]$ pg_ctl restart -D $PGDATA -l /tmp/logfile

waiting for server to shut down.... done

server stopped

waiting for server to start.... done

server started

[pg10@data01 data]$ egrep -v "^#" $PGDATA/pg_hba.conf | egrep "scram-sha-256|reject"

local all postgres scram-sha-256

host all postgres 0.0.0.0/0 reject

host all all 0.0.0.0/0 scram-sha-256

[pg10@data01 ~]$ cat ~/.bashrc

# .bashrc

# Source global definitions

if [ -f /etc/bashrc ]; then

. /etc/bashrc

# Uncomment the following line if you don't like systemctl's auto-paging feature:

# export SYSTEMD_PAGER=

# User specific aliases and functions

export PGHOME=/home/pg10/soft

export PGDATA=/home/pg10/data

export PATH=${PGHOME}/bin:${PATH}

[pg10@data01 ~]$ scp ~/.bashrc data02:/home/pg10/

.bashrc 100% 325 119.5KB/s 00:00

[pg10@data01 ~]$ scp ~/.bashrc data03:/home/pg10/

.bashrc 100% 325 163.8KB/s 00:00

[pg10@data01 ~]$

配置repmgr

[pg10@data01 repmgr-5.1.0]$ ./configure --prefix=/home/pg10/soft

checking for a sed that does not truncate output... /bin/sed

checking for pg_config... /home/pg10/soft/bin/pg_config

configure: building against PostgreSQL 10.14

checking for gnused... no

checking for gsed... no

checking for sed... yes

configure: creating ./config.status

config.status: creating Makefile

config.status: creating Makefile.global

config.status: creating config.h

[pg10@data01 repmgr-5.1.0]$

[pg10@data01 repmgr-5.1.0]$ make -j24 && make install -j24

Building against PostgreSQL 10

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg10/soft/include/postgresql/internal -I/home/pg10/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg10/soft/include/postgresql/server -I/home/pg10/soft/include/postgresql/internal -D_GNU_SOURCE -c -o repmgr.o repmgr.c

sed -E 's/REPMGR_VERSION_DATE.*""/REPMGR_VERSION_DATE "2021-01-06"/' repmgr_version.h.in >repmgr_version.h; \

sed -i -E 's/PG_ACTUAL_VERSION_NUM/PG_ACTUAL_VERSION_NUM 100014/' repmgr_version.h

flex -o'configfile-scan.c' configfile-scan.l

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg10/soft/include/postgresql/internal -I/home/pg10/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg10/soft/include/postgresql/server -I/home/pg10/soft/include/postgresql/internal -D_GNU_SOURCE -c -o repmgrd.o repmgrd.c

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=gnu89 -I/home/pg10/soft/include/postgresql/internal -I/home/pg10/soft/include -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I./ -I/home/pg10/soft/include/postgresql/server -I/home/pg10/soft/include/postgresql/internal -D_GNU_SOURCE -c -o repmgrd-physical.o repmgrd-physical.c

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-af

dbutils.o strutil.o controldata.o compat.o sysutils.o -L/home/pg10/soft/lib -lpgcommon -lpgport -L/home/pg10/soft/lib -lpq -L/home/pg10/soft/lib -Wl,--as-needed -Wl,-rpath,'/home/pg10/soft/lib',--enable-new-dtags -lpgcommon -lpgport -lpthread -lssl -lcrypto -lz -lreadline -lrt -lcrypt -ldl -lm -o repmgrd

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC repmgr-client.o repmgr-action-primary.o repmgr-action-standby.o repmgr-action-witness.o repmgr-action-cluster.o repmgr-action-node.o repmgr-action-service.o repmgr-action-daemon.o configfile.o configfile-scan.o log.o strutil.o controldata.o dirutil.o compat.o dbutils.o sysutils.o -L/home/pg10/soft/lib -lpgcommon -lpgport -L/home/pg10/soft/lib -lpq -L/home/pg10/soft/lib -Wl,--as-needed -Wl,-rpath,'/home/pg10/soft/lib',--enable-new-dtags -lpgcommon -lpgport -lpthread -lssl -lcrypto -lz -lreadline -lrt -lcrypt -ldl -lm -o repmgr

Building against PostgreSQL 10

/bin/mkdir -p '/home/pg10/soft/lib/postgresql'

/bin/mkdir -p '/home/pg10/soft/share/postgresql/extension'

/bin/mkdir -p '/home/pg10/soft/bin'

/bin/install -c -m 755 repmgr.so '/home/pg10/soft/lib/postgresql/repmgr.so'

/bin/install -c -m 644 .//repmgr.control '/home/pg10/soft/share/postgresql/extension/'

/bin/install -c -m 644 .//repmgr--unpackaged--4.0.sql .//repmgr--unpackaged--5.1.sql .//repmgr--4.0.sql .//repmgr--4.0--4.1.sql .//repmgr--4.1.sql .//repmgr--4.1--4.2.sql .//repmgr--4.2.sql .//repmgr--4.2--4.3.sql .//repmgr--4.3.sql .//repmgr--4.3--4.4.sql .//repmgr--4.4.sql .//repmgr--4.4--5.0.sql .//repmgr--5.0.sql .//repmgr--5.0--5.1.sql .//repmgr--5.1.sql '/home/pg10/soft/share/postgresql/extension/'

/bin/install -c -m 755 repmgr repmgrd '/home/pg10/soft/bin/'

[pg10@data01 repmgr-5.1.0]$

创建 /home/pg10/conf 目录

[pg10@data01 repmgr-5.1.0]$ mkdir ~/conf

编辑配置文件

[pg10@data01 conf]$ cat ~/conf/repmgr.conf

node_id=1

node_name='10.10.10.101'

conninfo='host=10.10.10.101 user=repmgr dbname=postgres password=1qaz@WSX port=5666'

data_directory='/home/pg10/data'

log_level='info'

log_facility='STDERR'

log_file='/home/pg10/conf/repmgr.log'

这一点很重要（位置）

[pg10@data01 ~]$ egrep "repmgr" $PGDATA/pg_hba.conf

host all repmgr 0.0.0.0/0 trust ---->放在host一行最前边

host replication repmgr 10.10.10.0/24 trust

[pg10@data01 ~]$

[pg10@data01 ~]$ psql -U postgres -d postgres

psql (10.14)

Type "help" for help.

postgres=# \q

[pg10@data01 ~]$ repmgr -f ~/conf/repmgr.conf primary register

INFO: connecting to primary database...

NOTICE: attempting to install extension "repmgr"

NOTICE: "repmgr" extension successfully installed

NOTICE: primary node record (ID: 1) registered

[pg10@data01 ~]$

[pg10@data01 ~]$ repmgr -f ~/conf/repmgr.conf primary register

INFO: connecting to primary database...

NOTICE: attempting to install extension "repmgr"

NOTICE: "repmgr" extension successfully installed

NOTICE: primary node record (ID: 1) registered

[pg10@data01 ~]$ repmgr -f ~/conf/repmgr.conf cluster show

----+--------------+---------+-----------+----------+----------+----------+----------+---------------------------------------------------------------------------

1 | 10.10.10.101 | primary | * running | | default | 100 | 1 | host=10.10.10.101 user=repmgr dbname=postgres password=1qaz@WSX port=5666

[pg10@data01 ~]$ repmgr -f ~/conf/repmgr.conf service status

----+--------------+---------+-----------+----------+-------------+-----+---------+--------------------

1 | 10.10.10.101 | primary | * running | | not running | n/a | n/a | n/a

[pg10@data01 ~]$ vim $PGDATA/postgresql.conf

[pg10@data01 ~]$ pg_ctl restart -D $PGDATA -l /tmp/logfile

waiting for server to shut down.... done

server stopped

waiting for server to start.... done

server started

[pg10@data01 ~]$ repmgr -f ~/conf/repmgr.conf service status

----+--------------+---------+-----------+----------+-------------+-----+---------+--------------------

1 | 10.10.10.101 | primary | * running | | not running | n/a | n/a | n/a

[pg10@data01 ~]$ repmgr -f ~/conf/repmgr.conf cluster show

----+--------------+---------+-----------+----------+----------+----------+----------+---------------------------------------------------------------------------

1 | 10.10.10.101 | primary | * running | | default | 100 | 1 | host=10.10.10.101 user=repmgr dbname=postgres password=1qaz@WSX port=5666

[pg10@data01 ~]$ tail -1 $PGDATA/postgresql.conf

shared_preload_libraries = 'passwordcheck,repmgr'

[pg10@data01 ~]$

节点二配置

--此处选择编译

[root@data02 ~]# yum install -y openssl-devel zlib-devel readline-devel libevent-devel

[pg10@data02 ~]$ cd resource/

[pg10@data02 resource]$ tar -zxf postgresql-10.14.tar.gz

[pg10@data02 resource]$ cd postgresql-10.14/

[pg10@data02 postgresql-10.14]$ ./configure --prefix=/home/pg10/soft --with-openssl --with-pgport=5666

[pg10@data02 postgresql-10.14]$ make world -j24 && make install-world -j24

--配置环境变量

--此环境变量可以从主节点将 /home/pg10/.bashrc 拷贝过来

[pg10@data01 ~]$ scp ~/.bashrc pg10@data02:/home/pg10/

--读取环境变量

[pg10@data02 ~]$ . ~/.bashrc

--从主节点将 /home/pg10/resouce/postgresql-10.14/contrib 拷贝到备用节点

[pg10@data01 postgresql-10.14]$ scp -r contrib/ data02:/home/pg10/resource/postgresql-10.14/

--编译 pgaudit 模块

[pg10@data02 contrib]$ cd pgaudit-REL_10_STABLE/

[pg10@data02 pgaudit-REL_10_STABLE]$ make install

--编译安装 repmgr(过程略)

--创建配置文件存放目录

[pg10@data02 ~]$ mkdir ~/conf

--创建表空间信息和pglog 目录

[pg10@data02 ~]$ sudo mkdir -p /exam

[pg10@data02 ~]$ sudo chown pg10.pg10 /exam/

[pg10@data02 ~]$ sudo mkdir -p /pglog

[pg10@data02 ~]$ sudo chown pg10.pg10 /pglog/

--编辑配置文件(快速编辑从主节点拷贝过来修改)

[pg10@data01 conf]$ scp ~/conf/repmgr.conf pg10@data02:/home/pg10/conf/

--修改如下

[pg10@data02 conf]$ cat repmgr.conf

node_id=2

node_name='10.10.10.102'

conninfo='host=10.10.10.102 user=repmgr dbname=postgres password=1qaz@WSX port=5666'

data_directory='/home/pg10/data'

log_level='info'

log_facility='STDERR'

log_file='/home/pg10/conf/repmgr.log'

--测试克隆信息

[pg10@data02 conf]$ repmgr -h 10.10.10.101 -U repmgr -d postgres -f ~/conf/repmgr.conf standby clone --dry-run -p5666

--开始克隆

[pg10@data02 ~]$ repmgr -h 10.10.10.101 -U repmgr -d postgres -f ~/conf/repmgr.conf standby clone -p5666

NOTICE: destination directory "/home/pg10/data" provided

INFO: connecting to source node

DETAIL: connection string is: host=10.10.10.101 user=repmgr port=5666 dbname=postgres

DETAIL: current installation size is 42 MB

NOTICE: checking for available walsenders on the source node (2 required)

NOTICE: checking replication connections can be made to the source server (2 required)

INFO: checking and correcting permissions on existing directory "/home/pg10/data"

NOTICE: starting backup (using pg_basebackup)...

HINT: this may take some time; consider using the -c/--fast-checkpoint option

INFO: executing:

pg_basebackup -l "repmgr base backup" -D /home/pg10/data -h 10.10.10.101 -p 5666 -U repmgr -X stream

NOTICE: standby clone (using pg_basebackup) complete

NOTICE: you can now start your PostgreSQL server

HINT: for example: pg_ctl -D /home/pg10/data start

HINT: after starting the server, you need to register this standby with "repmgr standby register"

--启动备用节点

[pg10@data02 ~]$ pg_ctl start -D $PGDATA -l /tmp/logfile

--注册备用节点

[pg10@data02 ~]$ repmgr -f ~/conf/repmgr.conf standby register

INFO: connecting to local node "standby" (ID: 2)

INFO: connecting to primary database

WARNING: --upstream-node-id not supplied, assuming upstream node is primary (node ID 1)

INFO: standby registration complete

NOTICE: standby node "standby" (ID: 2) successfully registered

--备用节点状态查看

[pg10@data02 ~]$ repmgr -f ~/conf/repmgr.conf cluster show

----+---------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------------------------

[pg10@data02 repmgr-5.1.0]$ cd

[pg10@data02 ~]$ repmgr -h 10.10.10.101 -U repmgr -d postgres -f ~/conf/repmgr.conf standby clone --dry-run -p5666

NOTICE: destination directory "/home/pg10/data" provided

INFO: connecting to source node

DETAIL: connection string is: host=10.10.10.101 user=repmgr port=5666 dbname=postgres

DETAIL: current installation size is 30 MB

INFO: "repmgr" extension is installed in database "postgres"

INFO: parameter "max_wal_senders" set to 10

NOTICE: checking for available walsenders on the source node (2 required)

INFO: sufficient walsenders available on the source node

DETAIL: 2 required, 10 available

NOTICE: checking replication connections can be made to the source server (2 required)

INFO: required number of replication connections could be made to the source server

DETAIL: 2 replication connections required

NOTICE: standby will attach to upstream node 1

HINT: consider using the -c/--fast-checkpoint option

INFO: all prerequisites for "standby clone" are met

[pg10@data02 ~]$ repmgr -h 10.10.10.101 -U repmgr -d postgres -f ~/conf/repmgr.conf standby clone -p5666

NOTICE: destination directory "/home/pg10/data" provided

INFO: connecting to source node

DETAIL: connection string is: host=10.10.10.101 user=repmgr port=5666 dbname=postgres

DETAIL: current installation size is 30 MB

NOTICE: checking for available walsenders on the source node (2 required)

NOTICE: checking replication connections can be made to the source server (2 required)

INFO: checking and correcting permissions on existing directory "/home/pg10/data"

NOTICE: starting backup (using pg_basebackup)...

HINT: this may take some time; consider using the -c/--fast-checkpoint option

INFO: executing:

pg_basebackup -l "repmgr base backup" -D /home/pg10/data -h 10.10.10.101 -p 5666 -U repmgr -X stream

NOTICE: standby clone (using pg_basebackup) complete

NOTICE: you can now start your PostgreSQL server

HINT: for example: pg_ctl -D /home/pg10/data start

HINT: after starting the server, you need to re-register this standby with "repmgr standby register --force" to update the existing node record

[pg10@data02 ~]$ pg_ctl start -D $PGDATA -l /tmp/logfile

waiting for server to start.... done

server started

[pg10@data02 ~]$

[pg10@data03 repmgr-5.1.0]$ repmgr -h 10.10.10.101 -U repmgr -d postgres -f ~/conf/repmgr.conf standby clone --dry-run -p5666

NOTICE: destination directory "/home/pg10/data" provided

INFO: connecting to source node

DETAIL: connection string is: host=10.10.10.101 user=repmgr port=5666 dbname=postgres

DETAIL: current installation size is 30 MB

INFO: "repmgr" extension is installed in database "postgres"

INFO: parameter "max_wal_senders" set to 10

NOTICE: checking for available walsenders on the source node (2 required)

INFO: sufficient walsenders available on the source node

DETAIL: 2 required, 10 available

NOTICE: checking replication connections can be made to the source server (2 required)

INFO: required number of replication connections could be made to the source server

DETAIL: 2 replication connections required

NOTICE: standby will attach to upstream node 1

HINT: consider using the -c/--fast-checkpoint option

INFO: all prerequisites for "standby clone" are met

[pg10@data03 repmgr-5.1.0]$ repmgr -h 10.10.10.101 -U repmgr -d postgres -f ~/conf/repmgr.conf standby clone -p5666

NOTICE: destination directory "/home/pg10/data" provided

INFO: connecting to source node

DETAIL: connection string is: host=10.10.10.101 user=repmgr port=5666 dbname=postgres

DETAIL: current installation size is 30 MB

NOTICE: checking for available walsenders on the source node (2 required)

NOTICE: checking replication connections can be made to the source server (2 required)

INFO: checking and correcting permissions on existing directory "/home/pg10/data"

NOTICE: starting backup (using pg_basebackup)...

HINT: this may take some time; consider using the -c/--fast-checkpoint option

INFO: executing:

pg_basebackup -l "repmgr base backup" -D /home/pg10/data -h 10.10.10.101 -p 5666 -U repmgr -X stream

NOTICE: standby clone (using pg_basebackup) complete

NOTICE: you can now start your PostgreSQL server

HINT: for example: pg_ctl -D /home/pg10/data start

HINT: after starting the server, you need to re-register this standby with "repmgr standby register --force" to update the existing node record

[pg10@data03 repmgr-5.1.0]$ pg_ctl start -D $PGDATA -l /tmp/logfile

waiting for server to start.... done

server started

[pg10@data03 repmgr-5.1.0]$

[pg10@data02 conf]$ cat repmgr.conf

node_id=2

node_name='10.10.10.102'

conninfo='host=10.10.10.102 user=repmgr dbname=postgres password=1qaz@WSX port=5666'

data_directory='/home/pg10/data'

log_level='info'

log_facility='STDERR'

log_file='/home/pg10/conf/repmgr.log'

[pg10@data02 conf]$

[pg10@data02 conf]$ repmgr -f ~/conf/repmgr.conf standby register

INFO: connecting to local node "10.10.10.102" (ID: 2)

INFO: connecting to primary database

WARNING: --upstream-node-id not supplied, assuming upstream node is primary (node ID 1)

INFO: standby registration complete

NOTICE: standby node "10.10.10.102" (ID: 2) successfully registered

[pg10@data02 conf]$

[pg10@data03 conf]$ repmgr -f ~/conf/repmgr.conf standby register

INFO: connecting to local node "10.10.10.103" (ID: 3)

INFO: connecting to primary database

WARNING: --upstream-node-id not supplied, assuming upstream node is primary (node ID 1)

INFO: standby registration complete

NOTICE: standby node "10.10.10.103" (ID: 3) successfully registered

[pg10@data03 conf]$

[pg10@data01 data]$ vim pg_hba.conf

[pg10@data01 data]$ repmgr -f ~/conf/repmgr.conf cluster show

----+--------------+---------+-----------+----------+----------+----------+----------+---------------------------------------------------------------------------

1 | 10.10.10.101 | primary | * running | | default | 100 | 1 | host=10.10.10.101 user=repmgr dbname=postgres password=1qaz@WSX port=5666

[pg10@data01 data]$ repmgr -f ~/conf/repmgr.conf cluster show

----+--------------+---------+-----------+--------------+----------+----------+----------+---------------------------------------------------------------------------

1 | 10.10.10.101 | primary | * running | | default | 100 | 1 | host=10.10.10.101 user=repmgr dbname=postgres password=1qaz@WSX port=5666

2 | 10.10.10.102 | standby | running | 10.10.10.101 | default | 100 | 1 | host=10.10.10.102 user=repmgr dbname=postgres password=1qaz@WSX port=5666

3 | 10.10.10.103 | standby | running | 10.10.10.101 | default | 100 | 1 | host=10.10.10.103 user=repmgr dbname=postgres password=1qaz@WSX port=5666

[pg10@data01 data]$

报错

[pg10@data02 repmgr-5.1.0]$ repmgr -h 10.10.10.101 -U repmgr -d postgres -f ~/conf/repmgr.conf standby clone --dry-run -p5666

NOTICE: destination directory "/home/pg10/data" provided

INFO: connecting to source node

DETAIL: connection string is: host=10.10.10.101 user=repmgr port=5666 dbname=postgres

ERROR: connection to database failed

DETAIL:

could not connect to server: No route to host

Is the server running on host "10.10.10.101" and accepting

TCP/IP connections on port 5666?

解决方法

Last login: Wed Jan 6 06:25:00 2021 from gateway

[root@data03 ~]# systemctl stop firewalld.service

[root@data03 ~]# systemctl disable firewalld.service

Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.

Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@data03 ~]#

码农公寓

相关文章