Обсуждение: Accessing Postgres Server and database from other Machine

I dont think you can use pgAdmin to do that kind of thing but I may be wrong. Google how to restart a service in Windows and then restart postgres 

On Fri, Dec 4, 2020 at 5:36 PM Muthukumar.GK <muthankumar@gmail.com> wrote:

No.I did not restart postgres.Please let me know the steps to restart  postgres using pgadmin4 tool.

On Fri, Dec 4, 2020 at 5:26 PM Hemil Ruparel <hemilruparel2002@gmail.com> wrote:

Are you sure you restarted postgres after the change in configuration?

On Fri, Dec 4, 2020 at 3:58 PM Muthukumar.GK <muthankumar@gmail.com> 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. 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

It looks to me like the line is still commented out (as it is by default). Remove any "#" (hash or pound) symbol from the beginning of the line then restart Postgres.

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

Hi Adrian,

> On 04. Dec, 2020, at 16:13, Adrian Klaver <adrian.klaver@aklaver.com> 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
automaticallystrip all comments and then diff the result to ones real world postgresql.conf or some other version
postgresql.conffile to find parameters that have been removed or changed with a new PostgreSQL version. This is highly
usefulfor planning migrations and have a quick reference what to check for before actually migrating. So for me this is
muchmore 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.1sample 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

On 12/4/20 8:03 AM, Paul Förster wrote:
> Hi Adrian,
> 
>> On 04. Dec, 2020, at 16:13, Adrian Klaver <adrian.klaver@aklaver.com> 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
automaticallystrip all comments and then diff the result to ones real world postgresql.conf or some other version
postgresql.conffile to find parameters that have been removed or changed with a new PostgreSQL version. This is highly
usefulfor planning migrations and have a quick reference what to check for before actually migrating. So for me this is
muchmore 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

On 4 December 2020 17:17:48 CET, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>On 12/4/20 8:03 AM, Paul Förster wrote:
>> Hi Adrian,
>>
>>> On 04. Dec, 2020, at 16:13, Adrian Klaver
><adrian.klaver@aklaver.com> 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
theoutside there. You will need a "host" entry accepting any ip address or specify what ip your collegue is connecting
fromif possible. I have missed this step a few times and banged my head.  

/Nicklas

Hi Hemil,

> On 05. Dec, 2020, at 07:50, Hemil Ruparel <hemilruparel2002@gmail.com> 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

> On 5 Dec 2020, at 10:05, Paul Förster <paul.foerster@gmail.com> wrote:
>
> Hi Hemil,
>
>> On 05. Dec, 2020, at 07:50, Hemil Ruparel <hemilruparel2002@gmail.com> 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.

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 <http://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 <nicklas.aven@jordogskog.no 
> <mailto:nicklas.aven@jordogskog.no>> wrote:
> 
> 
> 
>     On 4 December 2020 17:17:48 CET, Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>      >On 12/4/20 8:03 AM, Paul Förster wrote:
>      >> Hi Adrian,
>      >>
>      >>> On 04. Dec, 2020, at 16:13, Adrian Klaver
>      ><adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> 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 <nicklas.aven@jordogskog.no 
> <mailto:nicklas.aven@jordogskog.no>> wrote:
> 
> 
> 
>     On 4 December 2020 17:17:48 CET, Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>      >On 12/4/20 8:03 AM, Paul Förster wrote:
>      >> Hi Adrian,
>      >>
>      >>> On 04. Dec, 2020, at 16:13, Adrian Klaver
>      ><adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> 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

On 12/5/20 1:05 AM, Paul Förster wrote:
> Hi Hemil,
> 
>> On 05. Dec, 2020, at 07:50, Hemil Ruparel <hemilruparel2002@gmail.com> 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

Hi Adrian,

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

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

Cheers,
Paul