Re: database system identifier differs between the primary and standby

Hello List,

We are getting the following log while configuring hot standby,

2011-04-20 17:34:40 ETC/GMT FATAL: the database system is starting up
2011-04-20 17:34:41 ETC/GMT FATAL: database system identifier differs
between the primary and standby
2011-04-20 17:34:41 ETC/GMT DETAIL: The primary's identifier is
5592072752411433371, the standby's identifier is 5597615802844953578.

PostgreSQL Version: 9.0.2

Prymary configuration:
wal_level = 'hot_standby' - postgresql.conf
host replication all x.x.x.x/24 trust – pg_hba.conf

Secondary configuration:
Hot_standby=off – postgresql.conf
standby_mode = 'on' – recovery.conf
primary_conninfo = 'host=10.20.9.181 port=5432' – recovery.conf

Please suggest on the above log O/P.

Regards,
Rajib

--
View this message in context: http://postgresql.1045698.n5.nabble.com/database-system-identifier-differs-between-the-primary-and-standby-tp4317178p4317178.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Wed, Apr 20, 2011 at 9:28 AM, rajibdk <rajib(dot)deka(at)siemens(dot)com> wrote:
> We are getting the following log while configuring hot standby,
>
> 2011-04-20 17:34:40 ETC/GMT FATAL:  the database system is starting up
> 2011-04-20 17:34:41 ETC/GMT FATAL:  database system identifier differs
> between the primary and standby
> 2011-04-20 17:34:41 ETC/GMT DETAIL:  The primary's identifier is
> 5592072752411433371, the standby's identifier is 5597615802844953578.
>
> PostgreSQL Version: 9.0.2

You need to initialize the slave using a hot backup taken on the master.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

What does that database system identifier means? Is it related to DB transactions' or unique to a version?

Rajib Deka
SIEMENS Ltd.
Robert V Chandran Tower, First Floor, West Wing,
#149, Velechery Tambaram Main Road, Pallikaranai, Chennai-100, INDIA.
www.siemens.com<http://www.siemens.com>

Mob: +91-9176780669 | E-Mail: rajib(dot)deka(at)siemens(dot)com
________________________________
From: Robert Haas [via PostgreSQL] [mailto:ml-node+4326869-1711138747-200178(at)n5(dot)nabble(dot)com]
Sent: Wednesday, April 20, 2011 7:20 PM
To: Deka, Rajib IN MAA SL
Subject: Re: database system identifier differs between the primary and standby

On Wed, Apr 20, 2011 at 9:28 AM, rajibdk <[hidden email]</user/SendEmail.jtp?type=node&node=4326869&i=0&by-user=t>> wrote:
> We are getting the following log while configuring hot standby,
>
> 2011-04-20 17:34:40 ETC/GMT FATAL: the database system is starting up
> 2011-04-20 17:34:41 ETC/GMT FATAL: database system identifier differs
> between the primary and standby
> 2011-04-20 17:34:41 ETC/GMT DETAIL: The primary's identifier is
> 5592072752411433371, the standby's identifier is 5597615802844953578.
>
> PostgreSQL Version: 9.0.2

You need to initialize the slave using a hot backup taken on the master.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list ([hidden email]</user/SendEmail.jtp?type=node&node=4326869&i=1&by-user=t>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

________________________________
If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/database-system-identifier-differs-between-the-primary-and-standby-tp4317178p4326869.html
To unsubscribe from database system identifier differs between the primary and standby, click here<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=4317178&code=cmFqaWIuZGVrYUBzaWVtZW5zLmNvbXw0MzE3MTc4fC0zNTQ2NTE2Njg=>.

________________________________
Important notice: This e-mail and any attachment there to contains corporate proprietary information. If you have received it by mistake, please notify us immediately by reply e-mail and delete this e-mail and its attachments from your system.
Thank You.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/database-system-identifier-differs-between-the-primary-and-standby-tp4317178p4330373.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On 21.04.2011 12:31, rajibdk wrote:
> What does that database system identifier means? Is it related to DB transactions' or unique to a version?

It's an identifier unique to each PostgreSQL database cluster. It's
generated when you run initdb.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Thu, Apr 21, 2011 at 10:31 AM, rajibdk <rajib(dot)deka(at)siemens(dot)com> wrote:

> What does that database system identifier means? Is it related to DB
> transactions’ or unique to a version?

Regrettably, it means you didn't follow the documented procedure.

It isn't possible to do it any other way, so those questions are a distraction.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On Thu, Apr 21, 2011 at 6:38 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Thu, Apr 21, 2011 at 10:31 AM, rajibdk <rajib(dot)deka(at)siemens(dot)com> wrote:
>> What does that database system identifier means? Is it related to DB
>> transactions’ or unique to a version?
>
> Regrettably, it means you didn't follow the documented procedure.
>
> It isn't possible to do it any other way, so those questions are a distraction.

I think they are perfectly good questions. If someone is trying to
understand how our product works, we should encourage that.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

2011/4/21 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Thu, Apr 21, 2011 at 6:38 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> On Thu, Apr 21, 2011 at 10:31 AM, rajibdk <rajib(dot)deka(at)siemens(dot)com> wrote:
>>> What does that database system identifier means? Is it related to DB
>>> transactions’ or unique to a version?
>>
>> Regrettably, it means you didn't follow the documented procedure.
>>
>> It isn't possible to do it any other way, so those questions are a distraction.
>
> I think they are perfectly good questions.  If someone is trying to
> understand how our product works, we should encourage that.

Agree those are perfectly good question to ask on -general. (but on
hackers it looks excessive)
Rajib: this is relative to initdb, it produces one key to be able to
check later if postgresql is restoring the good files.
(
from sources :
/*
* Unique system identifier --- to ensure we match up xlog files with the
* installation that produced them.
*/
)

Robert, Please don't add confusion to your signature : PostgreSQL is a
community project not an enterprise product.

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support

Thanks Robert.

Rajib Deka
SIEMENS Ltd.
Robert V Chandran Tower, First Floor, West Wing,
#149, Velechery Tambaram Main Road, Pallikaranai, Chennai-100, INDIA.
www.siemens.com

Mob: +91-9176780669 | E-Mail: rajib(dot)deka(at)siemens(dot)com

-----Original Message-----
From: Robert Haas [mailto:robertmhaas(at)gmail(dot)com]
Sent: Thursday, April 21, 2011 8:39 PM
To: Simon Riggs
Cc: Deka, Rajib IN MAA SL; pgsql-hackers
Subject: Re: [HACKERS] Re: database system identifier differs between the primary and standby

On Thu, Apr 21, 2011 at 6:38 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Thu, Apr 21, 2011 at 10:31 AM, rajibdk <rajib(dot)deka(at)siemens(dot)com> wrote:
>> What does that database system identifier means? Is it related to DB
>> transactions' or unique to a version?
>
> Regrettably, it means you didn't follow the documented procedure.
>
> It isn't possible to do it any other way, so those questions are a distraction.

I think they are perfectly good questions. If someone is trying to
understand how our product works, we should encourage that.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Important notice: This e-mail and any attachment there to contains corporate proprietary information. If you have received it by mistake, please notify us immediately by reply e-mail and delete this e-mail and its attachments from your system.
Thank You.

Thanks a lot.
I got the idea now.

Rajib Deka
SIEMENS Ltd.
Robert V Chandran Tower, First Floor, West Wing,
#149, Velechery Tambaram Main Road, Pallikaranai, Chennai-100, INDIA.
www.siemens.com

Mob: +91-9176780669 | E-Mail: rajib(dot)deka(at)siemens(dot)com

-----Original Message-----
From: Cédric Villemain [mailto:cedric(dot)villemain(dot)debian(at)gmail(dot)com]
Sent: Thursday, April 21, 2011 9:56 PM
To: Robert Haas
Cc: Simon Riggs; Deka, Rajib IN MAA SL; pgsql-hackers
Subject: Re: [HACKERS] Re: database system identifier differs between the primary and standby

2011/4/21 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Thu, Apr 21, 2011 at 6:38 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> On Thu, Apr 21, 2011 at 10:31 AM, rajibdk <rajib(dot)deka(at)siemens(dot)com> wrote:
>>> What does that database system identifier means? Is it related to DB
>>> transactions' or unique to a version?
>>
>> Regrettably, it means you didn't follow the documented procedure.
>>
>> It isn't possible to do it any other way, so those questions are a distraction.
>
> I think they are perfectly good questions. If someone is trying to
> understand how our product works, we should encourage that.

Agree those are perfectly good question to ask on -general. (but on
hackers it looks excessive)
Rajib: this is relative to initdb, it produces one key to be able to
check later if postgresql is restoring the good files.
(
from sources :
/*
* Unique system identifier --- to ensure we match up xlog files with the
* installation that produced them.
*/
)

Robert, Please don't add confusion to your signature : PostgreSQL is a
community project not an enterprise product.

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support

Important notice: This e-mail and any attachment there to contains corporate proprietary information. If you have received it by mistake, please notify us immediately by reply e-mail and delete this e-mail and its attachments from your system.
Thank You.

Finally I setup two machines for primary and backup using the following link.
http://wiki.postgresql.org/wiki/Streaming_Replication

But after starting the backup I got the following messages and postgresql service doesn't start.

2011-04-22 14:37:54 ETC/GMT LOG: database system was shut down in recovery at 2011-04-22 14:23:02 ETC/GMT
2011-04-22 14:37:54 ETC/GMT DEBUG: restore_command = 'cp /opt/PostgreSQL/9.0/data/archive/%f "%p"'
2011-04-22 14:37:54 ETC/GMT DEBUG: standby_mode = 'on'
2011-04-22 14:37:54 ETC/GMT DEBUG: primary_conninfo = 'host=10.20.9.181 port=5432 user=postgres password=Password1'
2011-04-22 14:37:54 ETC/GMT DEBUG: trigger_file = '/tmp/trigger'
2011-04-22 14:37:54 ETC/GMT LOG: entering standby mode
2011-04-22 14:37:54 ETC/GMT DEBUG: executing restore command "cp /opt/PostgreSQL/9.0/data/archive/000000010000000000000007 "pg_xlog/RECOVERYXLOG""
2011-04-22 14:37:54 ETC/GMT LOG: restored log file "000000010000000000000007" from archive
2011-04-22 14:37:54 ETC/GMT DEBUG: got WAL segment from archive
2011-04-22 14:37:54 ETC/GMT LOG: record with zero length at 0/700011C
2011-04-22 14:37:54 ETC/GMT LOG: record with zero length at 0/700011C
2011-04-22 14:37:54 ETC/GMT DEBUG: find_in_dynamic_libpath: trying "/opt/PostgreSQL/9.0/lib/postgresql/libpqwalreceiver"
2011-04-22 14:37:54 ETC/GMT DEBUG: find_in_dynamic_libpath: trying "/opt/PostgreSQL/9.0/lib/postgresql/libpqwalreceiver.so"
2011-04-22 14:37:55 ETC/GMT FATAL: the database system is starting up
2011-04-22 14:37:55 ETC/GMT DEBUG: shmem_exit(1): 0 callbacks to make
2011-04-22 14:37:55 ETC/GMT DEBUG: proc_exit(1): 1 callbacks to make
2011-04-22 14:37:55 ETC/GMT DEBUG: exit(1)
2011-04-22 14:37:55 ETC/GMT DEBUG: shmem_exit(-1): 0 callbacks to make
2011-04-22 14:37:55 ETC/GMT DEBUG: proc_exit(-1): 0 callbacks to make
2011-04-22 14:37:55 ETC/GMT DEBUG: forked new backend, pid=1256 socket=8
2011-04-22 14:37:55 ETC/GMT DEBUG: reaping dead processes
2011-04-22 14:37:55 ETC/GMT DEBUG: server process (PID 1256) exited with exit code 1

Please help me with this.

Regards,

Rajib Deka
SIEMENS Ltd.
Robert V Chandran Tower, First Floor, West Wing,
#149, Velechery Tambaram Main Road, Pallikaranai, Chennai-100, INDIA.
www.siemens.com

Mob: +91-9176780669 | E-Mail: rajib(dot)deka(at)siemens(dot)com

-----Original Message-----
From: Deka, Rajib IN MAA SL
Sent: Friday, April 22, 2011 12:26 PM
To: 'Cédric Villemain'; Robert Haas
Cc: Simon Riggs; pgsql-hackers
Subject: RE: [HACKERS] Re: database system identifier differs between the primary and standby

Thanks a lot.
I got the idea now.

Rajib Deka
SIEMENS Ltd.
Robert V Chandran Tower, First Floor, West Wing,
#149, Velechery Tambaram Main Road, Pallikaranai, Chennai-100, INDIA.
www.siemens.com

Mob: +91-9176780669 | E-Mail: rajib(dot)deka(at)siemens(dot)com

-----Original Message-----
From: Cédric Villemain [mailto:cedric(dot)villemain(dot)debian(at)gmail(dot)com]
Sent: Thursday, April 21, 2011 9:56 PM
To: Robert Haas
Cc: Simon Riggs; Deka, Rajib IN MAA SL; pgsql-hackers
Subject: Re: [HACKERS] Re: database system identifier differs between the primary and standby

2011/4/21 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Thu, Apr 21, 2011 at 6:38 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> On Thu, Apr 21, 2011 at 10:31 AM, rajibdk <rajib(dot)deka(at)siemens(dot)com> wrote:
>>> What does that database system identifier means? Is it related to DB
>>> transactions' or unique to a version?
>>
>> Regrettably, it means you didn't follow the documented procedure.
>>
>> It isn't possible to do it any other way, so those questions are a distraction.
>
> I think they are perfectly good questions. If someone is trying to
> understand how our product works, we should encourage that.

Agree those are perfectly good question to ask on -general. (but on
hackers it looks excessive)
Rajib: this is relative to initdb, it produces one key to be able to
check later if postgresql is restoring the good files.
(
from sources :
/*
* Unique system identifier --- to ensure we match up xlog files with the
* installation that produced them.
*/
)

Robert, Please don't add confusion to your signature : PostgreSQL is a
community project not an enterprise product.

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support

Important notice: This e-mail and any attachment there to contains corporate proprietary information. If you have received it by mistake, please notify us immediately by reply e-mail and delete this e-mail and its attachments from your system.
Thank You.

"Deka, Rajib IN MAA SL" <rajib(dot)deka(at)siemens(dot)com> wrote:

> Finally I setup two machines for primary and backup using the
> following link.
> http://wiki.postgresql.org/wiki/Streaming_Replication
>
> But after starting the backup I got the following messages and
> postgresql service doesn't start.

> 2011-04-22 14:37:54 ETC/GMT DEBUG: executing restore command "cp
> /opt/PostgreSQL/9.0/data/archive/000000010000000000000007
> "pg_xlog/RECOVERYXLOG""
> 2011-04-22 14:37:54 ETC/GMT LOG: restored log file
> "000000010000000000000007"
> from archive
> 2011-04-22 14:37:54 ETC/GMT DEBUG: got WAL segment from archive
> 2011-04-22 14:37:54 ETC/GMT LOG: record with zero length at
> 0/700011C
> 2011-04-22 14:37:54 ETC/GMT LOG: record with zero length at
> 0/700011C

You will probably get better results starting a new thread on
pgsql-general with details about how you have this set up, in
addition to log entries such as you have shown. The pgsql-hackers
list is really not the right place to discuss this, and many people
selectively read messages based on the subject line, so a misleading
subject line may prevent the people best placed to help you from
reading the post.

-Kevin