| |
Contact PostgreSQL high availability, where the first stream replication experiments.
surroundings:
OS:
[Ha @ node0 ~] $ uname -a
Linux node0 2.6.32-358.el6.x86_64 # 1 SMP Fri Feb 22 00:31:26 UTC 2013 x86_64 x86_64 x86_64 GNU / Linux
Memory: 1G
CPU: 1 Nuclear
database:
postgres = # select version ();
version
-------------------------------------------------- -------------
PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120
313 (Red Hat 4.4.7-16), 64-bit
(1 row)
postgres = #
.bash_profile
[Ha @ node0 ~] $ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [-f ~ / .bashrc]; then
. ~ / .bashrc
fi
# User specific environment and startup programs
export PGHOME = / home / ha / pgdb /
export PGDATA = / home / ha / pgdb / data /
export LD_LIBRARY_PATH = $ LD_LIBRARY_PATH: $ PGHOME / lib /
export PATH = $ PGHOME / bin: $ PATH: $ HOME / bin
Build process:
1. Main Library Configuration
a.postgresql.conf:
listen_addresses = '*'
wal_level = hot_standby
archive_mode = off
max_wal_senders = 3
wal_keep_segments = 16
To note here is the parameter archive_mode, I once found parameters archive_mode and archive_command without setting may be copied complete flow during the experiment, so I have conducted research and found that this francs has been explained here reference francs conclusion:
"When setting up the stream replication environment, you do not have to set parameters on archive_mode, set this parameter to a lot of information on introducing the stream replication environment to build, may be out of Turn WAL archiving safer because, as in the main library downtime and when you take a long time, from the library can still read the WAL archive directory, thus ensuring data is not lost; on the other hand, if the primary database is set larger wal_keep_segments, also can not open archive_mode, because the main library preserves WAL enough, thereby greatly reducing the conditions required by the WAL library is covered by the main library and archives need to pick WAL so this regard, he said setting parameters and structures archive_mode stream copy and not directly related tips.: for busy library, built in the stream is copied from the library, main library is recommended to set a larger wal_keep_segments parameters. "
b.pg_hba.conf
host all all 192.168.238.0/24 md5
host replication rep 192.168.238.0/24 trust
Here it noted that there are other on-line replication set to md5, and the establishment of .pgpass files, this method is feasible.
c. New User rep
create user rep replication encrypted password 'rep';
2. pg_basebackup conducted from backup
[Ha @ localhost pgdb] $ pg_basebackup -D $ PGDATA -F p -X stream -v -P -h 192.168.238.130 -U rep
transaction log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
20945/20945 kB (100%), 1/1 tablespace
transaction log end point: 0 / 20000F0
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
[Ha @ localhost pgdb] $ ll
total 20
drwxrwxr-x. 2 ha ha 4096 Dec 26 00:54 bin
drwx ------. 18 ha ha 4096 Dec 26 12:02 data
drwxrwxr-x. 4 ha ha 4096 Dec 26 00:54 include
drwxrwxr-x. 4 ha ha 4096 Dec 26 00:54 lib
drwxrwxr-x. 4 ha ha 4096 Dec 26 00:54 share
[Ha @ localhost pgdb] $ cd data /
[Ha @ localhost data] $ ls
backup_label pg_hba.conf pg_replslot pg_subtrans postgresql.auto.conf
base pg_ident.conf pg_serial pg_tblspc postgresql.conf
global pg_logical pg_snapshots pg_twophase
pg_clog pg_multixact pg_stat PG_VERSION
pg_dynshmem pg_notify pg_stat_tmp pg_xlog
It should look at the directory described: data directory can be automatically created by pg_basebackup, but the other table spaces, you need to manually create and empowerment, to note here is that permission to 700.
3. From the Library Configuration
a.postgresql.conf
hot_standby = on
b.recovery.conf
standby_mode = on
primary_conninfo = 'host = 192.168.238.130 port = 5432 user = rep'
trigger_file = '/home/ha/pgdb/pg.trigger.file'
4. Before starting the standby database, the main library processes
[Ha @ localhost pgdb] $ ps -ef | grep post
root 2124 1 0 04:47? 00:00:00 / usr / libexec / postfix / master
postfix 2147 2124 0 04:47? 00:00:00 qmgr -l -t fifo -u
postfix 10385 2124 0 11:27? 00:00:00 pickup -l -t fifo -u
ha 10691 1 0 12:33 pts / 3 00:00:00 / home / ha / pgdb / bin / postgres_ora
ha 10693 10691 0 12:33 00:00:00 postgres:? checkpointer process
ha 10694 10691 0 12:33 00:00:00 postgres:? writer process
ha 10695 10691 0 12:33 00:00:00 postgres:? wal writer process
ha 10696 10691 0 12:33 00:00:00 postgres:? autovacuum launcher process
ha 10697 10691 0 12:33 00:00:00 postgres:? stats collector process
ha 10717 4087 0 12:37 pts / 3 00:00:00 grep post
5. After starting the standby database
a. main library processes
[Ha @ localhost pgdb] $ ps -ef | grep post
root 2124 1 0 04:47? 00:00:00 / usr / libexec / postfix / master
postfix 2147 2124 0 04:47? 00:00:00 qmgr -l -t fifo -u
postfix 10385 2124 0 11:27? 00:00:00 pickup -l -t fifo -u
ha 10691 1 0 12:33 pts / 3 00:00:00 / home / ha / pgdb / bin / postgres_ora
ha 10693 10691 0 12:33 00:00:00 postgres:? checkpointer process
ha 10694 10691 0 12:33 00:00:00 postgres:? writer process
ha 10695 10691 0 12:33 00:00:00 postgres:? wal writer process
ha 10696 10691 0 12:33 00:00:00 postgres:? autovacuum launcher process
ha 10697 10691 0 12:33 00:00:00 postgres:? stats collector process
ha 10718 10691 0 12:37 00:00:00 postgres:? wal sender process rep 192.168.238.131 (59195) streaming 0/3000060
ha 10720 4087 0 12:37 pts / 3 00:00:00 grep post
b. Preparation Process Library
[Ha @ localhost data] $ ps -ef | grep post
root 2086 1 0 04:48? 00:00:00 / usr / libexec / postfix / master
postfix 2108 2086 0 04:48? 00:00:00 qmgr -l -t fifo -u
postfix 9657 2086 0 11:27? 00:00:00 pickup -l -t fifo -u
ha 9782 1 0 12:36 pts / 2 00:00:00 / home / ha / pgdb / bin / postgres
ha 9783 9782 0 12:36 00:00:00 postgres:? startup process recovering 000000010000000000000003
ha 9784 9782 0 12:36 00:00:00 postgres:? checkpointer process
ha 9785 9782 0 12:36 00:00:00 postgres:? writer process
ha 9786 9782 0 12:36 00:00:00 postgres:? stats collector process
ha 9787 9782 0 12:36 00:00:00 postgres:? wal receiver process streaming 0/3000060
ha 9792 3744 0 12:37 pts / 2 00:00:00 grep post
6. The experimental results
a. main library
[Ha @ localhost pgdb] $ psql postgres
psql (9.4.5)
Type "help" for help.
postgres = # create table test (id int);
CREATE TABLE
postgres = # insert into test values (1), (2);
INSERT 0 2
postgres = # select * from test;
id
----
1
2
(2 rows)
postgres = #
b. Preparation of the library
[Ha @ localhost data] $ psql postgres
psql (9.4.5)
Type "help" for help.
postgres = # select * from test;
id
----
1
2
(2 rows) |
|
|
|