public inbox for [email protected]help / color / mirror / Atom feed
Unable to start the slave instance 5+ messages / 3 participants [nested] [flat]
* Unable to start the slave instance @ 2017-07-05 09:14 Daulat Ram <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Daulat Ram @ 2017-07-05 09:14 UTC (permalink / raw) To: pgsql-performance Hi experts, We have configured a replication environment in Windows 10. But I am getting an error 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. 2017-07-05 00:00:06 IST LOG: restored log file "000000010000000000000022" from archive 2017-07-05 00:00:06 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:06 IST FATAL: database system identifier differs between the primary and standby 2017-07-05 00:00:06 IST DETAIL: The primary's identifier is 6438799484563175092, the standby's identifier is 6379088242155134709. 2017-07-05 00:00:11 IST LOG: restored log file "000000010000000000000022" from archive 2017-07-05 00:00:11 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:11 IST FATAL: database system identifier differs between the primary and standby 2017-07-05 00:00:11 IST DETAIL: The primary's identifier is 6438799484563175092, the standby's identifier is 6379088242155134709. 2017-07-05 00:00:16 IST LOG: restored log file "000000010000000000000022" from archive 2017-07-05 00:00:16 IST LOG: WAL file is from different database system: WAL file database system identifier is 6438799484563175092, pg Below are the parameter at Primary/standby & Recovery.conf as well as hba.conf file. @MASTER wal_level = hot_standby shared_buffers = 128MB port = 5432 max_connections = 100 wal_level = hot_standby # archive_mode = on archive_command = 'copy "%p" "\\\\192.168.1.111\\archive\\%f"' max_wal_senders = 1 wal_keep_segments = 10 listen_addresses = '*' @SLAVE '[email protected]' port = 5432 hot_standby = on max_connections = 100 listen_addresses = '*' shared_buffers = 128MB In Recovery.conf restore_command = 'copy "\\\\192.168.1.111\\archive\\%f" "%p"' standby_mode = 'on' primary_coninfo = 'host = 192.168.1.111 port = 5432 user = postgres password = postgres' In Hba.conf @ Master side # TYPE DATABASE USER ADDRESS METHOD # replication privilege. host replication postgres 192.168.1.106/32 trust Please help on this issue. What things I have to changed & checked. Regards, Daulat ________________________________ 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 attachments for viruses before opening them. All views or opinions presented in this e-mail are those of the author and may not reflect the opinion of Cyient or those of our affiliates. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Unable to start the slave instance @ 2017-07-05 09:26 Daulat Ram <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Daulat Ram @ 2017-07-05 09:26 UTC (permalink / raw) To: pgsql-performance 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. 2017-07-05 00:00:06 IST LOG: restored log file "000000010000000000000022" from archive 2017-07-05 00:00:06 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:06 IST FATAL: database system identifier differs between the primary and standby 2017-07-05 00:00:06 IST DETAIL: The primary's identifier is 6438799484563175092, the standby's identifier is 6379088242155134709. 2017-07-05 00:00:11 IST LOG: restored log file "000000010000000000000022" from archive 2017-07-05 00:00:11 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:11 IST FATAL: database system identifier differs between the primary and standby 2017-07-05 00:00:11 IST DETAIL: The primary's identifier is 6438799484563175092, the standby's identifier is 6379088242155134709. 2017-07-05 00:00:16 IST LOG: restored log file "000000010000000000000022" from archive 2017-07-05 00:00:16 IST LOG: WAL file is from different database system: WAL file database system identifier is 6438799484563175092, pg Below are the parameters at Primary/standby & Recovery.conf as well as hba.conf file. @MASTER wal_level = hot_standby shared_buffers = 128MB port = 5432 max_connections = 100 wal_level = hot_standby # archive_mode = on archive_command = 'copy "%p" "\\\\192.168.1.111\\archive\\%f<file://192.168.1.111/archive/%25f>"' max_wal_senders = 1 wal_keep_segments = 10 listen_addresses = '*' @SLAVE '[email protected]' port = 5432 hot_standby = on max_connections = 100 listen_addresses = '*' shared_buffers = 128MB In Recovery.conf restore_command = 'copy "\\\\192.168.1.111\\archive\\%f<file://192.168.1.111/archive/%25f>" "%p"' standby_mode = 'on' primary_coninfo = 'host = 192.168.1.111 port = 5432 user = postgres password = postgres' In Hba.conf @ Master side # TYPE DATABASE USER ADDRESS METHOD # replication privilege. host replication postgres 192.168.1.106/32 trust Please help on this issue. What things I have to changed & checked. Regards, Daulat ________________________________ 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 attachments for viruses before opening them. All views or opinions presented in this e-mail are those of the author and may not reflect the opinion of Cyient or those of our affiliates. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Unable to start the slave instance @ 2017-07-05 13:10 Scott Marlowe <[email protected]> parent: Daulat Ram <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Scott Marlowe @ 2017-07-05 13:10 UTC (permalink / raw) To: Daulat Ram <[email protected]>; +Cc: pgsql-performance On Wed, Jul 5, 2017 at 3:26 AM, Daulat Ram <[email protected]> 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. -- Sent via pgsql-performance mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Unable to start the slave instance @ 2017-07-06 05:02 Daulat Ram <[email protected]> parent: Scott Marlowe <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Daulat Ram @ 2017-07-06 05:02 UTC (permalink / raw) To: Scott Marlowe <[email protected]>; +Cc: pgsql-performance 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 slave server’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 will transfer 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 for the 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 create a 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_test VALUES (‘data2’); INSERT INTO rep_test VALUES (‘data3’); INSERT INTO rep_test VALUES (‘data4’); INSERT INTO rep_test VALUES (‘data5’); You can now exit out of this interface by typing: \q Regards, Daulat -----Original Message----- From: Scott Marlowe [mailto:[email protected]] Sent: 05 July, 2017 6:40 PM To: Daulat Ram <[email protected]> Cc: [email protected] Subject: [EXTERNAL]Re: [PERFORM] Unable to start the slave instance On Wed, Jul 5, 2017 at 3:26 AM, Daulat Ram <[email protected]> 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 attachments for viruses before opening them. All views or opinions presented in this e-mail are those of the author and may not reflect the opinion of Cyient or those of our affiliates. -- Sent via pgsql-performance mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Unable to start the slave instance @ 2017-07-06 05:10 Michael Paquier <[email protected]> parent: Daulat Ram <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Michael Paquier @ 2017-07-06 05:10 UTC (permalink / raw) To: Daulat Ram <[email protected]>; +Cc: Scott Marlowe <[email protected]>; pgsql-performance On Thu, Jul 6, 2017 at 2:02 PM, Daulat Ram <[email protected]> wrote: > 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 Please do not top-post. That may be a problem. Versions of PostgreSQL compiled across different platforms are different things, and replication is not supported for that as things happen at a low binary level. > 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 will transfer 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 ();” Shouldn't you remove the data of the slave as well first? -- Michael -- Sent via pgsql-performance mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2017-07-06 05:10 UTC | newest] Thread overview: 5+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2017-07-05 09:14 Unable to start the slave instance Daulat Ram <[email protected]> 2017-07-05 09:26 Unable to start the slave instance Daulat Ram <[email protected]> 2017-07-05 13:10 ` Scott Marlowe <[email protected]> 2017-07-06 05:02 ` Daulat Ram <[email protected]> 2017-07-06 05:10 ` Michael Paquier <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox