[GENERAL] Pg-Logical output pkg; can't install 9.4 and 9.5 on same Wheezy box

2016-03-25 Thread Jerry Sievers 
Posted to Admin a few days ago...  I'll try here next before going to
Hackers or trying to locate the packagers.



Anyone else as enthused as I am about Pg-Logical and tried installing
 both version on same host?

Fails on the output plugin package due to header file conflict.

I recall as might be expected that the reverse is true also.  If you
wipe 9.5 and then install 9.4 it works then you get borked trying to
install the 9.5 version.

I wonder if those headers should be packaged down into a version
numbered directory?




# uname -a
Linux jerry 3.2.0-4-amd64 #1 SMP Debian 3.2.51-1 x86_64 GNU/Linux

# 
# echo $#; echo $*
4
postgresql-9.4-pglogical postgresql-9.4-pglogical-output 
postgresql-9.5-pglogical postgresql-9.5-pglogical-output

# dpkg -l | grep pglogical
ii  postgresql-9.5-pglogical 1.0.1-1wheezy   amd64  
 PGLogical plugin for PostgreSQL 9.5
ii  postgresql-9.5-pglogical-output  1.0.1-1wheezy   amd64  
 PGLogical Output plugin for PostgreSQL 9.5

# apt-get install -y $1
Reading package lists... Done
Building dependency tree   
Reading state information... Done
The following extra packages will be installed:
  postgresql-9.4-pglogical-output
The following NEW packages will be installed:
  postgresql-9.4-pglogical postgresql-9.4-pglogical-output
0 upgraded, 2 newly installed, 0 to remove and 308 not upgraded.
Need to get 0 B/249 kB of archives.
After this operation, 827 kB of additional disk space will be used.
debconf: unable to initialize frontend: Dialog
debconf: (Dialog frontend will not work on a dumb terminal, an emacs shell 
buffer, or without a controlling terminal.)
debconf: falling back to frontend: Readline
(Reading database ... 173140 files and directories currently installed.)
Unpacking postgresql-9.4-pglogical-output (from 
.../postgresql-9.4-pglogical-output_1.0.1-1wheezy_amd64.deb) ...
dpkg: error processing 
/var/cache/apt/archives/postgresql-9.4-pglogical-output_1.0.1-1wheezy_amd64.deb 
(--unpack):

 trying to overwrite '/usr/include/postgresql/pglogical_output/compat.h', which 
is also in package postgresql-9.5-pglogical-output 1.0.1-1wheezy


Selecting previously unselected package postgresql-9.4-pglogical.
Unpacking postgresql-9.4-pglogical (from 
.../postgresql-9.4-pglogical_1.0.1-1wheezy_amd64.deb) ...
Processing triggers for postgresql-common ...
debconf: unable to initialize frontend: Dialog
debconf: (Dialog frontend will not work on a dumb terminal, an emacs shell 
buffer, or without a controlling terminal.)
debconf: falling back to frontend: Readline
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
  en_us
Removing obsolete dictionary files:
Errors were encountered while processing:
 /var/cache/apt/archives/postgresql-9.4-pglogical-output_1.0.1-1wheezy_amd64.deb
E: Sub-process /usr/bin/dpkg returned an error code (1)

# dpkg -L $4
/.
/usr
/usr/share
/usr/share/postgresql
/usr/share/postgresql/9.5
/usr/share/postgresql/9.5/extension
/usr/share/postgresql/9.5/extension/pglogical_output--1.0.0.sql
/usr/share/postgresql/9.5/extension/pglogical_output--1.0.1.sql
/usr/share/postgresql/9.5/extension/pglogical_output--1.0.0--1.0.1.sql
/usr/share/postgresql/9.5/extension/pglogical_output.control
/usr/share/doc
/usr/share/doc/postgresql-9.5-pglogical-output
/usr/share/doc/postgresql-9.5-pglogical-output/copyright
/usr/share/doc/postgresql-9.5-pglogical-output/changelog.Debian.gz
/usr/share/doc/postgresql-9.5-pglogical-output/README.md.gz
/usr/lib
/usr/lib/postgresql
/usr/lib/postgresql/9.5
/usr/lib/postgresql/9.5/lib
/usr/lib/postgresql/9.5/lib/pglogical_output.so
/usr/include
/usr/include/postgresql
/usr/include/postgresql/pglogical_output

/usr/include/postgresql/pglogical_output/compat.h
^^

/usr/include/postgresql/pglogical_output/hooks.h

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

Re: [GENERAL] HELP! Uninstalled wrong version of postgres

2016-03-25 Thread Leonardo M . Ramé 

El 24/03/16 a las 14:19, Howard News escribió:

Hi,

I uninstalled the wrong version of postgres on Ubuntu using apt-get
remove postgresql-9.0, convinced that this was an old unused version.
You guess the rest...

The data files still appear to be there, all 485GB of them. Can these be
restored?

Thanks.



Ok, if the data files are still there I'd do this:

1) Assuming the data is in /var/lib/postgresql/9.0, rename that 
directory to /var/lib/9.0-old, AND COPY THAT DIRECTORY ELSEWHERE.
2) Reinstall 9.0 with "apt-get install postgresql-9.0". This should 
re-create the /var/lib/9.0 directory with an empty "main" dir.

3) Stop 9.0 with "pg_ctlcluster 9.0 main stop".
4) Rename the new directory /var/lib/9.0 to /var/lib/9.0-new
5) Rename the old dir (/var/lib/9.0-old) to /var/lib/9.0
6) Restart the cluster with "pg_ctlcluster 9.0 main start".

And everything should be fine again.

P.S.: All those steps should be done as root.

Regards,
--
Leonardo M. Ramé
http://leonardorame.blogspot.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] PostgreSQL advocacy

2016-03-25 Thread Mark Morgan Lloyd 

Jernigan, Kevin wrote:

On 3/25/16, 4:37 AM, "pgsql-general-ow...@postgresql.org on behalf of Mark Morgan 
Lloyd"  wrote:


Just because a corporate has a hundred sites cooperating for inventory 
management doesn't mean that the canteen menus have to be stored on 
Oracle RAC :-)



Right, but often the customer has paid for a site license, in which case the IT 
department will just keep spinning up more Oracle (or SQL Server or DB2) 
databases when requests come in - even if it’s overkill for the proposed use 
case / workload, it’s less work if IT only has one database technology to 
support.


OTOH, if the license takes the number of CPUs/cores into account then 
adding even unsophisticated unrelated databases will, eventually, cost.


--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]


--
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] PostgreSQL advocacy

2016-03-25 Thread Jernigan, Kevin 
On 3/25/16, 4:37 AM, "pgsql-general-ow...@postgresql.org on behalf of Mark 
Morgan Lloyd"  wrote:


>Jernigan, Kevin wrote:
>> On 3/22/16, 8:07 AM, "Bruce Momjian"  wrote:
>
>>>
>>> HA   Scaling  Upgrade Add/Remove
>>>Oracle RAC   50% 50%easyeasy
>>>Streaming Rep.  100% 25%*   hardeasy
>>>Sharding  0%100%hardhard
>>>
>>>* Allows read scaling
>>>
>>> -- 
>>>  Bruce Momjian  http://momjian.us
>>>  EnterpriseDB http://enterprisedb.com
>>>
>>> + As you are, so once was I. As I am, so you will be. +
>>> + Roman grave inscription +
>> 
>> Implementing RAC-equivalent functionality is extremely hard, as evidenced by 
>> the lack of any directly comparable capability from any other relational db 
>> engine, until the release of IBM DB2 Shareplex a few years ago. And given 
>> the improvement of PostgreSQL and other open source solutions over the past 
>> 20 years, it’s not clear that it makes sense to go through the initial 
>> design and implementation work and then the ongoing maintenance overhead - 
>> most of what RAC provides can be achieved through other existing 
>> capabilities. 
>
>Hearing what IBM's strong points are is always useful, since the various 
>flavours of DB2 obviously have facilities to which other databases 
>should aspire. As with Oracle, DB2's strong points aren't really 
>well-publicised, and things are further complicated by the variant 
>terminology which IBM has evolved over the half century they've been 
>building mainframes.
>
>> While I’m not sure that the percentage breakdowns in your chart are totally 
>> accurate, I agree with the general assessment, except for the highest-end 
>> applications which have zero-downtime requirements which can’t be met with 
>> streaming replication: the overhead of synchronous replication limits 
>> scalability, and the failover time for moving from primary to a failover 
>> target is significantly slower than RAC - which can be literally zero if 
>> configured correctly.
>> 
>> The higher-level point that I think is important is that while I may be able 
>> to win technical arguments that RAC is better for certain high-end extreme 
>> workloads - and maybe I can’t even win those arguments ;-) - the real issue 
>> is that there aren’t very many of those workloads, and the PostgreSQL 
>> community shouldn’t care: the vast majority of Oracle (and SQL Server etc) 
>> workloads don’t need all the fancy high-end RAC capabilities, or many of the 
>> other high-end commercial database capabilities. And those workloads can 
>> relatively easily be migrated to PostgreSQL, with minor disruption / change 
>> to schemas, data, triggers, constraints, procedural SQL…
>
>What I've seen so far suggests that if MS is positioning SQL Server to 
>challenge Oracle, it's basically looking for low-hanging fruit: in 
>particular supplementary databases which corporates have put onto Oracle 
>out of habit but which quite simply don't need some of the higher-end 
>facilities for which Oracle is harvesting revenue.
>
>Just because a corporate has a hundred sites cooperating for inventory 
>management doesn't mean that the canteen menus have to be stored on 
>Oracle RAC :-)
>
Right, but often the customer has paid for a site license, in which case the IT 
department will just keep spinning up more Oracle (or SQL Server or DB2) 
databases when requests come in - even if it’s overkill for the proposed use 
case / workload, it’s less work if IT only has one database technology to 
support.

For all kinds of often cloud-y reasons, there have been recent stories in the 
press of many enterprise customers not renewing their site licenses, in favor 
of cherry-picking their biggest / hardest workloads for the commercial 
databases, and then moving the rest to open source, often though not always to 
PostgreSQL, and often in the cloud. 

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

Re: [SPAM] Re: [GENERAL] PostgreSQL crash with PANIC message

2016-03-25 Thread Moreno Andreo 

Il 23/03/2016 21:57, Adrian Klaver ha scritto:

On 03/23/2016 12:02 PM, Moreno Andreo wrote:

Il 23/03/2016 19:57, Adrian Klaver ha scritto:


Might help to look in:

Control Panel --> Administrative Tools --> Event Viewer


No help there. Nothing strange or pointing to critical resource usage.


The only thing I have left is the generic answer for when things act 
funny on Windows:


Do you have AntiVirus software running against the Postgres data 
directory?


Just to close the thread:

a machine restart worked perfectly (now, while users are working, the 
memory allocated is about 1,5 GB), so I requested the admins to 
configure a monthly restart. Hope they replace that crap ASAP.


Thanks for your time!
Moreno



--
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] Only owners can ANALYZE tables...seems overly restrictive

2016-03-25 Thread Stephen Frost 
David,

* David G. Johnston (david.g.johns...@gmail.com) wrote:
> On Thu, Mar 24, 2016 at 4:51 AM, Stephen Frost  wrote:
> > I don't see any reason why the patch itself would be terribly difficult,
> > but are we sure we'd want just ANALYZE and not VACUUM also?  Which would
> > have to be another bit, since those are pretty different actions.
>
> In the limited experience that​ prompted this requested the benefit of
> performing a VACUUM is significantly less than the benefit of performing
> ANALYZE, and the cost of the former is considerably higher.  I'm quite
> content to leave VACUUM decisions to the auto-vacuum process which balances
> the benefit of removing bloat with the I/O cost of doing so.

I guess I don't entirely follow that logic.  autovacuum, even though
it's name doesn't imply it, is *also* quite responsible for ensuring
that ANALYZE is done regularly on the tables and even has options to
control when ANALYZE is run which would it to run more frequently than
vacuums.

Further, a lot of ETL could have very good reason to want to run a
VACUUM, especially with the changes that we continue to make which make
that process less and less expensive of an operation to run.

> > The question really is- what other things might we want as grantable
> > rights in the future?  Once these 16 bits are gone, it's a whole bunch
> > of work to get more.
> 
> If I am reading parsenodes.h correctly we presently use only 12 of 16 bits
> and those that are present all seem ancient.  With no other existing need
> to add a single additional grantable option, let alone 4, I'm not see this
> as being particularly concerning.

They're not all ancient- TRUNCATE was added not that long ago and took
quite a few years of convincing before it was accepted (I asked for it
when I first started working on PG, some 15-or-so years ago and it
wasn't actually included until 3 or 4 years ago, iirc).

Further, as we add new features, new kinds of GRANTs can be needed.
Consider the case of auditing, for example.  When we finally get around
to adding support for proper in-core auditing, it may be desirable for
individuals other than the owner of a relation to be able to control the
auditing of the table.

> Let someone else argue for inclusion of VACUUM before considering adding it
> - all I believe that we need is ANALYZE.  I want programs doing ETL to be
> able to get the system into "good-enough" shape to be functional;
> maintenance processes can deal with the rest.

ANALYZE is a maintenance process too, really, so I don't entirely buy
your argument here.  Either we support having these maintanence-type
actions being performed by non-owners, or we don't and encourage
everyone to configure autovacuum to meet their needs.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Way to get at parsed trigger 'WHEN' clause expression?

2016-03-25 Thread Melvin Davidson 
On Thu, Mar 24, 2016 at 6:58 PM, Melvin Davidson 
wrote:

>
>
> On Thu, Mar 24, 2016 at 4:05 PM, James Robinson 
> wrote:
>
>> Folks,
>>
>> I see that psql's \d displays trigger information of a table by
>> making a call to pg_catalog.pg_get_triggerdef(), which abstracts away most
>> all need to parse the contents of system catalog pg_trigger. However, we'd
>> like to be able to get at a human readable representation of just the WHEN
>> clause of the trigger expression. Function pg_get_expr() looked likely, but
>> fails when fed a pgqual value from pg_trigger ala:
>>
>> ERROR: bogus varno: 2
>>
>> I suspect that it is falling over dead trying to parse the
>> representation of NEW and / or OLD. Anything built in, or should we just
>> make call to pg_catalog.pg_get_triggerdef() and parse out the WHEN clause
>> text?
>>
>> Thanks!
>> --
>> James Robinson
>> Socialserve.com by Emphasys Software
>>
>>
>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
> Your problem description is a bit cloudy. Could you please give just a
> little more specific and provide
> 1. Your PostgreSQL version?
> 2. Your O./S ?
> 3. The exact trigger definition?
> 4. Your exact SQL query for pg_get_triggerdef() ?
>
> Little things like that make it so much easier to provide solutions.
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Did you mean something like



*SELECT substring(pg_get_triggerdef(tg.oid, TRUE) FROM position('WHEN' in
pg_get_triggerdef(tg.oid, TRUE))) AS when_cond  FROM pg_trigger tg WHERE
tg.tgname = 'your_trigger_name';*

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

Re: [GENERAL] what change in postgres 9.5 improvements for multi-CPU machines

2016-03-25 Thread Merlin Moncure 
On Fri, Mar 25, 2016 at 5:11 AM, 657985...@qq.com <657985...@qq.com> wrote:
> hello everyone:
>   I was bothered by the postgres spinlock for a long time . How to
> understand this sentence "postgres 9.5 performance improvements for
> multi-CPU machines"
> at present my database is 9.3.5 。  Can  it solve the  spinlock problem, if
> upgrade it to 9.5 ?

What problem?  Spinlock contention is a symptom, not a problem in and
of itself.  Spinlocks are good target for micro-optimization though,
and we're always looking for strategies to make things work better
if/when we get into high contention scenarios.

merlin


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

[GENERAL] HELP! Uninstalled wrong version of postgres

2016-03-25 Thread Howard News 

Hi,

I uninstalled the wrong version of postgres on Ubuntu using apt-get 
remove postgresql-9.0, convinced that this was an old unused version. 
You guess the rest...


The data files still appear to be there, all 485GB of them. Can these be 
restored?


Thanks.


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

[GENERAL] what change in postgres 9.5 improvements for multi-CPU machines

2016-03-25 Thread 657985...@qq.com 
hello everyone:
  I was bothered by the postgres spinlock for a long time . How to 
understand this sentence "postgres 9.5 performance improvements for multi-CPU 
machines"
at present my database is 9.3.5 。  Can  it solve the  spinlock problem, if 
upgrade it to 9.5 ?

Re: [GENERAL] PostgreSQL advocacy

2016-03-25 Thread Mark Morgan Lloyd 

Jernigan, Kevin wrote:

On 3/22/16, 8:07 AM, "Bruce Momjian"  wrote:




HA   Scaling  Upgrade Add/Remove
   Oracle RAC   50% 50%easyeasy
   Streaming Rep.  100% 25%*   hardeasy
   Sharding  0%100%hardhard
   
   * Allows read scaling


--
 Bruce Momjian  http://momjian.us
 EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


Implementing RAC-equivalent functionality is extremely hard, as evidenced by the lack of any directly comparable capability from any other relational db engine, until the release of IBM DB2 Shareplex a few years ago. And given the improvement of PostgreSQL and other open source solutions over the past 20 years, it’s not clear that it makes sense to go through the initial design and implementation work and then the ongoing maintenance overhead - most of what RAC provides can be achieved through other existing capabilities. 


Hearing what IBM's strong points are is always useful, since the various 
flavours of DB2 obviously have facilities to which other databases 
should aspire. As with Oracle, DB2's strong points aren't really 
well-publicised, and things are further complicated by the variant 
terminology which IBM has evolved over the half century they've been 
building mainframes.



While I’m not sure that the percentage breakdowns in your chart are totally 
accurate, I agree with the general assessment, except for the highest-end 
applications which have zero-downtime requirements which can’t be met with 
streaming replication: the overhead of synchronous replication limits 
scalability, and the failover time for moving from primary to a failover target 
is significantly slower than RAC - which can be literally zero if configured 
correctly.

The higher-level point that I think is important is that while I may be able to 
win technical arguments that RAC is better for certain high-end extreme 
workloads - and maybe I can’t even win those arguments ;-) - the real issue is 
that there aren’t very many of those workloads, and the PostgreSQL community 
shouldn’t care: the vast majority of Oracle (and SQL Server etc) workloads 
don’t need all the fancy high-end RAC capabilities, or many of the other 
high-end commercial database capabilities. And those workloads can relatively 
easily be migrated to PostgreSQL, with minor disruption / change to schemas, 
data, triggers, constraints, procedural SQL…


What I've seen so far suggests that if MS is positioning SQL Server to 
challenge Oracle, it's basically looking for low-hanging fruit: in 
particular supplementary databases which corporates have put onto Oracle 
out of habit but which quite simply don't need some of the higher-end 
facilities for which Oracle is harvesting revenue.


Just because a corporate has a hundred sites cooperating for inventory 
management doesn't mean that the canteen menus have to be stored on 
Oracle RAC :-)


--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]


--
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] Multixacts wraparound monitoring

2016-03-25 Thread Pavlov, Vladimir 
Hi, thank you very much for your help.
Pg_control out in the attachment.

Kind regards,
 
Vladimir Pavlov


-Original Message-
From: Alvaro Herrera [mailto:alvhe...@2ndquadrant.com] 
Sent: Friday, March 25, 2016 12:25 AM
To: Pavlov Vladimir
Cc: 'Adrian Klaver'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Multixacts wraparound monitoring

Pavlov, Vladimir wrote:
> There is nothing:
> select * from pg_prepared_xacts;
>  transaction | gid | prepared | owner | database
> -+-+--+---+--
> (0 rows)
> It is also noticed that a lot of files in a directory 
> main/pg_multixact/members/, now - 69640.

Can you attach pg_controldata output?

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


pg_control.out
Description: pg_control.out

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