date:20170813

Re: [GENERAL] Help on Index only scan

2017-08-13 Thread Tom Lane

=?utf-8?Q?Ertan_K=C3=BC=C3=A7=C3=BCko=C4=9Flu?=  
writes:
>>> I want to have an index only scan for my below query:
>>> select autoinc, fileversion from updates where filename = 'Robox.exe' order
>>> by autoinc desc;

>> On 14 Aug 2017, at 01:15, Melvin Davidson  wrote:
>> As far as "Index only scan" , since the table only has 2003 rows, the 
>> optimizer has determined it is faster just to
>> load all the rows into memory and then filter.

> Sorry, my question was misleading. I do not want to use "set enable_seqscan = 
> off" I want to be sure that when necessary (record count increases) relevant 
> index(es) will be used.

There's a considerable distance between "is the planner making appropriate
use of indexes" and "I insist on an index-only scan".  The reason you're
not getting an index-only scan here is that that requires an index that
includes every column referenced in the query, which you don't have.  At
minimum you'd need an index including all of autoinc, fileversion, and
filename to do this query with an IOS.  If you want it to be particularly
efficient for this query then you'd need the index's column order to be
(filename, autoinc, fileversion) --- putting filename means the entries
satisfying WHERE will be clumped in the index, and putting autoinc second
means that a backwards scan on that portion of the index is enough to
produce the requested sort ordering without an explicit sort step.

Whether it's worth maintaining an index this specialized depends on how
much update traffic you have versus how often you want to do this
particular query.  Often it's not worth the extra disk space and update
overhead to have such an index.

In any case, I wouldn't worry about it until you have an actual
performance problem.  Trying to tell on toy data what the planner
will do with production-sized data is usually a losing game.

regards, tom lane

-- 
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] Help on Index only scan

2017-08-13 Thread Melvin Davidson

On Sun, Aug 13, 2017 at 7:37 PM, Ertan Küçükoğlu <
ertan.kucuko...@1nar.com.tr> wrote:

>
> On 14 Aug 2017, at 01:15, Melvin Davidson  wrote:
>
>
> On Sun, Aug 13, 2017 at 5:59 PM, Ertan Küçükoğlu <
> ertan.kucuko...@1nar.com.tr> wrote:
>
>> Hello,
>>
>> My table details:
>> robox=# \dS+ updates
>>Table "public.updates"
>> Column |  Type   | Modifiers
>> | Storage  | Stats target | Description
>> ---+-+--
>> 
>> -+--+--+-
>>  autoinc   | integer | not null default
>> nextval('updates_autoinc_seq'::regclass) | plain|  |
>>  filename  | text|
>> | extended |  |
>>  dateofrelease | date|
>> | plain|  |
>>  fileversion   | text|
>> | extended |  |
>>  afile | text|
>> | extended |  |
>>  filehash  | text|
>> | extended |  |
>>  active| boolean |
>> | plain|  |
>> Indexes:
>> "updates_pkey" PRIMARY KEY, btree (autoinc)
>> "update_filename" btree (filename)
>> "updates_autoinc" btree (autoinc DESC)
>> "updates_dateofrelease" btree (dateofrelease)
>> "updates_filename_dateofrelease" btree (filename, dateofrelease)
>>
>>
>> robox=# select count(autoinc) from updates;
>>  count
>> ---
>>   2003
>> (1 row)
>>
>> robox=# select autoinc, filename, fileversion from updates limit 10;
>>  autoinc | filename | fileversion
>> -+--+-
>>   18 | Robox.exe| 1.0.1.218
>>   19 | Robox.exe| 1.0.1.220
>>   20 | Robox.exe| 1.0.1.220
>>   21 | 8423bfc5a669864f9b66b6b15ce908b9 | 1.1.1.1
>>   22 | 4fdabb0c7adbc5a89fbe679ce76ccef9 | 1.1.1.1
>>   23 | f469d77bfa86c8917c7846c0f871137c | 1.1.1.1
>>   24 | bc10af4c8789718a9ca6565ea14cb17d | 1.1.1.1
>>   25 | d9f87ee46cdb41cd15c2f71ed599faf9 | 1.1.1.1
>>   26 | 6f7428a5364aae1d5914a66cba3e6f3b | 1.1.1.1
>>   27 | 66ec4cdb8d64ca1414f75c1fb9eaa518 | 1.1.1.1
>> (10 rows)
>>
>> I want to have an index only scan for my below query:
>> select autoinc, fileversion from updates where filename = 'Robox.exe'
>> order
>> by autoinc desc;
>>
>> I simply could not understand planner and cannot provide right index for
>> it.
>> Below index names "update_filename" and "updates_autoinc" are added just
>> for
>> the query that I would like to have a index only scan plan. I also failed
>> with following indexes
>> "autoinc desc, filename, fileversion"
>> "autoinc desc, filename"
>>
>> First 3 rows in above select results are actual data. You will find that I
>> have inserted about 2000 rows of dummy data to have somewhat meaningful
>> plan
>> for the query.
>>
>> Current planner result:
>> robox=# vacuum full;
>> VACUUM
>> robox=# explain analyze
>> robox-# select autoinc, fileversion
>> robox-# from updates
>> robox-# where filename = 'Robox.exe'
>> robox-# order by autoinc desc;
>>   QUERY PLAN
>> 
>> 
>> --
>>  Sort  (cost=12.79..12.79 rows=3 width=12) (actual time=0.047..0.047
>> rows=3
>> loops=1)
>>Sort Key: autoinc DESC
>>Sort Method: quicksort  Memory: 25kB
>>->  Bitmap Heap Scan on updates  (cost=4.30..12.76 rows=3 width=12)
>> (actual time=0.040..0.040 rows=3 loops=1)
>>  Recheck Cond: (filename = 'Robox.exe'::text)
>>  Heap Blocks: exact=1
>>  ->  Bitmap Index Scan on update_filename  (cost=0.00..4.30 rows=3
>> width=0) (actual time=0.035..0.035 rows=3 loops=1)
>>Index Cond: (filename = 'Robox.exe'::text)
>>  Planning time: 1.873 ms
>>  Execution time: 0.076 ms
>> (10 rows)
>>
>>
>> I appreciate any help on having right index(es) as I simply failed myself.
>>
>> Regards,
>> Ertan Küçükoğlu
>>
>> *First, you do not need index "updates_autoinc", since autoinc is the
> Primary Key, you are just duplicating the index.*
>
>
> Is that true even if that index is a descending one?
>
>
> *As far as "Index only scan" , since the table only has 2003 rows, the
> optimizer has determined it is faster just to*
> *load all the rows into memory and then filter. If you really want to
> force an index scan, then you would have to do*
> *SET enable_seqscan = off; Before doing the query, however you are just
> shooting yourself in the foot by doing that*
> *as it will make the query slower.*
>
>
> I will try to load up more dummy rows to overflow the work_mem and observe
> results.
>
> Sorry, my question was misleading. I do not want to use "set
> enable_seqscan = off" I want to be sure that when necessary (record count
>

Re: [GENERAL] Help on Index only scan

2017-08-13 Thread Ertan Küçükoğlu


> On 14 Aug 2017, at 01:15, Melvin Davidson  wrote:
> 
> 
>> On Sun, Aug 13, 2017 at 5:59 PM, Ertan Küçükoğlu 
>>  wrote:
>> Hello,
>> 
>> My table details:
>> robox=# \dS+ updates
>>Table "public.updates"
>> Column |  Type   | Modifiers
>> | Storage  | Stats target | Description
>> ---+-+--
>> -+--+--+-
>>  autoinc   | integer | not null default
>> nextval('updates_autoinc_seq'::regclass) | plain|  |
>>  filename  | text|
>> | extended |  |
>>  dateofrelease | date|
>> | plain|  |
>>  fileversion   | text|
>> | extended |  |
>>  afile | text|
>> | extended |  |
>>  filehash  | text|
>> | extended |  |
>>  active| boolean |
>> | plain|  |
>> Indexes:
>> "updates_pkey" PRIMARY KEY, btree (autoinc)
>> "update_filename" btree (filename)
>> "updates_autoinc" btree (autoinc DESC)
>> "updates_dateofrelease" btree (dateofrelease)
>> "updates_filename_dateofrelease" btree (filename, dateofrelease)
>> 
>> 
>> robox=# select count(autoinc) from updates;
>>  count
>> ---
>>   2003
>> (1 row)
>> 
>> robox=# select autoinc, filename, fileversion from updates limit 10;
>>  autoinc | filename | fileversion
>> -+--+-
>>   18 | Robox.exe| 1.0.1.218
>>   19 | Robox.exe| 1.0.1.220
>>   20 | Robox.exe| 1.0.1.220
>>   21 | 8423bfc5a669864f9b66b6b15ce908b9 | 1.1.1.1
>>   22 | 4fdabb0c7adbc5a89fbe679ce76ccef9 | 1.1.1.1
>>   23 | f469d77bfa86c8917c7846c0f871137c | 1.1.1.1
>>   24 | bc10af4c8789718a9ca6565ea14cb17d | 1.1.1.1
>>   25 | d9f87ee46cdb41cd15c2f71ed599faf9 | 1.1.1.1
>>   26 | 6f7428a5364aae1d5914a66cba3e6f3b | 1.1.1.1
>>   27 | 66ec4cdb8d64ca1414f75c1fb9eaa518 | 1.1.1.1
>> (10 rows)
>> 
>> I want to have an index only scan for my below query:
>> select autoinc, fileversion from updates where filename = 'Robox.exe' order
>> by autoinc desc;
>> 
>> I simply could not understand planner and cannot provide right index for it.
>> Below index names "update_filename" and "updates_autoinc" are added just for
>> the query that I would like to have a index only scan plan. I also failed
>> with following indexes
>> "autoinc desc, filename, fileversion"
>> "autoinc desc, filename"
>> 
>> First 3 rows in above select results are actual data. You will find that I
>> have inserted about 2000 rows of dummy data to have somewhat meaningful plan
>> for the query.
>> 
>> Current planner result:
>> robox=# vacuum full;
>> VACUUM
>> robox=# explain analyze
>> robox-# select autoinc, fileversion
>> robox-# from updates
>> robox-# where filename = 'Robox.exe'
>> robox-# order by autoinc desc;
>>   QUERY PLAN
>> 
>> --
>>  Sort  (cost=12.79..12.79 rows=3 width=12) (actual time=0.047..0.047 rows=3
>> loops=1)
>>Sort Key: autoinc DESC
>>Sort Method: quicksort  Memory: 25kB
>>->  Bitmap Heap Scan on updates  (cost=4.30..12.76 rows=3 width=12)
>> (actual time=0.040..0.040 rows=3 loops=1)
>>  Recheck Cond: (filename = 'Robox.exe'::text)
>>  Heap Blocks: exact=1
>>  ->  Bitmap Index Scan on update_filename  (cost=0.00..4.30 rows=3
>> width=0) (actual time=0.035..0.035 rows=3 loops=1)
>>Index Cond: (filename = 'Robox.exe'::text)
>>  Planning time: 1.873 ms
>>  Execution time: 0.076 ms
>> (10 rows)
>> 
>> 
>> I appreciate any help on having right index(es) as I simply failed myself.
>> 
>> Regards,
>> Ertan Küçükoğlu
>> 
> 
> First, you do not need index "updates_autoinc", since autoinc is the Primary 
> Key, you are just duplicating the index.

Is that true even if that index is a descending one?

> 
> As far as "Index only scan" , since the table only has 2003 rows, the 
> optimizer has determined it is faster just to
> load all the rows into memory and then filter. If you really want to force an 
> index scan, then you would have to do
> SET enable_seqscan = off; Before doing the query, however you are just 
> shooting yourself in the foot by doing that
> as it will make the query slower.

I will try to load up more dummy rows to overflow the work_mem and observe 
results.

Sorry, my question was misleading. I do not want to use "set enable_seqscan = 
off" I want to be sure that when necessary (record count increases) relevant 
index(es) will be used.

Obviously I still can't read query plan as I did not understand that operation

Re: [GENERAL] Help on Index only scan

2017-08-13 Thread Melvin Davidson

On Sun, Aug 13, 2017 at 5:59 PM, Ertan Küçükoğlu <
ertan.kucuko...@1nar.com.tr> wrote:

> Hello,
>
> My table details:
> robox=# \dS+ updates
>Table "public.updates"
> Column |  Type   | Modifiers
> | Storage  | Stats target | Description
> ---+-+--
> 
> -+--+--+-
>  autoinc   | integer | not null default
> nextval('updates_autoinc_seq'::regclass) | plain|  |
>  filename  | text|
> | extended |  |
>  dateofrelease | date|
> | plain|  |
>  fileversion   | text|
> | extended |  |
>  afile | text|
> | extended |  |
>  filehash  | text|
> | extended |  |
>  active| boolean |
> | plain|  |
> Indexes:
> "updates_pkey" PRIMARY KEY, btree (autoinc)
> "update_filename" btree (filename)
> "updates_autoinc" btree (autoinc DESC)
> "updates_dateofrelease" btree (dateofrelease)
> "updates_filename_dateofrelease" btree (filename, dateofrelease)
>
>
> robox=# select count(autoinc) from updates;
>  count
> ---
>   2003
> (1 row)
>
> robox=# select autoinc, filename, fileversion from updates limit 10;
>  autoinc | filename | fileversion
> -+--+-
>   18 | Robox.exe| 1.0.1.218
>   19 | Robox.exe| 1.0.1.220
>   20 | Robox.exe| 1.0.1.220
>   21 | 8423bfc5a669864f9b66b6b15ce908b9 | 1.1.1.1
>   22 | 4fdabb0c7adbc5a89fbe679ce76ccef9 | 1.1.1.1
>   23 | f469d77bfa86c8917c7846c0f871137c | 1.1.1.1
>   24 | bc10af4c8789718a9ca6565ea14cb17d | 1.1.1.1
>   25 | d9f87ee46cdb41cd15c2f71ed599faf9 | 1.1.1.1
>   26 | 6f7428a5364aae1d5914a66cba3e6f3b | 1.1.1.1
>   27 | 66ec4cdb8d64ca1414f75c1fb9eaa518 | 1.1.1.1
> (10 rows)
>
> I want to have an index only scan for my below query:
> select autoinc, fileversion from updates where filename = 'Robox.exe' order
> by autoinc desc;
>
> I simply could not understand planner and cannot provide right index for
> it.
> Below index names "update_filename" and "updates_autoinc" are added just
> for
> the query that I would like to have a index only scan plan. I also failed
> with following indexes
> "autoinc desc, filename, fileversion"
> "autoinc desc, filename"
>
> First 3 rows in above select results are actual data. You will find that I
> have inserted about 2000 rows of dummy data to have somewhat meaningful
> plan
> for the query.
>
> Current planner result:
> robox=# vacuum full;
> VACUUM
> robox=# explain analyze
> robox-# select autoinc, fileversion
> robox-# from updates
> robox-# where filename = 'Robox.exe'
> robox-# order by autoinc desc;
>   QUERY PLAN
> 
> 
> --
>  Sort  (cost=12.79..12.79 rows=3 width=12) (actual time=0.047..0.047 rows=3
> loops=1)
>Sort Key: autoinc DESC
>Sort Method: quicksort  Memory: 25kB
>->  Bitmap Heap Scan on updates  (cost=4.30..12.76 rows=3 width=12)
> (actual time=0.040..0.040 rows=3 loops=1)
>  Recheck Cond: (filename = 'Robox.exe'::text)
>  Heap Blocks: exact=1
>  ->  Bitmap Index Scan on update_filename  (cost=0.00..4.30 rows=3
> width=0) (actual time=0.035..0.035 rows=3 loops=1)
>Index Cond: (filename = 'Robox.exe'::text)
>  Planning time: 1.873 ms
>  Execution time: 0.076 ms
> (10 rows)
>
>
> I appreciate any help on having right index(es) as I simply failed myself.
>
> Regards,
> Ertan Küçükoğlu
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

*First, you do not need index "updates_autoinc", since autoinc is the
Primary Key, you are just duplicating the index.*

*As far as "Index only scan" , since the table only has 2003 rows, the
optimizer has determined it is faster just to*
*load all the rows into memory and then filter. If you really want to force
an index scan, then you would have to do*
*SET enable_seqscan = off; Before doing the query, however you are just
shooting yourself in the foot by doing that*

*as it will make the query slower.*


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

[GENERAL] Help on Index only scan

2017-08-13 Thread Ertan Küçükoğlu

Hello,

My table details:
robox=# \dS+ updates
   Table "public.updates"
Column |  Type   | Modifiers
| Storage  | Stats target | Description
---+-+--
-+--+--+-
 autoinc   | integer | not null default
nextval('updates_autoinc_seq'::regclass) | plain|  |
 filename  | text|
| extended |  |
 dateofrelease | date|
| plain|  |
 fileversion   | text|
| extended |  |
 afile | text|
| extended |  |
 filehash  | text|
| extended |  |
 active| boolean |
| plain|  |
Indexes:
"updates_pkey" PRIMARY KEY, btree (autoinc)
"update_filename" btree (filename)
"updates_autoinc" btree (autoinc DESC)
"updates_dateofrelease" btree (dateofrelease)
"updates_filename_dateofrelease" btree (filename, dateofrelease)


robox=# select count(autoinc) from updates;
 count
---
  2003
(1 row)

robox=# select autoinc, filename, fileversion from updates limit 10;
 autoinc | filename | fileversion
-+--+-
  18 | Robox.exe| 1.0.1.218
  19 | Robox.exe| 1.0.1.220
  20 | Robox.exe| 1.0.1.220
  21 | 8423bfc5a669864f9b66b6b15ce908b9 | 1.1.1.1
  22 | 4fdabb0c7adbc5a89fbe679ce76ccef9 | 1.1.1.1
  23 | f469d77bfa86c8917c7846c0f871137c | 1.1.1.1
  24 | bc10af4c8789718a9ca6565ea14cb17d | 1.1.1.1
  25 | d9f87ee46cdb41cd15c2f71ed599faf9 | 1.1.1.1
  26 | 6f7428a5364aae1d5914a66cba3e6f3b | 1.1.1.1
  27 | 66ec4cdb8d64ca1414f75c1fb9eaa518 | 1.1.1.1
(10 rows)

I want to have an index only scan for my below query:
select autoinc, fileversion from updates where filename = 'Robox.exe' order
by autoinc desc;

I simply could not understand planner and cannot provide right index for it.
Below index names "update_filename" and "updates_autoinc" are added just for
the query that I would like to have a index only scan plan. I also failed
with following indexes
"autoinc desc, filename, fileversion"
"autoinc desc, filename"

First 3 rows in above select results are actual data. You will find that I
have inserted about 2000 rows of dummy data to have somewhat meaningful plan
for the query.

Current planner result:
robox=# vacuum full;
VACUUM
robox=# explain analyze
robox-# select autoinc, fileversion
robox-# from updates
robox-# where filename = 'Robox.exe'
robox-# order by autoinc desc;
  QUERY PLAN

--
 Sort  (cost=12.79..12.79 rows=3 width=12) (actual time=0.047..0.047 rows=3
loops=1)
   Sort Key: autoinc DESC
   Sort Method: quicksort  Memory: 25kB
   ->  Bitmap Heap Scan on updates  (cost=4.30..12.76 rows=3 width=12)
(actual time=0.040..0.040 rows=3 loops=1)
 Recheck Cond: (filename = 'Robox.exe'::text)
 Heap Blocks: exact=1
 ->  Bitmap Index Scan on update_filename  (cost=0.00..4.30 rows=3
width=0) (actual time=0.035..0.035 rows=3 loops=1)
   Index Cond: (filename = 'Robox.exe'::text)
 Planning time: 1.873 ms
 Execution time: 0.076 ms
(10 rows)


I appreciate any help on having right index(es) as I simply failed myself.

Regards,
Ertan Küçükoğlu




-- 
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] Where is pg_hba.conf

2017-08-13 Thread George Neuner

On Sun, 13 Aug 2017 10:55:00 -0400, Igor Korot 
wrote:

>Also, I presume that the address in this file is the address of the
>machine where the server is located, not the address from where the
>connection is initiated.

No.  The addresses / network segments in the file specify from where
the client(s) can connect.

George



-- 
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] Where is pg_hba.conf

2017-08-13 Thread David G. Johnston

On Sunday, August 13, 2017, Igor Korot  wrote:

> Also, I presume that the address in this file is the address of the
> machine where the server is located, not the address from where the
> connection is initiated.
>

Not according to the docs.

https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

David J.

Re: [GENERAL] Where is pg_hba.conf

2017-08-13 Thread Igor Korot

Also, I presume that the address in this file is the address of the
machine where the server is located, not the address from where the
connection is initiated.


On Sun, Aug 13, 2017 at 10:53 AM, Igor Korot  wrote:
> Hi,
> OK, I found it under the root account.
>
> Now I am modifying it as follows:
>
> # TYPE  DATABASEUSERADDRESS METHOD
>
> # "local" is for Unix domain socket connections only
> local   all all md5
> # IPv4 local connections:
> hostall all 192.168.1.3/32md5
> # IPv6 local connections:
> hostall all ::1/128 md5
> # Allow replication connections from localhost, by a user with the
> # replication privilege.
> #local   replication postgresmd5
> #hostreplication postgres127.0.0.1/32md5
> #hostreplication postgres::1/128 md5
>
> Should "METHOD" column be lept as 'md5' or as 'trusted' as in the link
> I posted in the OP?
>
> Thank you.
>
>
> On Sun, Aug 13, 2017 at 10:37 AM, Christoph Berg  wrote:
>> Re: Igor Korot 2017-08-13 
>> 
>>> draft=# SHOW hba_file
>>> draft-# SHOW hba_file;
>>>  ERROR:  syntax error at or near "SHOW"
>>> LINE 2: SHOW hba_file;
>>> ^
>>
>> Standard beginners error. If you forgot the ";" on the first line,
>> it'll process both lines as a single, erroneous command. Note the
>> "-#" prompt.
>>
>> If you run into that situation again, hit ^C.
>>
>> Christoph


-- 
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] Where is pg_hba.conf

2017-08-13 Thread Igor Korot

Hi,
OK, I found it under the root account.

Now I am modifying it as follows:

# TYPE  DATABASEUSERADDRESS METHOD

# "local" is for Unix domain socket connections only
local   all all md5
# IPv4 local connections:
hostall all 192.168.1.3/32md5
# IPv6 local connections:
hostall all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication postgresmd5
#hostreplication postgres127.0.0.1/32md5
#hostreplication postgres::1/128 md5

Should "METHOD" column be lept as 'md5' or as 'trusted' as in the link
I posted in the OP?

Thank you.

On Sun, Aug 13, 2017 at 10:37 AM, Christoph Berg  wrote:
> Re: Igor Korot 2017-08-13 
> 
>> draft=# SHOW hba_file
>> draft-# SHOW hba_file;
>>  ERROR:  syntax error at or near "SHOW"
>> LINE 2: SHOW hba_file;
>> ^
>
> Standard beginners error. If you forgot the ";" on the first line,
> it'll process both lines as a single, erroneous command. Note the
> "-#" prompt.
>
> If you run into that situation again, hit ^C.
>
> Christoph

-- 
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] Where is pg_hba.conf

2017-08-13 Thread Christoph Berg

Re: Igor Korot 2017-08-13 

> draft=# SHOW hba_file
> draft-# SHOW hba_file;
>  ERROR:  syntax error at or near "SHOW"
> LINE 2: SHOW hba_file;
> ^

Standard beginners error. If you forgot the ";" on the first line,
it'll process both lines as a single, erroneous command. Note the
"-#" prompt.

If you run into that situation again, hit ^C.

Christoph


-- 
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] Where is pg_hba.conf

2017-08-13 Thread Igor Korot

Hi, armand,

On Sun, Aug 13, 2017 at 10:12 AM, armand pirvu  wrote:
> Normally should reside in the data dir. My case below
>
> armandps-MacBook-Air:~ armandp$ ps -fu postgres |grep data
>   502 29591 1   0 Thu09PM ?? 0:01.63
> /Library/PostgreSQL/9.6/bin/postgres -D /Library/PostgreSQL/9.6/data
>
> armandps-MacBook-Air:~ armandp$ ls -l
> /Library/PostgreSQL/9.6/data/pg_hba.conf
> ls: /Library/PostgreSQL/9.6/data/pg_hba.conf: Permission denied
> armandps-MacBook-Air:~ armandp$ sudo ls -l
> /Library/PostgreSQL/9.6/data/pg_hba.conf
> -rw---  1 postgres  daemon  4248 Aug 10 21:17
> /Library/PostgreSQL/9.6/data/pg_hba.conf
>
> Is it possible that global search fails from a permission error ?

MyMac:/ igorkorot$ ps -fu postgres |grep data
  50277 1   0 11:38PM ?? 0:00.45
/Library/PostgreSQL/9.1/bin/postmaster -D/Library/PostgreSQL/9.1/data
  502  1733   243   0 10:42AM ?? 0:00.05
/System/Library/Frameworks/CoreServices.framework/Frameworks/Metadata.framework/Versions/A/Support/mdworker
-s mdworker -c MDSImporterWorker -m com.apple.mdworker.shared
MyMac:/ igorkorot$ ls -la /Library/PostgreSQL/9.1/data/
ls: : Permission denied
MyMac:/ igorkorot$ su
Password:
sh-3.2# ls -la /Library/PostgreSQL/9.1/data/
total 88
drwx--   20 postgres  daemon680 Aug 12 23:38 .
drwxr-xr-x   16 root  daemon544 Dec  4  2016 ..
-rw---1 postgres  daemon  4 Dec  4  2016 PG_VERSION
drwx--7 postgres  daemon238 Dec  9  2016 base
drwx--   42 postgres  daemon   1428 Aug 12 23:39 global
drwx--3 postgres  daemon102 Dec  4  2016 pg_clog
-rw---1 postgres  daemon   4222 Dec  4  2016 pg_hba.conf
-rw---1 postgres  daemon   1636 Dec  4  2016 pg_ident.conf
drwxr-xr-x  204 postgres  daemon   6936 Aug 13 00:00 pg_log
drwx--4 postgres  daemon136 Dec  4  2016 pg_multixact
drwx--3 postgres  daemon102 Aug 12 23:38 pg_notify
drwx--2 postgres  daemon 68 Dec  4  2016 pg_serial
drwx--3 postgres  daemon102 Aug 13 10:43 pg_stat_tmp
drwx--3 postgres  daemon102 Dec  4  2016 pg_subtrans
drwx--2 postgres  daemon 68 Dec  4  2016 pg_tblspc
drwx--2 postgres  daemon 68 Dec  4  2016 pg_twophase
drwx--5 postgres  daemon170 Dec 11  2016 pg_xlog
-rw-r--r--1 postgres  daemon  19162 Dec  4  2016 postgresql.conf
-rw---1 postgres  daemon 70 Aug 12 23:38 postmaster.opts
-rw---1 postgres  daemon 75 Aug 12 23:38 postmaster.pid
sh-3.2#

Apparently it looks like the failure is from the permission.

So how do I enable it? Or it has to stay for "root" only?

Thank you.

>
> Hope this helps
>
>
>
> On Aug 13, 2017, at 9:00 AM, Igor Korot  wrote:
>
> Hi,
> I have a Mac with OSX 10.8 installed. It has Postgre 9.1.
> According to
> https://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html
> and
> http://www.thegeekstuff.com/2014/02/enable-remote-postgresql-connection/?utm_source=tuicool
> I need to modify the pg_hba.conf file to get access to the DB
> remotely.
>
> However, I can't find this file anywhere on the system.
> I am able to connec to the server locally with psql. I can also
> connect to the server
> from the ODBC driver from my program. However doing a global search I
> can't find that file.
>
> Can someone please help?
>
> Thank you.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>


-- 
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] Where is pg_hba.conf

2017-08-13 Thread Igor Korot

Hi, Christoph,

On Sun, Aug 13, 2017 at 10:09 AM, Christoph Berg  wrote:
> Re: Igor Korot 2017-08-13 
> 
>> I need to modify the pg_hba.conf file to get access to the DB
>> remotely.
>>
>> However, I can't find this file anywhere on the system.
>
> Try "SHOW hba_file;".

Last login: Sat Aug 12 23:49:33 on ttys000
/Library/PostgreSQL/9.1/scripts/runpsql.sh; exit
MyMac:~ igorkorot$ /Library/PostgreSQL/9.1/scripts/runpsql.sh; exit
Server [localhost]:
Database [postgres]: draft
Port [5432]:
Username [postgres]:
Password for user postgres:
psql (9.1.24)
Type "help" for help.

draft=# SHOW hba_file
draft-# SHOW hba_file;
 ERROR:  syntax error at or near "SHOW"
LINE 2: SHOW hba_file;
^

Thank you.

>
> Christoph

-- 
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] Where is pg_hba.conf

2017-08-13 Thread armand pirvu

Normally should reside in the data dir. My case below

armandps-MacBook-Air:~ armandp$ ps -fu postgres |grep data
  502 29591 1   0 Thu09PM ?? 0:01.63 
/Library/PostgreSQL/9.6/bin/postgres -D /Library/PostgreSQL/9.6/data

armandps-MacBook-Air:~ armandp$ ls -l /Library/PostgreSQL/9.6/data/pg_hba.conf
ls: /Library/PostgreSQL/9.6/data/pg_hba.conf: Permission denied
armandps-MacBook-Air:~ armandp$ sudo ls -l 
/Library/PostgreSQL/9.6/data/pg_hba.conf
-rw---  1 postgres  daemon  4248 Aug 10 21:17 
/Library/PostgreSQL/9.6/data/pg_hba.conf

Is it possible that global search fails from a permission error ?

Hope this helps



> On Aug 13, 2017, at 9:00 AM, Igor Korot  wrote:
> 
> Hi,
> I have a Mac with OSX 10.8 installed. It has Postgre 9.1.
> According to https://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html
> and 
> http://www.thegeekstuff.com/2014/02/enable-remote-postgresql-connection/?utm_source=tuicool
> I need to modify the pg_hba.conf file to get access to the DB
> remotely.
> 
> However, I can't find this file anywhere on the system.
> I am able to connec to the server locally with psql. I can also
> connect to the server
> from the ODBC driver from my program. However doing a global search I
> can't find that file.
> 
> Can someone please help?
> 
> Thank 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] Where is pg_hba.conf

2017-08-13 Thread Christoph Berg

Re: Igor Korot 2017-08-13 

> I need to modify the pg_hba.conf file to get access to the DB
> remotely.
> 
> However, I can't find this file anywhere on the system.

Try "SHOW hba_file;".

Christoph


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

[GENERAL] Where is pg_hba.conf

2017-08-13 Thread Igor Korot

 Hi,
I have a Mac with OSX 10.8 installed. It has Postgre 9.1.
According to https://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html
and 
http://www.thegeekstuff.com/2014/02/enable-remote-postgresql-connection/?utm_source=tuicool
I need to modify the pg_hba.conf file to get access to the DB
remotely.

However, I can't find this file anywhere on the system.
I am able to connec to the server locally with psql. I can also
connect to the server
from the ODBC driver from my program. However doing a global search I
can't find that file.

Can someone please help?

Thank 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] Help on Index only scan

Re: [GENERAL] Help on Index only scan

Re: [GENERAL] Help on Index only scan

Re: [GENERAL] Help on Index only scan

[GENERAL] Help on Index only scan

Re: [GENERAL] Where is pg_hba.conf

Re: [GENERAL] Where is pg_hba.conf

Re: [GENERAL] Where is pg_hba.conf

Re: [GENERAL] Where is pg_hba.conf

Re: [GENERAL] Where is pg_hba.conf

Re: [GENERAL] Where is pg_hba.conf

Re: [GENERAL] Where is pg_hba.conf

Re: [GENERAL] Where is pg_hba.conf

Re: [GENERAL] Where is pg_hba.conf

[GENERAL] Where is pg_hba.conf

15 matches

Site Navigation

Mail list logo

Footer information