Replicador Postgres

gistfile1.txt

https://www.howtoforge.com/tutorial/how-to-set-up-master-slave-replication-for-postgresql-96-on-ubuntu-1604/
cd /etc/postgresql/9.4/main/
nano postgresql.conf

listen_addresses = 'IP_MASTER o * para cualquier IP'

wal_level = hot_standby
synchronous_commit = local

archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/9.4/main/archive/%f'

max_wal_senders = 2
wal_keep_segments = 10


synchronous_standby_names = 'pgslave001'

mkdir -p /var/lib/postgresql/9.4/main/archive/
chmod 700 /var/lib/postgresql/9.4/main/archive/
chown -R postgres:postgres /var/lib/postgresql/9.4/main/archive/

----------------------------------------
nano pg_hba.conf

# Localhost
host    replication     replica          127.0.0.1/32            md5

# PostgreSQL Master IP address
host    replication     replica          IP_MASTER/32            md5

# PostgreSQL SLave IP address
host    replication     replica          IP_SLAVE/32            md5

systemctl restart postgresql
-----------------------------------------
su - postgres
psql

CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'aqwe123@';

\du
-----------------------------------------
Configure SLAVE Server

systemctl stop postgresql

cd /etc/postgresql/9.4/main/
nano postgresql.conf

listen_addresses = 'IP_ESPEJO'

wal_level = hot_standby
synchronous_commit = local

max_wal_senders = 2
wal_keep_segments = 10

hot_standby = on

----------------------------------------------------

su - postgres

cd 9.4/
mv main main-bekup

mkdir main/
chmod 700 main/

pg_basebackup -h IP_MASTER -U replica -D /var/lib/postgresql/9.6/main -P --xlog


cd /var/lib/postgresql/9.4/main/
nano recovery.conf


standby_mode = 'on'
primary_conninfo = 'host=IP_MASTER port=5432 user=replica password=aqwe123@ application_name=pgslave001'

restore_command = 'cp /var/lib/postgresql/9.4/main/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'


chmod 600 recovery.conf

systemctl start postgresql


------------------------------------------------------
TESTING MASTER

su - postgres

psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
psql -x -c "select * from pg_stat_replication;"


su - postgres
psql


CREATE TABLE replica_test (hakase varchar(100));
INSERT INTO replica_test VALUES ('howtoforge.com');
INSERT INTO replica_test VALUES ('This is from Master');
INSERT INTO replica_test VALUES ('pg replication by hakase-labs');

------------------------------------------------------------
SLAVE

su - postgres
psql


select * from replica_test;





//INSERT INTO replica_test VALUES ('this is SLAVE');