Difference between revisions of "PostgreSQL"
From DHVLab
Wiki admin (talk | contribs) (Created page with "Category:OVirt_Engine_Cluster == Install dependencies ==") |
Wiki admin (talk | contribs) |
||
Line 1: | Line 1: | ||
[[Category:OVirt_Engine_Cluster]] | [[Category:OVirt_Engine_Cluster]] | ||
− | == | + | HA PostgreSQL stores the OVirt Engine configuration. It is configured with the following features:<br/> |
+ | # Fail-over of Master | ||
+ | # If Master breaks down, RA detects this fault and makes Master to stop, and Slave is promoted to new Master(promote). | ||
+ | # Switching between asynchronous and synchronous | ||
+ | # If Slave breaks down or LAN has some troubles, the transaction including Write operation will be stopped during the setting of synchronous replication. This means the stop of service. Therefore, RA switches dynamically from the synchronous to the asynchronous replication for prevented from stopping service. | ||
+ | # Automated discrimination of data old and new when initial starts | ||
+ | # When Pacemaker is started at the same time in the initial starts, RA compares the data of each node using "last xlog replay location" or "last receive replay location" to check which node has the newest data. The node which has the newest data will be Master. | ||
+ | |||
+ | == Node 1 == | ||
+ | <syntaxhighlight lang="bash"> | ||
+ | su – postgres | ||
+ | mkdir /var/lib/pgsql/pg_archive | ||
+ | cd /var/lib/pgsql/data | ||
+ | initdb | ||
+ | </syntaxhighlight> | ||
+ | /var/lib/pgsql/data/postgresql.conf | ||
+ | <syntaxhighlight lang="text"> | ||
+ | listen_addresses = '*' | ||
+ | max_connections = 200 | ||
+ | shared_buffers = 32MB | ||
+ | wal_level = hot_standby | ||
+ | synchronous_commit = on | ||
+ | archive_mode = on | ||
+ | archive_command = 'cp %p /var/lib/pgsql/pg_archive/%f' | ||
+ | max_wal_senders = 5 | ||
+ | wal_keep_segments = 32 | ||
+ | replication_timeout = 9000 | ||
+ | hot_standby = on | ||
+ | max_standby_archive_delay = -1 | ||
+ | max_standby_streaming_delay = -1 | ||
+ | wal_receiver_status_interval = 2 | ||
+ | hot_standby_feedback = on | ||
+ | logging_collector = on | ||
+ | log_filename = 'postgresql-%a.log' | ||
+ | log_truncate_on_rotation = on | ||
+ | log_rotation_age = 1d | ||
+ | log_rotation_size = 0 | ||
+ | log_timezone = 'Europe/Berlin' | ||
+ | datestyle = 'iso, mdy' | ||
+ | timezone = 'Europe/Berlin' | ||
+ | lc_messages = 'en_US.UTF-8' | ||
+ | lc_monetary = 'en_US.UTF-8' | ||
+ | lc_numeric = 'en_US.UTF-8' | ||
+ | lc_time = 'en_US.UTF-8' | ||
+ | default_text_search_config = 'pg_catalog.english' | ||
+ | restart_after_crash = off | ||
+ | include '/var/lib/pgsql/tmp/rep_mode.conf' | ||
+ | </syntaxhighlight> | ||
+ | Add permissions for PostgreSQL replication and database access over network by editing the<br/> | ||
+ | /var/lib/pgsql/pg_hba.conf | ||
+ | <syntaxhighlight lang="text"> | ||
+ | local all all trust | ||
+ | host all all 127.0.0.1/32 trust | ||
+ | host all all ::1/128 trust | ||
+ | host all all 127.0.0.1/32 trust | ||
+ | host all all OVIRTM/32 md5 | ||
+ | host all all CLUSTERLAN_CLUSTER_N1_IP/32 trust | ||
+ | host all all CLUSTERLAN_CLUSTER_N2_IP/32 trust | ||
+ | host all all CLUSTERLAN_CLUSTER_IP/32 trust | ||
+ | host replication all CLUSTERLAN_CLUSTER_N1_IP/32 trust | ||
+ | host replication all CLUSTERLAN_CLUSTER_N2_IP/32 trust | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | == Node 2 == | ||
+ | <syntaxhighlight lang=bash> | ||
+ | su - postgres | ||
+ | rm -rf /var/lib/pgsql/data/* | ||
+ | pg_basebackup -hCLUSTERLAN_CLUSTER_N1_IP -Upostgres -D/var/lib/pgsql/data -Xstream -P | ||
+ | mkdir /var/lib/pgsql/pg_archive | ||
+ | </syntaxhighlight> | ||
+ | /var/lib/pgsql/data/recover.conf | ||
+ | <syntaxhighlight lang="text"> | ||
+ | standby_mode = 'on' | ||
+ | primary_conninfo = 'host=CLUSTERLAN_CLUSTER_N2_IP port=5432 user=postgres | ||
+ | application_name=NODE2' | ||
+ | restore_command = 'cp /var/lib/pgsql/pg_archive/%f %p' | ||
+ | recovery_target_timeline = 'latest' | ||
+ | </syntaxhighlight> |
Revision as of 13:32, 8 September 2016
HA PostgreSQL stores the OVirt Engine configuration. It is configured with the following features:
- Fail-over of Master
- If Master breaks down, RA detects this fault and makes Master to stop, and Slave is promoted to new Master(promote).
- Switching between asynchronous and synchronous
- If Slave breaks down or LAN has some troubles, the transaction including Write operation will be stopped during the setting of synchronous replication. This means the stop of service. Therefore, RA switches dynamically from the synchronous to the asynchronous replication for prevented from stopping service.
- Automated discrimination of data old and new when initial starts
- When Pacemaker is started at the same time in the initial starts, RA compares the data of each node using "last xlog replay location" or "last receive replay location" to check which node has the newest data. The node which has the newest data will be Master.
Node 1
su – postgres
mkdir /var/lib/pgsql/pg_archive
cd /var/lib/pgsql/data
initdb
/var/lib/pgsql/data/postgresql.conf
listen_addresses = '*'
max_connections = 200
shared_buffers = 32MB
wal_level = hot_standby
synchronous_commit = on
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/pg_archive/%f'
max_wal_senders = 5
wal_keep_segments = 32
replication_timeout = 9000
hot_standby = on
max_standby_archive_delay = -1
max_standby_streaming_delay = -1
wal_receiver_status_interval = 2
hot_standby_feedback = on
logging_collector = on
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_timezone = 'Europe/Berlin'
datestyle = 'iso, mdy'
timezone = 'Europe/Berlin'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
restart_after_crash = off
include '/var/lib/pgsql/tmp/rep_mode.conf'
Add permissions for PostgreSQL replication and database access over network by editing the
/var/lib/pgsql/pg_hba.conf
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
host all all 127.0.0.1/32 trust
host all all OVIRTM/32 md5
host all all CLUSTERLAN_CLUSTER_N1_IP/32 trust
host all all CLUSTERLAN_CLUSTER_N2_IP/32 trust
host all all CLUSTERLAN_CLUSTER_IP/32 trust
host replication all CLUSTERLAN_CLUSTER_N1_IP/32 trust
host replication all CLUSTERLAN_CLUSTER_N2_IP/32 trust
Node 2
su - postgres
rm -rf /var/lib/pgsql/data/*
pg_basebackup -hCLUSTERLAN_CLUSTER_N1_IP -Upostgres -D/var/lib/pgsql/data -Xstream -P
mkdir /var/lib/pgsql/pg_archive
/var/lib/pgsql/data/recover.conf
standby_mode = 'on'
primary_conninfo = 'host=CLUSTERLAN_CLUSTER_N2_IP port=5432 user=postgres
application_name=NODE2'
restore_command = 'cp /var/lib/pgsql/pg_archive/%f %p'
recovery_target_timeline = 'latest'