Replikasi basis data PostgreSQL master-slave¶

Latar belakang dan informasi umum¶

Karena kebutuhan untuk mengolah data mentah dari Odoo dan ditampilkan secara cantik melalui Dashboard BI - Knowage, maka diperlukan cerminan atau replikasi basis data.

Tujuan¶

Install PostgreSQL pada VM atau VPS.
Konfigurasi akses remote PostgreSQL.
Konfigurasi firewall pada porta.
Terhubung ke PostgreSQL melalui klien remote.
Membuat pengguna untuk aktivitas replikasi.
Membuat direktori penyimpanan berkas arsip.
Menyuntin berkas konfigurasi: pg_hba.conf and postgresql.conf.

Persiapan¶

Sebelum melakukan proses instalasi, pastikan sudah tersedia 2 VM, berikut adalah informasi umum yang akan digunakan:

IP psql master: 10.1.1.21
IP psql slave: 10.1.1.22
Versi PostgreSQL: 9.4
Distro Linux: Debian Stretch

Instalasi¶

Untuk menggunakan PostgreSQL perlu diinstal terlebih dan membuat pengguna terlebih dahulu.

ssh ke VM

IP_MASTER_SERVER=""
ssh -v $IP_MASTER_SERVER

Menambahkan repositori

# https://www.postgresql.org/download/linux/debian/
DISTRO_VERSION=""
PSQL_VERSION=""
echo "deb http://apt.postgresql.org/pub/repos/apt/ ${DISTRO_VERSION}-pgdg main" \
    > /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
    apt-key add -

Pembaruan paket dan instalasi PostgreSQL versi spesifik

apt-get update
apt-get install postgresql-$PSQL_VERSION \
    postgresql-client-$PSQL_VERSION \
    postgresql-contrib-$PSQL_VERSION

Pengaturan dan pengelolaan menggunakan `psql`¶

PostgreSQL setelah selesai proses instalasi secara bawaan akan memiliki pengguna postgres. Pengguna tersebut tidak memiliki kata sandi, maka diperlukan untuk membuat sandi, dan sangat tidak disarankan untuk menggunakan nama pengguna tersebut selama proses pengelolaan hari-hari

Melalui terminal ssh jalankan:
```
sudo -u postgres psql postgres
```
Perintah di atas akan menjalankan psql sebagai pengguna postgres, yang ditentukan dengan parameter -u alih-alih sebagai pengguna root, dan mengkases basis data postgres. Tampilan berikutnya akan seperti ini: postgres=#
Tetapkan kata sandi pengguna postgres“
```
\password postgres
```
Isikan kata sandi dan ulangin untuk verifikasi, dang pastikan kombinasi kata sandi yang digunakan cukup kuat dan mudah diingat
Aktifkan ekstensi adminpack.”
```
CREATE EXTENSION adminpack;
```
adminpack adalah instrumen server tambahan yang akan membantu dalam pengelolaan server
Ketikkan \q untuk keluar dari konsol psql

Akses koneksi klien remote¶

Agar dapat mengakses server PostgreSQL melalui mesin aplikasi yang terpisah, perlu dilakukan penyesuaian konfigurasi. Secara bawaan Postgres hanya mengizinkan koneksi secara local atau tidak mengizinkan koneksi remote.
Pengaturan koneksi remote PostgreSQL

Sunting pg_hba.conf

Melalui terminal jalankan:

PSQL_VERSION=""
sudo nano /etc/postgresql/$PSQL_VERSION/main/pg_hba.conf

Gunakan ip4.me untuk mengetahui IP publik yang akan digunakan untuk mengelola server.

Pada baris paling bawah tambahkan

host    all    all    [PUB_CLIENT_IPV4_ADDRESS]/32    md5

CIDR sufiks /32 digunakan sebagai alamat tunggal, tidak pada suatu subnet tertentu.
Simpan dan keluar dengan menggunakan
Ctrl+X lalu Y+Enter

Sunting postgresql.conf

Sunting berkas postgresql.conf

PSQL_VERSION=""
sudo nano /etc/postgresql/$PSQL_VERSION/main/postgresql.conf

Cari baris #listen_addresses = 'localhost'.
Hapus karakter # untuk membuang komentar pada baris.
Ganti localhost dengan * atau PSQL_SERVER_IP ini bisa publik atau private jika penyedia layanan infrastuktur memilikinya. Pada bagian CONNECTIONS AND AUTHENTICATION -> Connection Settings
```
listen_addresses = '*'
# atau alamat server spesifik
# PSQL_SERVER_IP=""
listen_addresses = '$PSQL_SERVER_IP'
```
Karakter '*' memungkinkan IP server kenali secara publik atau private, ini adalah salah satu pengaturan umum, jika ingin lebih spesifik disarankan untuk menggunkan alamat IP private.
Simpan dan keluar dari editor.
Ctrl+X -> ++y+return+
Muat ulang layanan PostgreSQL
```
sudo service postgresql restart
```

Membuka porta firewall¶

Variabel

PUBLIC_INF="eth1"
PUBLIC_CLIENT_IP_ADDRESS=""
APP_IP_NETWORK=""
PSQL_USER=""
PSQL_PORT="5432"
PSQL_PRIVATE_IP_ADDRESS=""

Firewall rule

sudo ufw allow in on $PUBLIC_INF from $PUBLIC_CLIENT_IP_ADDRESS to any port $POSTGRES_PORT

Membuat tabel bukutamu¶

Pada server primer, buatlah tabel serderhana untuk nantinya digunakan sebagai wadah uji coba serderhana proses replikasi.

In the SSH terminal for the primary server

Jalankan root shell:
```
sudo -s
```
Dengan menggunakan pengguna postgres, jalankan psql untuk mengakses basis data postgres:
```
sudo -u postgres psql postgres
```

Pada prom psql jalankan kueri berikut untuk membuat tabel:

CREATE TABLE guestbook (
    visitor_email text, vistor_id serial, date timestamp, message text
    );

Masukkan entri pada tabel tersebut:

INSERT INTO guestbook (
    visitor_email, date, message
    )
VALUES (
    '[email protected]', current_date, 'Entri awal.'
    );

Akan tampil konfirmasi entri berhasil ditambahkan: INSERT 0 1.

Enter \q to exit PSQL.
Tidak perlu keluar dari shell, karena sepanjanga panduan ini kita akan menggunakan pengguna root

Konfigurasi peladen primer¶

Untuk konfigurasi peladen primer diperlukan:

Membuat pengguna PostgreSQL untuk aktivitas replikasi.
Membuat direktori penampungan berkas arsip.
Menyunting berkas konfigurasi: pg_hba.conf dan postgresql.conf.

Membuat pengguna untuk replikasi¶

Replikasi akan dilakukan oleh pengguna khusus, disebut juga sebagai role yang memiliki peran khusus.

Membuat pengguna replikasi pramurep

sudo -u postgres createuser -U postgres pramurep -P -c 5 --replication

Penjelasan perintah di atas adalah:

sudo -u postgres memastikan perintah createuser dijalankan oleh pengguna postgres. Jika tidak, PostgreSQL akan mencoba eksekusi perintah tersebut dengan otentikasi pengguna lokal, dalam hal ini root. Pengguna lokal tidak secara otomatis memiliki akses ke PostgreSQL, bahkan root sekalipun sehingga akan menampilkan galat.
-U opsi terhadap perintah createuser memastikan pengguna postgres digunakan untuk membuat pengguna baru.
Nama pengguna baru yang akan digunakan adalah pramurep. Pengguna tersebut yang akan digunakan pada pengaturan nanti.
-P opsi yang akan menampilkan interaksi untuk mengisikan kata sandi.
!!! warning “Penting”
Pastikan kata sandi yang digunakan memiliki kombinasi yang cukup kuat dan mudah diingat.
-c opsi untuk mengatur batas koneksi bagi pengguna yang dibuat. Nilai 5 ditimbang cukup untuk tujuan proses replikasi.
--replication opsi ini memberikan kewenangan peran REPLICATION bagi pengguna pramurep yang dibuat.

Membuat direktori arsip¶

Create a directory to store archive files. This directory is a subdirectory of the cluster’s data directory, which is named main by default. You’ll use this path in one of the configuration files. If you have configured your cluster to use a different directory for data, you must create your archive directory in that directory and then change the corresponding configuration setting.

In the SSH terminal for the primary server, enter the following command

mkdir -vp /var/lib/postgresql/main/mnt/server/archivedir

Edit `pg_hba.conf`¶

This configuration file contains the settings for client authentication. You
must add an entry for the user pramurep to enable replication.

Edit the file.

PSQL_VERSION=""
nano /etc/postgresql/$PSQL_VERSION/main/pg_hba.conf

After the example replication entries, add the following lines. Replace `` with the external IP address of the standby server:
```
# Allow replication connections
host    replication    pramurep    <standby-IP>/32    md5
```
Save and close the file.
Ctrl+X -> Y+Enter

Edit `postgresql.conf`¶

This configuration file contains the main settings for Postgres. Here, you will modify the file to enable archiving and replication.

Don’t forget to uncomment any lines you edit in the configuration files, or your changes won’t take effect.

Edit the file. In the terminal for the primary server, enter the following command:
```
PSQL_VERSION=""
nano /etc/postgresql/$PSQL_VERSION/main/postgresql.conf
```
In the WRITE AHEAD LOG section, in the Settings section, change the WAL level:
```
wal_level = hot_standby
```
In the Archiving section, change the archive mode:
```
archive_mode = on
```
Change the value for the archive command. This setting tells Postgres to write the archive files to the directory that you created in a previous step:
```
archive_command = 'test ! -f mnt/server/archivedir/%f && cp %p mnt/server/archivedir/%f'
```
In the REPLICATION section, in the Sending Server(s) section, change the value for the maximum number of WAL sender processes. The value of 3 is sufficient to enable backup and replication.
```
max_wal_senders = 3
```
Save and close the file.
Ctrl+X -> Y+Enter

Restart the primary server¶

Now that you’ve made the configuration changes, restart the server to make the changes effective. Enter the following command:

sudo service postgresql restart

Backing up the primary server to the standby server¶

Before making changes on the standby server, stop the service. In the SSH terminal for the standby server, run the following command:

sudo service postgresql stop

Don’t start the service again until all configuration and backup steps are complete. You must bring up the standby server in a state where it is ready to be a backup server. This means that all configuration settings must be in place and the databases must be already synchronized. Otherwise, streaming replication will fail to start.

Run the backup utility¶

The backup utility, named pg_basebackup, will copy files from the data directory on the primary server to the same directory on the standby server.

Make sure you’re running commands in the root shell. In the SSH terminal for the standby server, enter the following command. Continue to use the root shell for the remainder of this tutorial.
```
sudo -s
```
The backup utility won’t overwrite existing files, so you must rename the data directory on the standby server. Run the following command:
```
PSQL_VERSION=""
mv -v /var/lib/postgresql/$PSQL_VERSION/main /var/lib/postgresql/$PSQL_VERSION/main_old
```

Run the backup utility. Replace PRIMARY_IP with the external IP address of the primary server.

sudo su - postgres -s /bin/bash
PRIMARY_IP=""
pg_basebackup -h $PRIMARY_IP \
-D /var/lib/postgresql/$PSQL_VERSION/main \
-U pramurep -v -P --xlog-method=stream

The backup utility will prompt you for the password for the user named pramurep.

The backup process should take just a few moments. When it’s done, you can move on to configuring the standby server.

Configuring the standby server¶

To configure the standby server, you’ll edit postgresql.conf and create a new configuration file named recovery.conf.

Edit `postgresql.conf`¶

For the standby server, you only need to change one setting in this file. Follow these steps:

Edit the file. In the terminal for the standby server, enter the following command:
```
nano /etc/postgresql/$PSQL_VERSION/main/postgresql.conf
```
In the REPLICATION section, in the Standby Servers section, turn on Hot Standby and uncomment the line:
```
hot_standby = on
```
Save and close the file.
Ctrl+X -> Y+Enter

Create the recovery configuration file¶

When you implement a server in Hot Standby mode, you must supply a configuration file that contains the settings that will be used in the event of data recovery. This file is named recovery.conf.

To add this file to the standby server, follow these steps:

Copy the sample recovery file to the proper location. In the terminal for the standby server, enter the following command:

cp -avr /usr/share/postgresql/$PSQL_VERSION/recovery.conf.sample \
    /var/lib/postgresql/$PSQL_VERSION/main/recovery.conf

Edit the recovery file:

nano /var/lib/postgresql/$PSQL_VERSION/main/recovery.conf

In the STANDBY SERVER PARAMETERS section, change the standby mode:
```
standby_mode = on
```
Set the connection string to the primary server. Replace <primary-external-IP> with the external IP address of the primary server. Replace <password> with the password for the user named pramurep.
```
primary_conninfo = 'host=<primary-external-IP> port=5432 user=pramurep password=<password>'
```
(Optional) Set the trigger file location:
```
trigger_file = '/tmp/postgresql.trigger.5432'
```
The trigger_file path that you specify is the location where you can add a file when you want the system to fail over to the standby server. The presence of the file “triggers” the failover. Alternatively, you can use the pg_ctl promote command to trigger failover.
Save and close the file.
Ctrl+X -> Y+Enter

Start the standby server¶

You now have everything in place and are ready to bring up the standby server.

In the terminal for the standby server, enter the following command:

service postgresql start

Seeing the replication at work¶

To demonstrate that the replication between the primary and standby servers is working, you can add a row to the guestbook table on the primary server and then query the standby server to see the new row. Recall that you have already added one row to the table on the primary server.

Start by verifying that the standby server has the same information.

On the standby server, start PSQL:
```
sudo -u postgres psql postgres
```
At the PSQL prompt, enter the following query:
```
select * from guestbook;
```
You should see that the table contains the single row that you originally added. Now, add a second row on the primary server.
On the primary server, start PSQL:
```
sudo -u postgres psql postgres
```

At the PSQL prompt, enter the following command:

INSERT INTO guestbook (
    visitor_email, date, message
    )
VALUES (
    '[email protected]', current_date, 'Now we are replicating.'
    );

Switch back to the standby server terminal and repeat the query for all rows of the guestbook:
```
SELECT * FROM guestbook;
```
You should now see that the standby server has received the update from the primary server.
To exit PSQL, enter \q.
To exit the root shells, enter exit in each terminal window.

Troubleshooting¶

After completing all the steps, if you’re not seeing the data replicate, you
might have missed a step or some small detail. Common mistakes include:

Leaving a setting commented out.
Forgetting to replace placeholder text in a setting or command. For example, some settings require a host IP address or a password.
Entering the wrong IP address or password.
Using a primary server setting for the standby server or vice-versa.

If you find yourself in this state, here are the steps to follow:

Look at the Postgres log on each server. These logs can contain information that will help you troubleshoot the issue.
```
less ../../var/log/postgresql/postgresql-9.3-main.log
```
Check the primary server settings. If there are mistakes, fix them and then restart the server.
Shut down the standby server.
Check the standby server settings and correct them if needed.

On the standby server, rename the main folder to something new, such as main_old_2:

PSQL_VERSION=""
mv /var/lib/postgresql/$PSQL_VERSION/main \
    /var/lib/postgresql/$PSQL_VERSION/main_old_2

On the standby server, run pgbasebackup again to synchronize the data. Substitute <primary-IP> with your primary server’s external IP address:

sudo -u postgres pg_basebackup -h <primary-IP> \
    -D /var/lib/postgresql/$PSQL_VERSION/main -U pramurep \
    -v -P --xlog-method=stream

The main folder now needs a copy of recovery.conf. You can simply copy it from the folder that you renamed to main_old_2:

cp /var/lib/postgresql/$PSQL_VERSION/main_old_2/recovery.conf \
    /var/lib/postgresql/$PSQL_VERSION/main/recovery.conf

Start the standby server.
Retest the replication functionality.

Replikasi basis data PostgreSQL master-slave¶

Latar belakang dan informasi umum¶

Tujuan¶

Persiapan¶

Instalasi¶

Pengaturan dan pengelolaan menggunakan psql¶

Akses koneksi klien remote¶

Membuka porta firewall¶

Membuat tabel bukutamu¶

Konfigurasi peladen primer¶

Membuat pengguna untuk replikasi¶

Membuat direktori arsip¶

Edit pg_hba.conf¶

Edit postgresql.conf¶

Restart the primary server¶

Backing up the primary server to the standby server¶

Run the backup utility¶

Configuring the standby server¶

Edit postgresql.conf¶

Create the recovery configuration file¶

Start the standby server¶

Seeing the replication at work¶

Troubleshooting¶

Pengaturan dan pengelolaan menggunakan `psql`¶

Edit `pg_hba.conf`¶

Edit `postgresql.conf`¶

Edit `postgresql.conf`¶