pg数据库操作

admin

[postgres@aqzhxxh-pg-1 log]$ psql
2 y  Y5 k/ c% m9 x% Qpsql (13.3)
. O0 I! M2 k; |: a& i9 m+ mType "help" for help.
! I6 Q; [: v& S% Q- U) 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
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
---------+------------+-----------+-----------+------------+---------------+------------+------------
(0 rows)
' D" ?! v. p- v& A+ j" y( q5 s2 a' vpostgres=# 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
9 g& _: x: Z1 ]( d( ?% b* F& C | safe_wal_size
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+-----------
-+---------------
(0 rows)
postgres=# checkpoint ;
CHECKPOINT
" G: D) j3 ]  j6 Epostgres=#
: m5 p- N! {" y% m( w& X

admin

postgres=# show max_wal_size;
  u+ X& ^- ^3 p max_wal_size
--------------
8GB
, f# i: i; _1 i; S& l1 s' N(1 row)

postgres=#


% V, f' |0 f9 K" `& \[postgres@aqzhxxh-pg-1 pgdata]$ pg_ctl status
pg_ctl: server is running (PID: 11857)
- r' s+ T* l) Z$ i% Y9 J/home/postgres/FlyingDB13/bin/postgres
! ^" s/ c. ^: M2 a. A[postgres@aqzhxxh-pg-1 pgdata]$ psql
5 j) L+ ?! f& b) g- F( qpsql (13.3)
Type "help" for help.

) i! v4 |( N5 rpostgres=# show archive_command ;
archive_command
0 \: @) q. c' V' l/ Y% e1 I-----------------
date
/ Y/ Y/ D" _- a8 h# a+ h(1 row)
0 v- T) ^* N' g$ K[postgres@aqzhxxh-pg-1 pgdata]$ psql
! L6 L# w; `3 W" ypsql (13.3)
6 b. W- p! U& m' ?. gType "help" for help.

7 w! ^, D/ X+ G8 i+ }1 q3 Ypostgres=# show archive_command ;
" R) P. m. z' P! a& z5 z% n# [* L archive_command
3 C6 l, x9 ~8 F1 @2 u-----------------
date
0 `! N0 M! A  k. r" g(1 row)
8 s. l) C  \2 V+ g$ J
postgres=# checkpoint ;
- M4 [' ~% f- t, I: s! RCHECKPOINT
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
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
---------+------------+-----------+-----------+------------+---------------+------------+------------
(0 rows)

postgres=# select pg_is_in_recovery();
pg_is_in_recovery
% x  S$ b) i6 d9 Q: }-------------------
  K% Q& _7 h3 d8 k$ L- j/ g% h! v f
(1 row)
3 M4 I7 u1 i) ?; T# @- t
postgres=#

admin

postgres=# show   primary_conninfo;
6 s  ^  R+ w- b& o                                                                                                           primary_conninfo                                 
, n% O& t* R- ^                                                                          
, w/ `: _/ Q0 c# _------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
% @+ J; T' c7 Z4 }+ H 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
waiting for server to shut down......... done
server stopped
/ }# q, S8 @, z7 r[postgres@aqzhxxh-pg-2 ~]$ mv /data/pgdata/ /data/pgdata_0113
2 C; X. o/ j, H( q  E: \. F[postgres@aqzhxxh-pg-2 ~]$ pg_basebackup -Ft -v -P -R -D /data/pgdata -U replicate -h 10.101.102.86 -p5432
4 t1 q+ D3 O4 g9 L8 w7 z% wpg_basebackup: initiating base backup, waiting for checkpoint to complete
  {+ b% o" c! L; Zpg_basebackup: checkpoint completed
; O/ V5 l0 c. P$ ?pg_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"
7 s. D5 C6 `8 q! X$ {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 ...
pg_basebackup: syncing data to disk ...
, i8 _+ M8 y5 ^" J4 }pg_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
2 d' p/ M# D' t) }) n) Lpg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 29/75000028 on timeline 1
pg_basebackup: starting background WAL receiver
+ K# y4 _! x4 q6 X; mpg_basebackup: created temporary replication slot "pg_basebackup_29262"
5 g0 D3 x9 r) }- Q140644606/140644606 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 29/7522D570
pg_basebackup: waiting for background process to finish streaming ...
$ c0 }* Y0 N8 dpg_basebackup: syncing data to disk ...
/ n# ^& E( {8 M( Z, [8 X, `pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
[postgres@aqzhxxh-pg-2 ~]$ pg_ctl -D /data/pgdata start
2 k& H( d3 e* ]( C9 wwaiting for server to start....2022-01-13 16:24:13.290 CST [9337] LOG:  redirecting log output to logging collector process
$ M: J8 F7 A5 w7 q% G* e: ^2022-01-13 16:24:13.290 CST [9337] HINT:  Future log output will appear in directory "log".
done
  n/ `# ~2 G. _  p, G' [server started
; X$ [) m( P' R* t  i3 s[postgres@aqzhxxh-pg-2 ~]$ psql
psql (13.3)
Type "help" for help.
" H. ~- Q) J( z2 u3 R+ ^
postgres=# select * from pg_stat_replication ;
: ~! n0 [2 B6 `% y4 h pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | f
( g) z4 }! o8 ^lush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
9 u3 V* r' {* \) }-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
! x) J/ b$ n* Y5 J& }2 |' {---------+------------+-----------+-----------+------------+---------------+------------+------------
(0 rows)


) q8 u" I: n4 ?6 k3 R' y, w[postgres@aqzhxxh-pg-1 ~]$ psql
psql (13.3)
Type "help" for help.
postgres=# select * from pg_stat_replication ;
: z* G8 F1 Q1 x4 G4 d# B& x  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           
-------+----------+-----------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------
+-------------+-------------+-------------+-------------+-----------------+-----------------+-----------------+---------------+------------+----------------
---------------
5 L5 `$ e4 N2 M# D5 X- Z% l 29581 |    16384 | replicate | walreceiver      | 10.101.102.87 |                 |        2193 | 2022-01-13 16:23:56.283942+08 |              | streaming
* |5 R' `6 q+ Q1 O, [) || 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
5 s: M7 ?' [0 G4:17.862534+08
5 p, H6 F. N" X* P, v(1 row)


3 R5 o6 q5 {' L6 s4 l6 t- L! U! ^3 M8 ?