Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Use pg_basebackup build PostgreSQL streaming replication environment     - CentOS install pip (Linux)

- SSH does not require a password to log on to a Linux server (Server)

- Installation and deployment of Hadoop 2.7.1 on Ubuntu 14.04 LTS (Server)

- Oracle 11g tracking and monitoring system-level triggers to drop misuse (Database)

- Ubuntu Linux use ufw or iptables firewall configuration (Linux)

- SVN hook code set to synchronize Web directory (Server)

- List Leaves (Programming)

- Use FFmpeg processing high quality GIF (Programming)

- MongoDB uses aggregate, group, match mysql achieve in having (count (1)> 1) features (Database)

- Linux installed xdotool simulate keystrokes and mouse movements (Linux)

- Java MVC CRUD examples (Programming)

- Install MATE desktop environment adjustment tools Mate Tweak 3.3.6 (Linux)

- Linux firewall iptables beginner tutorial (Linux)

- GRUB and Linux system repair (Linux)

- Using BBED repair ORA-01190 error (Database)

- pscp use Detailed Windows and Linux each file transfer tool (Linux)

- Two alert log ORA Errors (Database)

- OpenGL ES 3.0 vertex buffer (Programming)

- To create and manage virtual machines on Ubuntu Redhat (Linux)

- MongoDB collection data migration to MySQL database (Database)

 
         
  Use pg_basebackup build PostgreSQL streaming replication environment
     
  Add Date : 2018-11-21      
         
         
         
  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)
     
         
         
         
  More:      
 
- Bootstrap 3.3.5 release download, Web front-end UI framework (Linux)
- iOS development -Launch Image and Launchscreen (Programming)
- Ubuntu system cp: omitting directory problem (Linux)
- php for Linux the MySQL extension module installation and configuration (Database)
- How to protect your eyes automatically adjust the screen brightness on Linux (Linux)
- TeamCity continuous integration in the Linux installation (Linux)
- Linux Network Programming --TCP and UDP datagram type Explanation (Programming)
- How do I use Linux development environment (Linux)
- Red Hat Enterprise Linux configuration VNC multi-user access methods (Linux)
- FileZilla 3.10.1.1 install on Ubuntu 14.10 (Linux)
- Linux user groups, file permissions Detailed (Linux)
- Servlet 3.0 interfaces of AsyncListener (Programming)
- Linux, see picture not resolve the problem (Linux)
- CentOS terminal display Chinese (Linux)
- Embedded Linux to solve the problem in the time zone (Linux)
- Installation GitLab appears ruby_block supervise_redis_sleep action run (Linux)
- Use HttpClient remote interface testing (Programming)
- Ubuntu 12.04 64bit Install Source Insight 3.5 and create desktop icons (Linux)
- Help you make Git Bisect (Linux)
- Vi / Vim prompt solutions do not have permission to save time (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.