CentOS 7 postgresqlのインストールと使用


CentOS 7 postgresqlのインストールと使用
配置の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
  • は、PostgreSQL管理者ユーザのパスワードを設定し、ユーザを追加して試験データベースを追加する.
  • [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
    
  • は、追加されたばかりのユーザIDでログインし、DataBaseをテスト動作とする.
  • [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マスタ配置
  • hosts
  • 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