Re: [PERFORM] Unable to start the slave instance

Hi Michael,

We are using different releases of windows. Is this issue reported due to different versions of windows releases.
Master server : Windows 7 Professional
Slave server : Windows 10 Professional

Note: We have followed the following steps to configure the replication

Step:1
sudo passwd postgres
Switch over to the postgres user like this:
sudo su – postgres

Step:2 Generate an ssh key for the postgres user:
ssh-keygen
Press “ENTER” to all the prompts that follow.

Step:3
Transfer the keys to the other server by following cmd:
ssh-copy-id IP if opposite_server

Step:4
Configure the Master Server
First, we will create a user called “rep” that can be used solely for replication:
psql -c “CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD ‘yourpassword’;”

Step:5
cd /etc/postgresql/9.3/main
vim  pg_hba.conf
add below line at bottom of the file
host    replication     rep     xxx.xxx.xxx.xx/32 (IP address of slave)   md5
: wq! (SAVE FILE)
Step:6
vim postgresql.conf
Find below parameters. Uncomment them if they are commented.
listen_addresses = ‘localhost, xxx.xxx.xxx.xx’ (IP address of current host) wal_level = ‘hot_standby’
archive_mode = on
archive_command = ‘cd.’
max_wal_senders = 1
hot_standby = on
: wq! (SAVE FILE)
Restart the master server to take effect your changes:
service postgresql restart
Step:7
Configure the Slave Server
service postgresql stop
cd /etc/postgresql/9.3/main
Adjust the access file to allow the other server to connect to this.
vim pg_hba.conf
add below line at bottom of the file
host    replication     rep     xxx.xxx.xxx.xx/32 (IP address of master)  md5
: wq! (SAVE FILE)

Step:8
vim postgresql.conf
You can use the same configuration options you set for the master server, modifying only the IP address to reflect the
slaveserver’s address:
 
listen_addresses = ‘localhost, xxx.xxx.xxx.xx’ (IP address of THIS host) wal_level = ‘hot_standby’
archive_mode = on
archive_command = ‘cd.’
max_wal_senders = 1
hot_standby = on
:wq!(SAVE FILE)

Step:9
Replicating the Initial database:
On the master server, we can use an internal postgres backup start command to create a backup label command. We then
willtransfer the database data to our slave and then issue an internal backup stop command to clean up:
 
psql -c “select pg_start_backup(‘initial_backup’);”
rsync -cva –inplace –exclude=*pg_xlog* /var/lib/postgresql/9.3/main/ slave_IP_address:/var/lib/postgresql/9.3/main/
psql -c “select pg_stop_backup ();”

Step:10
We now have to configure a recovery file on our slave.
cd /var/lib/postgresql/9.3/main
vim recovery. conf
Fill in the following information in to it, make sure to change the IP address of your master server and the password
forthe rep user you created:
 
standby_mode = ‘on’
primary_conninfo = ‘host=master_IP_address port=5432 user=rep password=yourpassword’
trigger_file = ‘/tmp/postgresql.trigger.5432’

The last line in the file, trigger file, is one of the most interesting parts of the entire configuration. If you
createa file at that location on your slave machine, your slave will reconfigure itself to act as a master.
 
Now start your slave server. Type:
service postgresql start

Step:11
You’ll want to check the logs to see if there are any problems. They are located on both machines here:
less /var/log/postgresql/postgresql-9.3-main.log

Step:12
Test the Replication
On the master server, as the postgres user, log into the postgres system by typing:
psql
We will create a test table to create some changes:
CREATE TABLE rep_test (test varchar(50)); Now insert value into it INSERT INTO rep_test VALUES (‘data1’); INSERT INTO
rep_testVALUES (‘data2’); INSERT INTO rep_test VALUES (‘data3’); INSERT INTO rep_test VALUES (‘data4’); INSERT INTO
rep_testVALUES (‘data5’); You can now exit out of this interface by typing:
 
\q

Regards,
Daulat


-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: 05 July, 2017 6:40 PM
To: Daulat Ram <Daulat.Ram@cyient.com>
Cc: pgsql-performance@postgresql.org
Subject: [EXTERNAL]Re: [PERFORM] Unable to start the slave instance

On Wed, Jul 5, 2017 at 3:26 AM, Daulat Ram <Daulat.Ram@cyient.com> wrote:
> Hi experts,
>
> We have configured a replication environment in Windows 10. But I am
> getting below the error messages while starting slave instance.
>
>
>
> Error:
>
>
>
> 2017-07-05 00:00:02 IST LOG:  restored log file "000000010000000000000022"
> from archive
>
> 2017-07-05 00:00:02 IST LOG:  WAL file is from different database system:
> WAL file database system identifier is 6438799484563175092, pg_control
> database system identifier is 6379088242155134709.
>
> 2017-07-05 00:00:02 IST FATAL:  database system identifier differs
> between the primary and standby
>
> 2017-07-05 00:00:02 IST DETAIL:  The primary's identifier is
> 6438799484563175092, the standby's identifier is 6379088242155134709.
>

So how did you get to here? It doesn't look like a proper rsync or pg_basebackup method got you here.

________________________________

DISCLAIMER:

This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged
information.Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended
recipient,please contact the sender by reply email and destroy all copies of the original message. Check all
attachmentsfor viruses before opening them. All views or opinions presented in this e-mail are those of the author and
maynot reflect the opinion of Cyient or those of our affiliates.