Fatal error after starting postgres : sys identifiers must be different

Indrajit Roychoudhury escribi�:

Hi Alvaro,

Could you please let me know what does the error "system identifiers are
same" mean?

Please post to the hackers list, perhaps CC Andres Freund. This list is
not appropriate.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-www mailing list (pgsql-www@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-www

Hi,

Could you please let me know what does the error "system identifiers are
same" mean? Below is the snapshot from one of the masters.
I am setting up BDR as per the wiki
http://wiki.postgresql.org/wiki/BDR_User_Guide#Initial_setup
and source @
git://git.postgresql.org/git/users/andresfreund/postgres.git

irc1@ubuntuirc:~/bdr2/postgres-d6ed89e/postgres-bdr-bin/bin$ ./postgres -D
~/bdr2/postgres-d6ed89e/postgres-bdr-bin/bin/data2/
LOG: bgworkers, connection: dbname=testdb2
LOG: option: dbname, val: testdb2
LOG: registering background worker: bdr apply: ubuntuirc2
LOG: loaded library "bdr"
LOG: database system was shut down at 2013-03-17 10:56:52 PDT
LOG: doing logical startup from 0/17B6410
LOG: starting up replication identifier with ckpt at 0/17B6410
LOG: autovacuum launcher started
LOG: starting background worker process "bdr apply: ubuntuirc2"
LOG: database system is ready to accept connections
LOG: bdr apply: ubuntuirc2 initialized on testdb2, remote dbname=testdb2
replication=true fallback_application_name=bdr
FATAL: system identifiers must differ between the nodes
DETAIL: Both system identifiers are 5856368744762683487.
LOG: worker process: bdr apply: ubuntuirc2 (PID 16712) exited with exit
code 1
^CLOG: received fast shutdown request
LOG: aborting any active transactions
LOG: autovacuum launcher shutting down
LOG: shutting down

pgcontrol_data outputs different database system ids for the two nodes. So
don't understand why it says identifiers are same.
postgresql.conf content in one of the masters is like this-

/////////////////
shared_preload_libraries = 'bdr'
bdr.connections = 'ubuntuirc2'
bdr.ubuntuirc2.dsn = 'dbname=testdb2'
/////////////////

Two nodes are ubuntuirc and ubuntuirc2. Above is the output of the
postgresql.conf from ubuntuirc.

Any help on this will be appreciated.

Thanks.

On Thu, Jul 18, 2013 at 9:48 AM, Indrajit Roychoudhury <
indrajit.roychoudhury@gmail.com> wrote:

On Fri, Jul 19, 2013 at 5:02 AM, Indrajit Roychoudhury
<indrajit.roychoudhury@gmail.com> wrote:

Could you please let me know what does the error "system identifiers are
same" mean? Below is the snapshot from one of the masters.
I am setting up BDR as per the wiki
http://wiki.postgresql.org/wiki/BDR_User_Guide#Initial_setup
and source @
git://git.postgresql.org/git/users/andresfreund/postgres.git

irc1@ubuntuirc:~/bdr2/postgres-d6ed89e/postgres-bdr-bin/bin$ ./postgres -D
~/bdr2/postgres-d6ed89e/postgres-bdr-bin/bin/data2/
LOG: bgworkers, connection: dbname=testdb2
LOG: option: dbname, val: testdb2
LOG: registering background worker: bdr apply: ubuntuirc2
LOG: loaded library "bdr"
LOG: database system was shut down at 2013-03-17 10:56:52 PDT
LOG: doing logical startup from 0/17B6410
LOG: starting up replication identifier with ckpt at 0/17B6410
LOG: autovacuum launcher started
LOG: starting background worker process "bdr apply: ubuntuirc2"
LOG: database system is ready to accept connections
LOG: bdr apply: ubuntuirc2 initialized on testdb2, remote dbname=testdb2
replication=true fallback_application_name=bdr
FATAL: system identifiers must differ between the nodes
DETAIL: Both system identifiers are 5856368744762683487.

I am not the best specialist about logical replication, but as it
looks to be a requirement to have 2 nodes with different system
identifiers, you shouldn't link 1 node to another node whose data
folder has been created from the base backup of the former (for
example create the 2nd node based on a base backup of the 1st node).
The error returned here would mean so.

LOG: worker process: bdr apply: ubuntuirc2 (PID 16712) exited with exit
code 1
^CLOG: received fast shutdown request
LOG: aborting any active transactions
LOG: autovacuum launcher shutting down
LOG: shutting down

pgcontrol_data outputs different database system ids for the two nodes. So
don't understand why it says identifiers are same.

Are you sure? Please re-ckeck.

postgresql.conf content in one of the masters is like this-

/////////////////
shared_preload_libraries = 'bdr'
bdr.connections = 'ubuntuirc2'
bdr.ubuntuirc2.dsn = 'dbname=testdb2'
/////////////////

Two nodes are ubuntuirc and ubuntuirc2. Above is the output of the
postgresql.conf from ubuntuirc.

If this behavior is confirmed, I think that this bug should be
reported directly to Andres and not this mailing list, just because
logical replication is not integrated into Postgres core as of now.
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

*I am not the best specialist about logical replication, but as it
looks to be a requirement to have 2 nodes with different system
identifiers, you shouldn't link 1 node to another node whose data
folder has been created from the base backup of the former (for
example create the 2nd node based on a base backup of the 1st node).
The error returned here would mean so.*
**
*- *I didn't create the /data folders from any backup for both the nodes;
they were created fresh by initdb individually. Is there anything specific
that I need to do apart from initdb?

*pgcontrol_data outputs different database system ids for the two nodes. So

don't understand why it says identifiers are same.

Are you sure? Please re-ckeck.*
**
*- *I re-verified and they are different. Pasting the snapshots below-

irc1@ubuntuirc:~/bdr2/postgres-d6ed89e/postgres-bdr-bin/bin$
./pg_controldata ./data2/
pg_control version number: 937
Catalog version number: 201305061
Database system identifier: *5856368744762683487*
Database cluster state: in production
....

irc2@ubuntuirc2:~/bdr/postgres-bin/bin$ ./pg_controldata ./data/
pg_control version number: 937
Catalog version number: 201305061
Database system identifier: *5901663435516996514*
Database cluster state: in production

Also, am pasting the below snapshot to let you know that postgres doesn't
seem to be the su; in case this problem is due to that.

irc1@ubuntuirc:~/bdr2/postgres-d6ed89e/postgres-bdr-bin/bin$ ./psql testdb2
psql (9.3beta1)
Type "help" for help.
testdb2=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
irc1 | Superuser, Create role, Create DB, Replication | {}

irc2@ubuntuirc2:~/bdr/postgres-bin/bin$ ./psql testdb2
psql (9.3beta1)
Type "help" for help.
testdb2=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
irc2 | Superuser, Create role, Create DB, Replication | {}

Please let me know which mailing list will be more appropriate to raise my
concern regarding this postgres dev flavor.
I am yet to receive any response from Andres.

Thanks.

On Thu, Jul 18, 2013 at 3:31 PM, Michael Paquier
<michael.paquier@gmail.com>wrote:

Hi!

On 2013-07-19 07:31:07 +0900, Michael Paquier wrote:

If this behavior is confirmed, I think that this bug should be
reported directly to Andres and not this mailing list, just because
logical replication is not integrated into Postgres core as of now.

I think I agree, although I don't know where to report it, but to me
personally, so far. Hackers seems to be the wrong crowd for it, given
most of the people on it haven't even heard of bdr, much less read its
code.
We're definitely planning to propose it for community inclusion in some
form, but there are several rather large preliminary steps (like getting
changeset extraction in) that need to be done first.

Not sure what's best.

On 2013-07-19 07:31:07 +0900, Michael Paquier wrote:

On Fri, Jul 19, 2013 at 5:02 AM, Indrajit Roychoudhury
<indrajit.roychoudhury@gmail.com> wrote:

Could you please let me know what does the error "system identifiers are
same" mean? Below is the snapshot from one of the masters.
I am setting up BDR as per the wiki
http://wiki.postgresql.org/wiki/BDR_User_Guide#Initial_setup
and source @
git://git.postgresql.org/git/users/andresfreund/postgres.git

irc1@ubuntuirc:~/bdr2/postgres-d6ed89e/postgres-bdr-bin/bin$ ./postgres -D
~/bdr2/postgres-d6ed89e/postgres-bdr-bin/bin/data2/
LOG: bgworkers, connection: dbname=testdb2
LOG: option: dbname, val: testdb2
LOG: registering background worker: bdr apply: ubuntuirc2
LOG: loaded library "bdr"
LOG: database system was shut down at 2013-03-17 10:56:52 PDT
LOG: doing logical startup from 0/17B6410
LOG: starting up replication identifier with ckpt at 0/17B6410
LOG: autovacuum launcher started
LOG: starting background worker process "bdr apply: ubuntuirc2"
LOG: database system is ready to accept connections
LOG: bdr apply: ubuntuirc2 initialized on testdb2, remote dbname=testdb2
replication=true fallback_application_name=bdr
FATAL: system identifiers must differ between the nodes
DETAIL: Both system identifiers are 5856368744762683487.

I am not the best specialist about logical replication, but as it
looks to be a requirement to have 2 nodes with different system
identifiers, you shouldn't link 1 node to another node whose data
folder has been created from the base backup of the former (for
example create the 2nd node based on a base backup of the 1st node).
The error returned here would mean so.

Yes, that's correct.

LOG: worker process: bdr apply: ubuntuirc2 (PID 16712) exited with exit
code 1
^CLOG: received fast shutdown request
LOG: aborting any active transactions
LOG: autovacuum launcher shutting down
LOG: shutting down

pgcontrol_data outputs different database system ids for the two nodes. So
don't understand why it says identifiers are same.

Are you sure? Please re-ckeck.

postgresql.conf content in one of the masters is like this-

/////////////////
shared_preload_libraries = 'bdr'
bdr.connections = 'ubuntuirc2'
bdr.ubuntuirc2.dsn = 'dbname=testdb2'
/////////////////

Two nodes are ubuntuirc and ubuntuirc2. Above is the output of the
postgresql.conf from ubuntuirc.

The problem seems to be that your dsn doesn't include a hostname but
only a database name. So, what probably happens is both your hosts try
to connect to themselves since connecting to the local host is the
default when no hostname is specified. Which is one of the primary
reasons the requirement for differing system identifiers exist...

Greetings,

Andres Freund

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

One more change was required to add both the users in each node's db as
super users and replication started!!

Thanks.

On Thu, Jul 18, 2013 at 5:35 PM, Andres Freund <andres@anarazel.de> wrote:

Hi Indrajit

/////////////////
shared_preload_libraries = 'bdr'
bdr.connections = 'ubuntuirc2'
bdr.ubuntuirc2.dsn = 'dbname=testdb2'
/////////////////

I think, you need to set *bdr.ubuntuirc2.dsn* properly including host

bdr.ubuntuirc2.dsn = 'host=<ip> user=postgres dbname=testdb2'

please try this, I hope it will work .

Thanks & Regards,
Amul

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Re-Fatal-error-after-starting-postgres-sys-identifiers-must-be-different-tp5764305p5764374.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice