CentOS 7 postgresqlのインストールと使用
10497 ワード
CentOS 7 postgresqlのインストールと使用
配置のpostgresqlをインストールします.防火設定 は、PostgreSQL管理者ユーザのパスワードを設定し、ユーザを追加して試験データベースを追加する. は、追加されたばかりのユーザIDでログインし、DataBaseをテスト動作とする. hosts は、プライマリサーバ を構成する.は、サーバ から構成されている.設定が完了しました.マスタがいるサーバーの状態を確認します.
配置のpostgresqlをインストールします.
#
[root@vm-06 ~]# yum -y install postgresql-server
#
[root@vm-06 ~]# postgresql-setup initdb
Initializing database ... OK
# postgresql
[root@vm-06 ~]# vi /var/lib/pgsql/data/postgresql.conf
# 59 , :
listen_addresses = '*'
# 395 ,
log_line_prefix = '%t %u %d '
[root@vm-06 ~]# systemctl start postgresql
[root@vm-06 ~]# systemctl enable postgresql
[root@vm-06 ~]# firewall-cmd --add-service=postgresql --permanent
success
[root@vm-06 ~]# firewall-cmd --reload
success
[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 0 1
testdb=> select * from test;
no | name
----+--------
1 | devops
(1 row)
testdb=> drop table test;
DROP TABLE
testdb=> \q
[devops@vm-06 ~]$ dropdb testdb
PostgreSQLマスタ配置192.168.1.124 master
192.168.1.123 slave01
PostgreSQLコピー設定を設定します.設定は主従設定です.[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 #
# 59 , :
listen_addresses = '*'
# 165 , :
wal_level = hot_standby
# 168 , :
# on ⇒ sync
# remote_write ⇒ memory sync
# local ⇒ slave is asynchronous
# off ⇒ asynchronous
synchronous_commit = local
# 194 , :
archive_mode = on
# 196 , :
archive_command = 'cp %p /var/lib/pgsql/archive/%f'
# 212 , :
max_wal_senders = 2
# 214 , :
wal_keep_segments = 10
# 221 , :
synchronous_standby_names = 'slave01'
#
[root@vm-06 ~]# vi /var/lib/pgsql/data/pg_hba.conf
#
# 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 for new role: #password
Enter it again: #password
#
[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
[root@vm-06 ~]# su - postgres
Last login: Tue Jun 12 15:35:12 +08 2018 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