Re: [GENERAL] cluster question

[Alex Samad]

On 17 August 2017 at 10:51, Ian Barwick  wrote:

> On 08/16/2017 02:41 PM, Alex Samad wrote:
> (...)
> >
> > okay think I have it setup, but when i do a switch over it gets stuck
> here.
> >
> >
> >
> > NOTICE: STANDBY PROMOTE successful
> > NOTICE: Executing pg_rewind on old master server
> > NOTICE: 3 files copied to /var/lib/pgsql/9.6/data
> > NOTICE: restarting server using '/usr/pgsql-9.6/bin/pg_ctl  -w -D
> /var/lib/pgsql/9.6/data -m fast restart'
> > pg_ctl: PID file "/var/lib/pgsql/9.6/data/postmaster.pid" does not exist
> > Is server running?
> > starting server anyway
> > NOTICE: STANDBY FOLLOW successful
>
> From the repmgr README:
>
> >> You must ensure that following a server start using `pg_ctl`, log output
> >> is not send to STDERR (the default behaviour). If logging is not
> configured,
> >> we recommend setting `logging_collector=on` in `postgresql.conf` and
> >> providing an explicit `-l/--log` setting in `repmgr.conf`'s
> `pg_ctl_options`
> >> parameter.
>
> i.e. when the old primary is restarted with:
>
> /usr/pgsql-9.6/bin/pg_ctl  -w -D /var/lib/pgsql/9.6/data -m fast
> restart
>
> the calling process hangs, waiting for logging output from pg_ctl.
> In "repmgr.conf" set "pg_ctl_options" to something like:
>
> pg_ctl_options='-l /path/to/log'
>
>
> Regards


Thanks, simple when you know, too many new things to look at



>
>
> Ian Barwick
>
> --
>  Ian Barwick   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

Re: [GENERAL] no pg_hba.conf entry for replication connection

[armand pirvu]

Yep relaoded

But darn typo

Finger going too fast I guess

Thanks bunch
AP 

> On Aug 16, 2017, at 8:03 PM, Ian Barwick  wrote:
> 
> On 08/17/2017 05:26 AM, armand pirvu wrote:
>> Hi
>> master (172.16.26.7) and slave (172.16.26.4)
>> master runs on port 5433 though
>> SELECT pglogical.create_subscription( subscription_name := 'shw_sub',
>> replication_sets := '{shw_set}',
>> provider_dsn := 'host=172.16.26.7 port=5433 dbname=levregdb user=repuser');
>> ERROR:  could not connect to the postgresql server in replication mode: 
>> FATAL:  no pg_hba.conf entry for replication connection from host 
>> "172.16.26.4", user "repuser", SSL off
>> DETAIL:  dsn was:  host=172.16.26.7 port=5433 dbname=levregdb user=repuser
>> My pg_hba.conf from both
>> local  replication  repuser  md5
>> host   replication  repuser  127.0.0.1/32  mds5
>> host   replication  repuser  0.0.0.0/0 mds5
>> local  all repuser  md5
>> host   all repuser  127.0.0.1/32  md5
>> host   all repuser  0.0.0.0/0 md5
>> So what am I missing ?
>> Strange is that I followed
> 
> Did you reload the configuration after changing pg_hba.conf, e.g.
> "SELECT pg_reload_conf()"?
> 
> Also, looks like you have a typo:
> 
> > host   replication  repuser  127.0.0.1/32  mds5
> > host   replication  repuser  0.0.0.0/0 mds5
> 
> mds5 -> md5
> 
> 
> Regards
> 
> Ian Barwick
> 
> -- 
> Ian Barwick   http://www.2ndQuadrant.com/ 
> 
> PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] no pg_hba.conf entry for replication connection

[Ian Barwick]

On 08/17/2017 05:26 AM, armand pirvu wrote:

Hi



master (172.16.26.7) and slave (172.16.26.4)


master runs on port 5433 though

SELECT pglogical.create_subscription( subscription_name := 'shw_sub',
replication_sets := '{shw_set}',
provider_dsn := 'host=172.16.26.7 port=5433 dbname=levregdb user=repuser');
ERROR:  could not connect to the postgresql server in replication mode: FATAL:  no pg_hba.conf 
entry for replication connection from host "172.16.26.4", user "repuser", SSL 
off
DETAIL:  dsn was:  host=172.16.26.7 port=5433 dbname=levregdb user=repuser

My pg_hba.conf from both

local  replication  repuser  md5
host   replication  repuser  127.0.0.1/32  mds5
host   replication  repuser  0.0.0.0/0 mds5
local  all repuser  md5
host   all repuser  127.0.0.1/32  md5
host   all repuser  0.0.0.0/0 md5

So what am I missing ?
Strange is that I followed


Did you reload the configuration after changing pg_hba.conf, e.g.
"SELECT pg_reload_conf()"?

Also, looks like you have a typo:

> host   replication  repuser  127.0.0.1/32  mds5
> host   replication  repuser  0.0.0.0/0 mds5

mds5 -> md5


Regards

Ian Barwick

--
 Ian Barwick   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


--
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] cluster question

[Ian Barwick]

On 08/16/2017 02:41 PM, Alex Samad wrote:
(...)
>
> okay think I have it setup, but when i do a switch over it gets stuck here.
>
>
>
> NOTICE: STANDBY PROMOTE successful
> NOTICE: Executing pg_rewind on old master server
> NOTICE: 3 files copied to /var/lib/pgsql/9.6/data
> NOTICE: restarting server using '/usr/pgsql-9.6/bin/pg_ctl  -w -D 
/var/lib/pgsql/9.6/data -m fast restart'
> pg_ctl: PID file "/var/lib/pgsql/9.6/data/postmaster.pid" does not exist
> Is server running?
> starting server anyway
> NOTICE: STANDBY FOLLOW successful

From the repmgr README:

>> You must ensure that following a server start using `pg_ctl`, log output
>> is not send to STDERR (the default behaviour). If logging is not configured,
>> we recommend setting `logging_collector=on` in `postgresql.conf` and
>> providing an explicit `-l/--log` setting in `repmgr.conf`'s `pg_ctl_options`
>> parameter.

i.e. when the old primary is restarted with:

/usr/pgsql-9.6/bin/pg_ctl  -w -D /var/lib/pgsql/9.6/data -m fast restart

the calling process hangs, waiting for logging output from pg_ctl.
In "repmgr.conf" set "pg_ctl_options" to something like:

pg_ctl_options='-l /path/to/log'


Regards

Ian Barwick

--
 Ian Barwick   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


--
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] Begginers question

[Alex Samad]

Great I will add it to my notes.

Thanks

On 16 August 2017 at 20:55, Achilleas Mantzios  wrote:

> On 16/08/2017 13:46, Alex Samad wrote:
>
>
>
> On 16 August 2017 at 16:16, Michael Paquier 
> wrote:
>
>> On Wed, Aug 16, 2017 at 2:32 PM, Alex Samad  wrote:
>> > 1) why did it fill up this time and not previously
>> > I add this
>> > archive_command = '/bin/true'
>> > wal_keep_segments = 1000 # <<< I'm guessing its this
>> >
>> > 2) how do I fix up, can I just remove the files from the pg_xlog
>> directory
>>
>> Don't do that. those files are managed by Postgres so you may finish
>> with a corrupted cluster. Instead you should lower the value of
>>
>
> Too late, its okay its a learning experience.
>
>
>> wal_keep_segments, reload the server parameters, and then enforce two
>> checkpoints to force WAL segments to be recycled. Note that this
>>
>
> how do I force check points
>
> checkpoint ;
> (the ; is not meant as a smiley or whatever )
>
>
>
>> depends also on the values of checkpoint_segments
>> (max_wal_size/min_wal_size in Postgres 9.5 and onwards).
>> --
>> Michael
>>
>
> thanks
>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>

[GENERAL] no pg_hba.conf entry for replication connection

[armand pirvu]

Hi



master (172.16.26.7) and slave (172.16.26.4)


master runs on port 5433 though

SELECT pglogical.create_subscription( subscription_name := 'shw_sub',
replication_sets := '{shw_set}',
provider_dsn := 'host=172.16.26.7 port=5433 dbname=levregdb user=repuser');
ERROR:  could not connect to the postgresql server in replication mode: FATAL:  
no pg_hba.conf entry for replication connection from host "172.16.26.4", user 
"repuser", SSL off
DETAIL:  dsn was:  host=172.16.26.7 port=5433 dbname=levregdb user=repuser

My pg_hba.conf from both

local  replication  repuser  md5
host   replication  repuser  127.0.0.1/32  mds5
host   replication  repuser  0.0.0.0/0 mds5
local  all repuser  md5
host   all repuser  127.0.0.1/32  md5
host   all repuser  0.0.0.0/0 md5

So what am I missing ?
Strange is that I followed

Thks for help

AP





-- 
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] Logging failed connections

[Jerry Sievers]

Stephen Cook  writes:

> Hello!
>
> When a client gets the error message about "remaining connection slots
> are reserved for non-replication superuser connections", is this logged?
> What should I be grep-ing for?

Grep for the token FATAL in your logs.

>
> Thanks!
>
>
> -- Stephen

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


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

[GENERAL] Logging failed connections

[Stephen Cook]

Hello!

When a client gets the error message about "remaining connection slots
are reserved for non-replication superuser connections", is this logged?
What should I be grep-ing for?

Thanks!


-- Stephen


-- 
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] optimize pg_tables query ( text vs varchar ) ...why ?

[gmb]

Thanks for this , Tom



--
View this message in context: 
http://www.postgresql-archive.org/optimize-pg-tables-query-text-vs-varchar-why-tp5978592p5978654.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] optimize pg_tables query ( text vs varchar ) ...why ?

[Tom Lane]

gmb  writes:
> Tom Lane-2 wrote
>> Personally I'd have left the function parameters as text and inserted
>> explicit coercions:

> Just out of curiosity , is there a reason why this will be you preference ? 

Well, if the rest of your code thinks that table names are of type text
(which is reasonable, as most of what you might want to do with them
would be better served by text), then it seems like you want to keep
this odd catalog interaction isolated within the function rather than
advertise it in the function's API.

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] optimize pg_tables query ( text vs varchar ) ...why ?

[gmb]

Thanks for taking the time, Tom.


Tom Lane-2 wrote
> After that, the planner has to implement the query, and the problem
> is that the available indexes are on "schemaname" not "schemaname::text",
> and they can only use the name = name operator anyway.  

Did some digging earlier, and found exactly what you refer to here: seq scan
when using 'text' and index scan on 'name'.
I was not aware that an "incorrect" typecast can have that effect on how the
planner choose to use indexes or not use them ( may have to go back and
review a lot of other poor performing queries as well ).


Tom Lane-2 wrote
> Personally I'd have left the function parameters as text and inserted
> explicit coercions:

Just out of curiosity , is there a reason why this will be you preference ? 
I ran some benchmarks using *function tableexists(  s name, t name )* and
the performance turned out pretty well. 
I guess, from a "readability" point of view it may be unclear to uninformed
people what a 'name' type actually is ( it was unknown to me until this
morning ), so that may be reason enough to stick with "known" types like
TEXT.

Thanks, appreciate this.

Regards
gmb



--
View this message in context: 
http://www.postgresql-archive.org/optimize-pg-tables-query-text-vs-varchar-why-tp5978592p5978619.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] optimize pg_tables query ( text vs varchar ) ...why ?

[Tom Lane]

gmb  writes:
> CREATE FUNCTION tableexists( s TEXT , t TEXT ) returns boolean as 
> $$
>   SELECT count(tablename) = 1 FROM pg_tables WHERE schemaname=$1 and
> tablename=$2;
> $$
> language sql

> When change the params of above function to VARCHAR (instead of TEXT),
> performance improved dramatically.
> We then changed params to NAME ( as per pg_tables column type ) , but the
> performance stayed more or less the same.

> Can somebody explain this to me ?

The parser has two plausible choices for interpreting the "=" operators
in your WHERE clause: they could mean the text = text operator, or the
name = name operator.  (Type varchar has no operators of its own.)

When the presented situation is name = text, the parser will choose
the text = text operator because text is a preferred type.  When the
presented situation is name = varchar or name = name, it will choose
the name = name operator due to being a closer match.  See
https://www.postgresql.org/docs/current/static/typeconv.html
So you end up with either something like "schemaname::text = param"
or "schemaname = param::name".

After that, the planner has to implement the query, and the problem
is that the available indexes are on "schemaname" not "schemaname::text",
and they can only use the name = name operator anyway.  So you're
getting either a plan like

regression=# explain SELECT count(tablename) = 1 FROM pg_tables WHERE 
schemaname='foo'::text and tablename='bar'::text;
   QUERY PLAN   
 
-
 Aggregate  (cost=116.23..116.24 rows=1 width=1)
   ->  Nested Loop  (cost=0.00..116.22 rows=1 width=64)
 Join Filter: (c.relnamespace = n.oid)
 ->  Seq Scan on pg_namespace n  (cost=0.00..1.72 rows=1 width=4)
   Filter: ((nspname)::text = 'foo'::text)
 ->  Seq Scan on pg_class c  (cost=0.00..114.48 rows=2 width=72)
   Filter: ((relkind = ANY ('{r,p}'::"char"[])) AND 
((relname)::text = 'bar'::text))
(7 rows)

or one like

regression=# explain SELECT count(tablename) = 1 FROM pg_tables WHERE 
schemaname='foo'::name and tablename='bar'::name;
QUERY PLAN  
  
--
 Aggregate  (cost=9.91..9.92 rows=1 width=1)
   ->  Nested Loop  (cost=0.28..9.91 rows=1 width=64)
 Join Filter: (c.relnamespace = n.oid)
 ->  Index Scan using pg_class_relname_nsp_index on pg_class c  
(cost=0.28..8.30 rows=1 width=72)
   Index Cond: (relname = 'bar'::name)
   Filter: (relkind = ANY ('{r,p}'::"char"[]))
 ->  Seq Scan on pg_namespace n  (cost=0.00..1.60 rows=1 width=4)
   Filter: (nspname = 'foo'::name)
(8 rows)

You don't generally have to worry about this when you're writing queries
with simple literal comparison values, because the parser will interpret
untyped literals as having the appropriate type automatically.  But in a
function, those parameters already have types, and they might not be the
most desirable ones for the purpose.

Personally I'd have left the function parameters as text and inserted
explicit coercions:

  SELECT count(tablename) = 1 FROM pg_tables
  WHERE schemaname = $1::name and tablename = $2::name;

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] optimize pg_tables query ( text vs varchar ) ...why ?

[vinny]

On 2017-08-16 14:41, gmb wrote:

Hi
For DDL purposes we make significant use of pg_catalog tables/views.
Were investigating performance issues in a typical function:

CREATE FUNCTION tableexists( s TEXT , t TEXT ) returns boolean as
$$
  SELECT count(tablename) = 1 FROM pg_tables WHERE schemaname=$1 and
tablename=$2;
$$
language sql

When change the params of above function to VARCHAR (instead of TEXT),
performance improved dramatically.
We then changed params to NAME ( as per pg_tables column type ) , but 
the

performance stayed more or less the same.

Can somebody explain this to me ? Is there a better way in which to 
handle

these ?
(This will be implemented on most object in the catalog e.g. columns,
sequences, functions, etc )

Regards
gmb



--
View this message in context:
http://www.postgresql-archive.org/optimize-pg-tables-query-text-vs-varchar-why-tp5978592.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.



A wild stab in the dark: typecasting?
pg_tables returns 'name' type, not TEXT, so some sort of transformation 
has to be done and that takestime.



--
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] pg_column_size strange result...

[Tom Lane]

ma...@kset.org writes:
> Is there a reason pg_column_size returns different sizes for a constant 
> and the same value from a table column?

The constant probably has the default choice of a four-byte length word,
while the on-disk form has been compressed by switching to a one-byte
length word.

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

[GENERAL] optimize pg_tables query ( text vs varchar ) ...why ?

[gmb]

Hi 
For DDL purposes we make significant use of pg_catalog tables/views.
Were investigating performance issues in a typical function:

CREATE FUNCTION tableexists( s TEXT , t TEXT ) returns boolean as 
$$
  SELECT count(tablename) = 1 FROM pg_tables WHERE schemaname=$1 and
tablename=$2;
$$
language sql

When change the params of above function to VARCHAR (instead of TEXT),
performance improved dramatically.
We then changed params to NAME ( as per pg_tables column type ) , but the
performance stayed more or less the same.

Can somebody explain this to me ? Is there a better way in which to handle
these ? 
(This will be implemented on most object in the catalog e.g. columns,
sequences, functions, etc )

Regards
gmb



--
View this message in context: 
http://www.postgresql-archive.org/optimize-pg-tables-query-text-vs-varchar-why-tp5978592.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] Begginers question

[Achilleas Mantzios]

On 16/08/2017 13:46, Alex Samad wrote:



On 16 August 2017 at 16:16, Michael Paquier > wrote:

On Wed, Aug 16, 2017 at 2:32 PM, Alex Samad > wrote:
> 1) why did it fill up this time and not previously
> I add this
> archive_command = '/bin/true'
> wal_keep_segments = 1000 # <<< I'm guessing its this
>
> 2) how do I fix up, can I just remove the files from the pg_xlog directory

Don't do that. those files are managed by Postgres so you may finish
with a corrupted cluster. Instead you should lower the value of


Too late, its okay its a learning experience.

wal_keep_segments, reload the server parameters, and then enforce two
checkpoints to force WAL segments to be recycled. Note that this


how do I force check points

checkpoint ;
(the ; is not meant as a smiley or whatever )


depends also on the values of checkpoint_segments
(max_wal_size/min_wal_size in Postgres 9.5 and onwards).
--
Michael


thanks



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: [GENERAL] Begginers question

[Alex Samad]

On 16 August 2017 at 16:16, Michael Paquier 
wrote:

> On Wed, Aug 16, 2017 at 2:32 PM, Alex Samad  wrote:
> > 1) why did it fill up this time and not previously
> > I add this
> > archive_command = '/bin/true'
> > wal_keep_segments = 1000 # <<< I'm guessing its this
> >
> > 2) how do I fix up, can I just remove the files from the pg_xlog
> directory
>
> Don't do that. those files are managed by Postgres so you may finish
> with a corrupted cluster. Instead you should lower the value of
>

Too late, its okay its a learning experience.


> wal_keep_segments, reload the server parameters, and then enforce two
> checkpoints to force WAL segments to be recycled. Note that this
>

how do I force check points


> depends also on the values of checkpoint_segments
> (max_wal_size/min_wal_size in Postgres 9.5 and onwards).
> --
> Michael
>

thanks

[GENERAL] pg_column_size strange result...

[marin]

Hi,

I was calculating row sizes with pg_column_size and came to this strange 
result:


CREATE TABLE t1(
c1 NUMERIC(10,7) NOT NULL DEFAULT 123.1234567
);

INSERT INTO t1(c1) VALUES (DEFAULT);

SELECT pg_column_size(c1) AS first, 
pg_column_size(123.1234567::NUMERIC(10,7)) as second, c1 = 
123.1234567::NUMERIC(10,7) AS are_equal

FROM t1;

+---++---+
| first | second | are_equal |
+---++---+
| 9 | 12 | t |
+---++---+

Is there a reason pg_column_size returns different sizes for a constant 
and the same value from a table column?


Regards,
Mladen Marinović


--
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] Begginers question

[Michael Paquier]

On Wed, Aug 16, 2017 at 2:32 PM, Alex Samad  wrote:
> 1) why did it fill up this time and not previously
> I add this
> archive_command = '/bin/true'
> wal_keep_segments = 1000 # <<< I'm guessing its this
>
> 2) how do I fix up, can I just remove the files from the pg_xlog directory

Don't do that. those files are managed by Postgres so you may finish
with a corrupted cluster. Instead you should lower the value of
wal_keep_segments, reload the server parameters, and then enforce two
checkpoints to force WAL segments to be recycled. Note that this
depends also on the values of checkpoint_segments
(max_wal_size/min_wal_size in Postgres 9.5 and onwards).
-- 
Michael


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