pg同一主机 测试主从复制

数据库版本

(PostgreSQL) 13.4

1，初始化主数据库

initdb -D /data/master

-bash-4.2$ initdb -D /data/master
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.utf8".
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/master ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: 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 /data/master -l logfile start

2，启动主数据库

pg_ctl -D /data/master/ start

-bash-4.2$ pg_ctl -D /data/master/ start
waiting for server to start....2021-10-24 13:52:44.897 CST [31511] LOG:  starting PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2021-10-24 13:52:44.897 CST [31511] LOG:  listening on IPv6 address "::1", port 5432
2021-10-24 13:52:44.898 CST [31511] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-10-24 13:52:44.922 CST [31511] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-10-24 13:52:44.996 CST [31512] LOG:  database system was shut down at 2021-10-24 13:50:30 CST
2021-10-24 13:52:45.033 CST [31511] LOG:  database system is ready to accept connections
 done
server started

3，建立备数据库目录，并备份

-bash-4.2$ mkdir /data/s001
-bash-4.2$ chmod 700 /data/s001/
-bash-4.2$ pg_basebackup -D /data/s001/ -U postgres  -Fp -X stream -R  -Pv -p5432 -h 127.0.0.1
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000060 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_32218"
24287/24287 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/2000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

4，修改备数据库端口为5433

postgresql.conf

-bash-4.2$ vi postgresql.conf
-bash-4.2$ cat postgresql.conf |grep port
port = 5433                             # (change requires restart)
#ssl_passphrase_command_supports_reload = off
                                        # supported by the operating system:
                                        # supported by the operating system:
                                        # supported by the operating system:
                                        #   %r = remote host and port

5，启动备数据库

-bash-4.2$ pg_ctl -D /data/s001/ start

waiting for server to start....2021-10-24 13:56:30.553 CST [727] LOG:  starting PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2021-10-24 13:56:30.554 CST [727] LOG:  listening on IPv6 address "::1", port 5433
2021-10-24 13:56:30.554 CST [727] LOG:  listening on IPv4 address "127.0.0.1", port 5433
2021-10-24 13:56:30.606 CST [727] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5433"
2021-10-24 13:56:30.644 CST [733] LOG:  database system was interrupted; last known up at 2021-10-24 13:54:20 CST
.2021-10-24 13:56:31.822 CST [733] LOG:  entering standby mode
2021-10-24 13:56:31.874 CST [733] LOG:  redo starts at 0/2000060
2021-10-24 13:56:31.920 CST [733] LOG:  consistent recovery state reached at 0/2000138
2021-10-24 13:56:31.922 CST [727] LOG:  database system is ready to accept read only connections
2021-10-24 13:56:31.987 CST [761] LOG:  started streaming WAL from primary at 0/3000000 on timeline 1
 done
server started

6，检查状态

主数据库检查状态：

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 762
usesysid         | 10
usename          | postgres
application_name | walreceiver
client_addr      | 127.0.0.1
client_hostname  |
client_port      | 36328
backend_start    | 2021-10-24 13:56:31.967546+08
backend_xmin     |
state            | streaming
sent_lsn         | 0/3000060
write_lsn        | 0/3000060
flush_lsn        | 0/3000060
replay_lsn       | 0/3000060
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2021-10-24 13:57:12.087302+08

备数据库状态检查：

-bash-4.2$ psql  -p 5433
psql (13.4)
Type "help" for help.

postgres=# \x
Expanded display is on.
 
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 761
status                | streaming
receive_start_lsn     | 0/3000000
receive_start_tli     | 1
written_lsn           | 0/3000060
flushed_lsn           | 0/3000060
received_tli          | 1
last_msg_send_time    | 2021-10-24 13:58:32.20877+08
last_msg_receipt_time | 2021-10-24 13:58:32.208836+08
latest_end_lsn        | 0/3000060
latest_end_time       | 2021-10-24 13:56:31.987405+08
slot_name             |
sender_host           | 127.0.0.1
sender_port           | 5432
conninfo              | user=postgres passfile=/var/lib/pgsql/.pgpass channel_binding=disable dbname=replication host=127.0.0.1 port=5432 fallback_application_name=walreceiver sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any

postgres=#