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

易陆发现论坛

 找回密码
 开始注册
易陆发现论坛»操作系统区 › 数据库区 › MySql/Mariadb数据库 › pg数据库操作
查看: 475|回复: 4
收起左侧

pg数据库操作

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

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

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

x
[postgres@aqzhxxh-pg-1 log]$ psql + l# {) N$ y, J7 _, S) D
psql (13.3)
4 N& v' i* `/ M- K$ J! D" k1 k! A; MType "help" for help.
. X# _% E0 c+ |8 }postgres=# select * from pg_stat_replication;! o' h0 M0 [0 k, h% p9 k! {
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | f) }5 I$ l% C4 |! i. T
lush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time : t; r6 w3 G' m- @) D; o
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
7 I) s" \/ ]$ s- e---------+------------+-----------+-----------+------------+---------------+------------+------------
8 w! A- E+ d5 G& H0 n7 L0 _. F% J(0 rows)( \: l) b1 Q' A% J- g% d+ c* L
postgres=# select * from pg_replication_slots ;+ P+ X7 x$ W( j" l
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status% m- x; p# P& k+ f/ M8 U7 l2 q
| safe_wal_size
  a. v& I9 \( Q" q( H2 s( X-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+-----------
, \" }3 R9 n) p+ p7 g-+---------------- `' [' p- W5 x; }
(0 rows)
6 O3 v. y* n7 l8 P* f; X2 |' g0 X& E( ppostgres=# checkpoint ;
2 k# y, I% D6 w+ gCHECKPOINT
  M+ R* u5 F" l1 |( hpostgres=# / D- @% {. I/ ~
点评回复

使用道具 举报

admin
 楼主| 发表于 2022-1-13 14:39:09 | 显示全部楼层
postgres=# show max_wal_size;7 W0 H# k" r8 x0 n+ {$ a: R
max_wal_size
  d$ A% N$ f; B0 p- m5 T% W--------------
5 c$ A( z9 Y" D  o/ ? 8GB: r. r: G# y  B, Z# ?$ |- |
(1 row)
  I& L! v5 @4 B- d& e% N/ F
8 C( I$ x# x! }$ O; S' k2 {postgres=# ' a3 E% {0 _* C2 g

5 s- Z4 R1 q4 W+ V, Q: ^1 c* c' N1 S# d' R) ]3 a. L
[postgres@aqzhxxh-pg-1 pgdata]$ pg_ctl status
- a9 ~5 A1 c) K4 H8 fpg_ctl: server is running (PID: 11857)7 U  P, g- W& Q6 `# u9 }# r- d
/home/postgres/FlyingDB13/bin/postgres
, q: |3 J" |. c0 S2 u$ C$ `[postgres@aqzhxxh-pg-1 pgdata]$ psql6 B; p8 F# I- o8 V" K
psql (13.3)
- C' ^+ ]) m3 N& b- AType "help" for help.
5 y$ m) ?6 F0 d, y! f
5 B; R" y. o, f! O3 F! Spostgres=# show archive_command ;
5 S! M# h2 j5 A' t' [( e4 Y! F$ ?) D archive_command ! d9 a) a2 V( \1 k9 E5 Z4 \
-----------------
' ~( h! ^/ z7 O2 `3 X date
, Q, r0 l( w' \: ^" T# ^  e7 b(1 row), l( p. |+ K+ W) @
[postgres@aqzhxxh-pg-1 pgdata]$ psql
2 ?3 x; A6 S. e6 T! W4 k2 ^) Z8 f/ lpsql (13.3)
6 Z: {4 _+ b+ S" x1 TType "help" for help.3 m  [2 H$ `  ?7 E9 p

7 w/ @# t$ w, s7 m' L7 wpostgres=# show archive_command ;6 m& \$ A% m9 Z% Z
archive_command 5 b" i8 }) t# f  r
-----------------7 L3 l) P7 W( I5 F1 _
date
" |- Q" m# ]3 |3 s' R/ f(1 row)
! j0 U: Q9 v" [' ^) K% D* A7 C( a# X# h8 m
postgres=# checkpoint ;! V5 `& Q8 B7 n6 z# i- G( F* Z" E
CHECKPOINT) Q7 V, ~$ g( j  S0 p
postgres=# select * from pg_stat_replication ;- a' t* G" f: I5 Z' x0 |$ ~. _' u! @
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | f# W8 o3 v' Q5 Q5 H0 n+ J
lush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
( O: g/ R4 |) P( L/ Q) e-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
5 T. l: P% U& m---------+------------+-----------+-----------+------------+---------------+------------+------------; ]5 f7 v3 w6 G4 o1 W1 y8 i/ l
(0 rows)! ]9 ^- `) [/ X1 h" l
6 _* u7 z' I6 V6 S2 h: ]
postgres=# select pg_is_in_recovery();
! l8 M* F3 {! ^& a) V1 o pg_is_in_recovery
. u% J* X+ j5 u8 e4 y0 I9 g-------------------
# l# r9 f" H- v* h, |9 I, P f
6 N1 R4 @1 ~) Z; U# }* Z# @(1 row)1 h8 v: s  ]  e2 b8 I$ M

& ~7 f6 \  `, l0 ]/ Z8 T1 qpostgres=#
点评回复 支持 反对

使用道具 举报

admin
 楼主| 发表于 2022-1-13 15:41:35 | 显示全部楼层
postgres=# show   primary_conninfo;$ u6 G7 |% t% x4 O8 h1 ]2 ?
                                                                                                           primary_conninfo                                 7 Y2 M) D* c5 q' l+ a& j7 p
                                                                          ( l" Z2 l8 @" L6 P" X6 I
------------------------------------------------------------------------------------------------------------------------------------------------------------
. B" R8 c$ P" q4 [6 q+ ~--------------------------------------------------------------------------/ `% }5 X9 P5 T1 S1 M0 j* @" o5 p
user=replicate passfile='/home/postgres/.pgpass' channel_binding=prefer host=10.101.102.86 port=5432 sslmode=prefer sslcompression=0 ssl_min_protocol_versi7 Y3 {1 {  e3 N, n
on=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any8 F: F, {5 b  I5 h% b/ N$ Q# U
(1 row)
2 {$ z# u" g/ ?* T+ x; {' V# {
点评回复 支持 反对

使用道具 举报

admin
 楼主| 发表于 2022-1-13 16:04:09 | 显示全部楼层
[postgres@aqzhxxh-pg-2 ~]$ pg_ctl -D /data/pgdata stop
; {- O3 @- K2 R' V4 o% q. }) Ewaiting for server to shut down......... done
( P% ~$ S' b! _  tserver stopped
( M3 @. h2 y1 x  y' `) X[postgres@aqzhxxh-pg-2 ~]$ mv /data/pgdata/ /data/pgdata_0113% B+ L6 W7 k" q) D) _4 i* R
[postgres@aqzhxxh-pg-2 ~]$ pg_basebackup -Ft -v -P -R -D /data/pgdata -U replicate -h 10.101.102.86 -p54329 R7 M) g/ t" {6 i* ~
pg_basebackup: initiating base backup, waiting for checkpoint to complete
3 `  X- g+ E% W- w& A# xpg_basebackup: checkpoint completed. Z# z6 K+ q5 M
pg_basebackup: write-ahead log start point: 29/72000028 on timeline 1& X, y  V8 u. O* {
pg_basebackup: starting background WAL receiver0 t( J. p0 }$ X$ E4 I8 Z" [* m: A! T
pg_basebackup: created temporary replication slot "pg_basebackup_28129"1 w& ~0 s8 G+ A
140636462/140636462 kB (100%), 1/1 tablespace                                         
( o" k/ ~/ i- h" {; npg_basebackup: write-ahead log end point: 29/7218EAF0. Y' k3 O4 F" [/ j% {- E
pg_basebackup: waiting for background process to finish streaming ...
* k) b/ v( w/ ?+ a; cpg_basebackup: syncing data to disk ...
( a4 k$ y1 L6 ]1 `* R$ ?; Y, upg_basebackup: renaming backup_manifest.tmp to backup_manifest" j1 S: e. s5 S3 n
pg_basebackup: base backup completed, c9 G3 A& @5 y
[postgres@aqzhxxh-pg-2 ~]$
点评回复 支持 反对

使用道具 举报

admin
 楼主| 发表于 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. w4 ], k4 ^6 i1 X+ O4 B% U
pg_basebackup: initiating base backup, waiting for checkpoint to complete
& T/ c5 M6 N1 y/ \pg_basebackup: checkpoint completed
1 Q* J) K, W( D9 k' V4 P" |: Ypg_basebackup: write-ahead log start point: 29/75000028 on timeline 19 y+ E0 c; B- R4 M9 y* _+ O; @
pg_basebackup: starting background WAL receiver
$ \1 Q$ W1 C$ }5 l6 lpg_basebackup: created temporary replication slot "pg_basebackup_29262"3 E! l; r8 y- d1 k% o
140644606/140644606 kB (100%), 1/1 tablespace                                         
1 V' x4 C% t$ f7 D6 e+ epg_basebackup: write-ahead log end point: 29/7522D570$ a7 A- P5 w' Z1 W: I! I' v1 y
pg_basebackup: waiting for background process to finish streaming ...4 H  [1 \1 H$ p, j0 o' A+ F
pg_basebackup: syncing data to disk ...1 ^! n4 @5 N- z8 o
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
4 k# E+ m+ \% g0 tpg_basebackup: base backup completed
. k8 i/ y/ ^+ |' p4 p+ I[postgres@aqzhxxh-pg-2 ~]$ pg_ctl -D /data/pgdata start
" r( [) k% o" Iwaiting for server to start....2022-01-13 16:24:13.290 CST [9337] LOG:  redirecting log output to logging collector process
. P- s' B' h% X0 X2022-01-13 16:24:13.290 CST [9337] HINT:  Future log output will appear in directory "log".
1 ?0 ?! i" ^& }# U5 W% e; I4 | done
4 K3 o* h/ L, ^, V; O. j2 C+ Kserver started9 t% l' A8 ]8 A* T  K% F
[postgres@aqzhxxh-pg-2 ~]$ psql
# X. n+ z  _! ^% [2 Upsql (13.3)
# o" B8 k& k& a6 }# @Type "help" for help.
8 y4 {& S% q: k
: r. N6 F, f* Z' c% [: r8 Opostgres=# select * from pg_stat_replication ;
4 \% z) Q) p5 b" k( R8 G/ s3 n pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | f
; g/ W/ N- [0 f; |3 _lush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time / t3 s' Y. k6 N
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--6 @2 U0 \: r2 R
---------+------------+-----------+-----------+------------+---------------+------------+------------1 P$ O& p9 n* R" b
(0 rows)
3 m# d# k% b- V( I2 r$ F
* ~0 U3 h6 a2 w: {7 {* `
, D# w* K% m# b3 j1 M$ r9 E[postgres@aqzhxxh-pg-1 ~]$ psql  m$ w; N' o; t; t
psql (13.3)* S/ {/ X) r+ {
Type "help" for help.
0 f# u# I) Q  g! T9 spostgres=# select * from pg_stat_replication ;
- j/ \9 A! |/ T' I% m  pid  | usesysid |  usename  | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   
# j: q- M' W& J- W|  sent_lsn   |  write_lsn  |  flush_lsn  | replay_lsn  |    write_lag    |    flush_lag    |   replay_lag    | sync_priority | sync_state |          reply_
, q: j5 V% j' L8 Q- r$ ~0 `time           ' O7 G; C8 V5 p/ H) _
-------+----------+-----------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------/ |: _* V/ f1 q& l- S- P9 a
+-------------+-------------+-------------+-------------+-----------------+-----------------+-----------------+---------------+------------+----------------* Q/ A& q% T& U
---------------$ S$ j* V3 A  W0 |# Y8 C! h* H" f
29581 |    16384 | replicate | walreceiver      | 10.101.102.87 |                 |        2193 | 2022-01-13 16:23:56.283942+08 |              | streaming
( d3 G) a8 B& w# W* k| 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
8 E, q0 s1 O$ F7 n- [# o. n4:17.862534+08
# j% X2 B- ]5 k4 j! p7 C(1 row)
7 t0 t" `" f; Z3 Z5 H( b; I7 t9 E/ ^* N- G: W
8 I0 ?$ q& F% J6 R/ t
点评回复 支持 反对

使用道具 举报

返回列表 发新帖 回复
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 开始注册

本版积分规则

关闭

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

如有购买积分卡请联系497906712

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

GMT+8, 2022-5-23 22:57 , Processed in 0.043035 second(s), 21 queries .

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

© 2012-2022 Comsenz Inc.

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