Streaming Replication woes

*
*
On Fri, Nov 4, 2011 at 8:20 PM, Sean Patronis <spatronis@add123.com> wrote:

I am running Postgres 9.1

I have followed the howto here:
http://wiki.postgresql.org/wiki/Streaming_Replication

I am attempting to replicate an existing database.

On the Master, I get the following error in the postgres log file:

FATAL: must be replication role to start walsender

On the slave I get this:

FATAL: could not connect to the primary server: FATAL: must be
replication role to start walsender

I have googled both of those log entries to no avail.

note that the sender process on the master is not running.

What simple step am I missing?

Step 3 from wiki. and reload PG-instance

- *3.* Set up connections and authentication so that the standby server
can successfully connect to the *replication* pseudo-database on the
primary.

$ $EDITOR postgresql.conf

listen_addresses = '192.168.0.10'

$ $EDITOR pg_hba.conf

# The standby server must have superuser access privileges.
host replication postgres 192.168.0.20/22 trust

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

On 11/04/2011 10:59 AM, Thom Brown wrote:

On 4 November 2011 16:50, Sean Patronis<spatronis@add123.com> wrote:

I am running Postgres 9.1

I have followed the howto here:
http://wiki.postgresql.org/wiki/Streaming_Replication

I am attempting to replicate an existing database.

On the Master, I get the following error in the postgres log file:

FATAL: must be replication role to start walsender

On the slave I get this:
FATAL: could not connect to the primary server: FATAL: must be replication
role to start walsender

I have googled both of those log entries to no avail.

note that the sender process on the master is not running.

What simple step am I missing?

What have you got primary_conninfo set to on the standby in
recovery.conf? Are you trying to use a regular user? If so, you will
have to grant it REPLICATION permissions on the primary, which was
introduced in 9.1.

The primary_conninfo in the recovery.conf is set to :
primary_conninfo = 'host=192.168.127.12 port=5432 user=postgres'

So I should just have to grant the postgres user REPLICATION
permissions, and be good?

On 4 November 2011 17:19, Sean Patronis <spatronis@add123.com> wrote:

On 11/04/2011 10:59 AM, Thom Brown wrote:

On 4 November 2011 16:50, Sean Patronis<spatronis@add123.com>  wrote:

I am running Postgres 9.1

I have followed the howto here:
http://wiki.postgresql.org/wiki/Streaming_Replication

I am attempting to replicate an existing database.

On the Master, I get the following error in the postgres log file:

FATAL:  must be replication role to start walsender

On the slave I get this:
FATAL:  could not connect to the primary server: FATAL:  must be
replication
role to start walsender

I have googled both of those log entries to no avail.

note that the sender process on the master is not running.

What simple step am I missing?

What have you got primary_conninfo set to on the standby in
recovery.conf?  Are you trying to use a regular user?  If so, you will
have to grant it REPLICATION permissions on the primary, which was
introduced in 9.1.

The primary_conninfo in the recovery.conf is set to :
primary_conninfo      = 'host=192.168.127.12 port=5432 user=postgres'

So I should just have to grant the postgres user REPLICATION permissions,
and be good?

Well the postgres user will be a superuser, so doesn't need to be
granted such a permission.

Have you got the necessary entry in pg_hba.conf as Raghavendra
highlighted? It will need configuring to accept a connection from the
IP address of the standby server.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Nov 4, 2011 at 2:56 PM, Raghavendra
<raghavendra.rao@enterprisedb.com> wrote:

# The standby server must have superuser access privileges.
host replication postgres 192.168.0.20/22 trust

I strongly recommend you don't use those settings, since they result
in no security at all.

It won't block you from getting replication working, but it won't
block anyone else either.

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

On 11/04/2011 11:25 AM, Thom Brown wrote:

On 4 November 2011 17:19, Sean Patronis<spatronis@add123.com> wrote:

On 11/04/2011 10:59 AM, Thom Brown wrote:

On 4 November 2011 16:50, Sean Patronis<spatronis@add123.com> wrote:

I am running Postgres 9.1

I have followed the howto here:
http://wiki.postgresql.org/wiki/Streaming_Replication

I am attempting to replicate an existing database.

On the Master, I get the following error in the postgres log file:

FATAL: must be replication role to start walsender

On the slave I get this:
FATAL: could not connect to the primary server: FATAL: must be
replication
role to start walsender

I have googled both of those log entries to no avail.

note that the sender process on the master is not running.

What simple step am I missing?

What have you got primary_conninfo set to on the standby in
recovery.conf? Are you trying to use a regular user? If so, you will
have to grant it REPLICATION permissions on the primary, which was
introduced in 9.1.

The primary_conninfo in the recovery.conf is set to :
primary_conninfo = 'host=192.168.127.12 port=5432 user=postgres'

So I should just have to grant the postgres user REPLICATION permissions,
and be good?

Well the postgres user will be a superuser, so doesn't need to be
granted such a permission.

Have you got the necessary entry in pg_hba.conf as Raghavendra
highlighted? It will need configuring to accept a connection from the
IP address of the standby server.

I have both these entries on the pg_hba.conf Master server:
host replication all 192.168.127.6/32 trust
host all all 192.168.127.6/32 trust

and still cannot get replication to start.

I can make normal postgresql database connections fine to the master
database from the slave with these pg_hba.conf settings, so it is surely
not a firewall issue.

On 11/04/2011 11:31 AM, Simon Riggs wrote:

On Fri, Nov 4, 2011 at 2:56 PM, Raghavendra
<raghavendra.rao@enterprisedb.com> wrote:

# The standby server must have superuser access privileges.
host replication postgres 192.168.0.20/22 trust

I strongly recommend you don't use those settings, since they result
in no security at all.

It won't block you from getting replication working, but it won't
block anyone else either.

I agree, you should use the default trust of that network. But at this
point, I just want it to replicate in this test environment. I can lock
it down after it is working.

Sean,

I saw your email on PostgreSQL general list about replication.

We are using 9.1.1 and were having the same issue, unable to setup
replication even though following wiki precisely.  I was able to
setup replication following wiki steps using PG 9.0.3, but not 9.1.1.
Then I found this page:
http://www.postgresql.org/docs/9.1/static/role-attributes.html

where it says:

initiating replication
A role must explicitly be given permission to initiate streaming
replication. A role used for streaming replication must always have
LOGIN permission as well. To create such a role, use CREATE ROLE name
REPLICATION LOGIN.

Does that mean "postgres" superuser is no longer able to also be a
replication user in 9.1? If so this is not yet updated on the
Replication wiki.

I went to the master and created a new user specifically for replication:

CREATE ROLE REPLICATOR REPLICATION LOGIN PASSWORD '....';

Then changed my master's pg_hba.conf, and slave's recovery.conf to
reference the new user "replicator" instead of "postgres", and I no
longer get the same error, and our replication is working.

Hope this helps!

Thanks,
____
Konstantin Gredeskoul
Principal Ruby Engineer
ModCloth, Inc. San Francisco

On Mon, Nov 7, 2011 at 8:41 AM, Konstantin Gredeskoul <kigster@gmail.com> wrote:

Does that mean "postgres" superuser is no longer able to also be a
replication user in 9.1?  If so this is not yet updated on the
Replication wiki.

No. Superuser has the replication privilege by default. But if you granted
the "postgres" user the superuser privilege by using ALTER ROLE, the
replication privilege would not be granted.

You can check whether the "postgres" user has the replication privilege
by executing "\du" command.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

Dear all,

For our customer in Dubai, we are looking for references of PostgreSQL implementations in the Middle East, preferably in the GCC countries, preferably in the United Arab Emirates (Dubai / Abu Dhabi).

Please help me in this matter.

Thanks in advance!

Best regards,

Jan Geluk
Managing Director

Collis UAE
PO Box 341060
Dubai Silicon Oasis
M: + 971 50 674 2380
www.collisdubai.com
 
Collis Netherlands
T: + 31 71 581 36 36
F: + 31 71 581 36 30
M: + 31 65 112 16 07
www.collis.nl
This e-mail message is confidential and may be protected by legal privilege. If you are not the intended recipient, any disclosure, distribution or forwarding, copying or printing of this message is strictly prohibited. If you receive this message in error please return it to the sender, and delete your copy from your system.

On Sun, Nov 6, 2011 at 11:04 PM, Jan Geluk (Collis) <geluk@collis.nl> wrote:

Dear all,

For our customer in Dubai, we are looking for references of PostgreSQL implementations in the Middle East, preferably in the GCC countries, preferably in the United Arab Emirates (Dubai / Abu Dhabi).

Please help me in this matter.

Thanks in advance!

some quick googling turned out this company in the UAE that does
postgres hosting...maybe you might find some good contacts there:
http://www.uaehosting.org/

this guy looks like a good contact:
http://www.kannayath.com/

merlin

On Tue, Nov 8, 2011 at 3:45 AM, Konstantin Gredeskoul <kigster@gmail.com> wrote:

The user 'postgres' was created using standard database installation
procedure.  It has superuser, but does not include an explicit replication
role:

my_db=# \du
                      List of roles
Role name  |            Attributes             | Member of
------------+-----------------------------------+-----------
postgres   | Superuser, Create role, Create DB | {}
replicator | Replication                       | {}

Did you restore the database from the dump file created by pg_dumpall
in 9.0 instead of 9.1? If yes, that dump file would contain the "ALTER ROLE
postgres" command and revoke the replication privilege, I guess.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

Konstantin,

Just back from some time off and read this reply.

This was exactly the issue. The superuser "postgres" did not have this
role since this was a dump/restore/upgrade from postgres 8.4. I just
created a new Role and user, and it all worked!

Thanks!

--Sean