Thread: 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?
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?
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
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 inthe 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 messagein 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 inthe 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 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 >