   Copyright statement: This article is an original article by Shaon Puppet. Please indicate the original address for reprinting. Thank you very much. https://blog.csdn.net/wh211212/article/details/80666735

CentOS7 postgresql installation and use#

Installation configuration postgresql

# installation
[ root@vm-06~]# yum -y install postgresql-server
# initialization
[ root@vm-06~]# postgresql-setup initdb 
Initializing database ... OK
# Set up postgresql can be remotely connected to log in
[ root@vm-06~]# vi /var/lib/pgsql/data/postgresql.conf
# Uncomment line 59 and change it to:
listen_addresses ='*'
# Line 395, add
log_line_prefix ='%t %u %d '[root@vm-06~]# systemctl start postgresql  
[ root@vm-06~]# systemctl enable postgresql

Fire protection settings

[ root@vm-06~]# firewall-cmd --add-service=postgresql --permanent 
success
[ root@vm-06~]# firewall-cmd --reload 
success

Set the password of the PostgreSQL administrator user and add a user and add a test database.

[ root@vm-06~]# su - postgres 
- bash-4.2$ psql -c "alter user postgres with password 'password'"
ALTER ROLE
- bash-4.2$ createuser devops
- bash-4.2$ createdb testdb -O devops
- bash-4.2$ exit
logout

[ root@vm-06~]# su - devops
[ devops@vm-06~]$ 
[ devops@vm-06~]$ 
[ devops@vm-06~]$ 
[ devops@vm-06~]$ psql -l
         List of databases
 Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 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    | devops   | UTF8     | en_US.UTF-8| en_US.UTF-8|(4 rows)[devops@vm-06~]$ psql testdb
psql(9.2.23)
Type "help"for help.

testdb=> alter user devops with password 'password';
ALTER ROLE
testdb=> create table test(no int,name text );
CREATE TABLE
testdb=> insert into test(no,name)values(1,'devops');         
INSERT 01
testdb=> select *from test;
 no |  name  
- - - - +- - - - - - - - 1| devops(1 row)

testdb=> drop table test;
DROP TABLE
testdb=> \q
[ devops@vm-06~]$ dropdb testdb

PostgreSQL master-slave configuration##

hosts

192.168.1.124 master
192.168.1.123 slave01

Configure PostgreSQL replication settings. The configuration is a master-slave setting.

Configure the main server

[ root@vm-06~]# yum -y install postgresql-server
[ root@vm-06~]# postgresql-setup initdb 
Initializing database ... OK
[ root@vm-06~]# vi /var/lib/pgsql/data/postgresql.conf #Edit configuration file
# Line 59, uncomment and change to:
listen_addresses ='*'
# Line 165, uncomment and change to:
wal_level = hot_standby
# Line 168, uncomment and change to:
# on ⇒ sync
# remote_write ⇒ memory sync
# local ⇒ slave is asynchronous
# off ⇒ asynchronous
synchronous_commit = local
# On line 194, uncomment and change to:
archive_mode = on
# On line 196, uncomment and change to:
archive_command ='cp %p /var/lib/pgsql/archive/%f'
# On line 212, uncomment and change to:
max_wal_senders =2
# On line 214, uncomment and change to:
wal_keep_segments =10
# On line 221, uncomment and change to:
synchronous_standby_names ='slave01'
# 
[ root@vm-06~]# vi /var/lib/pgsql/data/pg_hba.conf
# File added at the end
# host replication [replication user][allowed IP addresses] password
 host    replication     replica          127.0.0.1/32            md5
host    replication     replica          192.168.1.1/32            md5

[ root@vm-06~]# systemctl start postgresql 
[ root@vm-06~]# systemctl enable postgresql 
# create a user for replication
[ root@vm-06~]# su - postgres
- bash-4.2$ createuser --replication -P replica 
Enter password fornewrole: #password
Enter it again: #password

Configure slave server

# installation
[ root@vm-05~]# yum -y install postgresql-server
[ root@vm-05~]# su - postgres
# get backup from Master Server
- bash-4.2$ pg_basebackup -h 192.168.1.124-U replica -D /var/lib/pgsql/data -P --xlog 
Password:     # "replica" user's password
- bash-4.2$ vi /var/lib/pgsql/data/postgresql.conf

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

restore_command ='scp 192.168.1.124:/var/lib/pgsql/archive/%f %p'

standby_mode = on

primary_conninfo ='host=192.168.1.124 port=5432 user=replica password=password application_name=slave01'-bash-4.2$ exit 
logout
[ root@vm-05~]# systemctl start postgresql 
[ root@vm-05~]# systemctl enable postgresql

After the configuration is complete, check the status of the server where the master is located:

[ root@vm-06~]# su - postgres        
Last login: Tue Jun 1215:35:12+082018 on pts/0-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)

1
1
1
1