pg数据库操作

admin

[postgres@aqzhxxh-pg-1 log]$ psql
" {4 v$ h" q. c; k. Q+ r/ l. Ypsql (13.3)
Type "help" for help.
( Z  x) e9 G$ K* M- }0 k: D% Xpostgres=# select * from pg_stat_replication;
+ {7 c( G! w! z1 o" b5 S4 f 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
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
---------+------------+-----------+-----------+------------+---------------+------------+------------
(0 rows)
8 p8 f2 M& z6 N' v- ]% z, ypostgres=# select * from pg_replication_slots ;
0 [' p9 L1 y1 ^* E slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status
| safe_wal_size
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+-----------
; V, B1 z: z' f+ v* @-+---------------
(0 rows)
6 X6 ?4 g: L% u/ g2 tpostgres=# checkpoint ;
CHECKPOINT
7 s8 O$ a, ?5 @- i+ zpostgres=#

admin

postgres=# show max_wal_size;
/ {+ \- L, P5 C' B1 q3 n, L max_wal_size
--------------
2 `/ |7 g5 ^: `9 V7 [* z 8GB
, `# O" d0 ?* }) `(1 row)
9 j# m* N. y0 a" v8 f
/ x! B) }) Z, o# u, d0 P, \  H; Zpostgres=#

% O. X4 ]' k/ A8 [) N- h* {
3 f% o* h* E: V4 Y* L[postgres@aqzhxxh-pg-1 pgdata]$ pg_ctl status
pg_ctl: server is running (PID: 11857)
/home/postgres/FlyingDB13/bin/postgres
[postgres@aqzhxxh-pg-1 pgdata]$ psql
psql (13.3)
0 B$ G& l( c& Z: n* b; v. L. PType "help" for help.
3 I. O: e, I0 k  f4 E0 V8 ]( Q) R
. e+ [5 D1 R+ W- j1 ~. mpostgres=# show archive_command ;
archive_command
4 {8 E( Z9 A% _  Q" D- L/ n-----------------
' h# e1 d( I2 _) p- _ date
, c- q' U: X. Y7 C7 n(1 row)
[postgres@aqzhxxh-pg-1 pgdata]$ psql
" G2 g, [5 ^# J' f7 jpsql (13.3)
/ j! E; i7 S' [Type "help" for help.

postgres=# show archive_command ;
archive_command
. j. |8 n1 t/ u% `" R+ u) N4 K-----------------
* q" X6 @* S: ?5 i' T date
(1 row)

: y* f9 }9 Z9 O" `$ R4 tpostgres=# checkpoint ;
% ^" Q; o+ m5 P/ {: f- V9 lCHECKPOINT
( L, [- s* w7 @/ O3 mpostgres=# select * from pg_stat_replication ;
1 D5 T1 r' e. |% }9 [& o! _ 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
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
---------+------------+-----------+-----------+------------+---------------+------------+------------
(0 rows)

7 ~' Y# Q: l' m, W/ Vpostgres=# select pg_is_in_recovery();
pg_is_in_recovery
4 M) B4 w1 i' j0 u% A" l-------------------
, X- \5 I' _( q' v f
$ r/ q1 B6 f9 S# F7 ~(1 row)
8 |  f8 t4 s- @5 ]
0 o, g) e, j) _3 ~) a6 Upostgres=#

admin

postgres=# show   primary_conninfo;
1 x4 l# N: q3 S$ e6 ^' j5 @+ O                                                                                                           primary_conninfo                                 
- T: }# X9 L7 c; w                                                                          
* \" ^$ q- N( Q9 L# N------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
user=replicate passfile='/home/postgres/.pgpass' channel_binding=prefer host=10.101.102.86 port=5432 sslmode=prefer sslcompression=0 ssl_min_protocol_versi
on=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
(1 row)

admin

[postgres@aqzhxxh-pg-2 ~]$ pg_ctl -D /data/pgdata stop
/ |6 p4 J/ I, p# \! L9 o- iwaiting for server to shut down......... done
/ A6 a& {7 d; O  K3 W; Y& F1 Qserver stopped
[postgres@aqzhxxh-pg-2 ~]$ mv /data/pgdata/ /data/pgdata_0113
[postgres@aqzhxxh-pg-2 ~]$ pg_basebackup -Ft -v -P -R -D /data/pgdata -U replicate -h 10.101.102.86 -p5432
, [- M4 E/ x5 {! j5 [" T: epg_basebackup: initiating base backup, waiting for checkpoint to complete
3 ?3 \7 F+ _" ~. D8 l- f% \pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 29/72000028 on timeline 1
- S/ m+ V2 ]# [: Q  S/ \! npg_basebackup: starting background WAL receiver
4 _7 ?7 J# q) Mpg_basebackup: created temporary replication slot "pg_basebackup_28129"
140636462/140636462 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 29/7218EAF0
pg_basebackup: waiting for background process to finish streaming ...
4 C" M0 \) N1 @pg_basebackup: syncing data to disk ...
/ p5 n; b6 H% m* e7 Bpg_basebackup: renaming backup_manifest.tmp to backup_manifest
2 n" ^' o4 F, ~pg_basebackup: base backup completed
[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
3 N( x0 l  D; Ppg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 29/75000028 on timeline 1
! H' Y3 {& _4 _  G8 |) J* j  Opg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_29262"
140644606/140644606 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 29/7522D570
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
9 L! T; h  F3 o0 j, G. K1 U% ypg_basebackup: base backup completed
[postgres@aqzhxxh-pg-2 ~]$ pg_ctl -D /data/pgdata start
5 X5 c! W' z4 ~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".
done
- I' G* x( \& Z; c9 R5 h$ r$ T$ Gserver started
5 h9 U# M8 w. B9 T6 O2 ~! ~[postgres@aqzhxxh-pg-2 ~]$ psql
psql (13.3)
Type "help" for help.

postgres=# select * from pg_stat_replication ;
( |) M  D& q3 X# x4 @1 R pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | f
5 I  \& _' g3 _# ?" I8 Nlush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
* h: L$ f& H- h+ `3 n: y-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
---------+------------+-----------+-----------+------------+---------------+------------+------------
( N; Z9 l2 @' x! H  Q3 r* ^9 A(0 rows)
' I( g; A( U$ p/ e4 I
. h* S+ [) X+ e( B0 g0 V
( f* T3 T8 a/ h/ w1 x[postgres@aqzhxxh-pg-1 ~]$ psql
) a" V% Q+ t2 Jpsql (13.3)
3 N/ T; z- @: }' D! Y6 n2 uType "help" for help.
postgres=# select * from pg_stat_replication ;
8 Q( d& b0 a/ m1 @- S  pid  | usesysid |  usename  | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   
|  sent_lsn   |  write_lsn  |  flush_lsn  | replay_lsn  |    write_lag    |    flush_lag    |   replay_lag    | sync_priority | sync_state |          reply_
time           
-------+----------+-----------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------
+-------------+-------------+-------------+-------------+-----------------+-----------------+-----------------+---------------+------------+----------------
---------------
29581 |    16384 | replicate | walreceiver      | 10.101.102.87 |                 |        2193 | 2022-01-13 16:23:56.283942+08 |              | streaming
% m* L5 T8 Z* J# P: j5 [1 W$ N, L| 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
4:17.862534+08
$ t/ x  Y2 k1 K( g, E7 ^(1 row)

# f- ~& Y' O; |) d