pg数据库操作

admin

[postgres@aqzhxxh-pg-1 log]$ psql
7 T  A7 ^* q! m' A* `psql (13.3)
; q" O7 [& o3 J  p# n. [4 LType "help" for help.
6 c  A) O" r6 S# p3 ^$ u  upostgres=# select * from pg_stat_replication;
7 Y/ y" l  i4 J4 ^ 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
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
4 u( h" p! e1 h' a3 W. `---------+------------+-----------+-----------+------------+---------------+------------+------------
0 c9 O) n9 t6 K  b# z" Z. \(0 rows)
postgres=# select * from pg_replication_slots ;
* [3 O& i# A  f. k8 ^4 A: | slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status
| safe_wal_size
( u; @$ _2 v, m* f-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+-----------
-+---------------
(0 rows)
postgres=# checkpoint ;
4 l8 w3 c' ^; {& v. sCHECKPOINT
postgres=#

admin

postgres=# show max_wal_size;
3 x4 ]3 d, U8 z& T4 G/ B) S1 ` max_wal_size
& {2 x( b5 |# n% u6 \* `3 x% c--------------
9 z2 e% j$ s8 Y4 S( t/ @! Y+ F( v 8GB
& P' q% g1 ?8 v$ W% x(1 row)

+ a* S7 _! w- u# u( o# kpostgres=#
1 n, Z0 @+ s) O1 J7 p+ o

) B/ y+ f% T, E8 {6 Y. J0 G[postgres@aqzhxxh-pg-1 pgdata]$ pg_ctl status
pg_ctl: server is running (PID: 11857)
, l- M' h6 B0 I, _/home/postgres/FlyingDB13/bin/postgres
[postgres@aqzhxxh-pg-1 pgdata]$ psql
4 o1 M8 B3 g( d$ h" B% n3 f2 `psql (13.3)
Type "help" for help.
4 `; R  T; g) Y0 z1 V
2 z# g4 O3 E, q) k7 _& Bpostgres=# show archive_command ;
$ M7 O* E" c9 k' e. b archive_command
- v6 w1 Z9 \4 M-----------------
date
- h  H; z. f& o3 o7 q2 i( K(1 row)
( d+ V& t7 V( n' x1 Y[postgres@aqzhxxh-pg-1 pgdata]$ psql
# H5 K% I. B- s- I9 w, x' _; lpsql (13.3)
Type "help" for help.

postgres=# show archive_command ;
archive_command
3 [; f' }- F) ]& U: N-----------------
2 k& B" Y; S% D  S1 { date
(1 row)
5 `; y& C5 Q8 v; i& N% U
postgres=# checkpoint ;
CHECKPOINT
6 X# `2 c1 @/ E8 e+ k2 ~) \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
7 e. n% L# `( D/ F! P2 }lush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
---------+------------+-----------+-----------+------------+---------------+------------+------------
(0 rows)

' N0 v" \4 d. A; K; w4 ^- q2 \postgres=# select pg_is_in_recovery();
5 ?5 X/ X& H; ?# c; ]! p pg_is_in_recovery
( U" t1 K/ n2 ^3 d0 B$ D6 l: U-------------------
$ M% F+ X5 F. e0 U f
(1 row)

) p8 J5 F0 o, Z* o' n+ R: F, Ypostgres=#

admin

postgres=# show   primary_conninfo;
                                                                                                           primary_conninfo                                 
                                                                          
/ P( Q2 n+ @" E------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
user=replicate passfile='/home/postgres/.pgpass' channel_binding=prefer host=10.101.102.86 port=5432 sslmode=prefer sslcompression=0 ssl_min_protocol_versi
+ m* c3 D( u. ]! \' O. P8 Zon=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
5 t% X8 o$ _9 x+ V+ n(1 row)
( l3 S! B: k6 w. {/ ^3 }/ F6 f! u

admin

[postgres@aqzhxxh-pg-2 ~]$ pg_ctl -D /data/pgdata stop
waiting for server to shut down......... done
1 J! H1 N" Y" r9 E; q2 k- ?server stopped
[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
. D* e6 c& k0 ~# u, Xpg_basebackup: write-ahead log start point: 29/72000028 on timeline 1
$ d% f: D! R8 i; C; ~! gpg_basebackup: starting background WAL receiver
5 i7 I! K+ v/ L7 C8 q$ [pg_basebackup: created temporary replication slot "pg_basebackup_28129"
- s3 \' @& }7 J. `1 I) J140636462/140636462 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 29/7218EAF0
. m/ K3 Y9 j# b) }9 ?7 Wpg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
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
/ O$ f3 a/ v" S- ~) S1 r' ^4 K; e" ppg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 29/75000028 on timeline 1
' A: G. F; x# p. R, B2 Z/ \/ K# a0 spg_basebackup: starting background WAL receiver
% W" o$ R5 t. ^$ C. h: ]pg_basebackup: created temporary replication slot "pg_basebackup_29262"
# M  m4 t6 x+ l' m3 D140644606/140644606 kB (100%), 1/1 tablespace                                         
# D! [1 I5 V+ C. q2 vpg_basebackup: write-ahead log end point: 29/7522D570
pg_basebackup: waiting for background process to finish streaming ...
; `" D, L; d# e  I( X" i* i( E  c1 Ppg_basebackup: syncing data to disk ...
- H2 R7 D+ Z$ n8 z" i% o: L- Z1 Cpg_basebackup: renaming backup_manifest.tmp to backup_manifest
$ _/ {: d/ p1 q5 ^( I7 rpg_basebackup: base backup completed
6 c% k+ ~5 a; G8 W- y/ x$ }[postgres@aqzhxxh-pg-2 ~]$ pg_ctl -D /data/pgdata start
! c- y* @, E- m, F& Wwaiting 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
/ e7 e8 z1 s, v. v: O[postgres@aqzhxxh-pg-2 ~]$ psql
psql (13.3)
Type "help" for help.

/ l  f; {( v- ]0 G6 |6 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
$ V0 F3 u8 ?* v# r! ylush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--
---------+------------+-----------+-----------+------------+---------------+------------+------------
( R' C5 E/ L/ s& f5 ~(0 rows)
1 t( Q1 U+ M) H; x9 r" }7 Y% F; J
0 A# s/ D# E- a1 y6 a
: V$ T. f5 O+ k  d* `6 Y$ u[postgres@aqzhxxh-pg-1 ~]$ psql
& O; ^; F5 g( ^! N5 L+ Epsql (13.3)
% ~9 Q( c0 }  s& H$ l1 U7 ]Type "help" for help.
! [* o+ I$ \# _# [1 fpostgres=# select * from pg_stat_replication ;
* f/ l; d9 K9 V& A  pid  | usesysid |  usename  | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   
  {- ^6 o5 b+ |. A8 H|  sent_lsn   |  write_lsn  |  flush_lsn  | replay_lsn  |    write_lag    |    flush_lag    |   replay_lag    | sync_priority | sync_state |          reply_
time           
-------+----------+-----------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------
+ ~& a1 E: W; m+ h! S$ j5 k& Z+-------------+-------------+-------------+-------------+-----------------+-----------------+-----------------+---------------+------------+----------------
---------------
29581 |    16384 | replicate | walreceiver      | 10.101.102.87 |                 |        2193 | 2022-01-13 16:23:56.283942+08 |              | streaming
/ l, x& D4 {3 i# @; J+ d| 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 _7 ^% W) c, c4:17.862534+08
(1 row)
* q' o2 L3 V. v
3 F$ S3 ~9 c' ?. s" C5 a, ~' B
8 ~/ b, w- X5 a) q