<commit_before># PostgreSQL + PGPool

## Env
- Master (172.17.3.9) accepts connections from the client with read and write permissions
- Slave (172.17.3.12) the standby server runs a copy of the data from the master server with read-only permission.

![](https://user-images.githubusercontent.com/11453229/33374601-311f6c12-d54a-11e7-99d2-6983a6ee4e7f.png)


## Install PostgreSQL Master/Slave
- Install
```
sudo apt update
sudo apt install postgresql-9.5-pgpool2
```
- Check
```
dpkg -L postgresql-9.5-pgpool2
dpkg -L postgresql-9.5
```
- Config
```
sudo su - postgres
psql
# \password postgres
Enter new password:
Enter it again:

# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432"
```

## Master Server
- /etc/postgresql/9.5/main/postgresql.conf
```
listen_addresses = '*'
#----------------------------
# WRITE AHEAD LOG
#---------------------------
wal_level = hot_standby
synchronous_commit = local
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/9.5/main/archive/%f'

#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'pgslave001'
```
-  archive directory 생성
```
sudo mkdir -p /var/lib/postgresql/9.5/main/archive/
sudo chmod 700 /var/lib/postgresql/9.5/main/archive/
sudo chown -R postgres:postgres /var/lib/postgresql/9.5/main/archive/
```
- /etc/postgresql/9.5/main/pg_hba.conf
```
# localhost
host    replication     replica        127.0.0.1/32            md5
# master ip address
host    replication     replica        172.17.3.9/32           md5
# slave ip address
host    replication     replica        172.17.3.12/32          md5
```

- Resetart
```
sudo systemctl restart postgresql
```

- Check replica user
```
$ sudo su - postgres
$ psql
# CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD '<PASSWORD>@';
# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 replica   | Replication                                                | {}<commit_msg>Update postgresql-ha1.md<commit_after># PostgreSQL + PGPool

## Env
- Master (172.17.3.9) accepts connections from the client with read and write permissions
- Slave (172.17.3.12) the standby server runs a copy of the data from the master server with read-only permission.

![](https://user-images.githubusercontent.com/11453229/33374601-311f6c12-d54a-11e7-99d2-6983a6ee4e7f.png)


## Install PostgreSQL Master/Slave
- Install
```
sudo apt update
sudo apt install postgresql-9.5-pgpool2
```
- Check
```
dpkg -L postgresql-9.5-pgpool2
dpkg -L postgresql-9.5
```
- Config
```
sudo su - postgres
psql
# \password postgres
Enter new password:
Enter it again:

# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432"
```

## Master Server
- /etc/postgresql/9.5/main/postgresql.conf
```
listen_addresses = '*'
#----------------------------
# WRITE AHEAD LOG
#---------------------------
wal_level = hot_standby
synchronous_commit = local
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/9.5/main/archive/%f'

#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'pgslave001'
```
-  archive directory 생성
```
sudo mkdir -p /var/lib/postgresql/9.5/main/archive/
sudo chmod 700 /var/lib/postgresql/9.5/main/archive/
sudo chown -R postgres:postgres /var/lib/postgresql/9.5/main/archive/
```
- /etc/postgresql/9.5/main/pg_hba.conf
```
host    all             postgres        172.17.3.0/24           md5
# localhost
host    replication     replica        127.0.0.1/32            md5
# master ip address
host    replication     replica        172.17.3.9/32           md5
# slave ip address
host    replication     replica        172.17.3.12/32          md5
```

- Resetart
```
sudo systemctl restart postgresql
```

- Check replica user
```
$ sudo su - postgres
$ psql
# CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD '<PASSWORD>@';
# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 replica   | Replication                                                | {}
{'fraction_non_alphanumeric': 0.22012707522033204, 'fraction_numerical': 0.049395367903258866, 'mean_word_length': 1.7338935574229692, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 11, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5182700', 'n_tokens_mistral': 1637, 'n_tokens_neox': 1476, 'n_words': 378}