pg数据库操作

admin

[postgres@aqzhxxh-pg-1 log]$ psql
/ Q( g; s2 b  s! ~% Z4 upsql (13.3)
Type "help" for help.
postgres=# select * from pg_stat_replication;
" _1 z( r( I* g" M# F2 k+ _7 K- M 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
' v% @& w, S2 y, u4 M+ d7 \-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
---------+------------+-----------+-----------+------------+---------------+------------+------------
(0 rows)
" p) E, X9 [9 t0 r! ]postgres=# select * from pg_replication_slots ;
+ \/ C( r! l6 | slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status
$ F- }, E9 N# {9 E+ [ | safe_wal_size
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+-----------
-+---------------
$ b: o. ~# I2 e$ f(0 rows)
, Y4 u8 q" `0 n& m! J+ p- y4 ~postgres=# checkpoint ;
' ~) p& P, a  Z% C/ cCHECKPOINT
/ t, |! \% d# m7 V, z0 k/ Hpostgres=#
3 @- I. k7 A5 P  x

admin

postgres=# show max_wal_size;
7 q5 ]4 x' S% {; o8 b max_wal_size
% _% Q) b  k( A$ C7 X--------------
# h3 ?# n5 g* A' b# w! c 8GB
3 a" p' T$ W$ z4 T& G8 n% p(1 row)
7 L2 W5 {; z& c3 t+ y
postgres=#
* l( \' o6 B3 K9 y- Q) b

[postgres@aqzhxxh-pg-1 pgdata]$ pg_ctl status
  B2 U7 S; l5 v, a# {7 k1 y# p9 Qpg_ctl: server is running (PID: 11857)
) k0 [! {: h* ]7 v: t  n8 U% L, W/home/postgres/FlyingDB13/bin/postgres
; h7 t2 j. }) a$ {( C* ?[postgres@aqzhxxh-pg-1 pgdata]$ psql
9 D# d3 p/ u: Y2 I3 X0 j$ {psql (13.3)
4 D8 A$ J) x' h( P6 n$ n' T' CType "help" for help.
- e9 z- F( ~4 g# E- D" r9 d$ d! b
& p5 Y2 J: j0 r5 M: ~* R5 fpostgres=# show archive_command ;
archive_command
$ r! @3 _, k8 N& D* T-----------------
date
(1 row)
[postgres@aqzhxxh-pg-1 pgdata]$ psql
; I6 O7 u6 q9 N# s: {psql (13.3)
Type "help" for help.
' u; j- b8 l& ~
3 l! R. }9 Z2 C+ P1 tpostgres=# show archive_command ;
archive_command
" b; ^2 [+ H- X4 o: y-----------------
date
% b, m: O0 ^$ a) f8 H) o" [) d" g(1 row)

postgres=# checkpoint ;
CHECKPOINT
9 _, D) i& b2 gpostgres=# 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
8 L" @. K# `) m+ |lush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
/ }# O- P( ~' A0 c9 r0 G-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
) u, ~! E2 T% D( j9 O---------+------------+-----------+-----------+------------+---------------+------------+------------
( r. {; s) g1 r! L; z6 G2 v(0 rows)
/ I- {" b6 p$ T- a3 a' h) K% ^# _
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
2 I& ~( @% j7 `$ w' Y% q" q f
7 R0 C) x) Z1 a  |6 H* p( w+ K- _(1 row)

postgres=#

admin

postgres=# show   primary_conninfo;
                                                                                                           primary_conninfo                                 
                                                                          
------------------------------------------------------------------------------------------------------------------------------------------------------------
( d' j: }& a  ~/ W--------------------------------------------------------------------------
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
3 D0 S* ]" K2 L+ E2 W(1 row)
* e( N  B1 h3 ^1 Y7 u; C" K  N9 n

admin

[postgres@aqzhxxh-pg-2 ~]$ pg_ctl -D /data/pgdata stop
- _$ J6 P4 ~# ?$ [* n4 Twaiting for server to shut down......... done
server stopped
. H) ^9 m% _7 P5 G5 U7 f3 U[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
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 29/72000028 on timeline 1
: j& e# V; i# ^. |" Z& Tpg_basebackup: starting background WAL receiver
9 E; h: \- d1 {6 Wpg_basebackup: created temporary replication slot "pg_basebackup_28129"
% N5 Y- C6 ~+ @140636462/140636462 kB (100%), 1/1 tablespace                                         
# Q0 g! @& x1 q- _, w& T' C  _pg_basebackup: write-ahead log end point: 29/7218EAF0
3 s2 l7 a2 _3 xpg_basebackup: waiting for background process to finish streaming ...
& W* m$ {/ {- b0 C8 Q) H% Gpg_basebackup: syncing data to disk ...
" [# A; m+ ]; [% Rpg_basebackup: renaming backup_manifest.tmp to backup_manifest
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
pg_basebackup: checkpoint completed
1 n& m1 i- r4 C- C3 _pg_basebackup: write-ahead log start point: 29/75000028 on timeline 1
pg_basebackup: starting background WAL receiver
; |# o& e4 @; spg_basebackup: created temporary replication slot "pg_basebackup_29262"
& B) V- {% b& X2 E& X. F140644606/140644606 kB (100%), 1/1 tablespace                                         
" e) P9 Y' b* c- |! U: mpg_basebackup: write-ahead log end point: 29/7522D570
/ @& `9 o, m6 T" H) Ppg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
- y& z' b7 w' p% S4 bpg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
[postgres@aqzhxxh-pg-2 ~]$ pg_ctl -D /data/pgdata start
0 V* n6 V  D5 f# @) Q: zwaiting 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".
4 Z8 |; W$ E5 S9 P* L( r$ @, ~ done
server started
7 `* Z+ h7 a3 q9 Z. G[postgres@aqzhxxh-pg-2 ~]$ psql
2 K! M0 q, v, [7 a% m; opsql (13.3)
* C+ L' S7 `; x1 A, w* C* DType "help" for help.

( h% W9 n" K9 q# c! Wpostgres=# select * from pg_stat_replication ;
6 [% _: L$ V2 s5 C5 V3 E 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
& I) f! D0 }+ ]! c( k% h-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
---------+------------+-----------+-----------+------------+---------------+------------+------------
4 O9 j: P! i" B# y' _) S(0 rows)


) z- I+ H5 U% m  F3 V! A. P[postgres@aqzhxxh-pg-1 ~]$ psql
psql (13.3)
Type "help" for help.
1 E' O% F8 B+ v2 ~" l" }postgres=# select * from pg_stat_replication ;
  pid  | usesysid |  usename  | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   
* z( t) p, S: N$ d|  sent_lsn   |  write_lsn  |  flush_lsn  | replay_lsn  |    write_lag    |    flush_lag    |   replay_lag    | sync_priority | sync_state |          reply_
time           
% L) ?2 j7 O% }' A-------+----------+-----------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------
/ n  {8 d& I1 u; d9 i( j3 E8 D- [+-------------+-------------+-------------+-------------+-----------------+-----------------+-----------------+---------------+------------+----------------
( o2 q* ^; u+ h8 `2 @  V* ~- D% Z---------------
- [/ l2 O/ S7 J! |5 H. {, r 29581 |    16384 | replicate | walreceiver      | 10.101.102.87 |                 |        2193 | 2022-01-13 16:23:56.283942+08 |              | streaming
; M. ]5 \6 J' `% e: o1 S| 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)
. l9 ^( r0 B% n1 V

- N# Y6 [. G/ P