R00t: /* db query */

2020-02-18T21:05:48Z

‎db query

R00t at 21:05, 18 February 2020

2020-02-18T21:05:10Z

R00t: Created page with "=Master/Slave Replication= A simple example of master/slave replication on PostgreSQL."

2020-02-17T22:24:20Z

Created page with "=Master/Slave Replication= A simple example of master/slave replication on PostgreSQL."

New page

=Master/Slave Replication=
A simple example of master/slave replication on PostgreSQL.

@@ Line 92: / Line 92: @@
   pgslavel01 | streaming |             1 | sync
 (1 row)</nowiki>
   <nowiki>bash$ psql -x -c "select * from pg_stat_replication;"
 -[ RECORD 1 ]----+------------------------------

← Older revision		Revision as of 21:05, 18 February 2020
Line 1:		Line 1:
	=Master/Slave Replication=		=Master/Slave Replication=
	A simple example of master/slave replication on PostgreSQL.		A simple example of master/slave replication on PostgreSQL.
		+	<br>Done on CentOS7
		+	==Master configuration==
		+	===Installation===
		+	Install PostgreSQL
		+	<nowiki>~$ sudo yum install -y postgresql-server net-tools vim</nowiki>
		+	Setup the initial database
		+	<nowiki>~$ sudo /usr/bin/postgresql-setup initdb
		+	Initializing database ... OK</nowiki>
		+	Start PostgreSQL
		+	<nowiki>~$ sudo systemctl start postgresql
		+	~$ sudo systemctl enable postgresql</nowiki>
		+	Set postgresql password
		+	<nowiki>~$ sudo su - postgres
		+	bash$ psql
		+	postgres=# \password postgres
		+	Enter new password:
		+	Enter it again:
		+	postgres=# \q</nowiki>
		+	===Configuration===
		+	Setup firewalld
		+	<nowiki>~$ sudo firewall-cmd --add-service=postgresql --permanent
		+	~$ sudo firewall-cmd --reload</nowiki>
		+	Configure <code>postgresql.conf</code>
		+	<nowiki> ~$ 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'</nowiki>
		+	Configure <code>pg_hba.conf</code> (add to the end)
		+	<nowiki>~$ 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</nowiki>
		+	Restart postgres and create replication user.
		+	<nowiki>~$ sudo systemctl restart postgresql
		+	~$ sudo su - postgres
		+	bash$ createuser --replication -P replica
		+	Enter password for new role:
		+	Enter it again:</nowiki>
		+
		+	==Slave Configuration==
		+	===Installation===
		+	Install PostgreSQL
		+	<nowiki>~$ sudo yum install -y postgresql-server net-tools vim</nowiki>
		+	===Configuration===
		+	Setup firewalld
		+	<nowiki>~$ sudo firewall-cmd --add-service=postgresql --permanent
		+	~$ sudo firewall-cmd --reload</nowiki>
		+	Stop PostgreSQL
		+	<nowiki>~$ sudo systemctl stop postgresql</nowiki>
		+	Copy data from Master
		+	<nowiki>~$ sudo su - postgres
		+	bash$ pg_basebackup -h 10.10.0.40 -U replica -D /var/lib/pgsql/data -P --xlog</nowiki>
		+	Configure <code>postgresql.conf</code>
		+	<nowiki>~$ sudo vim /var/lib/pgsql/data/postgresql.conf
		+	listen_addresses = '*'
		+	hot_standby = on</nowiki>
		+	Configure <code>recovery.conf</code>
		+	<nowiki>~$ 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'</nowiki>
		+	Start PostgreSQL
		+	<nowiki>~$ sudo systemctl start postgresql</nowiki>
		+
		+	==Testing==
		+	===netstat===
		+	Use <code>netstat</code> to look for the proper listening ports on both the master and slave.
		+	<nowiki>~$ 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</nowiki>
		+
		+	===db query===
		+	From the '''Master''':
		+	<nowiki>~$ 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)</nowiki>
		+	<nowiki>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</nowiki>
		+
		+	===db data replication===
		+	Test data replication from the master to slave. <br>
		+	From the '''Master''':
		+	<nowiki>~$ sudo su - postgres
		+	bash$ psql
		+	postgres=# CREATE TABLE test_table (test varchar(100));
		+	postgres=# INSERT INTO test_table VALUES ('This is VPSCheap.net');</nowiki>
		+	From the '''Slave''':
		+	<nowiki>~$ sudo su - postgres
		+	bash$ psql -c "SELECT * FROM test_table;"
		+	test
		+	----------------------
		+	This is VPSCheap.net
		+	(1 row)</nowiki>

Oracle Linux/PostgreSQL - Revision history

R00t: /* db query */

R00t at 21:05, 18 February 2020

R00t: Created page with "=Master/Slave Replication= A simple example of master/slave replication on PostgreSQL."