Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade

[Henrik Cednert (Filmlance)]

On 22 Nov 2017, at 22:07, Patrick KUI-LI 
mailto:pku...@hipay.com>> wrote:


Hello,

I had this behaviors when the upgraded pg 9.5 was on ssl mode by default.

So i deactivated ssl mode in postgresql.conf. That's all.

Regards,

Patrick


Hello

And you just uncommented the  'ssl = off' line in the config for this?

Is this default behaviour different from 8.4? Is there a 'show running config' 
for pgsql?

I tried that in the test vm and didn't really give me a significant difference.

COMPRESSION LEVEL: 6, SSL ON
real 82m33.744s
user 60m55.069s
sys 3m3.375s


COMPRESSION LEVEL: 6, SSL OFF
real 76m31.083s
user 61m23.282s
sys 1m23.341s

Re: Query became very slow after 9.6 -> 10 upgrade

[Dmitry Shalashov]

We tried to apply the patch on 10.1 source, but something is wrong it seems:

patch -p1 < ../1.patch
(Stripping trailing CRs from patch; use --binary to disable.)
patching file src/backend/optimizer/plan/analyzejoins.c
(Stripping trailing CRs from patch; use --binary to disable.)
patching file src/backend/utils/adt/selfuncs.c
Hunk #1 succeeded at 3270 (offset -91 lines).
Hunk #2 succeeded at 3304 (offset -91 lines).
Hunk #3 succeeded at 3313 (offset -91 lines).
Hunk #4 succeeded at 3393 (offset -91 lines).
patch unexpectedly ends in middle of line
Hunk #5 succeeded at 3570 with fuzz 1 (offset -91 lines).


Dmitry Shalashov, relap.io & surfingbird.ru

2017-11-23 2:07 GMT+03:00 Tom Lane :

> Dmitry Shalashov  writes:
> > Turns out we had not 9.6 but 9.5.
>
> I'd managed to reproduce the weird planner behavior locally in the
> regression database:
>
> regression=# create table foo (f1 int[], f2 int);
> CREATE TABLE
> regression=# explain select * from tenk1 where unique2 in (select distinct
> unnest(f1) from foo where f2=1);
> QUERY PLAN
> 
> ---
>  Nested Loop  (cost=30.85..80.50 rows=6 width=244)
>->  HashAggregate  (cost=30.57..30.63 rows=6 width=4)
>  Group Key: (unnest(foo.f1))
>  ->  HashAggregate  (cost=30.42..30.49 rows=6 width=4)
>Group Key: unnest(foo.f1)
>->  ProjectSet  (cost=0.00..28.92 rows=600 width=4)
>  ->  Seq Scan on foo  (cost=0.00..25.88 rows=6
> width=32)
>Filter: (f2 = 1)
>->  Index Scan using tenk1_unique2 on tenk1  (cost=0.29..8.30 rows=1
> width=244)
>  Index Cond: (unique2 = (unnest(foo.f1)))
> (10 rows)
>
> Digging into it, the reason for the duplicate HashAggregate step was that
> query_supports_distinctness() punted on SRFs-in-the-targetlist, basically
> on the argument that it wasn't worth extra work to handle that case.
> Thinking a bit harder, it seems to me that the correct analysis is:
> 1. If we are proving distinctness on the grounds of a DISTINCT clause,
> then it doesn't matter whether there are any SRFs, because DISTINCT
> removes duplicates after tlist SRF expansion.
> 2. But tlist SRFs break the ability to prove distinctness on the grounds
> of GROUP BY, unless all of them are within grouping columns.
> It still seems like detecting the second case is harder than it's worth,
> but we can trivially handle the first case, with little more than some
> code rearrangement.
>
> The other problem is that the output rowcount of the sub-select (ie, of
> the HashAggregate) is being estimated as though the SRF weren't there.
> This turns out to be because estimate_num_groups() doesn't consider the
> possibility of SRFs in the grouping columns.  It never has, but in 9.6 and
> before the problem was masked by the fact that grouping_planner scaled up
> the result rowcount by tlist_returns_set_rows() *after* performing
> grouping.  Now we're effectively doing that in the other order, which is
> more correct, but that means estimate_num_groups() has to apply some sort
> of adjustment.  I suggest that it just multiply its old estimate by the
> maximum of the SRF expansion counts.  That's likely to be an overestimate,
> but it's really hard to do better without specific knowledge of the
> individual SRF's behavior.
>
> In short, I propose the attached fixes.  I've checked this and it seems
> to fix Dmitry's original problem according to the test case he sent
> off-list.
>
> regards, tom lane
>
>

Re: Query became very slow after 9.6 -> 10 upgrade

[Tom Lane]

Dmitry Shalashov  writes:
> We tried to apply the patch on 10.1 source, but something is wrong it seems:
> patch -p1 < ../1.patch
> (Stripping trailing CRs from patch; use --binary to disable.)
> patching file src/backend/optimizer/plan/analyzejoins.c
> (Stripping trailing CRs from patch; use --binary to disable.)
> patching file src/backend/utils/adt/selfuncs.c
> Hunk #1 succeeded at 3270 (offset -91 lines).
> Hunk #2 succeeded at 3304 (offset -91 lines).
> Hunk #3 succeeded at 3313 (offset -91 lines).
> Hunk #4 succeeded at 3393 (offset -91 lines).
> patch unexpectedly ends in middle of line
> Hunk #5 succeeded at 3570 with fuzz 1 (offset -91 lines).

The line number offsets are expected when applying to v10, but it looks
like you failed to transfer the attachment cleanly ... there were
certainly not CRs in it when I mailed it.  The output on v10
should just look like

patching file src/backend/optimizer/plan/analyzejoins.c
patching file src/backend/utils/adt/selfuncs.c
Hunk #1 succeeded at 3270 (offset -91 lines).
Hunk #2 succeeded at 3304 (offset -91 lines).
Hunk #3 succeeded at 3313 (offset -91 lines).
Hunk #4 succeeded at 3393 (offset -91 lines).
Hunk #5 succeeded at 3570 (offset -91 lines).

regards, tom lane

pgpool + repmgr - who should be responsible for failover

[Mariel Cherkassky]

I configured replication with repmgr on 2 postgresql 9.6.3 nodes. Both of
those utilities can handle failover but I should let only one of them do
it. So, I wanted to know who should be the one responsible for the failover
and why ?

Thanks .

Issue with postgres login

[Daulat Ram]

Hi team,

We are facing the below issue while logging with postgres as given below:

[centos@ip-192-90-2-208 ~]$ su - postgres
Password:
Last login: Thu Nov 23 16:15:45 UTC 2017 on pts/1
-bash-4.2$ psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
-bash-4.2$


OS: Centos
AWS environment

Regards,
Daulat



DISCLAIMER:

This email message is for the sole use of the intended recipient(s) and may 
contain confidential and privileged information. Any unauthorized review, use, 
disclosure or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply email and destroy all copies of 
the original message. Check all attachments for viruses before opening them. 
All views or opinions presented in this e-mail are those of the author and may 
not reflect the opinion of Cyient or those of our affiliates.

Re: Issue with postgres login

[Bob Strecansky]

It seems you may have just the postgresql package installed and not the
postgresql-server package. This Linode guide may be helpful:

https://www.linode.com/docs/databases/postgresql/how-to-install-postgresql-relational-databases-on-centos-7

-B
On Thu, Nov 23, 2017 at 22:28 Daulat Ram  wrote:

> Hi team,
>
>
>
> We are facing the below issue while logging with postgres as given below:
>
>
>
> [centos@ip-192-90-2-208 ~]$ su - postgres
>
> Password:
>
> *Last login: Thu Nov 23 16:15:45 UTC 2017 on pts/1*
>
> *-bash-4.2$ psql*
>
> *psql: could not connect to server: No such file or directory*
>
> *Is the server running locally and accepting*
>
> *connections on Unix domain socket "/tmp/.s.PGSQL.5432"?*
>
> -bash-4.2$
>
>
>
>
>
> *OS: Centos*
>
> *AWS environment*
>
>
>
> Regards,
>
> Daulat
>
> --
>
> DISCLAIMER:
>
> This email message is for the sole use of the intended recipient(s) and
> may contain confidential and privileged information. Any unauthorized
> review, use, disclosure or distribution is prohibited. If you are not the
> intended recipient, please contact the sender by reply email and destroy
> all copies of the original message. Check all attachments for viruses
> before opening them. All views or opinions presented in this e-mail are
> those of the author and may not reflect the opinion of Cyient or those of
> our affiliates.
>
-- 
Thanks,

-B

RE: Re: Issue with postgres login

[Daulat Ram]

Hi team,

But it was working fine earlier.

Another message I have seen after starting by
-bash-4.2$  /usr/pgsql-9.3/bin/pg_ctl -D /var/lib/pgsql/9.3/data/ -l logfile 
start
< 2017-11-24 04:00:40.780 UTC >FATAL:  data directory "/var/lib/pgsql/9.3/data" 
has group or world access
< 2017-11-24 04:00:40.780 UTC >DETAIL:  Permissions should be u=rwx (0700).

For more details please refer to trail mail
Regards,
Daulat
From: Bob Strecansky [mailto:b...@mailchimp.com]
Sent: 24 November, 2017 9:07 AM
To: Daulat Ram 
Cc: pgsql-performa...@postgresql.org
Subject: [EXTERNAL]Re: Issue with postgres login

It seems you may have just the postgresql package installed and not the 
postgresql-server package. This Linode guide may be helpful:

https://www.linode.com/docs/databases/postgresql/how-to-install-postgresql-relational-databases-on-centos-7

-B
On Thu, Nov 23, 2017 at 22:28 Daulat Ram 
mailto:daulat@cyient.com>> wrote:
Hi team,

We are facing the below issue while logging with postgres as given below:

[centos@ip-192-90-2-208 ~]$ su - postgres
Password:
Last login: Thu Nov 23 16:15:45 UTC 2017 on pts/1
-bash-4.2$ psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
-bash-4.2$


OS: Centos
AWS environment

Regards,
Daulat



DISCLAIMER:

This email message is for the sole use of the intended recipient(s) and may 
contain confidential and privileged information. Any unauthorized review, use, 
disclosure or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply email and destroy all copies of 
the original message. Check all attachments for viruses before opening them. 
All views or opinions presented in this e-mail are those of the author and may 
not reflect the opinion of Cyient or those of our affiliates.
--
Thanks,

-B

Re: Re: Issue with postgres login

[Bob Strecansky]

It appears the permissions with your data directory have changed. According
to the error message, you should set the appropriate permissions for
/var/lib/pgsql/9.3/data

-B

On Thu, Nov 23, 2017 at 23:04 Daulat Ram  wrote:

> Hi team,
>
>
>
> But it was working fine earlier.
>
>
>
> Another message I have seen after starting by
>
> -bash-4.2$  /usr/pgsql-9.3/bin/pg_ctl -D /var/lib/pgsql/9.3/data/ -l
> logfile start
>
> < 2017-11-24 04:00:40.780 UTC >FATAL:  data directory
> "/var/lib/pgsql/9.3/data" has group or world access
>
> < 2017-11-24 04:00:40.780 UTC >DETAIL:  Permissions should be u=rwx (0700).
>
>
>
> For more details please refer to trail mail
>
> Regards,
>
> Daulat
>
> *From:* Bob Strecansky [mailto:b...@mailchimp.com]
> *Sent:* 24 November, 2017 9:07 AM
> *To:* Daulat Ram 
> *Cc:* pgsql-performa...@postgresql.org
> *Subject:* [EXTERNAL]Re: Issue with postgres login
>
>
>
> It seems you may have just the postgresql package installed and not the
> postgresql-server package. This Linode guide may be helpful:
>
>
> https://www.linode.com/docs/databases/postgresql/how-to-install-postgresql-relational-databases-on-centos-7
>
> -B
>
> On Thu, Nov 23, 2017 at 22:28 Daulat Ram  wrote:
>
> Hi team,
>
>
>
> We are facing the below issue while logging with postgres as given below:
>
>
>
> [centos@ip-192-90-2-208 ~]$ su - postgres
>
> Password:
>
> *Last login: Thu Nov 23 16:15:45 UTC 2017 on pts/1*
>
> *-bash-4.2$ psql*
>
> *psql: could not connect to server: No such file or directory*
>
> *Is the server running locally and accepting*
>
> *connections on Unix domain socket "/tmp/.s.PGSQL.5432"?*
>
> -bash-4.2$
>
>
>
>
>
> *OS: Centos*
>
> *AWS environment*
>
>
>
> Regards,
>
> Daulat
>
>
> --
>
>
> DISCLAIMER:
>
> This email message is for the sole use of the intended recipient(s) and
> may contain confidential and privileged information. Any unauthorized
> review, use, disclosure or distribution is prohibited. If you are not the
> intended recipient, please contact the sender by reply email and destroy
> all copies of the original message. Check all attachments for viruses
> before opening them. All views or opinions presented in this e-mail are
> those of the author and may not reflect the opinion of Cyient or those of
> our affiliates.
>
> --
>
> Thanks,
>
> -B
>
-- 
Thanks,

-B

RE: Re: Re: Issue with postgres login

[Daulat Ram]

Hello team,

We have the data directory under /var/lib/pgsql/9.3/data/

FYI. Below are the permissions to group and user on each directory. Please do 
help .

drwxr-xr-x. 19 root root  267 Nov 22 08:42 var/
drwxr-xr-x. 30 root root 4096 Nov 23 09:09 lib/
drwxrwxr-x. 3 postgres postgres  129 Nov 23 16:05 pgsql/
drwxrwxr-x. 4 postgres postgres   66 Nov 24 04:00 9.3/
drwxrwxr-x. 16 postgres postgres 4096 Nov 24 03:42 data/

-rwxrwxrwx. 1 postgres postgres 4 Nov 23 09:09 PG_VERSION
drwxrwxr-x. 2 postgres postgres 6 Nov 23 09:09 pg_twophase
drwxrwxr-x. 2 postgres postgres 6 Nov 23 09:09 pg_tblspc
drwxrwxr-x. 2 postgres postgres 6 Nov 23 09:09 pg_snapshots
drwxrwxr-x. 2 postgres postgres 6 Nov 23 09:09 pg_serial
drwxrwxr-x. 4 postgres postgres36 Nov 23 09:09 pg_multixact
-rwxrwxrwx. 1 postgres postgres  1636 Nov 23 09:09 pg_ident.conf
drwxrwxr-x. 3 postgres postgres60 Nov 23 09:09 pg_xlog
drwxrwxr-x. 2 postgres postgres18 Nov 23 09:09 pg_subtrans
drwxrwxr-x. 2 postgres postgres18 Nov 23 09:09 pg_clog
drwxrwxr-x. 5 postgres postgres41 Nov 23 09:09 base
-rwxrwxrwx. 1 postgres postgres59 Nov 23 09:12 postmaster.opts
drwxrwxr-x. 2 postgres postgres18 Nov 23 09:12 pg_notify
drwxrwxr-x. 2 postgres postgres32 Nov 23 09:12 pg_log
drwxrwxr-x. 2 postgres postgres  4096 Nov 23 09:12 global
-rwxrwxrwx. 1 postgres postgres  4232 Nov 23 10:50 pg_hba.conf_backup
-rwxrwxrwx. 1 postgres postgres  9273 Nov 23 13:27 pg_hba.conf
drwxrwxr-x. 2 postgres postgres 6 Nov 23 14:32 pg_stat_tmp
drwxrwxr-x. 2 postgres postgres63 Nov 23 14:32 pg_stat
-rwxr-xr-x. 1 root root 20137 Nov 23 14:41 postgresql.conf.bak_231117
-rwxrwxrwx. 1 postgres postgres 20230 Nov 23 15:03 postgresql.conf_original
-rwx--. 1 postgres postgres 20137 Nov 24 03:30 postgresql.conf_24_nov
-rwx--. 1 postgres postgres 20124 Nov 24 03:42 postgresql.conf

Also we have change the below parameters in Postgres.conf file to resolve the 
issue but still not resolved.

listen_addresses = '*
port = 5432
max_connections = 100
unix_socket_directories = '/tmp'
unix_socket_group = ''
unix_socket_permissions = 0777

Regards,
Daulat

From: Bob Strecansky [mailto:b...@mailchimp.com]
Sent: 24 November, 2017 9:37 AM
To: Daulat Ram 
Cc: pgsql-performa...@postgresql.org
Subject: [EXTERNAL]Re: Re: Issue with postgres login

It appears the permissions with your data directory have changed. According to 
the error message, you should set the appropriate permissions for 
/var/lib/pgsql/9.3/data

-B

On Thu, Nov 23, 2017 at 23:04 Daulat Ram 
mailto:daulat@cyient.com>> wrote:
Hi team,

But it was working fine earlier.

Another message I have seen after starting by
-bash-4.2$  /usr/pgsql-9.3/bin/pg_ctl -D /var/lib/pgsql/9.3/data/ -l logfile 
start
< 2017-11-24 04:00:40.780 UTC >FATAL:  data directory "/var/lib/pgsql/9.3/data" 
has group or world access
< 2017-11-24 04:00:40.780 UTC >DETAIL:  Permissions should be u=rwx (0700).

For more details please refer to trail mail
Regards,
Daulat
From: Bob Strecansky [mailto:b...@mailchimp.com]
Sent: 24 November, 2017 9:07 AM
To: Daulat Ram mailto:daulat@cyient.com>>
Cc: pgsql-performa...@postgresql.org
Subject: [EXTERNAL]Re: Issue with postgres login

It seems you may have just the postgresql package installed and not the 
postgresql-server package. This Linode guide may be helpful:

https://www.linode.com/docs/databases/postgresql/how-to-install-postgresql-relational-databases-on-centos-7

-B
On Thu, Nov 23, 2017 at 22:28 Daulat Ram 
mailto:daulat@cyient.com>> wrote:
Hi team,

We are facing the below issue while logging with postgres as given below:

[centos@ip-192-90-2-208 ~]$ su - postgres
Password:
Last login: Thu Nov 23 16:15:45 UTC 2017 on pts/1
-bash-4.2$ psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
-bash-4.2$


OS: Centos
AWS environment

Regards,
Daulat



DISCLAIMER:

This email message is for the sole use of the intended recipient(s) and may 
contain confidential and privileged information. Any unauthorized review, use, 
disclosure or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply email and destroy all copies of 
the original message. Check all attachments for viruses before opening them. 
All views or opinions presented in this e-mail are those of the author and may 
not reflect the opinion of Cyient or those of our affiliates.
--
Thanks,

-B
--
Thanks,

-B

Re: Re: Re: Issue with postgres login

[Bob Strecansky]

It appears our file permissions are too wide open (which correlated with
the error message you shared in your previous email. Changing those to 700
should get you going in the correct direction.

-B

On Thu, Nov 23, 2017 at 23:23 Daulat Ram  wrote:

> Hello team,
>
>
>
> We have the data directory under */var/lib/pgsql/9.3/data/*
>
>
>
> FYI. Below are the permissions to group and user on each directory. Please
> do help .
>
>
>
> drwxr-xr-x. 19 root root  267 Nov 22 08:42 *var/*
>
> drwxr-xr-x. 30 root root 4096 Nov 23 09:09 *lib/*
>
> drwxrwxr-x. 3 postgres postgres  129 Nov 23 16:05 *pgsql/*
>
> drwxrwxr-x. 4 postgres postgres   66 Nov 24 04:00 *9.3/*
>
> drwxrwxr-x. 16 postgres postgres 4096 Nov 24 03:42 *data/*
>
>
>
> -rwxrwxrwx. 1 postgres postgres 4 Nov 23 09:09 PG_VERSION
>
> drwxrwxr-x. 2 postgres postgres 6 Nov 23 09:09 pg_twophase
>
> drwxrwxr-x. 2 postgres postgres 6 Nov 23 09:09 pg_tblspc
>
> drwxrwxr-x. 2 postgres postgres 6 Nov 23 09:09 pg_snapshots
>
> drwxrwxr-x. 2 postgres postgres 6 Nov 23 09:09 pg_serial
>
> drwxrwxr-x. 4 postgres postgres36 Nov 23 09:09 pg_multixact
>
> -rwxrwxrwx. 1 postgres postgres  1636 Nov 23 09:09 pg_ident.conf
>
> drwxrwxr-x. 3 postgres postgres60 Nov 23 09:09 pg_xlog
>
> drwxrwxr-x. 2 postgres postgres18 Nov 23 09:09 pg_subtrans
>
> drwxrwxr-x. 2 postgres postgres18 Nov 23 09:09 pg_clog
>
> drwxrwxr-x. 5 postgres postgres41 Nov 23 09:09 base
>
> -rwxrwxrwx. 1 postgres postgres59 Nov 23 09:12 postmaster.opts
>
> drwxrwxr-x. 2 postgres postgres18 Nov 23 09:12 pg_notify
>
> drwxrwxr-x. 2 postgres postgres32 Nov 23 09:12 pg_log
>
> drwxrwxr-x. 2 postgres postgres  4096 Nov 23 09:12 global
>
> -rwxrwxrwx. 1 postgres postgres  4232 Nov 23 10:50 pg_hba.conf_backup
>
> -rwxrwxrwx. 1 postgres postgres  9273 Nov 23 13:27 pg_hba.conf
>
> drwxrwxr-x. 2 postgres postgres 6 Nov 23 14:32 pg_stat_tmp
>
> drwxrwxr-x. 2 postgres postgres63 Nov 23 14:32 pg_stat
>
> -rwxr-xr-x. 1 root root 20137 Nov 23 14:41
> postgresql.conf.bak_231117
>
> -rwxrwxrwx. 1 postgres postgres 20230 Nov 23 15:03 postgresql.conf_original
>
> -rwx--. 1 postgres postgres 20137 Nov 24 03:30 postgresql.conf_24_nov
>
> -rwx--. 1 postgres postgres 20124 Nov 24 03:42 postgresql.conf
>
>
>
> Also we have change the below parameters in *Postgres.conf* file to
> resolve the issue but still not resolved.
>
>
>
> *listen_addresses = '*   *
>
> *port = 5432 *
>
> *max_connections = 100   *
>
> *unix_socket_directories = '/tmp'*
>
> *unix_socket_group = ''  *
>
> *unix_socket_permissions = 0777  *
>
>
>
> Regards,
>
> Daulat
>
>
>
> *From:* Bob Strecansky [mailto:b...@mailchimp.com]
> *Sent:* 24 November, 2017 9:37 AM
>
>
> *To:* Daulat Ram 
> *Cc:* pgsql-performa...@postgresql.org
>
> *Subject:* [EXTERNAL]Re: Re: Issue with postgres login
>
>
>
> It appears the permissions with your data directory have changed.
> According to the error message, you should set the appropriate permissions
> for /var/lib/pgsql/9.3/data
>
>
>
> -B
>
>
>
> On Thu, Nov 23, 2017 at 23:04 Daulat Ram  wrote:
>
> Hi team,
>
>
>
> But it was working fine earlier.
>
>
>
> Another message I have seen after starting by
>
> -bash-4.2$  /usr/pgsql-9.3/bin/pg_ctl -D /var/lib/pgsql/9.3/data/ -l
> logfile start
>
> < 2017-11-24 04:00:40.780 UTC >FATAL:  data directory
> "/var/lib/pgsql/9.3/data" has group or world access
>
> < 2017-11-24 04:00:40.780 UTC >DETAIL:  Permissions should be u=rwx (0700).
>
>
>
> For more details please refer to trail mail
>
> Regards,
>
> Daulat
>
> *From:* Bob Strecansky [mailto:b...@mailchimp.com]
> *Sent:* 24 November, 2017 9:07 AM
> *To:* Daulat Ram 
> *Cc:* pgsql-performa...@postgresql.org
> *Subject:* [EXTERNAL]Re: Issue with postgres login
>
>
>
> It seems you may have just the postgresql package installed and not the
> postgresql-server package. This Linode guide may be helpful:
>
>
> https://www.linode.com/docs/databases/postgresql/how-to-install-postgresql-relational-databases-on-centos-7
>
> -B
>
> On Thu, Nov 23, 2017 at 22:28 Daulat Ram  wrote:
>
> Hi team,
>
>
>
> We are facing the below issue while logging with postgres as given below:
>
>
>
> [centos@ip-192-90-2-208 ~]$ su - postgres
>
> Password:
>
> *Last login: Thu Nov 23 16:15:45 UTC 2017 on pts/1*
>
> *-bash-4.2$ psql*
>
> *psql: could not connect to server: No such file or directory*
>
> *Is the server running locally and accepting*
>
> *connections on Unix domain socket "/tmp/.s.PGSQL.5432"?*
>
> -bash-4.2$
>
>
>
>
>
> *OS: Centos*
>
> *AWS environment*
>
>
>
> Regards,
>
> Daulat
>
>
> --
>
>
> DISCLAIMER:
>
> This email message is for the sole use of the intended recipient(s) and
> may contain confidential and privileged information. Any unauthorized
> review, use, disclosure or distribution is prohibited. If you are not th

RE: Re: Re: Re: Issue with postgres login

[Daulat Ram]

Thanks for your response. But now I am giving below error while restore the 
backup
./pg_restore -i -h localhost -p 5432 -U postgres --role postgres -d fleotan -W 
-v /backup/fleotanOne11.backup >& /backup/fleotanOne32_restore.log

pg_restore: [archiver (db)] connection to database "fleotan" failed: FATAL:  
Ident authentication failed for user "postgres"


pga_hba.conf have :

# TYPE  DATABASEUSERADDRESS METHOD

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

Plz suggest

Regards,
Daulat
From: Bob Strecansky [mailto:b...@mailchimp.com]
Sent: 24 November, 2017 9:56 AM
To: Daulat Ram 
Cc: pgsql-performa...@postgresql.org
Subject: [EXTERNAL]Re: Re: Re: Issue with postgres login

It appears our file permissions are too wide open (which correlated with the 
error message you shared in your previous email. Changing those to 700 should 
get you going in the correct direction.

-B

On Thu, Nov 23, 2017 at 23:23 Daulat Ram 
mailto:daulat@cyient.com>> wrote:
Hello team,

We have the data directory under /var/lib/pgsql/9.3/data/

FYI. Below are the permissions to group and user on each directory. Please do 
help .

drwxr-xr-x. 19 root root  267 Nov 22 08:42 var/
drwxr-xr-x. 30 root root 4096 Nov 23 09:09 lib/
drwxrwxr-x. 3 postgres postgres  129 Nov 23 16:05 pgsql/
drwxrwxr-x. 4 postgres postgres   66 Nov 24 04:00 9.3/
drwxrwxr-x. 16 postgres postgres 4096 Nov 24 03:42 data/

-rwxrwxrwx. 1 postgres postgres 4 Nov 23 09:09 PG_VERSION
drwxrwxr-x. 2 postgres postgres 6 Nov 23 09:09 pg_twophase
drwxrwxr-x. 2 postgres postgres 6 Nov 23 09:09 pg_tblspc
drwxrwxr-x. 2 postgres postgres 6 Nov 23 09:09 pg_snapshots
drwxrwxr-x. 2 postgres postgres 6 Nov 23 09:09 pg_serial
drwxrwxr-x. 4 postgres postgres36 Nov 23 09:09 pg_multixact
-rwxrwxrwx. 1 postgres postgres  1636 Nov 23 09:09 pg_ident.conf
drwxrwxr-x. 3 postgres postgres60 Nov 23 09:09 pg_xlog
drwxrwxr-x. 2 postgres postgres18 Nov 23 09:09 pg_subtrans
drwxrwxr-x. 2 postgres postgres18 Nov 23 09:09 pg_clog
drwxrwxr-x. 5 postgres postgres41 Nov 23 09:09 base
-rwxrwxrwx. 1 postgres postgres59 Nov 23 09:12 postmaster.opts
drwxrwxr-x. 2 postgres postgres18 Nov 23 09:12 pg_notify
drwxrwxr-x. 2 postgres postgres32 Nov 23 09:12 pg_log
drwxrwxr-x. 2 postgres postgres  4096 Nov 23 09:12 global
-rwxrwxrwx. 1 postgres postgres  4232 Nov 23 10:50 pg_hba.conf_backup
-rwxrwxrwx. 1 postgres postgres  9273 Nov 23 13:27 pg_hba.conf
drwxrwxr-x. 2 postgres postgres 6 Nov 23 14:32 pg_stat_tmp
drwxrwxr-x. 2 postgres postgres63 Nov 23 14:32 pg_stat
-rwxr-xr-x. 1 root root 20137 Nov 23 14:41 postgresql.conf.bak_231117
-rwxrwxrwx. 1 postgres postgres 20230 Nov 23 15:03 postgresql.conf_original
-rwx--. 1 postgres postgres 20137 Nov 24 03:30 postgresql.conf_24_nov
-rwx--. 1 postgres postgres 20124 Nov 24 03:42 postgresql.conf

Also we have change the below parameters in Postgres.conf file to resolve the 
issue but still not resolved.

listen_addresses = '*
port = 5432
max_connections = 100
unix_socket_directories = '/tmp'
unix_socket_group = ''
unix_socket_permissions = 0777

Regards,
Daulat

From: Bob Strecansky [mailto:b...@mailchimp.com]
Sent: 24 November, 2017 9:37 AM

To: Daulat Ram mailto:daulat@cyient.com>>
Cc: pgsql-performa...@postgresql.org
Subject: [EXTERNAL]Re: Re: Issue with postgres login

It appears the permissions with your data directory have changed. According to 
the error message, you should set the appropriate permissions for 
/var/lib/pgsql/9.3/data

-B

On Thu, Nov 23, 2017 at 23:04 Daulat Ram 
mailto:daulat@cyient.com>> wrote:
Hi team,

But it was working fine earlier.

Another message I have seen after starting by
-bash-4.2$  /usr/pgsql-9.3/bin/pg_ctl -D /var/lib/pgsql/9.3/data/ -l logfile 
start
< 2017-11-24 04:00:40.780 UTC >FATAL:  data directory "/var/lib/pgsql/9.3/data" 
has group or world access
< 2017-11-24 04:00:40.780 UTC >DETAIL:  Permissions should be u=rwx (0700).

For more details please refer to trail mail
Regards,
Daulat
From: Bob Strecansky [mailto:b...@mailchimp.com]
Sent: 24 November, 2017 9:07 AM
To: Daulat Ram mailto:daulat@cyient.com>>
Cc: p