[GENERAL] Streaming Replication woes
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?
Re: [GENERAL] Streaming Replication woes
On 11/04/2011 10:59 AM, Thom Brown wrote: On 4 November 2011 16:50, Sean Patronis 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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Streaming Replication woes
On 11/04/2011 11:25 AM, Thom Brown wrote: On 4 November 2011 17:19, Sean Patronis wrote: On 11/04/2011 10:59 AM, Thom Brown wrote: On 4 November 2011 16:50, Sean Patroniswrote: 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: hostreplication all 192.168.127.6/32 trust hostall 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [BULK] Re: [GENERAL] Streaming Replication woes
On 11/04/2011 11:31 AM, Simon Riggs wrote: On Fri, Nov 4, 2011 at 2:56 PM, Raghavendra 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [BULK] Re: [GENERAL] Streaming Replication woes
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 On 11/06/2011 06:41 PM, Konstantin Gredeskoul wrote: 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
