Re: [GENERAL] Fwd: getting error while parsing log file using pgbadger

[PAWAN SHARMA]

On Wed, Jul 26, 2017 at 11:36 PM,  wrote:

> On Wed, Jul 26, 2017 at 3:18 PM, PAWAN SHARMA 
> wrote:
> >
> > On Wed, Jul 26, 2017 at 2:42 PM, Michael Paquier <
> michael.paqu...@gmail.com> wrote:
> > On Wed, Jul 26, 2017 at 10:59 AM, PAWAN SHARMA
> >  wrote:
> > >
> > > Hi All,
> > >
> > > I am facing below error while parsing log file.
> > >
> > > [postgres@abc pgaudit]$ pgbadger -f stderr
> postgres-2017-07-25_121445.csv
> > > Can't locate Text/CSV_XS.pm in @INC (@INC contains:
> /usr/local/lib64/perl5
> > > /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl
> > > /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at
> > > /bin/pgbadger line 2620.
> >
> ...
> > Running make install
> > "/usr/bin/perl" -MExtUtils::Command::MM -e 'cp_nonempty' -- CSV_XS.bs
> blib/arch/auto/Text/CSV_XS/CSV_XS.bs 644
> > Manifying 1 pod document
> > Files found in blib/arch: installing files in blib/lib into architecture
> dependent library tree
> > Installing /root/perl5/lib/perl5/x86_64-linux-thread-multi/auto/Text/
> CSV_XS/CSV_XS.so
> > Installing /root/perl5/lib/perl5/x86_64-linux-thread-multi/Text/CSV_
> XS.pm
> > Installing /root/perl5/man/man3/Text::CSV_XS.3pm
> > Appending installation info to /root/perl5/lib/perl5/x86_64-
> linux-thread-multi/perllocal.pod
> >   HMBRAND/Text-CSV_XS-1.31.tgz
> >   /bin/make install  -- OK
> ...
> > [postgres@abc pgaudit]$ pgbadger -f stderr
> postgres-2017-07-26_00.csv  -o abc.html
> > Can't locate Text/CSV_XS.pm in @INC (@INC contains:
> /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl
> /usr/share/perl5/vendor_perl /usr/lib64/ perl5 /usr/share/perl5 .) at
> /bin/pgbadger line 2620.
>
> You have an environment problem in that the 2 different users have a
> different
> PATH and you're getting 2 different perl executables, or at least that's
> what
> it looks like to me. Please note that where you installed the module to is
> not
> listed in the @INC of the other command.
>
> I saw in another post that you have it fixed, but all you've done (from
> what I
> can see) is that you've only put a band-aide on the problem not fixed the
> root issue
> because you've installed the module into 2 different places. The root
> problem should
> still exist.
>
> You can run with a custom perl, we do; but that also means you must make
> sure
> that all apps use it by setting PATH and PERLLIB appropriately, usually by
> changing a system file and making sure all environments source it. If you
> do that,
> then PgBadger will work just fine -and- use the same perl as all of your
> other programs.
>
> HTH,
> Kevin
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Hi Kevin,

Thanks for Response.

Can you please help me, how can I run this using custom, Perl.

[GENERAL] tzdata version

[Jerome Wagner]

Hello,

As i understand it, the tzdata on which the timezone information is based
is updated regularly (semi-manually) in https://github.com/
postgres/postgres/tree/master/src/timezone

Am i correct in stating that there is currently no way to update the tzdata
database except by recompiling postgres after a new version of the tz
database has been adapted for postgres ?

Is there a way to introspect the current version of the tzdata release
(2014j, 2016h, 2017b, ..) that is embedded in a pre-compiled postgres (at
runtime or in another way) ?

Thanks
Jérôme

Re: [GENERAL] tzdata version

[Tom Lane]

Jerome Wagner  writes:
> As i understand it, the tzdata on which the timezone information is based
> is updated regularly (semi-manually) in https://github.com/
> postgres/postgres/tree/master/src/timezone

> Am i correct in stating that there is currently no way to update the tzdata
> database except by recompiling postgres after a new version of the tz
> database has been adapted for postgres ?

It's in the standard tzdata format, so if you have a copy of zic laying
about, you could download a new tzdata file set and run zic to install
the new files into the PG installation tree.  There's no need to recompile
Postgres per se.

However, if this seems like a problem to you and you are on a platform
whose vendor updates tzdata reliably, you should consider building PG
with --with-system-tzdata so that it will rely on the vendor copy.
We've always seen distributing a copy of tzdata as being mainly a
service to people stuck on platforms where that doesn't happen.

> Is there a way to introspect the current version of the tzdata release
> (2014j, 2016h, 2017b, ..) that is embedded in a pre-compiled postgres (at
> runtime or in another way) ?

I don't know of any version labeling in the tzdata files themselves
(not that I've looked very hard for one).  If you know your PG minor
release you could look into our release notes to see what tzdata
release it shipped with.

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] tzdata version

[Jerome Wagner]

Hello,

As i understand it, the tzdata on which the timezone information is based
is updated regularly (semi-manually) in
https://github.com/postgres/postgres/tree/master/src/timezone

Am i correct in stating that there is currently no way to update the tzdata
database except by recompiling postgres after a new version of the tz
database has been adapted for postgres ?

Is there a way to introspect the current version of the tzdata release
(2014j, 2016h, 2017b, ..) that is embedded in a pre-compiled postgres (at
runtime or in another way) ?

Thanks
Jérôme

Re: [GENERAL] RETURNS SETOF RECORD with 1 column

[David G. Johnston]

On Thursday, July 27, 2017, Vincenzo Romano 
wrote:
>
> The main difference is that with RETURNS SETOF RECORD I still get the
> "usual"(tm) function argument list in the usual place: between two
> parentheses.
> It's a matter of style. And a consistent one.
> But I still don't get the point for not having it for a single column.
>
>
Docs say: " When there are OUT or INOUT parameters, the RETURNS clause can
be omitted. ". Sounds like you should you do just that.

David J.

Re: [GENERAL] RETURNS SETOF RECORD with 1 column

[David G. Johnston]

On Thursday, July 27, 2017, David G. Johnston 
wrote:

> On Thursday, July 27, 2017, Vincenzo Romano  > wrote:
>>
>> The main difference is that with RETURNS SETOF RECORD I still get the
>> "usual"(tm) function argument list in the usual place: between two
>> parentheses.
>> It's a matter of style. And a consistent one.
>> But I still don't get the point for not having it for a single column.
>>
>>
> Docs say: " When there are OUT or INOUT parameters, the RETURNS clause
> can be omitted. ". Sounds like you should you do just that.
>
>
Except you'd have no where to put the "setof" modifier...So, yeah, you
probably aren't going to personal style preference catered to here.

David J.

Re: [GENERAL] Developer GUI tools for PostgreSQL

[vinny]

On 2017-07-28 06:31, Tim Uckun wrote:

I think it's funny that after all these years pgadmin3 is still the
most comprehensive GUI for postgres.


Have you looked at EMS SQL-Manager, I don't remember PgAdmin having any 
where near hte features that it has :-P



Even though it's prone to
crashing on my machine and I have paid for datagrip I still reach for
it first.




It depends entirely on your personal preference, not so much on the 
features of the tool,
there are whole subcultures in the IT world who swear by VIM. I tend to 
swear *at* VIM.


But in the end it's personal preference and requirements that decide 
which is the best tool.
I use DbSchema because of how quickly and visually I can create tables, 
but I use DataGrip to execute

queries to actually manage a database, create functions, views etc.

Most tools have free preview licences so download them try them out, see 
what feels good to you.
Just remember that a tool is not a substitute for knowledge, knowing 
where to click in a GUI

is not the same as knowing how to maintain a database.


--
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] RETURNS SETOF RECORD with 1 column

[Vincenzo Romano]

2017-07-28 8:36 GMT+02:00 David G. Johnston :
> On Thursday, July 27, 2017, David G. Johnston 
> wrote:
>>
>> On Thursday, July 27, 2017, Vincenzo Romano 
>> wrote:
>>>
>>> The main difference is that with RETURNS SETOF RECORD I still get the
>>> "usual"(tm) function argument list in the usual place: between two
>>> parentheses.
>>> It's a matter of style. And a consistent one.
>>> But I still don't get the point for not having it for a single column.
>>>
>>
>> Docs say: " When there are OUT or INOUT parameters, the RETURNS clause can
>> be omitted. ". Sounds like you should you do just that.
>>
>
> Except you'd have no where to put the "setof" modifier...So, yeah, you
> probably aren't going to personal style preference catered to here.
>
> David J.

Thanks David.

I have found the actual documentation of the inconsistent syntax.

It's here
https://www.postgresql.org/docs/9.6/static/sql-createfunction.html
at the description for the parameter "rettype":

[QUOTE]
When there are OUT or INOUT parameters, the RETURNS clause can be omitted.
If present, it must agree with the result type implied by the output parameters:
RECORD if there are **multiple output parameters**, or the same type
as the single output parameter.
The SETOF modifier indicates that the function will return a set of
items, rather than a single item.
The type of a column is referenced by writing table_name.column_name%TYPE.
[/QUOTE]

That single predicate, "multiple output parameters", is creating the
(useless?) special case for a single column output.
I would like to understand the typo protection mentioned by Tom earlier:
I need to understand the reason for creating that special case.

-- 
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS


-- 
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] Developer GUI tools for PostgreSQL

[Tim Uckun]

I don't run windows so I haven't looked at the EMS product.

On Fri, Jul 28, 2017 at 6:53 PM, vinny  wrote:

> On 2017-07-28 06:31, Tim Uckun wrote:
>
>> I think it's funny that after all these years pgadmin3 is still the
>> most comprehensive GUI for postgres.
>>
>
> Have you looked at EMS SQL-Manager, I don't remember PgAdmin having any
> where near hte features that it has :-P
>
> Even though it's prone to
>> crashing on my machine and I have paid for datagrip I still reach for
>> it first.
>>
>>
>>
> It depends entirely on your personal preference, not so much on the
> features of the tool,
> there are whole subcultures in the IT world who swear by VIM. I tend to
> swear *at* VIM.
>
> But in the end it's personal preference and requirements that decide which
> is the best tool.
> I use DbSchema because of how quickly and visually I can create tables,
> but I use DataGrip to execute
> queries to actually manage a database, create functions, views etc.
>
> Most tools have free preview licences so download them try them out, see
> what feels good to you.
> Just remember that a tool is not a substitute for knowledge, knowing where
> to click in a GUI
> is not the same as knowing how to maintain a database.
>

Re: [GENERAL] Fwd: getting error while parsing log file using pgbadger

> On Wed, Jul 27, 2017, PAWAN SHARMA  wrote:
> > On Wed, Jul 26, 2017,  wrote:
> > 
> > You have an environment problem in that the 2 different users have a 
> > different
> > PATH and you're getting 2 different perl executables, or at least that's 
> > what
> > it looks like to me. Please note that where you installed the module to is 
> > not
> > listed in the @INC of the other command.
> > ...
> > You can run with a custom perl, we do; but that also means you must make 
> > sure
> > that all apps use it by setting PATH and PERLLIB appropriately, usually by
> > changing a system file and making sure all environments source it. If you 
> > do that,
> > then PgBadger will work just fine -and- use the same perl as all of your 
> > other programs.
> 
> 
> Hi Kevin,
> 
> Thanks for Response.
>  
> Can you please help me, how can I run this using custom, Perl.


This is really beyond the scope of this list, but I'll give it 1 try to helpful.
Note: I can only answer for Linux type systems, and what you need to do is 
probably
distro specific.

The basics are to leave what was installed with the system there so the OS 
installed
tools will continue to work when they reference /usr/bin/perl. Install your 
custom
perl. Change a system file to reference it, which normally means adding the new 
dir
to PATH and PERLLIB; this might be /etc/bashrc or /etc/profile.d/perl.sh (yours 
as
it won't be from the distro) or perhaps something else. Make sure all processes 
get
these new values (the shotgun approach is to reboot the server). Make sure any 
of
your applications reference the new perl specifically (full path) or only say 
"perl"
to get it from PATH.

Those are the concepts; if you need more help beyond that, you should ask a good
system administrator who knows your server.

HTH,
Kevin


-- 
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] upsert: is there a shortcut?

[Peter Geoghegan]

Daniele Varrazzo  wrote:

Is there a way to avoid replicating the list of fields and use instead
something like (new.*) = (excluded.*) as one could do in a trigger?
(that would also imply an (id = excluded.id but it seems harmless).


This is certainly something that I've seen requests for before. I tend
to think that using such a feature would be a bit like using "SELECT *"
in production: something that provides an immediate convenience, but
creates unforeseen problems.

As an example, imagine if someone adds an "inserted_at" column, which
has "now()" as its default value. Today, a user can be pretty confident
that no existing or future query is going to change that itself, because
in order for that to happen the query would have to be written with the
explicit intention of updating "inserted_at". That property would go
away with the feature you describe. Subtleties like this could easily be
missed.

--
Peter Geoghegan


--
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] tzdata version

[Jerome Wagner]

Thank you for your answer.

When compiled with "--with-system-tzdata", does postgres need a restart
after the system is updated with the new tzdata ?

  regards, jérôme wagner

On Fri, Jul 28, 2017 at 4:06 PM, Tom Lane  wrote:

> Jerome Wagner  writes:
> > As i understand it, the tzdata on which the timezone information is based
> > is updated regularly (semi-manually) in https://github.com/
> > postgres/postgres/tree/master/src/timezone
>
> > Am i correct in stating that there is currently no way to update the
> tzdata
> > database except by recompiling postgres after a new version of the tz
> > database has been adapted for postgres ?
>
> It's in the standard tzdata format, so if you have a copy of zic laying
> about, you could download a new tzdata file set and run zic to install
> the new files into the PG installation tree.  There's no need to recompile
> Postgres per se.
>
> However, if this seems like a problem to you and you are on a platform
> whose vendor updates tzdata reliably, you should consider building PG
> with --with-system-tzdata so that it will rely on the vendor copy.
> We've always seen distributing a copy of tzdata as being mainly a
> service to people stuck on platforms where that doesn't happen.
>
> > Is there a way to introspect the current version of the tzdata release
> > (2014j, 2016h, 2017b, ..) that is embedded in a pre-compiled postgres (at
> > runtime or in another way) ?
>
> I don't know of any version labeling in the tzdata files themselves
> (not that I've looked very hard for one).  If you know your PG minor
> release you could look into our release notes to see what tzdata
> release it shipped with.
>
> regards, tom lane
>

Re: [GENERAL] tzdata version

[Tom Lane]

Jerome Wagner  writes:
> When compiled with "--with-system-tzdata", does postgres need a restart
> after the system is updated with the new tzdata ?

No, but existing sessions will carry on with whatever data they've read
from the tzdata files; we don't have a provision to reload a timezone
definition once read by a given process.

Whether you use --with-system-tzdata doesn't matter; the same would apply
if you were updating a Postgres-private copy of the tzdata files.

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] duplicate key value violates unique constraint and duplicated records

[Timokhin Maxim]

Hello, everybody.
I solved the problem. The index has been corrupted after replication despite it 
was appeared as not corrupted.
It was solved by recreating the index.
Thank you for the help.

-- 
Пожалуйста!
Используйте кнопку "ответить всем".
Не удаляйте историю переписки.
Спасибо. С уважением, Timokhin 'maf' Maxim


30.06.2017, 17:33, "Timokhin Maxim" :
> Sure, here it is.
>
> pg_basebackup -h servername -R -P -D /data/upgrade/94 -U pgsql -v 
> —xlog-method=stream —checkpoint=fast
>
> /usr/pgsql-9.5/bin/initdb -D /data/upgrade/95/ —encoding=utf8 
> —locale=ru_RU.utf8 —lc-collate=ru_RU.utf8 —lc-ctype=ru_RU.utf8 
> —lc-messages=en_US.utf8
>
> Then updating:
> /usr/pgsql-9.5/bin/pg_upgrade -b /usr/pgsql-9.4/bin/ -d /data/upgrade/94 -B 
> /usr/pgsql-9.5/bin/ -D /data/upgrade/95 -k
>
> and so on to 9.6
>
> after that server starts normally.
>
> --
> Timokhin 'maf' Maxim
>
> 30.06.2017, 16:07, "Adrian Klaver" :
>>  On 06/30/2017 04:58 AM, Timokhin Maxim wrote:
>>>   BTW, we are moving using:
>>>
>>>   pg_basebackup -h servername -R -P -D /data/upgrade/94 -U pgsql -v 
>>> —xlog-method=stream —checkpoint=fast
>>
>>  Going from 9.4 to 9.6 is a major version upgrade and you cannot use
>>  pg_basebackup for that. Besides I can't see how you even got the 9.6.3
>>  server to start:
>>
>>  /usr/local/pgsql94/bin/pg_basebackup -D /home/aklaver/pgback_test94 -U
>>  postgres -p 5412 -v --xlog-method=stream
>>
>>  /usr/local/pgsql96/bin/pg_ctl -D /home/aklaver/pgback_test94/ start
>>  server starting
>>  FATAL: database files are incompatible with server
>>  DETAIL: The data directory was initialized by PostgreSQL version 9.4,
>>  which is not compatible with this version 9.6.3
>>
>>>   After that we are upping version to 9.6.3.
>>
>>  Given the above how did you actually get 9.6.3 to start?
>>
>>>   I've looked through the documentation 
>>> https://postgrespro.ru/docs/postgrespro/9.6/app-pgbasebackup and didn't 
>>> find details about how pg_basebackup works with b-tree indexes.
>>>   Is it possible that pg_basebackup just copies indexes as is and that is 
>>> cause of corruption. Or it pass indexes as instruction that says "after 
>>> upping db make indexes" ?
>>>
>>>   Thank you.
>>>
>>>   --
>>>   Timokhin 'maf' Maxim
>>
>>  --
>>  Adrian Klaver
>>  adrian.kla...@aklaver.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] Developer GUI tools for PostgreSQL

[rakeshkumar464]

You can try DBeaver.  It is a generic GUI tool which works with practically
all RDBMS.  It is java based, and I find it bit slow. However judging by the
frequent updates I get, it seems to be very active.



--
View this message in context: 
http://www.postgresql-archive.org/Developer-GUI-tools-for-PostgreSQL-tp5972993p5973305.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] RETURNS SETOF RECORD with 1 column

[Tom Lane]

Vincenzo Romano  writes:
> I would like to understand the typo protection mentioned by Tom earlier:
> I need to understand the reason for creating that special case.

Well, case A:

create function foo(out x int4) returns setof int8 ...

This is indubitably a typo.

Case B:

create function foo(out x record) returns setof record ...

Now what?  Is the user expecting us to wrap x in an additional
layer of composite, or not?  Our current assumption is "not",
but it would be pretty inconsistent to do that if x did get
wrapped as long as it were any other type.

Yes, we could have resolved that ambiguity in one direction or the
other and then said that "returns record" or "returns setof record"
is OK regardless of the number of OUT parameters, but we didn't.
Considering that the SQL-standard syntax for this is TABLE(), and that
hasn't got these issues in the first place, I don't feel any need to
revisit the question.

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

[Andrew Kerber]

This may be the wrong list, but I am not sure where it needs to go.  I am
trying to set up pgpool, and I keeping on getting this message:

NOTICE:  add node from hostname:"xxx" port:9000 pgpool_port:
rejected.
Jul 28 22:11:49 xx pgpool[10768]: [172-2] 2017-07-28 22:11:49: pid
10768: DETAIL:  verify the other watchdog node configurations
Jul 28 22:11:49 x pgpool[10768]: [172-3] 2017-07-28 22:11:49: pid
10768: LOCATION:  watchdog.c:1481

I believe I have mismatched settings for the watchdog configuration, but I
cannot find them.  I have debugging turned all the way up, but nothing is
telling me what setting is the problem,  Is there any way I can figure out
what specific watchdog setting its complaining about?


-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'