Re: Accessing Postgres Server and database from other Machine

Re: Accessing Postgres Server and database from other Machine

On 12/5/20 1:05 AM, Paul Förster wrote:
> Hi Hemil,
> 
>> On 05. Dec, 2020, at 07:50, Hemil Ruparel  wrote:
>>
>> Did you restart postgres after changing pg_hba.conf?
> 
> that shouldn't be necessary for changes in pg_hba.conf. Just do either on the command line:
> 
> $ pg_ctl reload
> 
> or from psql:
> 
> postgres=# select pg_reload_conf();
> 
> You can then see the effective result immediately in pg_hab_file_rules:
> 
> postgres=# table pg_hba_file_rules;
> 
> Hope this helps.

Given that the OP changed pg_hba.conf.sample, probably not:).

> 
> Cheers,
> Paul
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Accessing Postgres Server and database from other Machine

On 4 December 2020 17:17:48 CET, Adrian Klaver  wrote:
>On 12/4/20 8:03 AM, Paul Förster wrote:
>> Hi Adrian,
>> 
>>> On 04. Dec, 2020, at 16:13, Adrian Klaver
> wrote:
>>> That is the wrong file, the *.sample is the giveaway.
>> 
>> hmmm, I'd rather call it essential reference documentation or
>template for automation. It's perfectly well suited to automatically
>strip all comments and then diff the result to ones real world
>postgresql.conf or some other version postgresql.conf file to find
>parameters that have been removed or changed with a new PostgreSQL
>version. This is highly useful for planning migrations and have a quick
>reference what to check for before actually migrating. So for me this
>is much more than just a giveaway.
>> 
>
>Yes, but for changing the behavior of a running instance it is the
>wrong 
>file and it's extension is a clue.


You will prabably also need to change in tge pg_hba.conf file. At least in linux there is no entry for connections from the outside there. You will need a "host" entry accepting any ip address or specify what ip your collegue is connecting from if possible. I have missed this step a few times and banged my head. 

/Nicklas

Re: Accessing Postgres Server and database from other Machine

On 12/4/20 10:26 PM, Muthukumar.GK wrote:
> Hi Nicklas,
> 
>    I have added a line "host  all  all 0.0.0.0/0  
> md5" in Pg_hba_conf.sample file and restarted postgres server. 

Again, changing lines in the *.sample file will not be of any use. The 
actual pg_hba.conf file is in the same location as I posted previously 
for the postgresql.conf file.

> porstgres port has been added in windows firewall (Windows10)as well.  
> But my colleague is still facing the below error when connecting my 
> postgres server from .net appplication.
> 
> Error is - no connection could be made because the target machine 
> actively refused it.
> 
> Regards
> Muthu
> 
> On Sat, Dec 5, 2020 at 1:36 AM Nicklas Avén  > wrote:
> 
> 
> 
>     On 4 December 2020 17:17:48 CET, Adrian Klaver
>     > wrote:
>      >On 12/4/20 8:03 AM, Paul Förster wrote:
>      >> Hi Adrian,
>      >>
>      >>> On 04. Dec, 2020, at 16:13, Adrian Klaver
>      >> wrote:
>      >>> That is the wrong file, the *.sample is the giveaway.
>      >>
>      >> hmmm, I'd rather call it essential reference documentation or
>      >template for automation. It's perfectly well suited to automatically
>      >strip all comments and then diff the result to ones real world
>      >postgresql.conf or some other version postgresql.conf file to find
>      >parameters that have been removed or changed with a new PostgreSQL
>      >version. This is highly useful for planning migrations and have a
>     quick
>      >reference what to check for before actually migrating. So for me this
>      >is much more than just a giveaway.
>      >>
>      >
>      >Yes, but for changing the behavior of a running instance it is the
>      >wrong
>      >file and it's extension is a clue.
> 
> 
>     You will prabably also need to change in tge pg_hba.conf file. At
>     least in linux there is no entry for connections from the outside
>     there. You will need a "host" entry accepting any ip address or
>     specify what ip your collegue is connecting from if possible. I have
>     missed this step a few times and banged my head.
> 
>     /Nicklas
> 
> 
> On Sat, Dec 5, 2020 at 1:36 AM Nicklas Avén  > wrote:
> 
> 
> 
>     On 4 December 2020 17:17:48 CET, Adrian Klaver
>     > wrote:
>      >On 12/4/20 8:03 AM, Paul Förster wrote:
>      >> Hi Adrian,
>      >>
>      >>> On 04. Dec, 2020, at 16:13, Adrian Klaver
>      >> wrote:
>      >>> That is the wrong file, the *.sample is the giveaway.
>      >>
>      >> hmmm, I'd rather call it essential reference documentation or
>      >template for automation. It's perfectly well suited to automatically
>      >strip all comments and then diff the result to ones real world
>      >postgresql.conf or some other version postgresql.conf file to find
>      >parameters that have been removed or changed with a new PostgreSQL
>      >version. This is highly useful for planning migrations and have a
>     quick
>      >reference what to check for before actually migrating. So for me this
>      >is much more than just a giveaway.
>      >>
>      >
>      >Yes, but for changing the behavior of a running instance it is the
>      >wrong
>      >file and it's extension is a clue.
> 
> 
>     You will prabably also need to change in tge pg_hba.conf file. At
>     least in linux there is no entry for connections from the outside
>     there. You will need a "host" entry accepting any ip address or
>     specify what ip your collegue is connecting from if possible. I have
>     missed this step a few times and banged my head.
> 
>     /Nicklas
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Accessing Postgres Server and database from other Machine

Hi Adrian,

> On 05. Dec, 2020, at 15:58, Adrian Klaver  wrote:
> 
> Given that the OP changed pg_hba.conf.sample, probably not:).

sorry, I overlook the *.sample part. :D

Cheers,
Paul

Re: Accessing Postgres Server and database from other Machine

On 12/4/20 8:03 AM, Paul Förster wrote:
> Hi Adrian,
> 
>> On 04. Dec, 2020, at 16:13, Adrian Klaver  wrote:
>> That is the wrong file, the *.sample is the giveaway.
> 
> hmmm, I'd rather call it essential reference documentation or template for automation. It's perfectly well suited to automatically strip all comments and then diff the result to ones real world postgresql.conf or some other version postgresql.conf file to find parameters that have been removed or changed with a new PostgreSQL version. This is highly useful for planning migrations and have a quick reference what to check for before actually migrating. So for me this is much more than just a giveaway.
> 

Yes, but for changing the behavior of a running instance it is the wrong 
file and it's extension is a clue.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Accessing Postgres Server and database from other Machine

> On 5 Dec 2020, at 10:05, Paul Förster  wrote:
> 
> Hi Hemil,
> 
>> On 05. Dec, 2020, at 07:50, Hemil Ruparel  wrote:
>> 
>> Did you restart postgres after changing pg_hba.conf?
> 
> that shouldn't be necessary for changes in pg_hba.conf. Just do either on the command line:
> 
> $ pg_ctl reload

While you’re in there, also verify that something is listening on the port (see below)

$ netstat -an


> or from psql:
> 
> postgres=# select pg_reload_conf();
> 
> You can then see the effective result immediately in pg_hab_file_rules:
> 
> postgres=# table pg_hba_file_rules;

Also:

postgres=# show listen_addresses;

postgres=# show port;

Those will tell you whether the server is listening on the network and on the expected port.

Alban Hertroys
--
There is always an exception to always.

Re: Accessing Postgres Server and database from other Machine

Hi Hemil,

> On 05. Dec, 2020, at 07:50, Hemil Ruparel  wrote:
> 
> Did you restart postgres after changing pg_hba.conf?

that shouldn't be necessary for changes in pg_hba.conf. Just do either on the command line:

$ pg_ctl reload

or from psql:

postgres=# select pg_reload_conf();

You can then see the effective result immediately in pg_hab_file_rules:

postgres=# table pg_hba_file_rules;

Hope this helps.

Cheers,
Paul

Re: Accessing Postgres Server and database from other Machine

Hi Adrian,

> On 04. Dec, 2020, at 16:13, Adrian Klaver  wrote:
> That is the wrong file, the *.sample is the giveaway.

hmmm, I'd rather call it essential reference documentation or template for automation. It's perfectly well suited to automatically strip all comments and then diff the result to ones real world postgresql.conf or some other version postgresql.conf file to find parameters that have been removed or changed with a new PostgreSQL version. This is highly useful for planning migrations and have a quick reference what to check for before actually migrating. So for me this is much more than just a giveaway.

A simple, but effective (Linux, bash) example:

#!/bin/bash
oldHome=/data/postgres/12.5
newHome=/data/postgres/13.1
# confOld=${oldHome}/share/postgresql.conf.sample
confOld=/data/pg01/cdb01a/db/postgresql.base.conf  # postgresql."base".conf because of Patroni
confNew=${newHome}/share/postgresql.conf.sample
sed -e "s/^#//; s/[[:space:]]*#.*$//; /^--*/d; /^ /d; /^$/d" ${confOld} | sort >/tmp/f1
sed -e "s/^#//; s/[[:space:]]*#.*$//; /^--*/d; /^ /d; /^$/d" ${confNew} | sort >/tmp/f2
diff -y /tmp/f1 /tmp/f2
rm /tmp/f1 /tmp/f2

Cheers,
Paul



Here's some sample output of my (still) 12.5 Patroni/etcd test-and-play-around-cluster run against the new PostgreSQL 13.1 sample file:

archive_command = 'cp %p /data/arch/cdb01a/%f'		      |	archive_cleanup_command = ''
archive_mode = on					      |	archive_command = ''
archive_timeout = 1800					      |	archive_mode = off
							      >	archive_timeout = 0
							      >	array_nulls = on
							      >	authentication_timeout = 1min
autovacuum_analyze_scale_factor = 0.1				autovacuum_analyze_scale_factor = 0.1
autovacuum_analyze_threshold = 50				autovacuum_analyze_threshold = 50
autovacuum_freeze_max_age = 200000000				autovacuum_freeze_max_age = 200000000
autovacuum_max_workers = 10				      |	autovacuum_max_workers = 3
autovacuum_multixact_freeze_max_age = 400000000			autovacuum_multixact_freeze_max_age = 400000000
autovacuum_naptime = 60s				      |	autovacuum_naptime = 1min
autovacuum = on							autovacuum = on
							      >	autovacuum_vacuum_cost_delay = 2ms
							      >	autovacuum_vacuum_cost_limit = -1
							      >	autovacuum_vacuum_insert_scale_factor = 0.2
							      >	autovacuum_vacuum_insert_threshold = 1000
autovacuum_vacuum_scale_factor = 0.2				autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_threshold = 50				autovacuum_vacuum_threshold = 50
checkpoint_timeout = 30s				      |	autovacuum_work_mem = -1
...
and so on

Re: Accessing Postgres Server and database from other Machine

Accessing Postgres Server and database from other Machine

Re: Accessing Postgres Server and database from other Machine

Re: Accessing Postgres Server and database from other Machine

Re: Accessing Postgres Server and database from other Machine

Re: Accessing Postgres Server and database from other Machine

Re: Accessing Postgres Server and database from other Machine

Re: Accessing Postgres Server and database from other Machine

Re: Accessing Postgres Server and database from other Machine

Re: Accessing Postgres Server and database from other Machine

On 12/4/20 2:27 AM, Muthukumar.GK wrote:
> Hi Team,
> 
> I have created Database in postgresqlv13 . My Office colleague needs to 
> access my postgres server and database from his machine. There is a 
> config file called 'Postgresql.conf.sample' in program files folder.

That is the wrong file, the *.sample is the giveaway. Assuming you are 
on Windows the correct file is:

C:\Program Files\PostgeSQL\13\data\postgresql

Un-comment and change setting there and then restart server.

> Even if I changed the connection settings of listen address from 
> localhost to *( Connection Settings - #*Listen address* =*'*' *) in 
> config file. But still my colleague is not able  to connect it. Kindly 
> provide some guidance on this.
> 
> Regards
> Muthu


-- 
Adrian Klaver
adrian.klaver@aklaver.com