Re: [GENERAL] no pg_hba.conf entry for replication connection from host

[Volkan Unsal]

Yup, just did. Can never be too paranoid about this. :)

On Thu, Apr 9, 2015 at 4:51 PM, Adrian Klaver 
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
>> > 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 
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
*Volkan Unsal*
*web and mobile development*
volkanunsal.com 

Re: [GENERAL] Regarding bytea column in Posgresql

[John R Pierce]

On 4/9/2015 4:10 AM, Bill Moran wrote:

1.   Is 'bytea' column intended for storing text data?

No, it's intended for storing binary data.


>2.   Typically a chat can have text data with several special characters 
(which can be represented in multi bytes), how these characters can be stored in a 
bytea column and retrieved back properly?

bytea won't help you here. You'll have to manage the special
characters entirely in your code. bytea gives you back the
exact same types you put in, with no changes or interpretation.

A better choice would be to use a text field with a proper
text encoding (such as utf-8).


one possible rationale for using BYTEA is that the data could be in 
various encodings, which the application wishes to preserve, and keeps 
track of somewhere else (perhaps in a field within the XML?).  
PostgreSQL text types would insist that all text be stored in the same 
encoding, and anything in a different encoding would have to be 
converted to the database encoding.   Me, I'd be inclined to convert 
everything to UTF8 and store it as such, and convert it back to the 
user's encoding on display, but the feasibility of this really depends 
on the use cases.


--
john r pierce, recycling bits in santa cruz



--
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] no pg_hba.conf entry for replication connection from host

[Adrian Klaver]

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 mailto:spockspla...@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 




--
*Volkan Unsal*
/web and mobile development/
volkanunsal.com 



--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] no pg_hba.conf entry for replication connection from host

[Adrian Klaver]

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/EF0

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 mailto:adrian.kla...@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
mailto:adrian.kla...@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


>

 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 
 



 --
 Adrian Klaver
adrian.kla...@aklaver.com 
>




--
*Volkan Unsal*
/web and mobile development/
volkanunsal.com  



--
Adrian Klaver
adrian.kla...@aklaver.com 




--
*Volkan Unsal*
/web and mobile development/
volkanunsal.com 



--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] no pg_hba.conf entry for replication connection from host

[Adrian Klaver]

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/E28
LOG:  consistent recovery state reached at 0/EF0

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.kla...@aklaver.com


--
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] no pg_hba.conf entry for replication connection from host

[Volkan Unsal]

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.






-- 
*Volkan Unsal*
*web and mobile development*
volkanunsal.com 

Re: [GENERAL] ecpg rejects input parameters

[Adrian Klaver]

On 04/09/2015 07:12 AM, Andrew Pennebaker wrote:

Makes sense.

Yes, it would be great if psql offered a flag for validating syntax.
Other programming languages do this, for example, bash -n, ruby -c, and
php -l.


Or pgsanity could take this:

CREATE DATABASE :db;

and convert it into:

CREATE DATABASE db;

before submitting it for syntax checking.

The basic issue is whose syntax are you interested in checking, SQL or 
the program that is  creating the SQL. If it is just the SQL end result, 
then it needs to rendered down to an actual valid SQL statement. If it 
is the program, then it gets complicated in a hurry. Already you have 
mentioned psql and ecpg. Then in Postgres there are various procedural 
languages that have their own way of creating SQL. Then there are 
external languages, for example the one I use a lot  Python. It has its 
own method(s) of passing in variable information via DB-API.





On Wed, Apr 8, 2015 at 3:53 PM, Tom Lane mailto:t...@sss.pgh.pa.us>> wrote:

Andrew Pennebaker mailto:andrew.penneba...@gmail.com>> writes:
> I can't find a relevant section to address my specific problem: ecpg
> complaining when I try to check the syntax of my .sql files that use input
> parameters.

I'm not sure why you think that should work.  psql and ecpg have quite
distinct input languages.  Both are extensions of SQL, but the key word
there is "extension".  ecpg certainly isn't going to accept psql's
backslash commands for instance, any more than psql would accept ecpg's
C code portions.  And I doubt it would be useful for ecpg to simply
ignore
the variable-interpolation symbols; but it has no way to know what's
going
to be substituted for those symbols.

It would be more interesting to consider giving psql a syntax-check-only
mode; though I'm afraid use of variable interpolation would still be
pretty
problematic, since the variables are commonly filled from execution of
previous commands.

 regards, tom lane




--
Cheers,

Andrew Pennebaker
www.yellosoft.us 



--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] no pg_hba.conf entry for replication connection from host

[Volkan Unsal]

HI Adrian,

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


The logs show that pg_basebackup competed successfully. But then something
goes wrong.

LOG:  database system was interrupted; last known up at 2015-04-09 19:22:50
> GMT
> LOG:  entering standby mode
> LOG:  redo starts at 0/A28
> LOG:  consistent recovery state reached at 0/AF0




-- 
*Volkan Unsal*
*web and mobile development*
volkanunsal.com 

Re: [GENERAL] no pg_hba.conf entry for replication connection from host

[Melvin Davidson]

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

hostreplication rep104.131.66.183/32md5

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  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 
>


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


-- 
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] no pg_hba.conf entry for replication connection from host

[Volkan Unsal]

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.

On Thu, Apr 9, 2015 at 4:32 PM, Adrian Klaver 
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 > > 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
>> 
>>
>> 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  
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>>
>> --
>> *Volkan Unsal*
>> /web and mobile development/
>> volkanunsal.com 
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
*Volkan Unsal*
*web and mobile development*
volkanunsal.com 

Re: [GENERAL] no pg_hba.conf entry for replication connection from host

[Volkan Unsal]

Hi Melvin,

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.
>

I tried this, but I am getting this error when I do that:

  FATAL:  no pg_hba.conf entry for replication connection from host
"104.131.66.183", user "rep", SSL off
  DETAIL:  Could not resolve client IP address to a host name: Name or
service not known.

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


Yes, the variables are defined in the environment for both master and
slave. These variables are passed through a preprocessor and replaced with
their actual values. But the REP_USER only exists in the master database.



-- 
*Volkan Unsal*
*web and mobile development*
volkanunsal.com 

Re: [GENERAL] unexpected (to me) sorting order

[Glyn Astill]

> From: Scott Marlowe 
 > To: Glyn Astill 
 > Cc: Björn Lundin ; "pgsql-general@postgresql.org" 
 > 
 > Sent: Thursday, 9 April 2015, 13:23
 > Subject: Re: [GENERAL] unexpected (to me) sorting order
 > 
> On Wed, Apr 8, 2015 at 3:33 AM, Glyn Astill  
> wrote:
> 
>> 
>>> From: Björn Lundin 
>>> To: pgsql-general@postgresql.org
>>> Sent: Wednesday, 8 April 2015, 10:09
>>> Subject: [GENERAL] unexpected (to me) sorting order
>>> 
>>> select * from T_SORT order by NAME ;
>>> 
>>> rollback;
>>> id |        name
>>> +
>>>   1 | FINISH_110_150_1
>>>   2 | FINISH_110_200_1
>>>   3 | FINISH_1.10_20.0_3
>>>   4 | FINISH_1.10_20.0_4
>>>   5 | FINISH_1.10_30.0_3
>>>   6 | FINISH_1.10_30.0_4
>>>   7 | FINISH_120_150_1
>>>   8 | FINISH_120_200_1
>>> (8 rows)
>>> 
>>> why is FINISH_1.10_20.0_3 between
>>> FINISH_110_200_1 and
>>> FINISH_120_150_1
>>> ?
>>> 
>>> That is why is '.' between 1 and 2 as in 110/120 ?
>>> 
>>> 
>>> pg_admin III reports the database is created like
>>> CREATE DATABASE bnl
>>>   WITH OWNER = bnl
>>>       ENCODING = 'UTF8'
>>>       TABLESPACE = pg_default
>>>       LC_COLLATE = 'en_US.UTF-8'
>>>       LC_CTYPE = 'en_US.UTF-8'
>>>       CONNECTION LIMIT = -1;
>>> 
>>> 
>> 
>> 
>> 
>> The collation of your "bnl" database is utf8, so the 
> "." punctuation character is seen as a "variable element" 
> and given a lower weighting in the sort to the rest of the characters.  
> That's just how the collate algorithm works in UTF8.
> 
> utf8 is an encoding method, not a collation. The collation is en_US,
> encoded in utf8. You can use C collation with utf8 encoding just fine.
> So just replace UTF8 with en_US in your sentence and you've got it
> right.
> 

Yes, thanks for the correction there, and we're talking about the wider unicode 
collate algorithm.
 

Re: [GENERAL] no pg_hba.conf entry for replication connection from host

[Volkan Unsal]

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...

On Thu, Apr 9, 2015 at 4:11 PM, Adrian Klaver 
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
>>
>> 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 
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
*Volkan Unsal*
*web and mobile development*
volkanunsal.com 

Re: [GENERAL] no pg_hba.conf entry for replication connection from host

[Adrian Klaver]

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
.


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







--
*Volkan Unsal*
/web and mobile development/
volkanunsal.com 



--
Adrian Klaver
adrian.kla...@aklaver.com


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

[GENERAL] Can a bdr enabled server belong to more than one bdr group?

[Dennis]

Can a server instance that has a db in one existing bdr group add that same db 
to a different/new bdr group so that the db belongs to two different bdr groups 
at the same time?

e.g.

node 1, db_x, bdr_group (node 1, node 2)
node 2, db_x, bdr_group (node 1, node 2) AND bdr_group(node 2, node 3)
node 3, db_x, bdr_group(node 2, node 3)

-- 
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] no pg_hba.conf entry for replication connection from host

[Melvin Davidson]

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:
hostreplication 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  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.
> hostreplication ${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/E28
>  LOG:  consistent recovery state reached at 0/EF0
>
>
> What am I doing wrong?
>
>
> --
> *Volkan Unsal*
> *web and mobile development*
> volkanunsal.com 
>


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


-- 
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] Cannot connect from local network to my postgresql server

[Adrian Klaver]

On 04/09/2015 03:00 AM, dlefebvre wrote:

Hi,

I use ubuntu 14.01 and i install a postgresql 9.3 server. I want to connect
an other pc from local network to this server. But the server seems to only
listen on localhost.

There is the conf files :

pg_hba.conf :

# TYPE  DATABASEUSERADDRESS METHOD

# IPv4 local connections:
hostall all 127.0.0.1/32
@authmethodhost@
# IPv6 local connections:
hostall all ::1/128
@authmethodhost@
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication @default_username@
@authmethodlocal@
#hostreplication @default_username@127.0.0.1/32
@authmethodhost@
#hostreplication @default_username@::1/128
@authmethodhost@

host all all 192.168.1.127 255.255.255.0 md5

(192.168.1.127 is the ip of the pc i want to connect)

postgresql.conf (with the only lines i changed) :

# - Connection Settings -

tcpip_socket = true

listen_addresses = '*'  # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for 
all
# (change requires restart)
port = 5432 # (change requires restart)

my iptables are setup :

:~$ sudo iptables -L
[sudo] password for dlefebvre:
Chain INPUT (policy ACCEPT)
target prot opt source   destination
ACCEPT tcp  --  anywhere anywhere tcp
dpt:postgresql

Chain FORWARD (policy ACCEPT)
target prot opt source   destination

Chain OUTPUT (policy ACCEPT)
target prot opt source   destination
ACCEPT tcp  --  anywhere anywhere tcp
dpt:postgresql
:~$

and so, using netstat it seems like the server only listen on localhost :

:~$ netstat -lt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address   Foreign Address State
tcp0  0 dlefebvre-ubuntu:domain *:* LISTEN
tcp0  0 localhost:ipp   *:* LISTEN
tcp0  0 localhost:postgresql*:* LISTEN
tcp6   0  0 ip6-localhost:ipp   [::]:*  LISTEN
:~$

Logs dosen't show any errors and of course the server is running, i can
access in localhost without any trouble.

I google all link i could found, every people who had the same issue made
mistake in configuration file. I think i am not, this is why i request your
help.

Thanks for your time, i stay avaible if you need any more information.


So did  you restart Postgres after making the changes to postgresql.conf?

Are you sure you are working on the correct postgresql.conf file?

Or to put it another way is there more than one instance of Postgres on 
the machine?


What error do you get on the remote client  end when you try to connect?



Cheers, Damien Lefebvre.




--
View this message in context: 
http://postgresql.nabble.com/Cannot-connect-from-local-network-to-my-postgresql-server-tp5845229.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] no pg_hba.conf entry for replication connection from host

[Volkan Unsal]

That was from the standby server logs.

On Thu, Apr 9, 2015 at 5:00 PM, Adrian Klaver 
wrote:

> 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/E28
> LOG:  consistent recovery state reached at 0/EF0
>
>
>> On Thu, Apr 9, 2015 at 4:51 PM, Adrian Klaver > > 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
>> > ad2173e2649393fcd3b6#file-__init-slave-sh-L32-L36
>> > 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  
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>>
>> --
>> *Volkan Unsal*
>> /web and mobile development/
>> volkanunsal.com 
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
*Volkan Unsal*
*web and mobile development*
volkanunsal.com 

Re: [GENERAL] no pg_hba.conf entry for replication connection from host

[Adrian Klaver]

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 



--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] no pg_hba.conf entry for replication connection from host

[Volkan Unsal]

>
>
> 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 

[GENERAL] no pg_hba.conf entry for replication connection from host

[Volkan Unsal]

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.
hostreplication ${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/E28
 LOG:  consistent recovery state reached at 0/EF0


What am I doing wrong?


-- 
*Volkan Unsal*
*web and mobile development*
volkanunsal.com 

Re: [GENERAL] no pg_hba.conf entry for replication connection from host

[Adrian Klaver]

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.
 hostreplication ${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/E28
  LOG:  consistent recovery state reached at 0/EF0


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 



--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] no pg_hba.conf entry for replication connection from host

[Volkan Unsal]

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...


On Thu, Apr 9, 2015 at 5: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?
>
> 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 
>



-- 
*Volkan Unsal*
*web and mobile development*
volkanunsal.com 

Re: [GENERAL] no pg_hba.conf entry for replication connection from host

[Adrian Klaver]

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 



--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] no pg_hba.conf entry for replication connection from host

[Volkan Unsal]

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 

Re: [GENERAL] no pg_hba.conf entry for replication connection from host

[Adrian Klaver]

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/E28
LOG:  consistent recovery state reached at 0/EF0



On Thu, Apr 9, 2015 at 4:51 PM, Adrian Klaver mailto:adrian.kla...@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

>.


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







--
*Volkan Unsal*
/web and mobile development/
volkanunsal.com  



--
Adrian Klaver
adrian.kla...@aklaver.com 




--
*Volkan Unsal*
/web and mobile development/
volkanunsal.com 



--
Adrian Klaver
adrian.kla...@aklaver.com


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

[GENERAL] Regarding bytea column in Posgresql

[Deole, Pushkar (Pushkar)]

Hi,

I have been assigned to a product that uses Postgresql 9.3 as backend database. 
I am new to postgresql.
The product provides chat functionality between the uses and the completed 
chats are stored in the database table in a 'bytea' column in the form of xml. 
When I query the data from this column I see xml file with text data. I have 
couple of queries:

1.   Is 'bytea' column intended for storing text data?

2.   Typically a chat can have text data with several special characters 
(which can be represented in multi bytes), how these characters can be stored 
in a bytea column and retrieved back properly?

Thanks,
Pushkar

Re: [GENERAL] no pg_hba.conf entry for replication connection from host

[Adrian Klaver]

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 



--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] no pg_hba.conf entry for replication connection from host

[Volkan Unsal]

Hi Melvin,

When I make this entry

hostreplication rep104.131.66.183/32md5


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.

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

| 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.
:)




On Thu, Apr 9, 2015 at 3:20 PM, Melvin Davidson 
wrote:

> So did you make the following entry in pg_hba,conf?
>
> hostreplication rep104.131.66.183/32md5
>
> 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  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 
> >
>
>
> --
> *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 

Re: [GENERAL] unexpected (to me) sorting order

[Scott Marlowe]

On Wed, Apr 8, 2015 at 3:33 AM, Glyn Astill  wrote:
>
>> From: Björn Lundin 
>>To: pgsql-general@postgresql.org
>>Sent: Wednesday, 8 April 2015, 10:09
>>Subject: [GENERAL] unexpected (to me) sorting order
>>
>>select * from T_SORT order by NAME ;
>>
>>rollback;
>> id |name
>>+
>>  1 | FINISH_110_150_1
>>  2 | FINISH_110_200_1
>>  3 | FINISH_1.10_20.0_3
>>  4 | FINISH_1.10_20.0_4
>>  5 | FINISH_1.10_30.0_3
>>  6 | FINISH_1.10_30.0_4
>>  7 | FINISH_120_150_1
>>  8 | FINISH_120_200_1
>>(8 rows)
>>
>>why is FINISH_1.10_20.0_3 between
>> FINISH_110_200_1 and
>> FINISH_120_150_1
>>?
>>
>>That is why is '.' between 1 and 2 as in 110/120 ?
>>
>>
>>pg_admin III reports the database is created like
>>CREATE DATABASE bnl
>>  WITH OWNER = bnl
>>   ENCODING = 'UTF8'
>>   TABLESPACE = pg_default
>>   LC_COLLATE = 'en_US.UTF-8'
>>   LC_CTYPE = 'en_US.UTF-8'
>>   CONNECTION LIMIT = -1;
>>
>>
>
>
>
> The collation of your "bnl" database is utf8, so the "." punctuation 
> character is seen as a "variable element" and given a lower weighting in the 
> sort to the rest of the characters.  That's just how the collate algorithm 
> works in UTF8.

utf8 is an encoding method, not a collation. The collation is en_US,
encoded in utf8. You can use C collation with utf8 encoding just fine.
So just replace UTF8 with en_US in your sentence and you've got it
right.


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

[GENERAL] Cannot connect from local network to my postgresql server

[dlefebvre]

Hi,

I use ubuntu 14.01 and i install a postgresql 9.3 server. I want to connect
an other pc from local network to this server. But the server seems to only
listen on localhost.

There is the conf files :

pg_hba.conf :

# TYPE  DATABASEUSERADDRESS METHOD

# IPv4 local connections:
hostall all 127.0.0.1/32   
@authmethodhost@
# IPv6 local connections:
hostall all ::1/128
@authmethodhost@
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication @default_username@   
@authmethodlocal@
#hostreplication @default_username@127.0.0.1/32   
@authmethodhost@
#hostreplication @default_username@::1/128
@authmethodhost@

host all all 192.168.1.127 255.255.255.0 md5

(192.168.1.127 is the ip of the pc i want to connect)

postgresql.conf (with the only lines i changed) :

# - Connection Settings -

tcpip_socket = true

listen_addresses = '*'  # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for 
all
# (change requires restart)
port = 5432 # (change requires restart)

my iptables are setup :

:~$ sudo iptables -L
[sudo] password for dlefebvre: 
Chain INPUT (policy ACCEPT)
target prot opt source   destination 
ACCEPT tcp  --  anywhere anywhere tcp
dpt:postgresql

Chain FORWARD (policy ACCEPT)
target prot opt source   destination 

Chain OUTPUT (policy ACCEPT)
target prot opt source   destination 
ACCEPT tcp  --  anywhere anywhere tcp
dpt:postgresql
:~$ 

and so, using netstat it seems like the server only listen on localhost :

:~$ netstat -lt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address   Foreign Address State  
tcp0  0 dlefebvre-ubuntu:domain *:* LISTEN 
tcp0  0 localhost:ipp   *:* LISTEN 
tcp0  0 localhost:postgresql*:* LISTEN 
tcp6   0  0 ip6-localhost:ipp   [::]:*  LISTEN 
:~$ 

Logs dosen't show any errors and of course the server is running, i can
access in localhost without any trouble.

I google all link i could found, every people who had the same issue made
mistake in configuration file. I think i am not, this is why i request your
help.

Thanks for your time, i stay avaible if you need any more information.

Cheers, Damien Lefebvre. 




--
View this message in context: 
http://postgresql.nabble.com/Cannot-connect-from-local-network-to-my-postgresql-server-tp5845229.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] Cannot connect from local network to my postgresql server

[John McKown]

On Thu, Apr 9, 2015 at 5:00 AM, dlefebvre  wrote:

> Hi,
>
> I use ubuntu 14.01 and i install a postgresql 9.3 server. I want to connect
> an other pc from local network to this server. But the server seems to only
> listen on localhost.
>

​I'm running 9.3.6 on Fedora 21 x86_64.​



>
> There is the conf files :
>
> pg_hba.conf :
>

 ​

​Mine looks about the same.​



>
> postgresql.conf (with the only lines i changed) :
>
> # - Connection Settings -
>
> tcpip_socket = true
>
> listen_addresses = '*'  # what IP address(es) to listen on;
> # comma-separated list of
> addresses;
> # defaults to 'localhost'; use '*'
> for all
> # (change requires restart)
>

​I have this commented out entirely in my configuration. I.e>

#listen_address = 'localhost"​




> port = 5432 # (change requires restart)
>
> my iptables are setup :
>
>
>
​As best as I can tell, this looks OK. ​



> and so, using netstat it seems like the server only listen on localhost :
>
> :~$ netstat -lt
> Active Internet connections (only servers)
> Proto Recv-Q Send-Q Local Address   Foreign Address State
> tcp0  0 dlefebvre-ubuntu:domain *:* LISTEN
> tcp0  0 localhost:ipp   *:* LISTEN
> tcp0  0 localhost:postgresql*:* LISTEN
> tcp6   0  0 ip6-localhost:ipp   [::]:*  LISTEN
> :~$
>

​My netstat -lt shows


netstat -lt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address   Foreign Address State

tcp0  0 0.0.0.0:ssh 0.0.0.0:*   LISTEN

tcp0  0 localhost:postgres  0.0.0.0:*   LISTEN

tcp0  0 localhost:smtp  0.0.0.0:*   LISTEN

tcp6   0  0 [::]:ssh[::]:*  LISTEN

tcp6   0  0 localhost:postgres  [::]:*  LISTEN


​

​Note that my entry for postgres shows 0.0.0.0:* instead of *:*​


> Logs dosen't show any errors and of course the server is running, i can
> access in localhost without any trouble.
>
> I google all link i could found, every people who had the same issue made
> mistake in configuration file. I think i am not, this is why i request your
> help.
>
> Thanks for your time, i stay avaible if you need any more information.
>
> Cheers, Damien Lefebvre.
>
>
>
>
> --
> View this message in context:
> http://postgresql.nabble.com/Cannot-connect-from-local-network-to-my-postgresql-server-tp5845229.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

Re: [GENERAL] no pg_hba.conf entry for replication connection from host

[Adrian Klaver]

On 04/09/2015 12:39 PM, Volkan Unsal wrote:

Hi Melvin,

When I make this entry

hostreplication rep 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 mailto:melvin6...@gmail.com>> wrote:

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

hostreplication 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 mailto:spockspla...@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  
 >


--
*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 



--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] no pg_hba.conf entry for replication connection from host

[Volkan Unsal]

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}'




On Thu, Apr 9, 2015 at 3:16 PM, Adrian Klaver 
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
>
> 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 
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
*Volkan Unsal*
*web and mobile development*
volkanunsal.com 

Re: [GENERAL] no pg_hba.conf entry for replication connection from host

[Volkan Unsal]

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

.





-- 
*Volkan Unsal*
*web and mobile development*
volkanunsal.com 

Re: [GENERAL] no pg_hba.conf entry for replication connection from host

[Adrian Klaver]

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 mailto:adrian.kla...@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


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  



--
Adrian Klaver
adrian.kla...@aklaver.com 




--
*Volkan Unsal*
/web and mobile development/
volkanunsal.com 



--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] no pg_hba.conf entry for replication connection from host

[Adrian Klaver]

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.kla...@aklaver.com


--
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] no pg_hba.conf entry for replication connection from host

[Volkan Unsal]

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'



In my logs, I see this line just before the server enters the standby mode:

LOG:  database system was interrupted; last known up at 2015-04-09 19:22:50
> GMT
> LOG:  entering standby mode





On Thu, Apr 9, 2015 at 3:41 PM, pgorg_aav 
wrote:

> Can you share your postgresql.conf settings?
>
> do you have the following set:
> hot_standby = on
>
>
> This is what i get for streaming in the log file:
>
> 0:2015-04-09 15:32:05 EDT::@:[8792]:[2-1] LOG:  entering standby mode
> scp: x8= snipped =8x /pg_xlog_archive/00010018004A: No such
> file or directory
> scp failed
> 0:2015-04-09 15:32:05 EDT::@:[8792]:[3-1] LOG:  redo starts at
> 18/4AEA6740
> 0:2015-04-09 15:32:05 EDT::@:[8792]:[4-1] LOG:  consistent recovery
> state reached at 18/4AF0F7A8
> 0:2015-04-09 15:32:05 EDT::@:[8792]:[5-1] LOG:  invalid record length
> at 18/4AF0F7A8
> 0:2015-04-09 15:32:05 EDT::@:[8786]:[4-1] LOG:  database system is
> ready to accept read only connections
> 0:2015-04-09 15:32:05 EDT::@:[8804]:[1-1] LOG:  started streaming WAL
> from primary at 18/4A00 on timeline 1
>
>
> Regards,
> Amit Varde
>
>
>
>
>   On Thursday, April 9, 2015 3:20 PM, Melvin Davidson <
> melvin6...@gmail.com> wrote:
>
>
>
> So did you make the following entry in pg_hba,conf?
>
> hostreplicationrep104.131.66.183/32md5
>
> 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  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 
> >
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
>


-- 
*Volkan Unsal*
*web and mobile development*
volkanunsal.com 

Re: [GENERAL] no pg_hba.conf entry for replication connection from host

[Volkan Unsal]

Hi Adrian,

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


Yes, here is that entire file:

standby_mode = 'on'
> primary_conninfo = 'host=${MASTER_PORT_5432_TCP_ADDR} port=5432
> user=${REP_USER} password=${REP_PASS}'
> trigger_file = '/tmp/promote_db_slave'


On Thu, Apr 9, 2015 at 3:56 PM, Adrian Klaver 
wrote:

> 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.kla...@aklaver.com
>



-- 
*Volkan Unsal*
*web and mobile development*
volkanunsal.com 

Re: [GENERAL] no pg_hba.conf entry for replication connection from host

[Adrian Klaver]

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 mailto:adrian.kla...@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


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  



--
Adrian Klaver
adrian.kla...@aklaver.com 




--
*Volkan Unsal*
/web and mobile development/
volkanunsal.com 



--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Regarding bytea column in Posgresql

[Bill Moran]

On Thu, 9 Apr 2015 11:03:30 +
"Deole, Pushkar (Pushkar)"  wrote:
> 
> I have been assigned to a product that uses Postgresql 9.3 as backend 
> database. I am new to postgresql.
> The product provides chat functionality between the uses and the completed 
> chats are stored in the database table in a 'bytea' column in the form of 
> xml. When I query the data from this column I see xml file with text data. I 
> have couple of queries:
> 
> 1.   Is 'bytea' column intended for storing text data?

No, it's intended for storing binary data.

> 2.   Typically a chat can have text data with several special characters 
> (which can be represented in multi bytes), how these characters can be stored 
> in a bytea column and retrieved back properly?

bytea won't help you here. You'll have to manage the special
characters entirely in your code. bytea gives you back the
exact same types you put in, with no changes or interpretation.

A better choice would be to use a text field with a proper
text encoding (such as utf-8).

Probably an even better choice would be to use the XML datatype
in PostgreSQL, since you say that you're storing XML anyway.

The place where people tend to get tripped up with TEXT and
XML datatypes is that they're strict. If you try to store
text in a TEXT data type that isn't valid (i.e., multi-byte
characters that aren't correct) you'll get an error and the
data won't be accepted. The same thing happens if you try to
store invalid XML in an XML field (such as XML without proper
closing tags, etc). It seems that this strictness causes a lot
of people to avoid those data types, as there seem to be a lot
of people who would rather have garbage data in their database
than actually go to the work of fixing their application.

-- 
Bill Moran


-- 
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] ecpg rejects input parameters

[Andrew Pennebaker]

Makes sense.

Yes, it would be great if psql offered a flag for validating syntax. Other
programming languages do this, for example, bash -n, ruby -c, and php -l.

On Wed, Apr 8, 2015 at 3:53 PM, Tom Lane  wrote:

> Andrew Pennebaker  writes:
> > I can't find a relevant section to address my specific problem: ecpg
> > complaining when I try to check the syntax of my .sql files that use
> input
> > parameters.
>
> I'm not sure why you think that should work.  psql and ecpg have quite
> distinct input languages.  Both are extensions of SQL, but the key word
> there is "extension".  ecpg certainly isn't going to accept psql's
> backslash commands for instance, any more than psql would accept ecpg's
> C code portions.  And I doubt it would be useful for ecpg to simply ignore
> the variable-interpolation symbols; but it has no way to know what's going
> to be substituted for those symbols.
>
> It would be more interesting to consider giving psql a syntax-check-only
> mode; though I'm afraid use of variable interpolation would still be pretty
> problematic, since the variables are commonly filled from execution of
> previous commands.
>
> regards, tom lane
>



-- 
Cheers,

Andrew Pennebaker
www.yellosoft.us