星空网 > 软件开发 > 数据库

PostgreSQL的HA解决方案

 一.部署说明

1.1 实施环境

本文档实验环境如下:

PGSQL主机: 192.168.1.45

PGSQL备机: 192.168.1.50

 

软件和系统版本

Pgsql 版本: pgsql 9.2.4

Linux 版本: Redhat 5.8

 

1.2 文档说明

  本文档详细说明了PG的双机扩展,使pgsql数据库具备双机热备流复制功能。注意,双机热备中存在主从关系,这样才能保证数据的一直性,主机操作都可以,但从机只能用作查询。所以热备的意义在于分担主机的查询压力,备份主机数据,当主机出问题后,从机稍加修改就可以变为主机。

  为了试验减少不必要的麻烦,本系列试验中,各个主机之间的通信,都是设置为无密码访问,但对于实际生产,需要根据实际需要,配置密码,增强集群的安全性。

二.配置步骤

  1.1 (主从一样)。编辑data/pg_hba.conf 访问控制文件。    

    在配置pgsql之前,首先要设置两台服务器使得postgres用户可以无密码相互访问。 配置过程一定要分清主从机。因为实验的两台虚拟机所在网段都是192.168.1.0/24.所以,新增如下:

# TYPE DATABASE    USER      ADDRESS         METHOD# "local" is for Unix domain socket connections onlylocal  all       all                   trust# IPv4 local connections:host  all       all       127.0.0.1/32      trusthost  all       all       192.168.1.0/24     trust# IPv6 local connections:host  all       all       ::1/128         trust# Allow replication connections from localhost, by a user with the# replication privilege.#local  replication   postgres                trust#host  replication   postgres    127.0.0.1/32      trusthost  replication   postgres    192.168.1.0/24      trust#host  replication   postgres    ::1/128         trust

  1.2 (主从一样)。编辑data/postgresql.conf 数据库配置文件。

    主从机的数据库postgresql.conf配置相同,主要是方便接下来的将主机的data文件直接备份还原到从机,并且也方便将来主从切换。请对照默认参数设置,设置相应参数,一些参数的设置,如

max_connections = 100       #最大连接数的设置,当超过最大链接时,数据库拒绝访问。

shared_buffers = 1280MB      #共享内存,主要是和服务器的硬件和实际的项目需求相关,理论上在硬件资源允许的情况下,此值越大越好。

wal_level = hot_standby       #开启主从热备模式

fsync = on              #异步数据同步

full_page_writes = on

checkpoint_segments = 32      #关于脏数据写回的
archive_mode = on                    #打开归档模式

archive_command ='cp %p /opt/bjca3/pgsql/data/archive/%f ' #将归档文件保存在data/archive目录下
max_wal_senders = 2         #根据从机的个数设置,实验中只有一个从机,所以只设置为1

wal_keep_segments = 250       

hot_standby = on             #在配置中,并不是每个配置都起作用的,数据库会根据当前的主从机的属性,自动判断哪些是主机的配置,哪些是从机的配置,配置文档中也有说明。
max_standby_archive_delay = 300s

max_standby_streaming_delay = 300s

wal_receiver_status_interval = 10s

hot_standby_feedback = on 

log_line_prefix = '[%t] '

    详细的配置及含义请看下边的配置文件。

PostgreSQL的HA解决方案PostgreSQL的HA解决方案
# -----------------------------# PostgreSQL configuration file# -----------------------------## This file consists of lines of the form:##  name = value## (The "=" is optional.) Whitespace may be used. Comments are introduced with# "#" anywhere on a line. The complete list of parameter names and allowed# values can be found in the PostgreSQL documentation.## The commented-out settings shown in this file represent the default values.# Re-commenting a setting is NOT sufficient to revert it to the default value;# you need to reload the server.## This file is read on server startup and when the server receives a SIGHUP# signal. If you edit the file on a running system, you have to SIGHUP the# server for the changes to take effect, or use "pg_ctl reload". Some# parameters, which are marked below, require a server shutdown and restart to# take effect.## Any parameter can also be given as a command-line option to the server, e.g.,# "postgres -c log_connections=on". Some parameters can be changed at run time# with the "SET" SQL command.## Memory units: kB = kilobytes    Time units: ms = milliseconds#        MB = megabytes           s  = seconds#        GB = gigabytes           min = minutes#                          h  = hours#                          d  = days#------------------------------------------------------------------------------# FILE LOCATIONS#------------------------------------------------------------------------------# The default values of these variables are driven from the -D command-line# option or PGDATA environment variable, represented here as ConfigDir.#data_directory = 'ConfigDir'    # use data in another directory          # (change requires restart)#hba_file = 'ConfigDir/pg_hba.conf'  # host-based authentication file          # (change requires restart)#ident_file = 'ConfigDir/pg_ident.conf'  # ident configuration file          # (change requires restart)# If external_pid_file is not explicitly set, no extra PID file is written.#external_pid_file = ''      # write an extra PID file          # (change requires restart)#------------------------------------------------------------------------------# CONNECTIONS AND AUTHENTICATION#------------------------------------------------------------------------------# - Connection Settings -#listen_addresses = 'localhost'    # what IP address(es) to listen on;listen_addresses = '*'      # what IP address(es) to listen on;          # comma-separated list of addresses;          # defaults to 'localhost'; use '*' for all          # (change requires restart)#port = 5432        # (change requires restart)port = 5432        # (change requires restart)max_connections = 100      # (change requires restart)# Note: Increasing max_connections costs ~400 bytes of shared memory per# connection slot, plus lock space (see max_locks_per_transaction).#superuser_reserved_connections = 3  # (change requires restart)#unix_socket_directory = ''    # (change requires restart)#unix_socket_group = ''      # (change requires restart)#unix_socket_permissions = 0777    # begin with 0 to use octal notation          # (change requires restart)#bonjour = off        # advertise server via Bonjour          # (change requires restart)#bonjour_name = ''      # defaults to the computer name          # (change requires restart)# - Security and Authentication -#authentication_timeout = 1min    # 1s-600s#ssl = off        # (change requires restart)#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH'  # allowed SSL ciphers          # (change requires restart)#ssl_renegotiation_limit = 512MB  # amount of data between renegotiations#ssl_cert_file = 'server.crt'    # (change requires restart)#ssl_key_file = 'server.key'    # (change requires restart)#ssl_ca_file = ''      # (change requires restart)#ssl_crl_file = ''      # (change requires restart)#password_encryption = on#db_user_namespace = off# Kerberos and GSSAPI#krb_server_keyfile = ''#krb_srvname = 'postgres'    # (Kerberos only)#krb_caseins_users = off# - TCP Keepalives -# see "man 7 tcp" for details#tcp_keepalives_idle = 0    # TCP_KEEPIDLE, in seconds;          # 0 selects the system default#tcp_keepalives_interval = 0    # TCP_KEEPINTVL, in seconds;          # 0 selects the system default#tcp_keepalives_count = 0    # TCP_KEEPCNT;          # 0 selects the system default#------------------------------------------------------------------------------# RESOURCE USAGE (except WAL)#------------------------------------------------------------------------------# - Memory -#shared_buffers = 32MB      # min 128kBshared_buffers = 256MB      # min 128kB          # (change requires restart)#temp_buffers = 8MB      # min 800kB#max_prepared_transactions = 0    # zero disables the feature          # (change requires restart)# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory# per transaction slot, plus lock space (see max_locks_per_transaction).# It is not advisable to set max_prepared_transactions nonzero unless you# actively intend to use prepared transactions.#work_mem = 1MB        # min 64kB#maintenance_work_mem = 16MB    # min 1MB#max_stack_depth = 2MB      # min 100kB# - Disk -#temp_file_limit = -1      # limits per-session temp file space          # in kB, or -1 for no limit# - Kernel Resource Usage -#max_files_per_process = 1000    # min 25          # (change requires restart)#shared_preload_libraries = ''    # (change requires restart)# - Cost-Based Vacuum Delay -#vacuum_cost_delay = 0ms    # 0-100 milliseconds#vacuum_cost_page_hit = 1    # 0-10000 credits#vacuum_cost_page_miss = 10    # 0-10000 credits#vacuum_cost_page_dirty = 20    # 0-10000 credits#vacuum_cost_limit = 200    # 1-10000 credits# - Background Writer -#bgwriter_delay = 200ms      # 10-10000ms between rounds#bgwriter_lru_maxpages = 100    # 0-1000 max buffers written/round#bgwriter_lru_multiplier = 2.0    # 0-10.0 multipler on buffers scanned/round# - Asynchronous Behavior -#effective_io_concurrency = 1    # 1-1000; 0 disables prefetching#------------------------------------------------------------------------------# WRITE AHEAD LOG#------------------------------------------------------------------------------# - Settings -#wal_level = minimal      # minimal, archive, or hot_standbywal_level = hot_standby      # minimal, archive, or hot_standby          # (change requires restart)#fsync = on        # turns forced synchronization on or offfsync = on        # turns forced synchronization on or off#synchronous_commit = on    # synchronization level;          # off, local, remote_write, or on#wal_sync_method = fsync    # the default is the first option          # supported by the operating system:          #  open_datasync          #  fdatasync (default on Linux)          #  fsync          #  fsync_writethrough          #  open_sync#full_page_writes = on      # recover from partial page writesfull_page_writes = on      # recover from partial page writes#wal_buffers = -1      # min 32kB, -1 sets based on shared_buffers          # (change requires restart)#wal_writer_delay = 200ms    # 1-10000 milliseconds#commit_delay = 0      # range 0-100000, in microseconds#commit_siblings = 5      # range 1-1000# - Checkpoints -#checkpoint_segments = 3    # in logfile segments, min 1, 16MB each#checkpoint_timeout = 5min    # range 30s-1h#checkpoint_completion_target = 0.5  # checkpoint target duration, 0.0 - 1.0#checkpoint_warning = 30s    # 0 disables# - Archiving -#archive_mode = off    # allows archiving to be donearchive_mode = on    # allows archiving to be done        # (change requires restart)#archive_command = ''    # command to use to archive a logfile segmentarchive_command = 'cp %p /usr/local/pgsql/data/archive/%f'    # command to use to archive a logfile segment        # placeholders: %p = path of file to archive        #        %f = file name only        # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'#archive_timeout = 0    # force a logfile segment switch after this        # number of seconds; 0 disables#------------------------------------------------------------------------------# REPLICATION#------------------------------------------------------------------------------# - Sending Server(s) -# Set these on the master and on any standby that will send replication data.#max_wal_senders = 0    # max number of walsender processesmax_wal_senders = 1    # max number of walsender processes        # (change requires restart)#wal_keep_segments = 0    # in logfile segments, 16MB each; 0 disableswal_keep_segments = 250    # in logfile segments, 16MB each; 0 disables#replication_timeout = 60s  # in milliseconds; 0 disables# - Master Server -# These settings are ignored on a standby server.#synchronous_standby_names = ''  # standby servers that provide sync repsynchronous_standby_names = '*'  # standby servers that provide sync rep        # comma-separated list of application_name        # from standby(s); '*' = all#vacuum_defer_cleanup_age = 0  # number of xacts by which cleanup is delayed# - Standby Servers -# These settings are ignored on a master server.#hot_standby = off      # "on" allows queries during recoveryhot_standby = on      # "on" allows queries during recovery          # (change requires restart)#max_standby_archive_delay = 30s  # max delay before canceling queriesmax_standby_archive_delay = 300s  # max delay before canceling queries          # when reading WAL from archive;          # -1 allows indefinite delay#max_standby_streaming_delay = 30s  # max delay before canceling queriesmax_standby_streaming_delay = 300s  # max delay before canceling queries          # when reading streaming WAL;          # -1 allows indefinite delay#wal_receiver_status_interval = 10s  # send replies at least this oftenwal_receiver_status_interval = 10s  # send replies at least this often          # 0 disables#hot_standby_feedback = off    # send info from standby to preventhot_standby_feedback = on    # send info from standby to prevent          # query conflicts#------------------------------------------------------------------------------# QUERY TUNING#------------------------------------------------------------------------------# - Planner Method Configuration -#enable_bitmapscan = on#enable_hashagg = on#enable_hashjoin = on#enable_indexscan = on#enable_indexonlyscan = on#enable_material = on#enable_mergejoin = on#enable_nestloop = on#enable_seqscan = on#enable_sort = on#enable_tidscan = on# - Planner Cost Constants -#seq_page_cost = 1.0      # measured on an arbitrary scale#random_page_cost = 4.0      # same scale as above#cpu_tuple_cost = 0.01      # same scale as above#cpu_index_tuple_cost = 0.005    # same scale as above#cpu_operator_cost = 0.0025    # same scale as above#effective_cache_size = 128MB# - Genetic Query Optimizer -#geqo = on#geqo_threshold = 12#geqo_effort = 5      # range 1-10#geqo_pool_size = 0      # selects default based on effort#geqo_generations = 0      # selects default based on effort#geqo_selection_bias = 2.0    # range 1.5-2.0#geqo_seed = 0.0      # range 0.0-1.0# - Other Planner Options -#default_statistics_target = 100  # range 1-10000#constraint_exclusion = partition  # on, off, or partition#cursor_tuple_fraction = 0.1    # range 0.0-1.0#from_collapse_limit = 8#join_collapse_limit = 8    # 1 disables collapsing of explicit          # JOIN clauses#------------------------------------------------------------------------------# ERROR REPORTING AND LOGGING#------------------------------------------------------------------------------# - Where to Log -#log_destination = 'stderr'    # Valid values are combinations oflog_destination = 'stderr'    # Valid values are combinations of          # stderr, csvlog, syslog, and eventlog,          # depending on platform. csvlog          # requires logging_collector to be on.# This is used when logging to stderr:#logging_collector = off    # Enable capturing of stderr and csvloglogging_collector = on      # Enable capturing of stderr and csvlog          # into log files. Required to be on for          # csvlogs.          # (change requires restart)# These are only used if logging_collector is on:#log_directory = 'pg_log'    # directory where log files are written,log_directory = 'pg_log'    # directory where log files are written,          # can be absolute or relative to PGDATA#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'  # log file name pattern,log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'  # log file name pattern,          # can include strftime() escapes#log_file_mode = 0600      # creation mode for log files,          # begin with 0 to use octal notation#log_truncate_on_rotation = off    # If on, an existing log file with the          # same name as the new log file will be          # truncated rather than appended to.          # But such truncation only occurs on          # time-driven rotation, not on restarts          # or size-driven rotation. Default is          # off, meaning append to existing files          # in all cases.#log_rotation_age = 1d      # Automatic rotation of logfiles will          # happen after that time. 0 disables.#log_rotation_size = 10MB    # Automatic rotation of logfiles will          # happen after that much log output.          # 0 disables.# These are relevant when logging to syslog:#syslog_facility = 'LOCAL0'#syslog_ident = 'postgres'# This is only relevant when logging to eventlog (win32):#event_source = 'PostgreSQL'# - When to Log -#client_min_messages = notice    # values in order of decreasing detail:          #  debug5          #  debug4          #  debug3          #  debug2          #  debug1          #  log          #  notice          #  warning          #  error#log_min_messages = warning    # values in order of decreasing detail:          #  debug5          #  debug4          #  debug3          #  debug2          #  debug1          #  info          #  notice          #  warning          #  error          #  log          #  fatal          #  panic#log_min_error_statement = error  # values in order of decreasing detail:           #  debug5          #  debug4          #  debug3          #  debug2          #  debug1           #  info          #  notice          #  warning          #  error          #  log          #  fatal          #  panic (effectively off)#log_min_duration_statement = -1  # -1 is disabled, 0 logs all statements          # and their durations, > 0 logs only          # statements running at least this number          # of milliseconds# - What to Log -#debug_print_parse = off#debug_print_rewritten = off#debug_print_plan = off#debug_pretty_print = on#log_checkpoints = off#log_connections = off#log_disconnections = off#log_duration = off#log_error_verbosity = default    # terse, default, or verbose messages#log_hostname = off#log_line_prefix = '[%t]'    # special values:          #  %a = application name          #  %u = user name          #  %d = database name          #  %r = remote host and port          #  %h = remote host          #  %p = process ID          #  %t = timestamp without milliseconds          #  %m = timestamp with milliseconds          #  %i = command tag          #  %e = SQL state          #  %c = session ID          #  %l = session line number          #  %s = session start timestamp          #  %v = virtual transaction ID          #  %x = transaction ID (0 if none)          #  %q = stop here in non-session          #    processes          #  %% = '%'          # e.g. '<%u%%%d> '#log_lock_waits = off      # log lock waits >= deadlock_timeout#log_statement = 'none'      # none, ddl, mod, all#log_temp_files = -1      # log temporary files equal or larger          # than the specified size in kilobytes;          # -1 disables, 0 logs all temp fileslog_timezone = 'PRC'#------------------------------------------------------------------------------# RUNTIME STATISTICS#------------------------------------------------------------------------------# - Query/Index Statistics Collector -#track_activities = on#track_counts = on#track_io_timing = off#track_functions = none      # none, pl, all#track_activity_query_size = 1024   # (change requires restart)#update_process_title = on#stats_temp_directory = 'pg_stat_tmp'# - Statistics Monitoring -#log_parser_stats = off#log_planner_stats = off#log_executor_stats = off#log_statement_stats = off#------------------------------------------------------------------------------# AUTOVACUUM PARAMETERS#------------------------------------------------------------------------------#autovacuum = on      # Enable autovacuum subprocess? 'on'          # requires track_counts to also be on.#log_autovacuum_min_duration = -1  # -1 disables, 0 logs all actions and          # their durations, > 0 logs only          # actions running at least this number          # of milliseconds.#autovacuum_max_workers = 3    # max number of autovacuum subprocesses          # (change requires restart)#autovacuum_naptime = 1min    # time between autovacuum runs#autovacuum_vacuum_threshold = 50  # min number of row updates before          # vacuum#autovacuum_analyze_threshold = 50  # min number of row updates before          # analyze#autovacuum_vacuum_scale_factor = 0.2  # fraction of table size before vacuum#autovacuum_analyze_scale_factor = 0.1  # fraction of table size before analyze#autovacuum_freeze_max_age = 200000000  # maximum XID age before forced vacuum          # (change requires restart)#autovacuum_vacuum_cost_delay = 20ms  # default vacuum cost delay for          # autovacuum, in milliseconds;          # -1 means use vacuum_cost_delay#autovacuum_vacuum_cost_limit = -1  # default vacuum cost limit for          # autovacuum, -1 means use          # vacuum_cost_limit#------------------------------------------------------------------------------# CLIENT CONNECTION DEFAULTS#------------------------------------------------------------------------------# - Statement Behavior -#search_path = '"$user",public'    # schema names#default_tablespace = ''    # a tablespace name, '' uses the default#temp_tablespaces = ''      # a list of tablespace names, '' uses          # only default tablespace#check_function_bodies = on#default_transaction_isolation = 'read committed'#default_transaction_read_only = off#default_transaction_deferrable = off#session_replication_role = 'origin'#statement_timeout = 0      # in milliseconds, 0 is disabled#vacuum_freeze_min_age = 50000000#vacuum_freeze_table_age = 150000000#bytea_output = 'hex'      # hex, escape#= 'base64'#= 'content'# - Locale and Formatting -datestyle = 'iso, mdy'#intervalstyle = 'postgres'timezone = 'PRC'#timezone_abbreviations = 'Default'   # Select the set of available time zone          # abbreviations. Currently, there are          #  Default          #  Australia          #  India          # You can create your own file in          # share/timezonesets/.#extra_float_digits = 0      # min -15, max 3#client_encoding = sql_ascii    # actually, defaults to database          # encoding# These settings are initialized by initdb, but they can be changed.lc_messages = 'C'      # locale for system error message          # stringslc_monetary = 'C'      # locale for monetary formattinglc_numeric = 'C'      # locale for number formattinglc_time = 'C'        # locale for time formatting# default configuration for text searchdefault_text_search_config = 'pg_catalog.english'# - Other Defaults -#dynamic_library_path = '$libdir'#local_preload_libraries = ''#------------------------------------------------------------------------------# LOCK MANAGEMENT#------------------------------------------------------------------------------#deadlock_timeout = 1s#max_locks_per_transaction = 64    # min 10          # (change requires restart)# Note: Each lock table slot uses ~270 bytes of shared memory, and there are# max_locks_per_transaction * (max_connections + max_prepared_transactions)# lock table slots.#max_pred_locks_per_transaction = 64  # min 10          # (change requires restart)#------------------------------------------------------------------------------# VERSION/PLATFORM COMPATIBILITY#------------------------------------------------------------------------------# - Previous PostgreSQL Versions -#array_nulls = on#backslash_quote = safe_encoding  # on, off, or safe_encoding#default_with_oids = off#escape_string_warning = on#lo_compat_privileges = off#quote_all_identifiers = off#sql_inheritance = on#standard_conforming_strings = on#synchronize_seqscans = on# - Other Platforms and Clients -#transform_null_equals = off#------------------------------------------------------------------------------# ERROR HANDLING#------------------------------------------------------------------------------#exit_on_error = off      # terminate session on any error?#restart_after_crash = on    # reinitialize after backend crash?#------------------------------------------------------------------------------# CUSTOMIZED OPTIONS#------------------------------------------------------------------------------# Add settings for extensions here

View Code

  1.3 此时主从数据库的配置是相同的,所以可以将主库直接备份,然后还原至从库。其实对于从库,配置本身很简单,只是因为考虑到接下来的主从切换麻烦,所以讲主从配置成一样的。

此时可以使用PG自带的pg_basebackup工具,对主机进行远程基础备份。

  在从机中执行:pg_basebackup -F p -D $PGDATA -x -v -h db1 -w

  将主机的数据库远程备份并恢复到从机中。其中db1是我添加在系统hosts里的,对于不想添加的,可以直接使用Ip地址。其实hosts中的文件就是给出不同IP的别名,方便使用。其他参数请--help.

  1.4 备份还原到从机后,需要添加另外一个配置文件:recovery.conf,此时才把主机与从机区分开来。在PGHOME目录下的share文件夹中有实例文件,将之拷贝过来,修改即可。

    执行cp /usr/local/pgsql/share/recovery.conf.sample /usr/local/pgsql/data/recovery.conf. 

    主要配置:

standby_mode = 'on'          #开启从机模式

trigger_file = '/usr/local/pgsql/data/pg.trigger'#注意路径是可以自定义的,但是当主机失效,从机变为主机是,为产生该文件,但当重新恢复为从机自己写的shell脚本要删除该文件,注意权限问题。

primary_conninfo = 'host=db1'

  1.5 此时可以启动从机的数据库了。此时会报如下错误:

FATAL:  data directory "/usr/local/pgsql/data" has group or world access
DETAIL:  Permissions should be u=rwx (0700).

  所以,此时要修改data的权限为700.修改之后启动,就没问题了。此时查看启动日志:data/pg_log中的日志如下:

LOG: creating missing WAL directory "pg_xlog/archive_status"LOG: entering standby modeLOG: redo starts at 0/4000020LOG: consistent recovery state reached at 0/40000C8LOG: database system is ready to accept read only connectionsLOG: streaming replication successfully connected to primary

  说明,此时从机已经开启了从机模式,只读模式等待连接,并且已经连接到主机的数据库。

 

  1.6验证数据同步。

    ①在主库中创建表,查看从库中是否有同样的数据表即可。

    ②当然也可以主机中在psql中执行:select * from pg_stat_replication;

pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | state | sent_location | write_locat
ion | flush_location | replay_location | sync_priority | sync_state
-------+----------+----------+------------------+--------------+-----------------+-------------+-------------------------------+-----------+---------------+------------
----+----------------+-----------------+---------------+------------
22877 | 10 | postgres | walreceiver | 192.168.1.50 | | 47967 | 2015-09-18 16:09:13.711784+08 | streaming | 0/5016334 | 0/5016334
| 0/5016334 | 0/5016334 | 1 | sync

    ③在主机中运行命令:top -c -u postgres

12707 postgres 15 0 4612 1368 1108 S 0.0 0.1 0:00.09 bash
13190 postgres 18 0 4992 1256 1072 T 0.0 0.1 0:00.02 vi pg_hba.conf
17668 postgres 15 0 281m 16m 15m S 0.0 1.6 0:00.03 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
17669 postgres 18 0 9124 660 356 S 0.0 0.1 0:00.00 postgres: logger process
17671 postgres 16 0 281m 3164 2808 S 0.0 0.3 0:00.34 postgres: checkpointer process
17672 postgres 15 0 281m 2544 2208 S 0.0 0.2 0:00.01 postgres: writer process
17673 postgres 15 0 281m 840 504 S 0.0 0.1 0:00.00 postgres: wal writer process
17674 postgres 18 0 282m 1680 984 S 0.0 0.2 0:00.00 postgres: autovacuum launcher process
17675 postgres 18 0 9168 688 356 S 0.0 0.1 0:00.00 postgres: archiver process last was 000000010000000000000004
17676 postgres 16 0 9168 796 424 S 0.0 0.1 0:00.03 postgres: stats collector process
22877 postgres 18 0 282m 2176 1360 S 0.0 0.2 0:00.00 postgres: wal sender process postgres 192.168.1.50(47967) streaming 0/5016334
25439 postgres 15 0 2432 1068 816 R 0.0 0.1 0:00.19 top -c -u postgres

    ④在从机中运行命令:top -c -u postgres

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12446 postgres 15 0 4612 1384 1120 S 0.0 0.1 0:00.08 bash
19711 postgres 15 0 4612 1376 1116 S 0.0 0.1 0:00.02 bash
20040 postgres 15 0 4612 1360 1124 S 0.0 0.1 0:00.01 bash
20152 postgres 15 0 4612 1388 1124 S 0.0 0.1 0:00.05 bash
21809 postgres 18 0 281m 16m 15m S 0.0 1.6 0:00.05 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
21810 postgres 22 0 9120 652 352 S 0.0 0.1 0:00.00 postgres: logger process
21811 postgres 15 0 282m 1784 1420 S 0.0 0.2 0:00.00 postgres: startup process recovering 000000010000000000
21812 postgres 16 0 281m 2988 2640 S 0.0 0.3 0:00.00 postgres: checkpointer process
21813 postgres 15 0 281m 2532 2200 S 0.0 0.2 0:00.00 postgres: writer process
21814 postgres 16 0 9164 780 408 S 0.0 0.1 0:00.00 postgres: stats collector process
21815 postgres 15 0 282m 1768 1244 S 0.0 0.2 0:00.28 postgres: wal receiver process streaming 0/50163B8
24569 postgres 15 0 2432 1072 816 R 0.0 0.1 0:00.14 top -c -u postgres

  都可以证明已经正常启动。

 




原标题:PostgreSQL的HA解决方案

关键词:sql

sql
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。
相关文章
我的浏览记录
最新相关资讯
海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流