PostgreSQL : Replication Settings

PostgreSQL : Replication Settings 2015/07/26

	Configure PostgreSQL Replication settings. This configuration is Master-Slave settings.
[1]	Install PostgreSQL Server, refer to here.
[2]	Configure Master Server. Login with PostgreSQL admin user.

[root@dlp ~]#  su - postgres # create a user for replication -bash-4.2$  createuser --replication -P replica  Enter password for new role: Enter it again: -bash-4.2$  vi /var/lib/pgsql/data/postgresql.conf # line 59: uncomment and change listen_addresses = ' * ' # line 165: uncomment and change wal_level =  hot_standby # line 168: uncomment and change # on ⇒ sync # remote_write ⇒ memory sync # local ⇒ slave is asynchronous # off ⇒ asynchronous synchronous_commit =  local # line 194: uncomment and change (enable archive_mode) archive_mode =  on # line 196: uncomment and change (command to get archives) archive_command = ' cp %p /var/lib/pgsql/archive/%f ' # line 212: uncomment and change (slave servers + 1) max_wal_senders =  2 # line 214: uncomment and change wal_keep_segments =  10 # line 221: uncomment and change (any name you like) synchronous_standby_names = ' slave01 ' -bash-4.2$  vi /var/lib/pgsql/data/pg_hba.conf # add to the end # host replication [replication user] [allowed IP addresses] password host replication replica 127.0.0.1/32 password host replication replica 10.0.0.30/32 password host replication replica 10.0.0.51/32 password -bash-4.2$  pg_ctl restart  waiting for server to shut down.... done server stopped server starting

[3]	Configure Slave Server. Login with PostgreSQL admin user.

[root@node01 ~]#  su - postgres # get backup from Master Server -bash-4.2$  pg_basebackup -h 10.0.0.30 -U replica -D /var/lib/pgsql/data -P --xlog  Password: -bash-4.2$  vi/var/lib/pgsql/data/postgresql.conf # line 230: uncomment and change hot_standby =  on -bash-4.2$  cp/usr/share/pgsql/recovery.conf.sample /var/lib/pgsql/data/recovery.conf  -bash-4.2$  vi /var/lib/pgsql/data/recovery.conf # line 44: uncomment and change (command to get archives) restore_command = ' scp 10.0.0.30:/var/lib/pgsql/archive/%f %p ' # line 108: uncomment and change standby_mode =  on # line 115: uncomment and change (connection info to Master Server) primary_conninfo = ' host=10.0.0.30 port=5432 user=replica password=password application_name=slave01 ' -bash-4.2$  pg_ctl restart  waiting for server to shut down.... done server stopped server starting

[4]	It's OK all if result of the command below is like follows. Make sure the setting works normally to create databases on Master Server.

-bash-4.2$  psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"  application_name | state | sync_priority | sync_state ------------------+-----------+---------------+------------ slave01 | streaming | 1 | sync (1 row)

PostgreSQL : Install phpPgAdmin

PostgreSQL : Install phpPgAdmin 2015/07/23

	Install phpPgAdmin to operate PostgreSQL on web browser from Client hosts.
[1]	Install and start Apache httpd, refer to here.
[2]	Install PHP, refer to here.
[3]	Install phpPgAdmin.

# install from EPEL [root@dlp ~]#  yum --enablerepo=epel -y install phpPgAdmin php-pgsql [root@dlp ~]#  vi /etc/phpPgAdmin/config.inc.php # line 18: add $conf['servers'][0]['host'] = ' localhost '; # line 93: change to false if you allow to login with priviledged user like postgres, root $conf['extra_login_security'] =  false ; # line 99: change $conf['owned_only'] =  true ; [root@dlp ~]#  vi /var/lib/pgsql/data/pg_hba.conf # line 82: change like follows & add access permission host all all 127.0.0.1/32 md5 host all all 10.0.0.0/24 md5 host all all ::1/128 md5 [root@dlp ~]#  vi /etc/httpd/conf.d/phpPgAdmin.conf # line 11: add access permission Require local Require ip 10.0.0.0/24 [root@dlp ~]#  systemctl restart postgresql httpd

[4]	Access to the "http://(hostname or IP address)/phpPgAdmin/" and click "PostgreSQL" on the left menu.

[5]	Autenticate a user and password which is in PostgreSQL.

[6]	Just logined. You can operate PostgreSQL on here.

PostgreSQL : Install

PostgreSQL : Install 2015/07/23

	Install PostgreSQL to configure database server.
[1]	Install and start PostgreSQL.

[root@dlp ~]#  yum -y install postgresql-server [root@dlp ~]#  postgresql-setup initdb  Initializing database ... OK [root@dlp ~]#  vi/var/lib/pgsql/data/postgresql.conf # line 59: uncomment and change if allow accesses from remote hosts listen_addresses = ' * ' # line 395: uncomment and change if change log format # the exmaple below is [Date User DB ***] format log_line_prefix = ' %t %u %d  ' [root@dlp ~]#  systemctl start postgresql  [root@dlp ~]#  systemctl enable postgresql

[2]	Set PostgreSQL admin user's password and add a user and also add a test database.

# set password [root@dlp ~]#  su - postgres  -bash-4.2$  psql -c "alter user postgres with password 'password'"  ALTER ROLE # add DB user "cent" -bash-4.2$  createuser cent # add test database (owner is just the user above) -bash-4.2$  createdb testdb -O cent

[3]	Login as a user just added above and operate DataBase as test.

# show Databases [cent@dlp ~]$  psql -l  List of databases Name | Owner | Encoding | Collate | Ctype | Access privileg es -----------+----------+----------+-------------+-------------+------------------ ----- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres testdb | cent | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (4 rows) # connect to test DB [cent@dlp ~]$  psql testdb  psql (9.2.13) Type "help" for help. # set password testdb=#  alter user cent with password 'password';  ALTER ROLE # create test table testdb=#  create table test ( no int,name text );  CREATE TABLE # insert test data testdb=#  insert into test (no,name) values (1,'cent');  INSERT 0 1 # show tables testdb=#  select * from test; no | name ----+------- 1 | cent (1 row) # delete test table testdb=#  drop table test;  DROP TABLE # quit testdb=#  \q # delete test DB [cent@dlp ~]$  dropdb testdb