将设为首页浏览此站
开启辅助访问 天气与日历 收藏本站联系我们切换到窄版

易陆发现论坛

 找回密码
 开始注册
查看: 42|回复: 4
收起左侧

pg数据库操作

[复制链接]
发表于 2022-1-13 13:36:13 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有帐号?开始注册

x
[postgres@aqzhxxh-pg-1 log]$ psql
7 n/ M9 x1 U7 e2 r! V+ tpsql (13.3)
' V) Q0 n% S+ U" p  C* S( M7 jType "help" for help.
) ], R; |6 J) K5 F4 z3 M6 Tpostgres=# select * from pg_stat_replication;- |! X. }6 a# A  {" g% N  Z
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | f! c, M: s# L0 t7 D. N
lush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
5 i2 K7 R* G4 V9 V* y; s-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--: C4 w& J  }4 Y: ]# O( J
---------+------------+-----------+-----------+------------+---------------+------------+------------
6 J2 \. s/ [- T8 ^(0 rows)
6 s( N2 g3 r# }) f) @7 Zpostgres=# select * from pg_replication_slots ;# ~) _0 {) ?/ K, F' p
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status; O" a2 Z- C6 q4 v2 F5 U, e+ d
| safe_wal_size
, L. \7 T0 J$ [4 p-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+-----------' A" A3 V* V9 h, r) k  R
-+---------------
' G) o* a5 Y% T5 u) B8 a+ V(0 rows)9 t) O' F5 u' O( |/ y' Y$ U
postgres=# checkpoint ;! O$ p0 C- ?% k& Y- l( H+ B) f! G5 W4 G
CHECKPOINT' R. w6 f; H; t. g, ~" Z. J4 y
postgres=#
1 P- n, t9 ?$ n- j6 u
 楼主| 发表于 2022-1-13 14:39:09 | 显示全部楼层
postgres=# show max_wal_size;, a: T' d* B7 A
max_wal_size
$ o$ S5 k  u+ A5 Z--------------# Z0 B$ K3 ?# l# x
8GB% |7 W2 m: F( k7 @5 y9 [7 U
(1 row)
! K8 }! M: Y, B: K3 h& ?  w3 l5 m% T
# O* Z# R2 {( L: zpostgres=#
% l# C& R5 Y$ [# t5 u0 ^0 S
1 }- m% g# m! \0 ?- z, G6 z9 z: L# _( M. z  g
[postgres@aqzhxxh-pg-1 pgdata]$ pg_ctl status6 r% h9 i: m5 V5 T0 ^
pg_ctl: server is running (PID: 11857)* n: G7 I- W2 n0 r. v& f
/home/postgres/FlyingDB13/bin/postgres
# }8 l# \! U9 |# p6 h5 F: k5 {[postgres@aqzhxxh-pg-1 pgdata]$ psql( ^' V- g, {& j5 w8 P
psql (13.3)
; R/ S/ m* Q3 ^# R; L- Y* S! Q8 z1 EType "help" for help.: M0 f  U! m5 J, {# h# o1 a8 `& a
4 m  h3 p$ F# k' z, j3 q) w/ c
postgres=# show archive_command ;
( e) X9 X9 A% e0 f# i' m. ] archive_command 6 K% _7 D; a1 f, ~3 C1 A- l  v* i: t
-----------------
" d, M6 z$ B1 E) f2 x date/ s/ L, l" Z' U% f
(1 row)5 Y+ u# ^$ ?( d' F( s8 X7 q
[postgres@aqzhxxh-pg-1 pgdata]$ psql$ s  W1 @) Y' n
psql (13.3)9 ^7 b7 _$ p2 S" g4 T, g) T
Type "help" for help.
7 t. i3 P6 f3 V6 Z" V8 k# K3 W% V, E/ o* S
postgres=# show archive_command ;6 F6 s7 ]; d" N1 W4 |. A
archive_command
0 S4 J. n. Z/ J7 J5 v4 P-----------------
' l9 Z; p) F# g7 @ date/ V7 G& C( r7 @  h& h
(1 row)
' h) I' b) g) m5 q/ ], {9 n" |
. F. b0 k/ {$ \postgres=# checkpoint ;, T. E. r1 _6 o9 S
CHECKPOINT- j6 }; X2 {2 G
postgres=# select * from pg_stat_replication ;) Z/ W4 E4 U: _, ?. ]: G5 l  }
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | f4 d% d0 p0 j" L0 k
lush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
9 y$ }5 s" A" A-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
6 p, A  t6 Q+ U& L! B---------+------------+-----------+-----------+------------+---------------+------------+------------& c' o9 s1 d- j3 a- O+ D
(0 rows)0 H2 A; \! W7 R7 s6 v' b
( e# ]$ n' M$ ?& \3 s
postgres=# select pg_is_in_recovery();
, S8 ]0 D7 H% u pg_is_in_recovery ) W+ L$ N/ H2 [; b
-------------------; L) V4 J0 W0 m% H3 C' X
f6 r% W* X" Y  `* \
(1 row)
$ O  _4 {' d4 C. Q4 b0 s$ Q
  l  M+ T2 Y  }' c3 [postgres=#
 楼主| 发表于 2022-1-13 15:41:35 | 显示全部楼层
postgres=# show   primary_conninfo;
% m% c8 H% Z6 ?% h' w                                                                                                           primary_conninfo                                 
8 b' L) s3 p  `% |$ v0 ^/ k                                                                          $ q9 p" s8 G3 H+ x6 Y* ^
------------------------------------------------------------------------------------------------------------------------------------------------------------* N& j  t- e- A
--------------------------------------------------------------------------! k" R. j& Q; J" f# y  L$ S# o
user=replicate passfile='/home/postgres/.pgpass' channel_binding=prefer host=10.101.102.86 port=5432 sslmode=prefer sslcompression=0 ssl_min_protocol_versi
" O4 }9 w8 D6 I4 Eon=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any$ k/ `1 x  J0 ]. {3 `- F5 G0 j
(1 row)+ k  Z2 y7 o' o- f) d2 `' i5 I; s
 楼主| 发表于 2022-1-13 16:04:09 | 显示全部楼层
[postgres@aqzhxxh-pg-2 ~]$ pg_ctl -D /data/pgdata stop
! f4 K4 w! `* a. z  H  vwaiting for server to shut down......... done
6 W2 M" t1 U6 R) }" {server stopped
" g# v1 [4 `$ o, F, j1 A9 Z  T[postgres@aqzhxxh-pg-2 ~]$ mv /data/pgdata/ /data/pgdata_0113
" l4 o# k8 S6 r/ H[postgres@aqzhxxh-pg-2 ~]$ pg_basebackup -Ft -v -P -R -D /data/pgdata -U replicate -h 10.101.102.86 -p5432
& W. h' k0 S8 G! |! mpg_basebackup: initiating base backup, waiting for checkpoint to complete
& s; R4 f/ X; [( Wpg_basebackup: checkpoint completed
. o& N& S8 k& ?( e- W' h2 M( G3 apg_basebackup: write-ahead log start point: 29/72000028 on timeline 17 r: i- x3 B$ j8 `( q' m6 r1 V7 ^
pg_basebackup: starting background WAL receiver
, X% H3 L& r7 }. Tpg_basebackup: created temporary replication slot "pg_basebackup_28129") Y, L, g, Q5 U" Q( k# w* u% m
140636462/140636462 kB (100%), 1/1 tablespace                                         
9 T0 \+ N0 x  kpg_basebackup: write-ahead log end point: 29/7218EAF0
0 s; N; `- l: q% }pg_basebackup: waiting for background process to finish streaming ..., F5 G7 t# w! _
pg_basebackup: syncing data to disk ...
0 C4 e0 ~1 \6 L3 f% s( F3 ~pg_basebackup: renaming backup_manifest.tmp to backup_manifest
0 t+ O5 J6 \3 r+ w$ ]( d) ]0 opg_basebackup: base backup completed, c* _6 Z6 O- V( X  y+ K: p) k5 N
[postgres@aqzhxxh-pg-2 ~]$
 楼主| 发表于 2022-1-13 16:26:43 | 显示全部楼层
[postgres@aqzhxxh-pg-2 ~]$ pg_basebackup -v -P -R -D /data/pgdata -U replicate -h 10.101.102.86 -p5432: ~! N+ Z4 R9 i. J
pg_basebackup: initiating base backup, waiting for checkpoint to complete- b8 J8 d% `/ y/ o$ B8 c- R
pg_basebackup: checkpoint completed# k; c/ `: z6 I8 a6 o% @
pg_basebackup: write-ahead log start point: 29/75000028 on timeline 17 b- I4 J& _0 p# {! W1 s
pg_basebackup: starting background WAL receiver9 T0 T$ K. F- j# I6 m
pg_basebackup: created temporary replication slot "pg_basebackup_29262"
) t: A% j& g& Q3 I5 `4 K140644606/140644606 kB (100%), 1/1 tablespace                                         
+ a( d2 Z9 z. d, x. d# m  F0 Qpg_basebackup: write-ahead log end point: 29/7522D570
4 d7 A1 B1 t9 K9 Gpg_basebackup: waiting for background process to finish streaming ...
* i( F# E0 O) X  [% {pg_basebackup: syncing data to disk ...$ I: z2 _7 s2 l' H. T+ N+ U2 [- E
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
5 a, z7 R8 @4 R, V9 Kpg_basebackup: base backup completed/ ^" S" D2 ?7 j
[postgres@aqzhxxh-pg-2 ~]$ pg_ctl -D /data/pgdata start4 L, ?0 \$ G; @
waiting for server to start....2022-01-13 16:24:13.290 CST [9337] LOG:  redirecting log output to logging collector process+ w% n, A, G, G
2022-01-13 16:24:13.290 CST [9337] HINT:  Future log output will appear in directory "log".2 f' K" h% g' y, C/ T) K) v3 c, P  v
done
$ J- w: A- Z+ O. K* X, Yserver started4 r$ m" T% s& q0 h# `! O
[postgres@aqzhxxh-pg-2 ~]$ psql
$ U* U! ^7 {- u  G; b8 Q( \/ Fpsql (13.3)
9 h5 G' V; J1 B( V. cType "help" for help.
: {% @3 o3 m3 `1 c& P2 Z7 {2 r# v
; c) N5 p* U9 T8 r( c9 b- ipostgres=# select * from pg_stat_replication ;
2 g) I& g6 v" r# X+ _ pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | f  K! p' n. g/ S
lush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time 0 |3 y( i6 ~' O
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
# n. z6 P+ V, l---------+------------+-----------+-----------+------------+---------------+------------+------------
5 ?1 z( u1 ?: D3 J6 b4 @, y(0 rows)
) b$ i* Z/ d0 \; p" N  f
. o  L/ w; |5 a8 [- _7 ?0 u: N# D2 J8 |; |+ S
[postgres@aqzhxxh-pg-1 ~]$ psql3 [7 U; b% ]' l% P( t/ ?
psql (13.3)! f( N) }& |. R; f, V+ N
Type "help" for help.  F$ R0 H7 i; m% u2 R
postgres=# select * from pg_stat_replication ;  E( Z: H4 K: \) w8 J+ |
  pid  | usesysid |  usename  | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   ( w5 _# H* r- o$ m" J1 w; \/ R
|  sent_lsn   |  write_lsn  |  flush_lsn  | replay_lsn  |    write_lag    |    flush_lag    |   replay_lag    | sync_priority | sync_state |          reply_
) P8 f2 _: H5 s- r6 [, jtime           
; V! `9 G; L9 q$ K3 Y& |& n-------+----------+-----------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------
" E# j2 x0 f7 x# G& K+-------------+-------------+-------------+-------------+-----------------+-----------------+-----------------+---------------+------------+----------------2 {! N6 y; r& @3 [
---------------# z& G: @  o  \9 f5 a( ^
29581 |    16384 | replicate | walreceiver      | 10.101.102.87 |                 |        2193 | 2022-01-13 16:23:56.283942+08 |              | streaming % f+ q( I2 _& c
| 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:26 A7 g  o, [2 l! r! v$ L# k
4:17.862534+08
4 [. q1 r# ]0 V+ v. }5 b& x(1 row)
$ G$ L4 S, o' d9 }+ s) q3 U. Q3 S- k% U  o. s1 k! C
  l3 K# J! ^' e' M" ?4 h3 N% F, s
您需要登录后才可以回帖 登录 | 开始注册

本版积分规则

关闭

站长推荐上一条 /4 下一条

如有购买积分卡请联系497906712

QQ|返回首页|Archiver|手机版|小黑屋|易陆发现 点击这里给我发消息

GMT+8, 2022-1-22 14:23 , Processed in 0.041244 second(s), 21 queries .

Powered by LR.LINUX.cloud bbs168x X3.2 Licensed

© 2012-2022 Comsenz Inc.

快速回复 返回顶部 返回列表