API tools faq
paste
Advertisement
phamthanhnhan14

install postgres replication

phamthanhnhan14
Apr 5th, 2017
529
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.42 KB | None | 0 0
raw download clone embed print report
  1. Master: 192.168.1.139
  2. Slave: 192.168.1.140
  3. Install:
  4. yum localinstall http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm
  5.  
  6. yum install postgresql94-server postgresql94-contrib postgresql94-devel repmgr94
  7. yum clean expire-cache
  8.  
  9.  
  10. All server:
  11. [root@postgre1 ~]# service postgresql-9.4 initdb
  12. Initializing database: [ OK ]
  13. [root@postgre1 ~]# service postgresql-9.4 start
  14. Starting postgresql-9.4 service: [ OK ]
  15. [root@postgre1 ~]# chkconfig postgresql-9.4 on
  16. passwd postgres
  17. New password:
  18. BAD PASSWORD: it is too simplistic/systematic
  19. BAD PASSWORD: is too simple
  20. Retype new password:
  21. passwd: all authentication tokens updated successfully.
  22.  
  23. -bash-4.1$ psql
  24. psql (9.4.2)
  25. Type "help" for help.
  26.  
  27. postgres=# \password
  28. Enter new password:
  29. Enter it again:
  30.  
  31. postgres=# CREATE ROLE repmgr_usr LOGIN SUPERUSER;
  32. CREATE ROLE
  33. postgres=# CREATE DATABASE repmgr_db OWNER repmgr_usr;
  34. CREATE DATABASE
  35.  
  36. postgres=# \q
  37. -bash-4.1$ pg_restore -U postgres -d repmgr_db /tmp/dvdrental.tar
  38.  
  39. [ -f /etc/profile ] && source /etc/profile
  40. PGDATA=/var/lib/pgsql/9.4/data
  41. export PGDATA
  42. # If you want to customize your settings,
  43. # Use the file below. This is not overridden
  44. # by the RPMS.
  45. [ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile
  46. PG_BINDIR=/usr/pgsql-9.4/bin
  47. export PG_BINDIR
  48. export PATH=$PATH:$PG_BINDIR
  49.  
  50.  
  51.  
  52. -bash-4.1$ echo $PGDATA
  53. /var/lib/pgsql/9.4/data
  54.  
  55. -bash-4.1$ cd $PGDATA
  56. -bash-4.1$ ls
  57. base pg_ident.conf pg_replslot pg_subtrans postgresql.auto.conf
  58. global pg_log pg_serial pg_tblspc postgresql.conf
  59. pg_clog pg_logical pg_snapshots pg_twophase postmaster.opts
  60. pg_dynshmem pg_multixact pg_stat PG_VERSION postmaster.pid
  61. pg_hba.conf pg_notify pg_stat_tmp pg_xlog
  62.  
  63. Master config:
  64. -bash-4.1$ vi pg_hba.conf
  65. # TYPE DATABASE USER ADDRESS METHOD
  66.  
  67. # "local" is for Unix domain socket connections only
  68. local all all peer
  69. # IPv4 local connections:
  70. host all all 192.168.1.0/24 trust
  71. # IPv6 local connections:
  72. host all all ::1/128 ident
  73. # Allow replication connections from localhost, by a user with the
  74. # replication privilege.
  75. #local replication postgres peer
  76. host replication repmgr_usr 192.168.1.0/24 trust
  77. host repmgr_db repmgr_usr 192.168.1.0/24 trust
  78. #host replication postgres ::1/128 ident
  79.  
  80. -bash-4.1$ vi postgresql.conf
  81. listen_addresses = 'localhost,192.168.1.133'
  82. wal_level = hot_standby
  83. archive_mode = on
  84. archive_command = 'cd .'
  85. max_wal_senders = 3
  86. hot_standby = on
  87.  
  88.  
  89. chown -hR postgres /etc/repmgr/
  90.  
  91. [root@postgre1 ~]# service postgresql-9.4 restart
  92. Stopping postgresql-9.4 service: [ OK ]
  93. Starting postgresql-9.4 service: [ OK ]
  94.  
  95. Slave config:
  96. [root@postgre2 ~]# service postgresql-9.4 stop
  97. Stopping postgresql-9.4 service: [ OK ]
  98.  
  99. [root@postgre2 ~]# su - postgres
  100. -bash-4.1$ echo $PGDATA
  101. /var/lib/pgsql/9.4/data
  102. -bash-4.1$ cd $PGDATA
  103. -bash-4.1$ ls
  104. base pg_ident.conf pg_replslot pg_subtrans postgresql.auto.conf
  105. global pg_log pg_serial pg_tblspc postgresql.conf
  106. pg_clog pg_logical pg_snapshots pg_twophase postmaster.opts
  107. pg_dynshmem pg_multixact pg_stat PG_VERSION
  108. pg_hba.conf pg_notify pg_stat_tmp pg_xlog
  109.  
  110. -bash-4.1$ vi pg_hba.conf
  111. # TYPE DATABASE USER ADDRESS METHOD
  112.  
  113. # "local" is for Unix domain socket connections only
  114. local all all peer
  115. # IPv4 local connections:
  116. host all all 192.168.1.0/24 md5
  117. # IPv6 local connections:
  118. host all all ::1/128 ident
  119. # Allow replication connections from localhost, by a user with the
  120. # replication privilege.
  121. #local replication postgres peer
  122. host replication rep 192.168.1.133 md5
  123. #host replication postgres ::1/128 ident
  124.  
  125. -bash-4.1$ vi /var/lib/pgsql/9.4/data/postgresql.conf
  126. listen_addresses = 'localhost,192.168.1.134'
  127. wal_level = hot_standby
  128. archive_mode = on
  129. archive_command = 'cd .'
  130. max_wal_senders = 3
  131. hot_standby = on
  132.  
  133. Replication :
  134. Tao ssh key
  135. -bash-4.1$ ssh-keygen -t rsa
  136. Generating public/private rsa key pair.
  137. Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa):
  138. Created directory '/var/lib/pgsql/.ssh'.
  139. Enter passphrase (empty for no passphrase):
  140. Enter same passphrase again:
  141. Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa.
  142. Your public key has been saved in /var/lib/pgsql/.ssh/id_rsa.pub.
  143. The key fingerprint is:
  144. 62:10:61:bc:e5:7a:30:da:19:89:90:03:5c:2c:e6:d7 postgres@postgre1.test
  145. The key's randomart image is:
  146. +--[ RSA 2048]----+
  147. |+.oo+. |
  148. |++ o... |
  149. |oo..o= |
  150. | ...*E. |
  151. | .o *o S |
  152. | . +... |
  153. | . |
  154. | |
  155. | |
  156. +-----------------+
  157. -bash-4.1$ ssh-copy-id postgres@192.168.1.134
  158. The authenticity of host '192.168.1.134 (192.168.1.134)' can't be established.
  159. RSA key fingerprint is b9:f4:80:5b:d8:47:19:94:3e:90:03:f2:d6:b3:e3:47.
  160. Are you sure you want to continue connecting (yes/no)? yes
  161. Warning: Permanently added '192.168.1.134' (RSA) to the list of known hosts.
  162. postgres@192.168.1.134's password:
  163. Now try logging into the machine, with "ssh 'postgres@192.168.1.134'", and check in:
  164.  
  165. .ssh/authorized_keys
  166.  
  167. to make sure we haven't added extra keys that you weren't expecting.
  168.  
  169. Master:
  170. [root@postgre1 ~]# su - postgres
  171. -bash-4.1$ psql -c "select pg_start_backup('initial_backup');"
  172. pg_start_backup
  173. -----------------
  174. 0/2000028
  175. (1 row)
  176.  
  177. rsync -cva --inplace --exclude=*pg_xlog* $PGDATA/ 192.168.1.134:$PGDATA/
  178. ...
  179. sent 135726 bytes received 4444 bytes 13349.52 bytes/sec
  180. total size is 21178953 speedup is 151.09
  181.  
  182.  
  183. -bash-4.1$ psql -c "select pg_stop_backup();"
  184. NOTICE: pg_stop_backup complete, all required WAL segments have been archived
  185. pg_stop_backup
  186. ----------------
  187. 0/2000128
  188. (1 row)
  189.  
  190. Slave:
  191. -bash-4.1$ vi /var/lib/pgsql/9.4/data/recovery.conf
  192.  
  193. standby_mode = 'on'
  194. primary_conninfo = 'host=192.168.1.133 port=5432 user=rep password=rep'
  195. trigger_file = '/tmp/postgresql.trigger.5432'
  196.  
  197. [root@postgre2 ~]# touch /tmp/postgresql.trigger.5432
  198. [root@postgre2 ~]# chmod -R 777 /tmp/postgresql.trigger.5432
  199.  
  200. [root@postgre2 ~]# service postgresql-9.4 start
  201. Starting postgresql-9.4 service: [ OK ]
  202.  
  203. vi /var/lib/pgsql/9.4/data/pg_log/postgresql-Fri.log
  204. < 2015-05-22 16:59:04.809 ICT >LOG: entering standby mode
  205. < 2015-05-22 16:59:04.814 ICT >LOG: started streaming WAL from primary at 0/4000000 on timeline 1
  206. < 2015-05-22 16:59:04.861 ICT >LOG: redo starts at 0/4000028
  207. < 2015-05-22 16:59:04.863 ICT >LOG: consistent recovery state reached at 0/4000128
  208. < 2015-05-22 16:59:04.863 ICT >LOG: database system is ready to accept read only connections
  209.  
  210. Check:
  211. Master:
  212. [root@postgre1 ~]# ps ax |grep sender
  213. 1622 ? Ss 0:00 postgres: wal sender process rep 192.168.1.134(52249) streaming 0/5000138
  214. 1659 pts/0 S+ 0:00 grep sender
  215. Slave:
  216. [root@postgre2 ~]# ps ax |grep receiver
  217. 2028 ? Ss 0:00 postgres: wal receiver process streaming 0/5000138
  218. 2064 pts/0 S+ 0:00 grep receiver
  219. Tạo mới db trên Master:
  220. -bash-4.1$ psql
  221. psql (9.4.1)
  222. Type "help" for help.
  223.  
  224.  
  225. ALTER USER Postgres WITH PASSWORD '<newpassword>';
  226.  
  227. psql (9.4.1)
  228. Type "help" for help.
  229.  
  230. postgres=# \l
  231. List of databases
  232. Name | Owner | Encoding | Collate | Ctype | Access privileg
  233. es
  234. -----------+----------+----------+-------------+-------------+------------------
  235. -----
  236. dvdrental | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
  237. postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
  238. template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
  239. +
  240. | | | | | postgres=CTc/post
  241. gres
  242. template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
  243. +
  244. | | | | | postgres=CTc/post
  245. gres
  246. testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
  247. (5 rows)
  248.  
  249. Slave list:
  250. [root@postgre2 ~]# su - postgres
  251. -bash-4.1$ psql
  252. psql (9.4.1)
  253. Type "help" for help.
  254.  
  255. postgres=# \l
  256. List of databases
  257. Name | Owner | Encoding | Collate | Ctype | Access privileg
  258. es
  259. -----------+----------+----------+-------------+-------------+------------------
  260. -----
  261. dvdrental | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
  262. postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
  263. template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
  264. +
  265. | | | | | postgres=CTc/post
  266. gres
  267. template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
  268. +
  269. | | | | | postgres=CTc/post
  270. gres
  271. testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
  272. (5 rows)
  273. Master check:
  274. -bash-4.1$ psql
  275. psql (9.4.1)
  276. Type "help" for help.
  277.  
  278. postgres=# \c testdb
  279. You are now connected to database "testdb" as user "postgres".
  280. testdb=# CREATE TABLE rep_test (test varchar(40));
  281. CREATE TABLE
  282. testdb=# INSERT INTO rep_test VALUES ('data one');
  283. INSERT 0 1
  284. testdb=# INSERT INTO rep_test VALUES ('some more words');
  285. INSERT 0 1
  286. testdb=# INSERT INTO rep_test VALUES ('lalala');
  287. INSERT 0 1
  288. testdb=# INSERT INTO rep_test VALUES ('hello there');
  289. INSERT 0 1
  290. testdb=# INSERT INTO rep_test VALUES ('blahblah');
  291. INSERT 0 1
  292. testdb=# \d
  293. List of relations
  294. Schema | Name | Type | Owner
  295. --------+----------+-------+----------
  296. public | rep_test | table | postgres
  297. (1 row)
  298.  
  299. Slave:
  300. -bash-4.1$ psql
  301. psql (9.4.1)
  302. Type "help" for help.
  303.  
  304. postgres=# \c testdb
  305. You are now connected to database "testdb" as user "postgres".
  306. testdb=# select * from rep_test;
  307. test
  308. -----------------
  309. data one
  310. some more words
  311. lalala
  312. hello there
  313. blahblah
  314. (5 rows)
  315.  
  316.  
  317. postgres=# select * from pg_stat_replication;
  318. pid | usesysid | usename | application_name | client_addr | client_hostname
  319. | client_port | backend_start | backend_xmin | state | sent_
  320. location | write_location | flush_location | replay_location | sync_priority | s
  321. ync_state
  322. ------+----------+---------+------------------+---------------+-----------------
  323. +-------------+-------------------------------+--------------+-----------+------
  324. ---------+----------------+----------------+-----------------+---------------+--
  325. ----------
  326. 1905 | 16384 | rep | walreceiver | 192.168.1.134 |
  327. | 52250 | 2015-05-22 17:21:48.802372+07 | | streaming | 0/601
  328. 31E8 | 0/60131E8 | 0/60131E8 | 0/60131E8 | 0 | a
  329. sync
  330. (1 row)
  331. Master = false, slave = true
  332. postgres=# select pg_is_in_recovery();
  333. pg_is_in_recovery
  334. -------------------
  335. f
  336. (1 row)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!