pg数据库操作

admin

[postgres@aqzhxxh-pg-1 log]$ psql
2 M% g( T3 A+ }psql (13.3)
Type "help" for help.
3 j/ L3 h. Y/ q9 g3 w7 ^& i" H2 Epostgres=# 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
9 t6 d9 L7 o; Q- C8 G. k7 u# flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
---------+------------+-----------+-----------+------------+---------------+------------+------------
(0 rows)
: x. f" n4 v4 q/ M8 W. G1 dpostgres=# select * from pg_replication_slots ;
$ r& ?% j, [5 |$ ?" u# K slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status
" R0 F6 e: y8 |  q | safe_wal_size
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+-----------
9 j8 s/ g, Y( I& D6 @$ T! _& t-+---------------
(0 rows)
postgres=# checkpoint ;
CHECKPOINT
postgres=#
% O  j, F* e$ {" @4 Q* C

admin

postgres=# show max_wal_size;
+ c2 b) J! ]* y. Q1 T5 ?$ N max_wal_size
--------------
8GB
7 n3 Q) |& o, M(1 row)

postgres=#
+ C% r& f7 n5 f! q

) B5 f& B. U' G0 W[postgres@aqzhxxh-pg-1 pgdata]$ pg_ctl status
% }8 o( I6 T! d( I6 }" C* X+ e0 U& hpg_ctl: server is running (PID: 11857)
/home/postgres/FlyingDB13/bin/postgres
[postgres@aqzhxxh-pg-1 pgdata]$ psql
8 i9 _0 k/ `; v5 {$ {psql (13.3)
Type "help" for help.
! x$ u# A* s  h6 p& Y# \
% `# e1 N* g8 d; M# a% A( H) F/ V* Xpostgres=# show archive_command ;
4 P& q0 T1 ~! x9 _, s archive_command
-----------------
4 r) R6 j$ E8 E2 e% c" E7 b+ E date
( b9 k# V; ?+ t# |7 K. N. F(1 row)
[postgres@aqzhxxh-pg-1 pgdata]$ psql
$ C' @0 [4 g- Ppsql (13.3)
Type "help" for help.

* \: Y" y4 k8 i! b, Kpostgres=# show archive_command ;
( K4 m6 V( M' F0 s archive_command
-----------------
0 u( ?5 t: ?. }4 [! q date
(1 row)
) m( y7 v8 v3 q$ A
, [7 I3 F4 F3 npostgres=# checkpoint ;
; {* P) f9 `+ gCHECKPOINT
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
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
; v4 B4 M: d  A9 e( ^---------+------------+-----------+-----------+------------+---------------+------------+------------
+ B+ \" D( `4 M+ ?: I(0 rows)

postgres=# select pg_is_in_recovery();
pg_is_in_recovery
+ P, G6 I; k" \) R-------------------
8 j9 q7 I# t7 z# A% P4 n# v f
(1 row)
" }9 H# }; e9 k5 w) E6 w% f
postgres=#

admin

postgres=# show   primary_conninfo;
6 X; q4 w$ e* P& B6 z                                                                                                           primary_conninfo                                 
                                                                          
. X7 U  O# X7 p9 D; h( K; m------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
9 p% C! ]% N/ b' D% _+ l user=replicate passfile='/home/postgres/.pgpass' channel_binding=prefer host=10.101.102.86 port=5432 sslmode=prefer sslcompression=0 ssl_min_protocol_versi
- J) O2 Q! \. }' V3 m$ L/ Ton=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
$ m$ J3 n8 c( D: h; }; S) o(1 row)

admin

[postgres@aqzhxxh-pg-2 ~]$ pg_ctl -D /data/pgdata stop
1 P, Y$ s& j$ F9 h2 h0 gwaiting for server to shut down......... done
server stopped
) ^/ M1 N2 g1 e6 H8 Z5 e. v[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
% h- \' }. k% }3 apg_basebackup: initiating base backup, waiting for checkpoint to complete
& W) z( i& T# [0 B7 f' w- kpg_basebackup: checkpoint completed
' _. E5 X9 i' @, M0 apg_basebackup: write-ahead log start point: 29/72000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_28129"
6 {5 G4 h4 Y- J( X+ R' P140636462/140636462 kB (100%), 1/1 tablespace                                         
6 V7 x; @) q( h6 L! N5 y! e/ f- _, n8 epg_basebackup: write-ahead log end point: 29/7218EAF0
, {) S* i. s: t# O' Jpg_basebackup: waiting for background process to finish streaming ...
+ C8 V$ A' P6 x2 D9 w3 Rpg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
2 j" b5 P* C2 z6 F. n[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
! I8 l: e& G9 C" d2 [/ epg_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
pg_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
8 x" c1 H- U5 k- J# E( k9 \+ f# Ppg_basebackup: waiting for background process to finish streaming ...
9 f# g$ n. N: b/ `pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
* Z; b2 I. B5 ~0 z" upg_basebackup: base backup completed
5 q( J8 P9 w; h; l# t; C+ m- J[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".
done
server started
" P) G; B' g& g0 v2 v[postgres@aqzhxxh-pg-2 ~]$ psql
psql (13.3)
. S5 e  p! X0 q) F4 D2 {" `3 iType "help" for help.
# R4 y3 h2 G/ F5 E
. \8 }1 w' R: C1 d8 C) npostgres=# 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
' C) N" _% n; v1 @! N$ U6 Glush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
---------+------------+-----------+-----------+------------+---------------+------------+------------
1 ]  i( M4 Y" ~(0 rows)


[postgres@aqzhxxh-pg-1 ~]$ psql
psql (13.3)
Type "help" for help.
6 e9 P9 N# K: E% Z! Ypostgres=# select * from pg_stat_replication ;
7 Z- N% T( E- a' B! z; O% S! U  pid  | usesysid |  usename  | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   
; |8 f: U- L# n- g& j: j|  sent_lsn   |  write_lsn  |  flush_lsn  | replay_lsn  |    write_lag    |    flush_lag    |   replay_lag    | sync_priority | sync_state |          reply_
time           
/ N# f& l# x* G- o$ r1 K-------+----------+-----------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------
+-------------+-------------+-------------+-------------+-----------------+-----------------+-----------------+---------------+------------+----------------
1 w; k: B1 ~8 `- P+ D+ d---------------
+ j2 X* V" W6 L7 c+ k4 U# w 29581 |    16384 | replicate | walreceiver      | 10.101.102.87 |                 |        2193 | 2022-01-13 16:23:56.283942+08 |              | streaming
( S; l8 `. a) @2 ~4 T# W8 @* R| 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)
/ g- c" z% B* e2 r8 B  f: U
4 g* a, N& S! D8 h. e% V