Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Use pg_basebackup build PostgreSQL streaming replication environment     - 5 fast Node.js application performance tips (Programming)

- C ++ Learning Notes: references (Programming)

- Summary Linux operating system some tips to prevent attacks (Linux)

- JIRA 3.6.2 Upgrade from older version to the new version 6.0.8 (Linux)

- Locale files under Ubuntu (Linux)

- Installation and configuration under Linux SVN server - Backup - Recovery - Scheduled Tasks (Server)

- JBPM6 Installation and Configuration Tutorial (Linux)

- Expand an existing RAID arrays and remove the failed disk in a RAID (Linux)

- Without Visual Studio .NET Windows application development (Programming)

- Ubuntu 14.04 build Android 5.1 development environment and compiler (Linux)

- Android Fragment everything you need to know (Programming)

- Linux Monitoring Command (Linux)

- Automated Password Generator: Linux under a special password generator (Linux)

- Bash Automated Customization Linux belongs to its own CentOS system (Linux)

- RHEL 6.5 x86_64 CentOS yum configuration source (Linux)

- VMware virtual machine Ubuntu install arm-linux-gcc cross-compiler environment (Linux)

- Struts2 configure a static resource files without Struts processing (regular match) (Programming)

- CentOS / Linux SWAP partitions added (Linux)

- ACL permissions Linux command (Linux)

- Linux shell scripts bubble sort (Programming)

 
         
  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:      
 
- How x2go set up Remote Desktop on Linux (Linux)
- Linux Telnet access Windows 7 configuration procedure (Linux)
- Three easy open source firewall on the Linux platform (Linux)
- Management Linux Container with Docker in Ubuntu (Linux)
- FastDFS installation and deployment (Server)
- Ubuntu 14.10 Server configuration wireless Internet access (Server)
- Axel install plug under CentOS 5/6 acceleration yum downloads (Linux)
- Dynamic programming Android (Programming)
- Linux check disk parameters trapping lack amendments (Linux)
- Ubuntu 14.04 Nvidia graphics driver installation and settings (Linux)
- The official release method to upgrade to Ubuntu 15.04 (Linux)
- Boot automatically remove Linux operating history records (Linux)
- How to add a new hard disk without restarting the CentOS 7 / RHEL 7 virtual machine (Linux)
- Linux Log File Browser --logrotate (Linux)
- Linux kernel modules related to the management Comments (Linux)
- Linux Command study manual - GPG command (Linux)
- Build a Linux development environment under STC89C52RC (Linux)
- iOS in the event delivery and the responder chain (Programming)
- Nginx Performance Tuning Guidelines (Server)
- Shell Script: Bulk add users, and set the random character password (Programming)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.