Обсуждение: no pg_hba.conf entry for replication connection from host

Three things:
1.
0.0.0.0 has the specific meaning "unspecified". So you really should
enter the specific ip address for the slave in the master pg_hba.conf.

2.
Is  ${REP_USER} defined on BOTH master & slave?
IS  ${REP_USER} defined as a valid user that can login in the database?

3.
I do not know of any case where I've seen a variable in a pg_hba.conf,
nor is it defined as a valid option in the documentation. You should
specify the replication user directly, which is usually postgres.

eg:
host    replication     postgres      999.1.1.1/32               md5

note: replace 999.1.1.1/32 with actual ip of slave.
After you have made changes:
in psql
SELECT pg_reload_conf();

Then check the postgres log on the master to verify connectcion.


On 4/9/15, Volkan Unsal <spocksplanet@gmail.com> wrote:
> I have been configuring a slave server that needs to connect to the host.
> Both the master and the standby servers have a pg_hba.conf that looks like
> this:
>
>     # Allow anyone to connect remotely so long as they have a valid
> username and
>     # password.
>     host    replication     ${REP_USER}     0.0.0.0/0               md5
>     host    ${DB_NAME}      ${DB_USER}      0.0.0.0/0               md5
>
> This should allow access from every IP address, right? Evidently, though,
> the standby server cannot connect using the REP_USER credentials via
> `primary_conninfo`
>
>     primary_conninfo = 'host=${MASTER_PORT_5432_TCP_ADDR} port=5432
> user=${REP_USER} password=${REP_PASS}'
>
> I know this doesn't work because I never see in my logs:
>
>     LOG:  streaming replication successfully connected to primary
>
> Instead, what I see is
>
>      LOG:  database system was interrupted; last known up at 2015-04-09
> 16:35:05 GMT
>      LOG:  entering standby mode
>      LOG:  redo starts at 0/E000028
>      LOG:  consistent recovery state reached at 0/E0000F0
>
>
> What am I doing wrong?
>
>
> --
> *Volkan Unsal*
> *web and mobile development*
> volkanunsal.com <http://bit.ly/1h1ebjy>
>


--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

On 04/09/2015 10:15 AM, Volkan Unsal wrote:
> I have been configuring a slave server that needs to connect to the
> host. Both the master and the standby servers have a pg_hba.conf that
> looks like this:
>
>      # Allow anyone to connect remotely so long as they have a valid
> username and
>      # password.
>      host    replication     ${REP_USER} 0.0.0.0/0 <http://0.0.0.0/0>
>              md5
>      host    ${DB_NAME}      ${DB_USER} 0.0.0.0/0 <http://0.0.0.0/0>
>            md5
>
> This should allow access from every IP address, right? Evidently,
> though, the standby server cannot connect using the REP_USER credentials
> via `primary_conninfo`
>
>      primary_conninfo = 'host=${MASTER_PORT_5432_TCP_ADDR} port=5432
> user=${REP_USER} password=${REP_PASS}'
>
> I know this doesn't work because I never see in my logs:
>
>      LOG:  streaming replication successfully connected to primary
>
> Instead, what I see is
>
>       LOG:  database system was interrupted; last known up at 2015-04-09
> 16:35:05 GMT
>       LOG:  entering standby mode
>       LOG:  redo starts at 0/E000028
>       LOG:  consistent recovery state reached at 0/E0000F0
>
>
> What am I doing wrong?

What interface is the primary database listening on?

http://www.postgresql.org/docs/9.4/interactive/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS

listen_addresses (string)

Can you connect remotely from the standby using psql?



>
>
> --
> *Volkan Unsal*
> /web and mobile development/
> volkanunsal.com <http://bit.ly/1h1ebjy>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/09/2015 12:05 PM, Volkan Unsal wrote:
> HI Adrian,
>
>     Can you connect remotely from the standby using psql?
>
>
>
> Yes, I can connect directly from the standby using psql and DB_USER and
> DB_PASS.

And you are sure the REP_USER is being correctly substituted in and that
it has the REPLICATION attribute.

Have you tried the  primary_conninfo  with the values directly entered?

http://www.postgresql.org/docs/9.4/interactive/sql-createrole.html

postgres@test=# create role rep_user with login replication;
CREATE ROLE

postgres@test=# \du rep_user
             List of roles
  Role name | Attributes  | Member of
-----------+-------------+-----------
  rep_user  | Replication | {}


Also what file do you have  primary_conninfo in?

>
>
>
>
> --
> *Volkan Unsal*
> /web and mobile development/
> volkanunsal.com <http://bit.ly/1h1ebjy>


--
Adrian Klaver
adrian.klaver@aklaver.com

So did you make the following entry in pg_hba,conf?

host    replication     rep        104.131.66.183/32            md5

Is rep a valid postgres user in the cluster?

Did you remember to do:
SELECT pg_reload_conf();

After making the changes to pg_hba.conf?


On 4/9/15, Volkan Unsal <spocksplanet@gmail.com> wrote:
> HI Adrian,
>
>
>
>> Can you connect remotely from the standby using psql?
>
>
>
> Yes, I can connect directly from the standby using psql and DB_USER and
> DB_PASS.
>
>
>
>
> --
> *Volkan Unsal*
> *web and mobile development*
> volkanunsal.com <http://bit.ly/1h1ebjy>
>


--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

On 04/09/2015 12:32 PM, Volkan Unsal wrote:
> Hi Adrian,
>
> I can confirm that the REP_USER is a role with replication attribute in
> the master
>
>      Role name  |                   Attributes                   | Member of
>
> -------------+------------------------------------------------+-----------
>     postgres      | Superuser, Create role, Create DB, Replication | {}
>     replication   | Replication                                   +| {}
>                         | 4 connections                                |
>
>     Also what file do you have  primary_conninfo in?
>
>
>
> This setting is in ${PGDATA}/recovery.conf and looks like this:
>
>     primary_conninfo = 'host=${MASTER_PORT_5432_TCP_ADDR} port=5432
>     user=${REP_USER} password=${REP_PASS}'

Best guess is something is not working properly in turning the ${}
variables into actual values.

What happens if you do not use the variable notation and use the actual
values in the above string?

Also what do the standby server logs show while you are trying to connect?

>
>
>
>
> On Thu, Apr 9, 2015 at 3:16 PM, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 04/09/2015 12:05 PM, Volkan Unsal wrote:
>
>         HI Adrian,
>
>              Can you connect remotely from the standby using psql?
>
>
>
>         Yes, I can connect directly from the standby using psql and
>         DB_USER and
>         DB_PASS.
>
>
>     And you are sure the REP_USER is being correctly substituted in and
>     that it has the REPLICATION attribute.
>
>     Have you tried the  primary_conninfo  with the values directly entered?
>
>     http://www.postgresql.org/__docs/9.4/interactive/sql-__createrole.html
>     <http://www.postgresql.org/docs/9.4/interactive/sql-createrole.html>
>
>     postgres@test=# create role rep_user with login replication;
>     CREATE ROLE
>
>     postgres@test=# \du rep_user
>                  List of roles
>       Role name | Attributes  | Member of
>     -----------+-------------+----__-------
>       rep_user  | Replication | {}
>
>
>     Also what file do you have  primary_conninfo in?
>
>
>
>
>
>
>         --
>         *Volkan Unsal*
>         /web and mobile development/
>         volkanunsal.com <http://volkanunsal.com> <http://bit.ly/1h1ebjy>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>
>
> --
> *Volkan Unsal*
> /web and mobile development/
> volkanunsal.com <http://bit.ly/1h1ebjy>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/09/2015 12:39 PM, Volkan Unsal wrote:
> Hi Melvin,
>
> When I make this entry
>
>     host    replication     rep 104.131.66.183/32
>     <http://104.131.66.183/32>            md5
>
>
> It complains that it cannot have both CIDR and mask number and fails to
> start –– I assume it's referring to /32 –– and when I remove that it
> starts alright, but it doesn't accept replication connections from that
> IP address.

The above looks alright, I would expect the complaint if you did
something like this:

host  replication  rep 104.131.66.183/32  255.255.255.255   md5

>
> (Also I changed REP_USER to "replication" in latest examples, but it's
> consistent in both master and slave.)

The other variables where changed also?

>
> | Did you remember to do: SELECT pg_reload_conf();
>
> Yes, in a manner of speaking. The database is in a Docker container, so
> I'm recreating the container everytime I change something in the
> configuration. :)


Hmm, that is another variable in the mix. So more information is in order.

1) What version(s) of Postgres are you using on either end?

2) Are the primary and the standby in different containers?

3) What is the container/machine/network layout?

>
>
>
>
> On Thu, Apr 9, 2015 at 3:20 PM, Melvin Davidson <melvin6925@gmail.com
> <mailto:melvin6925@gmail.com>> wrote:
>
>     So did you make the following entry in pg_hba,conf?
>
>     host    replication     rep 104.131.66.183/32
>     <http://104.131.66.183/32>            md5
>
>     Is rep a valid postgres user in the cluster?
>
>     Did you remember to do:
>     SELECT pg_reload_conf();
>
>     After making the changes to pg_hba.conf?
>
>
>     On 4/9/15, Volkan Unsal <spocksplanet@gmail.com
>     <mailto:spocksplanet@gmail.com>> wrote:
>     > HI Adrian,
>     >
>     >
>     >
>     >> Can you connect remotely from the standby using psql?
>     >
>     >
>     >
>     > Yes, I can connect directly from the standby using psql and DB_USER and
>     > DB_PASS.
>     >
>     >
>     >
>     >
>     > --
>      > *Volkan Unsal*
>      > *web and mobile development*
>      > volkanunsal.com <http://volkanunsal.com> <http://bit.ly/1h1ebjy>
>      >
>
>
>     --
>     *Melvin Davidson*
>     I reserve the right to fantasize.  Whether or not you
>     wish to share my fantasy is entirely up to you.
>
>
>
>
> --
> *Volkan Unsal*
> /web and mobile development/
> volkanunsal.com <http://bit.ly/1h1ebjy>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/09/2015 12:46 PM, Volkan Unsal wrote:
> Hi Amit,
>
> My postgresql.conf file in the standby server looks like this:
>
>     wal_level = hot_standby
>     hot_standby = on
>     max_standby_streaming_delay = 15min
>
>
> In the master server, it looks like this:
>
>     listen_addresses = '*'
>     wal_level = hot_standby
>     max_wal_senders = 10
>     max_connections=100
>     checkpoint_segments = 8
>     wal_keep_segments = 8
>     archive_mode = on
>     archive_command = 'cp %p /var/lib/postgresql/archive/%f'


So in your recovery.conf is standby_mode set to on?

--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/09/2015 01:00 PM, Volkan Unsal wrote:
> Hi Adrian,
>
>     1) What version(s) of Postgres are you using on either end?
>
>
> I'm using Postgres 9:3.
>
>
>     2) Are the primary and the standby in different containers?
>
>
> Yes, and they are on different servers as well.
>
>     3) What is the container/machine/network layout?
>
>
> I created a gist of all the files in my setup.
>
> https://gist.github.com/volkanunsal/ad2173e2649393fcd3b6
>
> The init-slave.sh and init-master.sh scripts are executed before the
> server is started, so that's where I do all my preprocessing of conf
> files. I checked the results and the substituted values are indeed correct.


Well if I am understanding. This:

primary_conninfo = 'host=${MASTER_PORT_5432_TCP_ADDR} port=5432

is getting translated to:

host=0.0.0.0 port=5432

Now the primary can receive connections from 0.0.0.0, which basically
means it can receive from the Internet. The problem is that the standby
can not connect to 0.0.0.0, that would mean it is connecting to the
whole Internet. You will need to provide either the actual IP for the
primary or its hostname.

>
>
>
>
>
>
> --
> *Volkan Unsal*
> /web and mobile development/
> volkanunsal.com <http://bit.ly/1h1ebjy>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/09/2015 01:14 PM, Volkan Unsal wrote:
> Oops. I used a dummy IP address in the yml file for privacy reasons. The
> actual IP addresses would be placed there in my setup. The pg_basebackup
> connection to the MASTER_PORT_5432_TCP___ADDR is successful, so I know
> that slave can connect to the master at least. But it just cannot open a
> streaming backup connection...

Yeah, that would have been too easy:) I am not seeing anything else at
the moment.

>
> On Thu, Apr 9, 2015 at 4:11 PM, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 04/09/2015 01:00 PM, Volkan Unsal wrote:
>
>         Hi Adrian,
>
>              1) What version(s) of Postgres are you using on either end?
>
>
>         I'm using Postgres 9:3.
>
>
>              2) Are the primary and the standby in different containers?
>
>
>         Yes, and they are on different servers as well.
>
>              3) What is the container/machine/network layout?
>
>
>         I created a gist of all the files in my setup.
>
>         https://gist.github.com/__volkanunsal/__ad2173e2649393fcd3b6
>         <https://gist.github.com/volkanunsal/ad2173e2649393fcd3b6>
>
>         The init-slave.sh and init-master.sh scripts are executed before the
>         server is started, so that's where I do all my preprocessing of conf
>         files. I checked the results and the substituted values are
>         indeed correct.
>
>
>
>     Well if I am understanding. This:
>
>     primary_conninfo = 'host=${MASTER_PORT_5432_TCP___ADDR} port=5432
>
>     is getting translated to:
>
>     host=0.0.0.0 port=5432
>
>     Now the primary can receive connections from 0.0.0.0, which
>     basically means it can receive from the Internet. The problem is
>     that the standby can not connect to 0.0.0.0, that would mean it is
>     connecting to the whole Internet. You will need to provide either
>     the actual IP for the primary or its hostname.
>
>
>
>
>
>
>
>
>         --
>         *Volkan Unsal*
>         /web and mobile development/
>         volkanunsal.com <http://volkanunsal.com> <http://bit.ly/1h1ebjy>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>
>
> --
> *Volkan Unsal*
> /web and mobile development/
> volkanunsal.com <http://bit.ly/1h1ebjy>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/09/2015 01:34 PM, Volkan Unsal wrote:
> Thanks for giving it a shot, Adrian. I'm scouring the Postgres source
> code looking for some inspiration around the error message "database
> system was interrupted". Let me know if you can think of anything else.
>

 From your original post:

LOG:  database system was interrupted; last known up at 2015-04-09
16:35:05 GMT
LOG:  entering standby mode
LOG:  redo starts at 0/E000028
LOG:  consistent recovery state reached at 0/E0000F0

You implied it was from the primary, is that the case?

Is there a chance you have a recovery.conf in your primary server directory?


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/09/2015 01:48 PM, Volkan Unsal wrote:
> HI Adrian,
>
>     Is there a chance you have a recovery.conf in your primary server
>     directory?
>
>
> No, this file is only in the standby server. From the gist, here is
> where recovery.conf gets created
> <https://gist.github.com/volkanunsal/ad2173e2649393fcd3b6#file-init-slave-sh-L32-L36>.

Yes, I saw that, but have you checked the primary $PGDATA anyway?

>
>
>
>
>
> --
> *Volkan Unsal*
> /web and mobile development/
> volkanunsal.com <http://bit.ly/1h1ebjy>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/09/2015 01:52 PM, Volkan Unsal wrote:
> Yup, just did. Can never be too paranoid about this. :)

So where did the below come from?:

LOG:  database system was interrupted; last known up at 2015-04-09
16:35:05 GMT
LOG:  entering standby mode
LOG:  redo starts at 0/E000028
LOG:  consistent recovery state reached at 0/E0000F0

>
> On Thu, Apr 9, 2015 at 4:51 PM, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 04/09/2015 01:48 PM, Volkan Unsal wrote:
>
>         HI Adrian,
>
>              Is there a chance you have a recovery.conf in your primary
>         server
>              directory?
>
>
>         No, this file is only in the standby server. From the gist, here is
>         where recovery.conf gets created
>         <https://gist.github.com/__volkanunsal/__ad2173e2649393fcd3b6#file-__init-slave-sh-L32-L36
>         <https://gist.github.com/volkanunsal/ad2173e2649393fcd3b6#file-init-slave-sh-L32-L36>>.
>
>
>     Yes, I saw that, but have you checked the primary $PGDATA anyway?
>
>
>
>
>
>
>
>         --
>         *Volkan Unsal*
>         /web and mobile development/
>         volkanunsal.com <http://volkanunsal.com> <http://bit.ly/1h1ebjy>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>
>
> --
> *Volkan Unsal*
> /web and mobile development/
> volkanunsal.com <http://bit.ly/1h1ebjy>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/09/2015 01:34 PM, Volkan Unsal wrote:
> Thanks for giving it a shot, Adrian. I'm scouring the Postgres source
> code looking for some inspiration around the error message "database
> system was interrupted". Let me know if you can think of anything else.

Not sure that is a problem as the logs show the database reaching a
consistent state:

LOG:  consistent recovery state reached at 0/E0000F0

The only I have left at the moment is removing the CONNECTION LIMIT on
the replication user. Also setting up logging connects/disconnects on
the primary to see if an attempt is even being made.

>
> On Thu, Apr 9, 2015 at 4:32 PM, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 04/09/2015 01:14 PM, Volkan Unsal wrote:
>
>         Oops. I used a dummy IP address in the yml file for privacy
>         reasons. The
>         actual IP addresses would be placed there in my setup. The
>         pg_basebackup
>         connection to the MASTER_PORT_5432_TCP___ADDR is successful, so
>         I know
>         that slave can connect to the master at least. But it just
>         cannot open a
>         streaming backup connection...
>
>
>     Yeah, that would have been too easy:) I am not seeing anything else
>     at the moment.
>
>
>         On Thu, Apr 9, 2015 at 4:11 PM, Adrian Klaver
>         <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>         <mailto:adrian.klaver@aklaver.__com
>         <mailto:adrian.klaver@aklaver.com>>> wrote:
>
>              On 04/09/2015 01:00 PM, Volkan Unsal wrote:
>
>                  Hi Adrian,
>
>                       1) What version(s) of Postgres are you using on
>         either end?
>
>
>                  I'm using Postgres 9:3.
>
>
>                       2) Are the primary and the standby in different
>         containers?
>
>
>                  Yes, and they are on different servers as well.
>
>                       3) What is the container/machine/network layout?
>
>
>                  I created a gist of all the files in my setup.
>
>         https://gist.github.com/____volkanunsal/____ad2173e2649393fcd3b6
>         <https://gist.github.com/__volkanunsal/__ad2173e2649393fcd3b6>
>
>         <https://gist.github.com/__volkanunsal/__ad2173e2649393fcd3b6
>         <https://gist.github.com/volkanunsal/ad2173e2649393fcd3b6>>
>
>                  The init-slave.sh and init-master.sh scripts are
>         executed before the
>                  server is started, so that's where I do all my
>         preprocessing of conf
>                  files. I checked the results and the substituted values are
>                  indeed correct.
>
>
>
>              Well if I am understanding. This:
>
>              primary_conninfo = 'host=${MASTER_PORT_5432_TCP_____ADDR}
>         port=5432
>
>              is getting translated to:
>
>              host=0.0.0.0 port=5432
>
>              Now the primary can receive connections from 0.0.0.0, which
>              basically means it can receive from the Internet. The
>         problem is
>              that the standby can not connect to 0.0.0.0, that would
>         mean it is
>              connecting to the whole Internet. You will need to provide
>         either
>              the actual IP for the primary or its hostname.
>
>
>
>
>
>
>
>
>                  --
>                  *Volkan Unsal*
>                  /web and mobile development/
>         volkanunsal.com <http://volkanunsal.com>
>         <http://volkanunsal.com> <http://bit.ly/1h1ebjy>
>
>
>
>              --
>              Adrian Klaver
>         adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>         <mailto:adrian.klaver@aklaver.__com
>         <mailto:adrian.klaver@aklaver.com>>
>
>
>
>
>         --
>         *Volkan Unsal*
>         /web and mobile development/
>         volkanunsal.com <http://volkanunsal.com> <http://bit.ly/1h1ebjy>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>
>
> --
> *Volkan Unsal*
> /web and mobile development/
> volkanunsal.com <http://bit.ly/1h1ebjy>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/09/2015 02:22 PM, Volkan Unsal wrote:
>
>     Not sure that is a problem as the logs show the database reaching a
>     consistent state:
>
>
> But the database doesn't open a port after reaching this state. Hence,
> when I nmap the IP address of the standby server, I don't see an open
> port for postgresql. Is that normal?

If you on the outside looking in would that not depend on firewall rules
also?

On the standby what does netstat -lt show?

>
>     Also setting up logging connects/disconnects on the primary to see
>     if an attempt is even being made.
>
>
> How would I set this up? I think I'm already seeing failed connection
> attempts since they raise FATAL errors, but maybe I'm not seeing
> successful connection attempts.

http://www.postgresql.org/docs/9.4/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT


What do the FATAL errors say?

>
>
>
> --
> *Volkan Unsal*
> /web and mobile development/
> volkanunsal.com <http://bit.ly/1h1ebjy>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/09/2015 02:32 PM, Volkan Unsal wrote:
> I set up logging for connections. Here is what I see:
>
>     LOG:  connection received: host=104.131.66.183 port=38912
>     LOG:  replication connection authorized: user=postgres
>     LOG:  connection received: host=104.131.66.183 port=38913
>     LOG:  replication connection authorized: user=postgres
>
>
> Two connection attempts are made. First is probably for pg_basebackup.
> The second might be for streaming replication. If it's true, then I
> don't understand why it is not succeeding...

How far apart are they?

Could it be that pg_basebackup is still running and your CONNECTION
LIMIT on the replication user is preventing any more connections?
>
>
> On Thu, Apr 9, 2015 at 5:22 PM, Volkan Unsal <spocksplanet@gmail.com
> <mailto:spocksplanet@gmail.com>> wrote:
>
>
>         Not sure that is a problem as the logs show the database
>         reaching a consistent state:
>
>
>     But the database doesn't open a port after reaching this state.
>     Hence, when I nmap the IP address of the standby server, I don't see
>     an open port for postgresql. Is that normal?
>
>         Also setting up logging connects/disconnects on the primary to
>         see if an attempt is even being made.
>
>
>     How would I set this up? I think I'm already seeing failed
>     connection attempts since they raise FATAL errors, but maybe I'm not
>     seeing successful connection attempts.
>
>
>
>     --
>     *Volkan Unsal*
>     /web and mobile development/
>     volkanunsal.com <http://bit.ly/1h1ebjy>
>
>
>
>
> --
> *Volkan Unsal*
> /web and mobile development/
> volkanunsal.com <http://bit.ly/1h1ebjy>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/09/2015 02:38 PM, Volkan Unsal wrote:
>     If you on the outside looking in would that not depend on firewall
>     rules also?
>
>
> Hm, I guess. But I'm not behind a firewall and neither is the server.
>
>
>     On the standby what does netstat -lt show?
>
>
> This is what I see on the standby
>
> Active Internet connections (only servers)
> Proto Recv-Q Send-Q Local Address           Foreign Address         State
> tcp        0      0 *:ssh                   *:*                     LISTEN
> tcp6       0      0 [::]:ssh                [::]:*                  LISTEN
> tcp6       0      0 [::]:postgresql         [::]:*                  LISTEN
>

So it is using IPv6. If you are looking for the port using IPv4 then you
will probably not see it.

Might try in your primary pg_hba.conf changing to:

host    replication     ${REP_USER}     all                     md5
host    replication     ${REP_USER}     0.0.0.0/0               md5
host    ${DB_NAME}      ${DB_USER}      0.0.0.0/0               md5


>
>     What do the FATAL errors say?
>
>
>
> I forgot their contents now, but I think they showed up for trying to
> connect via psql, and not as part of replication process.
>
>
>
>
>
> --
> *Volkan Unsal*
> /web and mobile development/
> volkanunsal.com <http://bit.ly/1h1ebjy>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/09/2015 03:01 PM, Volkan Unsal wrote:
> Good point. But that didn't work either. Back to reading the source code
> for me...

On the primary what does the below show when you have both servers running?:

select * from pg_stat_replication ;



--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/09/2015 03:01 PM, Volkan Unsal wrote:
> Good point. But that didn't work either. Back to reading the source code
> for me...
>

Before you sent:

My postgresql.conf file in the standby server looks like this:

     wal_level = hot_standby
     hot_standby = on
     max_standby_streaming_delay = 15min

Is that your entire postgresql.conf file on the standby?


--
Adrian Klaver
adrian.klaver@aklaver.com

Valkan,

I do not see any errors in the postgres log regarding no entry in
pg_hba.conf. So either you have sent the log from the slave, or you
are not specifying the correct address in the slave's recovery.conf
connection info

On 4/10/15, Volkan Unsal <spocksplanet@gmail.com> wrote:
> Hi Melvin,
>
> I followed your instructions above and modified the pg_hba.conf in master
> to hardcode the IP address of the standby server. I kept the same
> pg_hba.conf file for the standby server –– I hope that was right. The log
> output hasn't changed much, but I'm enclosing it in this gist:
>
> https://gist.github.com/volkanunsal/1778cf691223e77f2e30
>
> I hope the answer is in there...
>
>
>
> On Fri, Apr 10, 2015 at 9:28 AM, Melvin Davidson <melvin6925@gmail.com>
> wrote:
>
>> Volkan,
>>
>> What I see in the link is the original post.
>> I did not see any postgres log entry complaining about invalid entry in
>> pg_hba.conf.
>>
>> The pg_hba.conf entry in your original post is wrong. What you need is
>>
>> host    replication     replication     104.131.66.183/32        md5
>>
>> If you get an error after making that change, then please attach the
>> complete new
>> version of the pg_hba.conf AND the entry from postgres log where it
>> complains about
>> the invalid or non pg_hba.conf entry.
>>
>>
>
>
> --
> *Volkan Unsal*
> *web and mobile development*
> volkanunsal.com <http://bit.ly/1h1ebjy>
>


--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

On 04/10/2015 11:04 AM, Volkan Unsal wrote:
> Hi Melvin,
>
> I followed your instructions above and modified the pg_hba.conf in
> master to hardcode the IP address of the standby server. I kept the same
> pg_hba.conf file for the standby server –– I hope that was right. The
> log output hasn't changed much, but I'm enclosing it in this gist:
>
> https://gist.github.com/volkanunsal/1778cf691223e77f2e30
>
> I hope the answer is in there...
>

I would say it is this:

backend>
PostgreSQL stand-alone backend 9.3.6

My guess is these:

LOG:  connection received: host=104.131.66.183 port=46406
LOG:  replication connection authorized: user=replication
LOG:  connection received: host=104.131.66.183 port=46407

are pg_basebackup connecting.

Pretty sure the stand alone(single user) does not support streaming
replciation.

>
>
> On Fri, Apr 10, 2015 at 9:28 AM, Melvin Davidson <melvin6925@gmail.com
> <mailto:melvin6925@gmail.com>> wrote:
>
>     Volkan,
>
>     What I see in the link is the original post.
>     I did not see any postgres log entry complaining about invalid entry
>     in pg_hba.conf.
>
>     The pg_hba.conf entry in your original post is wrong. What you need is
>
>     host    replication     replication 104.131.66.183/32
>     <http://104.131.66.183/32>        md5
>
>     If you get an error after making that change, then please attach the
>     complete new
>     version of the pg_hba.conf AND the entry from postgres log where it
>     complains about
>     the invalid or non pg_hba.conf entry.
>
>
>
>
> --
> *Volkan Unsal*
> /web and mobile development/
> volkanunsal.com <http://bit.ly/1h1ebjy>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/10/2015 12:06 PM, Volkan Unsal wrote:
> Hi Adrian,
>
> I didn't realize stand-alone was the same thing as single-user mode ––
> are you sure? I guess I don't know what the alternative is to "a
> stand-alone backend."

As example:

postgres@killi:~> /usr/local/pgsql94/bin/postgres --single -D
/usr/local/pgsql94/data/ postgres

PostgreSQL stand-alone backend 9.4rc1
backend>

Your log entries in the second Gist match the command you have in
init-master.sh in your first Gist.


The alternative is either to use a system script to start Postgres or
pg_ctl:

http://www.postgresql.org/docs/9.3/interactive/app-pg-ctl.html

>
> The server is started using a straightforward call to "postgres". It's
> not using the --single flag, which is what I assume is needed for the
> single-user mode.


No, single-user mode is a special case for recovering from mistakes:

http://www.postgresql.org/docs/9.3/interactive/app-postgres.html

The postgres command can also be called in single-user mode. The primary
use for this mode is during bootstrapping by initdb. Sometimes it is
used for debugging or disaster recovery; note that running a single-user
server is not truly suitable for debugging the server, since no
realistic interprocess communication and locking will happen. When
invoked in single-user mode from the shell, the user can enter queries
and the results will be printed to the screen, but in a form that is
more useful for developers than end users. In the single-user mode, the
session user will be set to the user with ID 1, and implicit superuser
powers are granted to this user. This user does not actually have to
exist, so the single-user mode can be used to manually recover from
certain kinds of accidental damage to the system catalogs.


In the Gist I do not see a call to postgres by itself. I only see with
--single.

>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/10/2015 12:34 PM, Volkan Unsal wrote:
> Hi Adrian,
>
> I think there are some commands that are being run in single user mode,
> which is why we are seeing these lines in the output.
>
> https://gist.github.com/volkanunsal/1778cf691223e77f2e30#file-master-log-L57-L58
>
> But I believe the server itself is started in the interactive mode (if
> that's the right term for it) because immediately after those lines, you
> also see this
>
> https://gist.github.com/volkanunsal/1778cf691223e77f2e30#file-master-log-L59-L61
>
> My tests showed there are no LOG outputs if the server was started in
> the single user mode.

I would disagree:

postgres@killi:~> /usr/local/pgsql94/bin/postgres --single -D
/usr/local/pgsql94/data/ <<-EOSQL
    CREATE DATABASE test_db;
EOSQL








PostgreSQL stand-alone backend 9.4rc1


backend> LOG:  statement:    CREATE DATABASE test_db;





backend> postgres@killi:~>

I see nowhere in your  init-master.sh where Postgres is restarted.
Check ps ax | grep postgres on your primary to see what is actually
running. Could be that the single user mode is shut down and a regular
instance is started up, but with different settings then you think. We
still have not explored where the Postgres in your containers are coming
from and how they are set up in image: mdillon/postgis:9.3.


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/10/2015 01:06 PM, Volkan Unsal wrote:
> Hi Adrian,
>
>     I see nowhere in your  init-master.sh where Postgres is restarted.
>
>
>
> Yes, you're right. The base image takes care of that –– the base image
> of mdillon/postgis is postgres:9.3. See here, for instance:
>
> https://github.com/docker-library/postgres/blob/master/9.3/docker-entrypoint.sh#L70
>
> When the startup command –– CMD in Docker lingo –– is "postgres" it does
> a bunch of initialization operations, and then runs the user procided
> scripts in the /docker-entrypoint-initdb.d directory.
>
> Then finally it starts up the postgres process with /gosu postgres "$@".
> / The gosu is a wrapper that passes user commands to the end process,
> i.e. postgres, and I'm not sure what $@ is but I think it's to give the
> user a shell.

I do not have time to go through all the scripts, but wanna make a bet
that it is starting Postgres with a different set up conf files then you
think or a different instance of Postgres:)?

On your primary I would confirm what is actually run when all the
scripting is done. Also when the primary is running take a look at

http://www.postgresql.org/docs/9.3/static/monitoring-stats.html#PG-STAT-REPLICATION-VIEW

I am almost positive that somewhere in the switch from --single mode to
regular mode information is being lost.

>
>
>
>
> --
> *Volkan Unsal*
> /web and mobile development/
> volkanunsal.com <http://bit.ly/1h1ebjy>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/10/2015 02:10 PM, Volkan Unsal wrote:
> Hi Adrian,
>
> I spent the last hour checking the config files are indeed being used ––
> and they are. :P I'm getting silly after days of this configuration
> nightmare. Thanks for replying to all my posts and making helpful
> suggestions. After reading a bit more about log outputs, I enabled the
> maximum logging, and I think I am seeing something interesting in the logs:
>
> https://gist.github.com/volkanunsal/bb58fa52077ae3288dfd#file-slave-log-L60-L65
>
>

Please, what does:

select * from pg_stat_replication

show on the primary?


So the standby server is saying it is not finding:

pg_xlog/000000010000000000000013

and it is switching the WAL source back and forth from a WAL archive to
streaming looking for it.


Just to be sure is actually not there on the standby?

Is still in the pg_xlog directory on the primary?



--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/10/2015 02:10 PM, Volkan Unsal wrote:
> Hi Adrian,
>
> I spent the last hour checking the config files are indeed being used ––
> and they are. :P I'm getting silly after days of this configuration
> nightmare. Thanks for replying to all my posts and making helpful
> suggestions. After reading a bit more about log outputs, I enabled the
> maximum logging, and I think I am seeing something interesting in the logs:
>
> https://gist.github.com/volkanunsal/bb58fa52077ae3288dfd#file-slave-log-L60-L65
>
>

If my previous post does not yield positive results I would suggest a
new tack.

1) Create a primary/standby setup outside of Dockers containers,
preferably on a single machine to begin with, to get a hang of the
mechanics.

2) Once that is done, start up a single Docker container with the
primary and see that it works. Shut it down.  Then start up the standby
in another container and see that it loads correctly. Shut it down.

3) Start the primary Docker container, get it running. Start the standby
container. I know this is sort of what is happening now, but it is
happening all in one dockerfile. I would split the primary and standby
into separate files. I think there is too much happening at one time to
get a clear picture of what is going on.


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/10/2015 03:47 PM, Volkan Unsal wrote:
>
>     Please, what does:
>
>     select * from pg_stat_replication
>
>     show on the primary?
>
>
>
> It shows 0 rows.
>

Are sure there is only one instance of Postgres running on the primary
container?

What does ps ax | grep post show?

Is the primary database seeing any activity after being setup?

For the below, actual file listings would be nice, showing timestamps
and sizes.

>
> Here is the pg_xlog folder from standby:
>
>     root@cba9f6ba3f6e:/#  cat ${PGDATA}/pg_xlog/
>     00000001000000000000000C  archive_status
>
>
>
> And here it is from the primary:
>
>
>     root@ad4a83bdcf9e:/# cat ${PGDATA}/pg_xlog/
>     000000010000000000000005
>     000000010000000000000006
>     000000010000000000000007
>     000000010000000000000008
>     000000010000000000000009
>     00000001000000000000000A
>     00000001000000000000000B
>     00000001000000000000000C
>     00000001000000000000000C.00000028.backup
>     00000001000000000000000D
>     00000001000000000000000E
>     archive_status
>
>
>
>
> --
> *Volkan Unsal*
> /web and mobile development/
> volkanunsal.com <http://bit.ly/1h1ebjy>


--
Adrian Klaver
adrian.klaver@aklaver.com