pg数据库操作

admin

[postgres@aqzhxxh-pg-1 log]$ psql
) L% n- E( K: l$ e/ Q+ m* Cpsql (13.3)
Type "help" for help.
2 w' ?) P- D' z8 N. c- Hpostgres=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | f
2 d& M0 a8 {7 o0 g; m. hlush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
  K& N* a; ^1 Z: B4 t2 O, u# O7 X-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
---------+------------+-----------+-----------+------------+---------------+------------+------------
(0 rows)
8 W% o' g7 c( U. tpostgres=# select * from pg_replication_slots ;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status
1 j/ a0 H5 c; I3 W0 Q( I | safe_wal_size
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+-----------
-+---------------
% C; o2 e: E4 ~(0 rows)
postgres=# checkpoint ;
- o8 k' e  m$ p; f5 Y  f* CCHECKPOINT
postgres=#

admin

postgres=# show max_wal_size;
max_wal_size
% C5 M& i' ?  G! m3 j  \/ ?--------------
% `4 Z0 R" `+ S( t# k; u 8GB
/ {& h3 c( j( T  o* [(1 row)
" i! Z' m: |3 H
postgres=#
$ h# T! W0 Q3 ]3 m

[postgres@aqzhxxh-pg-1 pgdata]$ pg_ctl status
pg_ctl: server is running (PID: 11857)
8 A8 [1 |6 B- Q5 e' t/home/postgres/FlyingDB13/bin/postgres
[postgres@aqzhxxh-pg-1 pgdata]$ psql
1 c# i2 \6 K! i% C5 \# Wpsql (13.3)
Type "help" for help.
, k! c* [% M4 n# S' ?; |! L
* s" X2 q2 @2 a* }postgres=# show archive_command ;
( O% h3 b6 Z: T, |( H, X+ K0 L# K( U! c1 n archive_command
! h; |/ ]  t1 X- z1 [4 u, J-----------------
# U# Q( i3 }2 O; z0 `5 D; o  Q- j# ^$ ~ date
(1 row)
4 f) Z% f0 j, S- ]/ C[postgres@aqzhxxh-pg-1 pgdata]$ psql
psql (13.3)
Type "help" for help.

7 @2 G% L5 Z0 Y+ A. vpostgres=# show archive_command ;
5 Z1 q. H3 w; u+ w) ]& q9 s% @ archive_command
-----------------
date
(1 row)

4 R6 V6 q; Y+ L7 \- l8 j% t7 \8 \' ipostgres=# checkpoint ;
CHECKPOINT
" k* d8 F; K2 y' b2 I, c: wpostgres=# select * from pg_stat_replication ;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | f
lush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
3 w. a! [3 _. c8 i/ s-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
---------+------------+-----------+-----------+------------+---------------+------------+------------
(0 rows)

postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
2 }9 Q4 F7 p' Q4 R( m: w/ W# C
postgres=#

admin

postgres=# show   primary_conninfo;
$ w/ `- E( T+ d: S0 Z9 `                                                                                                           primary_conninfo                                 
                                                                          
/ T# _$ E. s8 c------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
( t5 v9 k# z# B user=replicate passfile='/home/postgres/.pgpass' channel_binding=prefer host=10.101.102.86 port=5432 sslmode=prefer sslcompression=0 ssl_min_protocol_versi
! D9 `& u6 z; Y- Son=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
/ P' s  Q$ A' ~/ y(1 row)
1 |- S! h; j4 Z6 Y) E; n# p4 I* \

admin

[postgres@aqzhxxh-pg-2 ~]$ pg_ctl -D /data/pgdata stop
waiting for server to shut down......... done
! f  b$ v0 \3 q7 b3 [5 e: Userver stopped
[postgres@aqzhxxh-pg-2 ~]$ mv /data/pgdata/ /data/pgdata_0113
: @3 j9 x- }) I4 E8 T! g( a% [$ s[postgres@aqzhxxh-pg-2 ~]$ pg_basebackup -Ft -v -P -R -D /data/pgdata -U replicate -h 10.101.102.86 -p5432
& r- d+ R+ v7 T8 u0 fpg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
% q9 b5 V; g6 z" }( r! a0 vpg_basebackup: write-ahead log start point: 29/72000028 on timeline 1
6 ?4 }$ C1 |- U* v. q: v1 ipg_basebackup: starting background WAL receiver
$ v2 M9 q( P+ C: x% v0 F4 spg_basebackup: created temporary replication slot "pg_basebackup_28129"
140636462/140636462 kB (100%), 1/1 tablespace                                         
# t3 O% C6 @% D! qpg_basebackup: write-ahead log end point: 29/7218EAF0
pg_basebackup: waiting for background process to finish streaming ...
4 ^; J$ }5 W0 f; ]pg_basebackup: syncing data to disk ...
" {7 R; j! k. xpg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
2 w4 [5 p! H1 n, N% D. V[postgres@aqzhxxh-pg-2 ~]$

admin

[postgres@aqzhxxh-pg-2 ~]$ pg_basebackup -v -P -R -D /data/pgdata -U replicate -h 10.101.102.86 -p5432
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 29/75000028 on timeline 1
- l- U& l9 u/ i+ ?! L- ~4 cpg_basebackup: starting background WAL receiver
7 b) T, p2 X  \* ^pg_basebackup: created temporary replication slot "pg_basebackup_29262"
8 j! ^  q+ R% L, J) {5 Q/ w140644606/140644606 kB (100%), 1/1 tablespace                                         
5 `' ]$ b* Q; O2 cpg_basebackup: write-ahead log end point: 29/7522D570
pg_basebackup: waiting for background process to finish streaming ...
- s) q3 Y% E6 p* s; spg_basebackup: syncing data to disk ...
, B( c2 c. u6 t! o( @. L; Fpg_basebackup: renaming backup_manifest.tmp to backup_manifest
+ H" w7 s( A  u# l% E0 rpg_basebackup: base backup completed
[postgres@aqzhxxh-pg-2 ~]$ pg_ctl -D /data/pgdata start
waiting for server to start....2022-01-13 16:24:13.290 CST [9337] LOG:  redirecting log output to logging collector process
2022-01-13 16:24:13.290 CST [9337] HINT:  Future log output will appear in directory "log".
" e# ]  s# g. X8 S: [ done
, V9 U$ B3 e7 `7 R- _/ [4 p4 pserver started
[postgres@aqzhxxh-pg-2 ~]$ psql
1 I7 I% f6 m- dpsql (13.3)
Type "help" for help.

postgres=# select * from pg_stat_replication ;
9 T( w% p& B  P' ]0 n; U+ r" q pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | f
lush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
4 D7 p8 G" i6 ], T5 I5 F6 x-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
---------+------------+-----------+-----------+------------+---------------+------------+------------
3 Y' W4 U7 ^5 Y(0 rows)
( b) J& [, K' ?
  X! K: D& s5 u# ^; _
[postgres@aqzhxxh-pg-1 ~]$ psql
: O3 p/ H5 W- p5 s5 ?psql (13.3)
Type "help" for help.
# w/ [% ~& a, j+ @$ ^postgres=# select * from pg_stat_replication ;
' Z4 m( Z. \: _0 N2 D6 P  pid  | usesysid |  usename  | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   
; J( b/ Q1 C2 I, q3 a$ g) e|  sent_lsn   |  write_lsn  |  flush_lsn  | replay_lsn  |    write_lag    |    flush_lag    |   replay_lag    | sync_priority | sync_state |          reply_
4 c- @, Q& I' N% ]; Ytime           
-------+----------+-----------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------
+-------------+-------------+-------------+-------------+-----------------+-----------------+-----------------+---------------+------------+----------------
---------------
29581 |    16384 | replicate | walreceiver      | 10.101.102.87 |                 |        2193 | 2022-01-13 16:23:56.283942+08 |              | streaming
| 29/7624DCE8 | 29/7624DCE8 | 29/7624DCE8 | 29/7624DCE8 | 00:00:00.000289 | 00:00:00.001832 | 00:00:00.001834 |             0 | async      | 2022-01-13 16:2
9 D1 u: U5 V6 ~2 B4:17.862534+08
( S7 I# a/ e9 r. r! |# l3 h8 f(1 row)

; p: Q$ Q7 k! W$ J/ A5 E/ u4 S
! M9 s4 E# ~# j3 Y