Accessing Postgres Server and database from other Machine

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:

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:

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:

On 04/12/2020 12:56, Hemil Ruparel wrote:
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<mailto: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<mailto: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<mailto: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.

Tim Clarke MBCS
IT Director
Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420

Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848
Web: https://www.manifest.co.uk/

Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom

________________________________

Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to admin@minerva.info<mailto:admin@minerva.info>
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.

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

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.

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

lol. Been there. Done that

On Sat, Dec 5, 2020 at 1:36 AM Nicklas Avén <nicklas.aven@jordogskog.no>
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. 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>
wrote:

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

Did you restart postgres after changing pg_hba.conf?

On Sat, Dec 5, 2020 at 11:56 AM Muthukumar.GK <muthankumar@gmail.com> wrote:

Yes. I restarted (Run->Services.msc->Postgresql13->Restart) after config
file.

I can able to connect my postgres server using .net application with help
of below connection string.

NpgsqlConnectionconn =newNpgsqlConnection("Server=127.0.0.1;User Id=postgres;
"+

"Password=Postgresql@1;Database=Training;");

but my colleague is facing restriction issue when trying to connect my
postgres server using *my Ip Address*,

NpgsqlConnectionconn =newNpgsqlConnection("Server=192.1xx.xx.50;User Id=
postgres; "+

"Password=Postgresql@1;Database=Training;");

Regards

Muthu

On Sat, Dec 5, 2020 at 12:21 PM Hemil Ruparel <hemilruparel2002@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

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&gt;
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