Oracle Linux/PostgreSQL

From r00tedvw.com wiki
Jump to: navigation, search

Contents

Master/Slave Replication

A simple example of master/slave replication on PostgreSQL.
Done on CentOS7

Master configuration

Installation

Install PostgreSQL

~$ sudo yum install -y postgresql-server net-tools vim

Setup the initial database

~$ sudo /usr/bin/postgresql-setup initdb
Initializing database ... OK

Start PostgreSQL

~$ sudo systemctl start postgresql
~$ sudo systemctl enable postgresql

Set postgresql password

~$ sudo su - postgres
bash$ psql
postgres=# \password postgres
Enter new password:
Enter it again:
postgres=# \q

Configuration

Setup firewalld

~$ sudo firewall-cmd --add-service=postgresql --permanent
~$ sudo firewall-cmd --reload

Configure postgresql.conf

 ~$ sudo mkdir -p /var/lib/pgsql/archive && sudo chmod 700 /var/lib/pgsql/archive && sudo chown -R postgres:postgres /var/lib/pgsql/archive
~$ sudo vim /var/lib/pgsql/data/postgresql.conf
listen_addresses = '*'
wal_level = hot_standby
synchronous_commit = local
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/archive/%f'
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'pgslave01'

Configure pg_hba.conf (add to the end)

~$ sudo vim /var/lib/pgsql/data/pg_hba.conf
#Localhost
host      replication     replica     127.0.0.1/32     md5
#Master
host      replication     replica     10.10.0.40/32     md5
#Slave
host      replication     replica     10.10.0.41/32     md5

Restart postgres and create replication user.

~$ sudo systemctl restart postgresql
~$ sudo su - postgres
bash$ createuser --replication -P replica
Enter password for new role:
Enter it again:

Slave Configuration

Installation

Install PostgreSQL

~$ sudo yum install -y postgresql-server net-tools vim

Configuration

Setup firewalld

~$ sudo firewall-cmd --add-service=postgresql --permanent
~$ sudo firewall-cmd --reload

Stop PostgreSQL

~$ sudo systemctl stop postgresql

Copy data from Master

~$ sudo su - postgres
bash$ pg_basebackup -h 10.10.0.40 -U replica -D /var/lib/pgsql/data -P --xlog

Configure postgresql.conf

~$ sudo vim /var/lib/pgsql/data/postgresql.conf
listen_addresses = '*'
hot_standby = on

Configure recovery.conf

~$ sudo touch /var/lib/pgsql/data/recovery.conf
~$ sudo chmod 600 /var/lib/pgsql/data/recovery.conf && sudo chown postgres:postgres /var/lib/pgsql/data/recovery.conf
~$ sudo vim /var/lib/pgsql/data/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=10.10.0.40 port=5432 user=replica password=Password application_name=pgslave01'
trigger_file = '/tmp/postgresql.trigger.5432'

Start PostgreSQL

~$ sudo systemctl start postgresql

Testing

netstat

Use netstat to look for the proper listening ports on both the master and slave.

~$ sudo netstat -plntu
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      1684/postgres

db query

From the Master:

~$ sudo su - postgres
bash$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
 application_name  |   state   | sync_priority | sync_state
-------------------+-----------+---------------+------------
 pgslavel01 | streaming |             1 | sync
(1 row)


bash$ psql -x -c "select * from pg_stat_replication;"
-[ RECORD 1 ]----+------------------------------
pid              | 1810
usesysid         | 16384
usename          | replica
application_name | pgslave01
client_addr      | 10.10.0.41
client_hostname  |
client_port      | 59828
backend_start    | 2020-02-18 12:36:53.638717-05
state            | streaming
sent_location    | 0/30145C8
write_location   | 0/30145C8
flush_location   | 0/30145C8
replay_location  | 0/30145C8
sync_priority    | 1
sync_state       | sync

db data replication

Test data replication from the master to slave.
From the Master:

~$ sudo su - postgres
bash$ psql
postgres=# CREATE TABLE test_table (test varchar(100));
postgres=# INSERT INTO test_table VALUES ('This is VPSCheap.net');

From the Slave:

~$ sudo su - postgres
bash$ psql -c "SELECT * FROM test_table;"
         test
----------------------
 This is VPSCheap.net
(1 row)
Personal tools
Namespaces

Variants
Actions
Navigation
Mediawiki
Confluence
DevOps Tools
Ubuntu
Oracle Linux
AWS
Windows
OpenVPN
Grafana
Pivotal
osTicket
OTRS
phpBB
WordPress
VmWare ESXI 5.1
Crypto currencies
HTML
CSS
Python
Java Script
PHP
Raspberry Pi
Canvas LMS
Kaltura Media Server
Plex Media Server
MetaSploit
Zoneminder
Photoshop CS2
Fortinet
Uploaded
Certifications
General Info
Games
Meal Plans
NC Statutes
Toolbox