pg数据库操作

admin

[postgres@aqzhxxh-pg-1 log]$ psql
psql (13.3)
& w0 g! U9 o, J) O, g( N' EType "help" for help.
1 c1 [  P' m& ~4 j% @! zpostgres=# 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
+ H- \. W  [- H( k-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
---------+------------+-----------+-----------+------------+---------------+------------+------------
(0 rows)
postgres=# select * from pg_replication_slots ;
/ s2 e" z/ \& l( Z+ z" j slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status
4 Y" U) ^# F: S | safe_wal_size
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+-----------
-+---------------
; r$ _, g; _. s6 @! f7 u6 V(0 rows)
postgres=# checkpoint ;
; U% b- ~3 ^4 f' m# LCHECKPOINT
postgres=#
& L8 U: y) `" z1 e; J

admin

postgres=# show max_wal_size;
max_wal_size
7 l* L6 h  W  B" m; x) J--------------
0 s' j2 `' X4 p% h, } 8GB
7 U3 w7 {% B2 }# B(1 row)
. W3 {+ H; r) c4 k$ v' y5 r
, b; O- _# O" I- rpostgres=#
$ J: i) N$ [, J5 ^$ o# `, Q4 y6 w
3 Y: t; C+ {  h7 m
[postgres@aqzhxxh-pg-1 pgdata]$ pg_ctl status
$ E* l4 j( r+ Y5 ypg_ctl: server is running (PID: 11857)
2 ~% l: R1 t' k/home/postgres/FlyingDB13/bin/postgres
[postgres@aqzhxxh-pg-1 pgdata]$ psql
psql (13.3)
8 i4 I# _3 S# c% i! LType "help" for help.

/ t# {. N( G% ?2 F/ z" o1 ppostgres=# show archive_command ;
2 j$ h% a, o0 |0 f archive_command
-----------------
date
(1 row)
. p2 s( T9 ^5 b4 }+ J0 N- G[postgres@aqzhxxh-pg-1 pgdata]$ psql
. t- n$ v% a/ a* K+ fpsql (13.3)
( j' S- m  @" n% s6 q8 ~1 k8 S4 o& lType "help" for help.

postgres=# show archive_command ;
archive_command
-----------------
9 e& q; K- P# u/ v- L date
! `* v# r0 q7 G7 e) F2 T# [1 j(1 row)
% m* o- ?8 A6 ^2 Y/ v
( X' o4 \- R9 A1 Q9 Epostgres=# checkpoint ;
3 m) c8 H6 u2 l0 Z) ~) {- OCHECKPOINT
( E6 x) N% `) ^. T# z& S- q. c+ u( H7 h, Upostgres=# 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
: Y. l+ z0 N3 g* @-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
---------+------------+-----------+-----------+------------+---------------+------------+------------
(0 rows)
5 \, W4 z" f# w6 Q
0 M+ X  v5 f2 ^3 q5 m2 n; Dpostgres=# select pg_is_in_recovery();
! |/ O/ Z6 i* j! d8 g& n pg_is_in_recovery
8 b, P" U5 k6 L0 G: U-------------------
6 B9 I3 e% r9 N1 x6 S  F  v f
+ Q2 B" R, S% v' M0 s" z(1 row)

( b1 g6 d  S, xpostgres=#

admin

postgres=# show   primary_conninfo;
                                                                                                           primary_conninfo                                 
                                                                          
' \+ v( m3 l2 T* q5 R------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
$ i1 ]: [7 L7 U& \8 _ 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
: O! e6 u9 y. m(1 row)
6 h, a5 x2 Z/ I# q+ _3 O1 R

admin

[postgres@aqzhxxh-pg-2 ~]$ pg_ctl -D /data/pgdata stop
3 f4 {& Z$ w. `1 O4 pwaiting for server to shut down......... done
server 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
pg_basebackup: initiating base backup, waiting for checkpoint to complete
& ~4 t& g! W0 K% `+ X* r+ vpg_basebackup: checkpoint completed
" O/ |& R# I. ?3 bpg_basebackup: write-ahead log start point: 29/72000028 on timeline 1
# W1 D1 t# {$ I2 R$ J8 mpg_basebackup: starting background WAL receiver
: v% E1 F; u7 X% ^& I. Ypg_basebackup: created temporary replication slot "pg_basebackup_28129"
2 ~2 |) l8 R4 N4 Z# X- c140636462/140636462 kB (100%), 1/1 tablespace                                         
8 H4 @( |% O7 ^# l2 ?4 Ppg_basebackup: write-ahead log end point: 29/7218EAF0
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
0 K7 p4 u, `  y/ t" A2 z, Tpg_basebackup: base backup completed
6 K, b0 p5 o6 R) g! s[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
4 W& ?. i# ?- q' B0 p$ q7 Zpg_basebackup: write-ahead log start point: 29/75000028 on timeline 1
pg_basebackup: starting background WAL receiver
6 i. ?) @  t+ o0 n+ }2 D# qpg_basebackup: created temporary replication slot "pg_basebackup_29262"
4 K$ r* \% ]' C8 ]140644606/140644606 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 29/7522D570
# i' D) ~1 n$ R: I0 L6 ~6 j* spg_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
+ {6 N- n. ^& i3 j[postgres@aqzhxxh-pg-2 ~]$ pg_ctl -D /data/pgdata start
' f4 h2 b8 }8 U' c: T+ G! gwaiting for server to start....2022-01-13 16:24:13.290 CST [9337] LOG:  redirecting log output to logging collector process
* E! B" X! q. f3 P' G2022-01-13 16:24:13.290 CST [9337] HINT:  Future log output will appear in directory "log".
0 B/ ]4 x$ n% W* i  o5 @ done
server started
[postgres@aqzhxxh-pg-2 ~]$ psql
psql (13.3)
' }2 Z# o4 ~% K* \# ]) T9 oType "help" for help.
- f. F: k0 f% L) c0 w
( o8 x% o# K- A9 |* v6 {postgres=# 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
, \5 \& m3 a3 }" R% `9 O-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
) b8 M& l7 T3 v! U---------+------------+-----------+-----------+------------+---------------+------------+------------
: M& X: q3 e4 a( F$ y* n2 i(0 rows)


[postgres@aqzhxxh-pg-1 ~]$ psql
5 F" G/ O, _. _8 g# L, Ypsql (13.3)
Type "help" for help.
! ]5 y8 }. P, F; X; M, X) {postgres=# select * from pg_stat_replication ;
8 h# T, J# o9 z% u" [7 k& t  pid  | usesysid |  usename  | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   
, s- V. q5 E7 t  {|  sent_lsn   |  write_lsn  |  flush_lsn  | replay_lsn  |    write_lag    |    flush_lag    |   replay_lag    | sync_priority | sync_state |          reply_
time           
0 ~+ @/ Z" M+ d-------+----------+-----------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------
+-------------+-------------+-------------+-------------+-----------------+-----------------+-----------------+---------------+------------+----------------
5 D% e% i7 _6 R2 H; m" U/ O---------------
29581 |    16384 | replicate | walreceiver      | 10.101.102.87 |                 |        2193 | 2022-01-13 16:23:56.283942+08 |              | streaming
: z, X. I! q4 L5 C+ q, d* _: ^| 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
(1 row)


% n( z. h, T+ W6 ]& ?6 \