Oke, I will teach you how
create or build MASTER SLAVE database on postgresql, now you can ssh
your ip master with user root and input your password, follow
command:
dba@dba-rizky:~$ ssh
root@IP_MASTER
root@IP_MASTERs password:
Last login: Thu Apr 19 16:54:32
2018 from dba
[root@prodservice ~]#
Move to user postgres and check
for the postgres location in progress. As for the checks that must be
done:
1. First check the status of
the existing network, if there is a postgres 5432 port, then postgres
is active
2. Next check location
postgres, because if you know location postgres, you can do that
configuration and setting for build replication, you just edit 2
file:
a. pg_hba.conf, in this file
just for give access user connection in here or access build
replication
b. postgresql.conf, in this
file, you can do custom port, cutom location log, and all about in
here file you can do that for build replication.
[root@prodservice ~]# su
- postgres
Last login: Kam Apr 5 17:33:05
WIB 2018 on pts/0
[postgres@prodservice ~]$
netstat -pltn
(Not all processes could be
identified, non-owned process info
will not be shown, you would
have to be root to see it all.)
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:111 0.0.0.0:*
LISTEN -
tcp 0 0 0.0.0.0:22 0.0.0.0:*
LISTEN -
tcp 0 0 0.0.0.0:5432
0.0.0.0:* LISTEN 3837/postgres
tcp 0 0 127.0.0.1:25 0.0.0.0:*
LISTEN -
tcp6 0 0 :::111 :::* LISTEN -
tcp6 0 0 :::8080 :::* LISTEN -
tcp6 0 0 :::22 :::* LISTEN -
tcp6 0 0 :::5432
:::* LISTEN 3837/postgres
tcp6 0 0 127.0.0.1:8005 :::*
LISTEN -
tcp6 0 0 :::8009 :::* LISTEN -
[postgres@prodservice ~]$ ps
-ef | grep postgres
postgres 3837 1 0 Apr05 ?
00:00:01 /usr/local/pgsql/bin/postgres -D
/usr/local/pgsql/data
And in here you know location
postgres, the postgres location is located in
"/usr/local/pgsql/data", configure the data in the
postgresql.conf file with the vi command.
[postgres@prodservice ~]$ cd
/usr/local/pgsql/data/
[postgres@prodservice data]$ vi
postgresql.conf
The files that need to be
edited in the file "postgresql.conf" for some commands are:
wal_level
= hot_standby
max_wal_senders
= 3
wal_keep_segments
= 32
hot_standby
= on
archive_mode
= on
archive_command
= 'cp %p /usr/local/pgsql/data/archive/%f'
log_line_prefix
= '%t [%p] %a %u %d %r %e'
log_destination = 'stderr'
logging_collector = on
log_directory = 'logs'
log_filename = 'logpsql-%Y-%m-%d.log'
log_rotation_age = 1d
log_destination = 'stderr'
logging_collector = on
log_directory = 'logs'
log_filename = 'logpsql-%Y-%m-%d.log'
log_rotation_age = 1d
Edit file “pg_hba.conf”
with command:
[postgres@prodservice
dbreplikasi]$ vi pg_hba.conf
And add ip remote to access to
ip replication
host all
all IP_PC_KITA/32 trust
host all
all IP_REPLICATION/32 trust
host
replication postgres IP_REPLICATION/32 trust
Do restart postgres data so
that the configuration file that has been done can run and can be
build replication data from ip master to ip replication.
[postgres@prodservice data]$
/usr/local/pgsql/bin/pg_ctl restart -D
/usr/local/pgsql/data
Open user root with ip
replication can do build for replication, and follow command:
dba@dbarizky:~$ ssh
root@IP_REPLICATION
root@IP_REPLICATION's password:
There was 1 failed login
attempt since the last successful login.
Last login: Fri Apr 6 16:24:21
2018 from dba
[root@prodservice-dbreplikasi
~]#
Check the location of the
postgres data by using the user root and perform the stop function of
the postgres link to snap the currently running postgres process
[root@prodservice-dbreplikasi
~]# ps -ef | grep postgres
postgres 13757 1 0 Apr05 ?
00:00:00 /usr/local/pgsql/bin/postgres -D /DATA
[root@prodservice-dbreplikasi
~]# /usr/local/pgsql/bin/pg_ctl stop -D /DATA
After the termination of
postgres process is running it can be done deletion of all data in
the folder / DATA. Login into the postgres user first before
pg_basebackup and create a new directory file named dbreplikasi and
change the mode to 700, the folder is used to place data files that
will be replicated from the master by using the command pg_basebackup
-P -R -X stream -c fast -h IP_MASTER -U postgres -D PGDATA.
[root@prodservice-dbreplikasi
~]# cd /DATA
[root@prodservice-dbreplikasi
DATA]# mkdir dbreplikasi
[root@prodservice-dbreplikasi
DATA]# chmod 700 dbreplikasi
[root@prodservice-dbreplikasi
DATA]# /usr/local/pgsql/bin/pg_basebackup -P -R
-X stream -c fast -h IP_MASTER -U postgres -D /DATA/dbreplikasi
But if you want to pg_basebackup with owner postgres, you can do step :
[root@prodservice-dbreplikasi DATA]# chmod 700 dbreplikasi
[root@prodservice-dbreplikasi DATA]# chown -R postgres:postgres /DATA/dbreplikasi
[root@prodservice-dbreplikasi DATA]# su - postgres
[postgres@prodservice-dbreplikasi ~]$ cd /DATA/dbreplikasi
[postgres@prodservice-dbreplikasi ~]$ /usr/local/pgsql/bin/pg_basebackup -P -R -X stream -c fast -h IP_MASTER -U postgres -D /DATA/dbreplikasi
Edit file recovery.conf located at location /DATA/dbreplikasi/ with command:
[root@prodservice-dbreplikasi DATA]# chmod 700 dbreplikasi
[root@prodservice-dbreplikasi DATA]# chown -R postgres:postgres /DATA/dbreplikasi
[root@prodservice-dbreplikasi DATA]# su - postgres
[postgres@prodservice-dbreplikasi ~]$ cd /DATA/dbreplikasi
[postgres@prodservice-dbreplikasi ~]$ /usr/local/pgsql/bin/pg_basebackup -P -R -X stream -c fast -h IP_MASTER -U postgres -D /DATA/dbreplikasi
Edit file recovery.conf located at location /DATA/dbreplikasi/ with command:
[root@prodservice-dbreplikasi
DATA]# cd dbreplikasi
[postgres@prodservice-dbreplikasi
dbreplikasi]$ vi recovery.conf
standby_mode
= 'on'
primary_conninfo
= 'user=postgres host=IP_MASTER port=5432 sslmode=disable
sslcompression=1'
recovery_target_timeline
= 'latest'
restore_command
= 'cp %p /DATA/dbreplikasi/archive/%f'
trigger_file
= ''/tmp/trigger_pg_sql.txt''
Do start to postgres
replication with command:
[postgres@prodservice-dbreplikasi
dbreplikasi]$ /usr/local/pgsql/bin/pg_ctl start
-D /DATA/dbreplikasi
To check between master and
replication running simultaneously, you can be done by using
pg_current, things need to be done, enter using user postgres and
type command 'psql -d db_marmer' and enter pg_current query on the
master and replicate it.
A. Master
[postgres@prodservice ~]$ psql
psql (9.6.3)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding |
Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
db_marmer
| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 |
en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 |
en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 |
en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=# \c
db_marmer
You are now connected to
database "db_marmer" as user "postgres".
db_marmer=# SELECT
pg_current_xlog_location();
pg_current_xlog_location
--------------------------
0/1B008C1
(1 row)
B. Replication
[postgres@prodservice-dbreplikasi
~]$ psql
psql (9.6.3)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding |
Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
db_marmer
| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 |
en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 |
en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 |
en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=# \c
db_marmer
You are now connected to
database "db_marmer" as user "postgres".
db_marmer=# SELECT
pg_last_xlog_receive_location();
-------------------------------
0/1B008C1
(1 row)
Comments
Post a Comment