Re: [GENERAL] Create trigger on Materialized View?

[Adrian Klaver]

On 03/31/2016 09:46 AM, Matthew Syphus wrote:

I was afraid of that.  Any idea if it is a technical or resource
limitation?  In other words, is such functionality impossible,
undesirable, anticipated, or in the works?


I would guess from here:
http://www.postgresql.org/docs/9.5/static/rules-materializedviews.html

"Materialized views in PostgreSQL use the rule system like views do, but 
persist the results in a table-like form. The main differences between:


CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;

and:

CREATE TABLE mymatview AS SELECT * FROM mytab;

are that the materialized view cannot subsequently be directly updated
..."



*From:*David G. Johnston [mailto:david.g.johns...@gmail.com]
*Sent:* Thursday, March 31, 2016 10:38 AM
*To:* Matthew Syphus
*Cc:* pgsql-general@postgresql.org
*Subject:* Re: [GENERAL] Create trigger on Materialized View?

On Thu, Mar 31, 2016 at 9:30 AM, Matthew Syphus > wrote:

I would like a trigger on a materialized view.  Is this possible? I
have tried both an INSTEAD OF and BEFORE trigger with no luck.  It
responds with:

"project_milestone_mv" is not a table or view.

It is absolutely present and spelled correctly. It is the same with
or without the schema qualification. Actual statement:

CREATE TRIGGER project_milestone_upsert_trigger INSTEAD OF UPDATE

ON tracking.project_milestone_mv FOR EACH ROW

EXECUTE PROCEDURE tracking.project_milestone_upsert();

The documentation at
http://www.postgresql.org/docs/current/static/sql-createtrigger.html
does not explicitly exclude nor include materialized views.  The
most pertinent part I’ve found simply states “The trigger will be
associated with the specified table, view, or foreign table” and
later in reference to table_name, “The name (optionally
schema-qualified) of the table, view, or foreign table the trigger
is for.” I’ve found no mailing list entry addressing triggers and
whether “view” does not include _/materialized/_ view.

Does this indicate, then, that materialized views cannot have triggers?

Postgres 9.5.1

CentOS 6.5

​That would seem to be sufficient evidence that indeed "materialized
views" are not the same as "views" (or "tables") and that they have not
been given the ability to be assigned triggers.

While still implicit the documentation page for "SQL Commands" has
separate entries for "CREATE VIEW" and "CREATE MATERIALIZED VIEW" which
further supports them being distinct as opposed to materialized views
being a specialization of view.

David J.



This email has been scanned for spam and viruses by Proofpoint
Essentials cloud email security - click here

to report this email as spam.




--
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] Missed LIMIT clause pushdown in FDW API

[Alexander Reshetov]

Tom, it's really good news. Thanks!

For now as workaround I think that it's possible to add additional column
in table.  In this way it would be possible to scan only needed part of solumn
in storage.  While quals is pushed down it will be possible to limit like this

select * from table where fake_column == 1

So this fake column would be used as flag for FDW engine to limit query
to one result.  Of course it can't replace LIMIT, but could help to use
at least lateral join with “limit 1” method.

But I'm not sure that some results would not be cached in this situation.
Will this workaround work as expected?  Or maybe there is some other possible
temporary solution (at least for specified lateral join)?

On Wed, Mar 30, 2016 at 4:33 PM, Tom Lane  wrote:
> Merlin Moncure  writes:
>> On Wed, Mar 30, 2016 at 3:52 AM, Alexander Reshetov
>>  wrote:
>>> As far as I know there is no LIMIT clause pushdown in FDW API.
>>> Is there some reasons not to support LIMIT clause pushdown?
>
> It was impossible to do it before 3fc6e2d7f5b652b4, committed 3 weeks
> ago.  Now that that infrastructure exists, someone might look into
> using it for this purpose ... but not before 9.7 at the earliest.
>
>> Working for me on 9.5 with postgres_fdw...
>
> Really?  It's true that postgres_fdw won't fetch more rows than it
> actually needs from the remote --- but that's not the same as telling
> the remote planner to prefer a fast-start plan.
>
> 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] Multixacts wraparound monitoring

[Thomas Munro]

On Fri, Apr 1, 2016 at 4:31 AM, Pavlov, Vladimir
 wrote:
> I understand correctly, that number of members cannot be more than 2^32 (also 
> uses a 32-bit counter)?

Correct.

> I had 69640 files in main/pg_multixact/members/, 69640*32*2045 = 4557241600 
> members, this is normal?

Where did you get 2045 from?  I thought it was like this:

number of members = number of member segment files * 1636 * 32
number of multixacts = number of offsets segment files * 2048 * 32

-- 
Thomas Munro
http://www.enterprisedb.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] Create trigger on Materialized View?

[Matthew Syphus]

I was afraid of that.  Any idea if it is a technical or resource limitation?  
In other words, is such functionality impossible, undesirable, anticipated, or 
in the works?

From: David G. Johnston [mailto:david.g.johns...@gmail.com]
Sent: Thursday, March 31, 2016 10:38 AM
To: Matthew Syphus
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Create trigger on Materialized View?

On Thu, Mar 31, 2016 at 9:30 AM, Matthew Syphus 
> wrote:
I would like a trigger on a materialized view.  Is this possible? I have tried 
both an INSTEAD OF and BEFORE trigger with no luck.  It responds with:
"project_milestone_mv" is not a table or view.
It is absolutely present and spelled correctly. It is the same with or without 
the schema qualification. Actual statement:

CREATE TRIGGER project_milestone_upsert_trigger INSTEAD OF UPDATE
   ON tracking.project_milestone_mv FOR EACH ROW
   EXECUTE PROCEDURE tracking.project_milestone_upsert();

The documentation at 
http://www.postgresql.org/docs/current/static/sql-createtrigger.html does not 
explicitly exclude nor include materialized views.  The most pertinent part 
I’ve found simply states “The trigger will be associated with the specified 
table, view, or foreign table” and later in reference to table_name, “The name 
(optionally schema-qualified) of the table, view, or foreign table the trigger 
is for.” I’ve found no mailing list entry addressing triggers and whether 
“view” does not include _materialized_ view.
Does this indicate, then, that materialized views cannot have triggers?

Postgres 9.5.1
CentOS 6.5


​That would seem to be sufficient evidence that indeed "materialized views" are 
not the same as "views" (or "tables") and that they have not been given the 
ability to be assigned triggers.

While still implicit the documentation page for "SQL Commands" has separate 
entries for "CREATE VIEW" and "CREATE MATERIALIZED VIEW" which further supports 
them being distinct as opposed to materialized views being a specialization of 
view.

David J.







This email has been scanned for spam and viruses by Proofpoint Essentials cloud 
email security - click 
here
 to report this email as spam.

Re: [GENERAL] Create trigger on Materialized View?

[David G. Johnston]

On Thu, Mar 31, 2016 at 9:30 AM, Matthew Syphus  wrote:

> I would like a trigger on a materialized view.  Is this possible? I have
> tried both an INSTEAD OF and BEFORE trigger with no luck.  It responds
> with:
>
> "project_milestone_mv" is not a table or view.
>
> It is absolutely present and spelled correctly. It is the same with or
> without the schema qualification. Actual statement:
>
>
>
> CREATE TRIGGER project_milestone_upsert_trigger INSTEAD OF UPDATE
>
>ON tracking.project_milestone_mv FOR EACH ROW
>
>EXECUTE PROCEDURE tracking.project_milestone_upsert();
>
>
>
> The documentation at
> http://www.postgresql.org/docs/current/static/sql-createtrigger.html does
> not explicitly exclude nor include materialized views.  The most pertinent
> part I’ve found simply states “The trigger will be associated with the
> specified table, view, or foreign table” and later in reference to
> table_name, “The name (optionally schema-qualified) of the table, view,
> or foreign table the trigger is for.” I’ve found no mailing list entry
> addressing triggers and whether “view” does not include _*materialized*_
> view.
>
> Does this indicate, then, that materialized views cannot have triggers?
>
>
>
> Postgres 9.5.1
>
> CentOS 6.5
>
>
>

​That would seem to be sufficient evidence that indeed "materialized views"
are not the same as "views" (or "tables") and that they have not been given
the ability to be assigned triggers.

While still implicit the documentation page for "SQL Commands" has separate
entries for "CREATE VIEW" and "CREATE MATERIALIZED VIEW" which further
supports them being distinct as opposed to materialized views being a
specialization of view.

David J.

[GENERAL] Create trigger on Materialized View?

[Matthew Syphus]

I would like a trigger on a materialized view.  Is this possible? I have tried 
both an INSTEAD OF and BEFORE trigger with no luck.  It responds with:
"project_milestone_mv" is not a table or view.
It is absolutely present and spelled correctly. It is the same with or without 
the schema qualification. Actual statement:

CREATE TRIGGER project_milestone_upsert_trigger INSTEAD OF UPDATE
   ON tracking.project_milestone_mv FOR EACH ROW
   EXECUTE PROCEDURE tracking.project_milestone_upsert();

The documentation at 
http://www.postgresql.org/docs/current/static/sql-createtrigger.html does not 
explicitly exclude nor include materialized views.  The most pertinent part 
I've found simply states "The trigger will be associated with the specified 
table, view, or foreign table" and later in reference to table_name, "The name 
(optionally schema-qualified) of the table, view, or foreign table the trigger 
is for." I've found no mailing list entry addressing triggers and whether 
"view" does not include _materialized_ view.
Does this indicate, then, that materialized views cannot have triggers?

Postgres 9.5.1
CentOS 6.5

Thank you,
MS

Re: [GENERAL] Multixacts wraparound monitoring

[Pavlov, Vladimir]

I understand correctly, that number of members cannot be more than 2^32 (also 
uses a 32-bit counter)?
I had 69640 files in main/pg_multixact/members/, 69640*32*2045 = 4557241600 
members, this is normal?

Kind regards,
 
Vladimir Pavlov


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

Pavlov, Vladimir wrote:
> Hello,
> If I get you right:
> Latest checkpoint's NextMultiXactId:  2075246000
> Latest checkpoint's oldestMultiXid:   2019511697
> Number of members files:  10820
> Size pg_multixact/members/ (bytes) (2.7Gb):   2887696384
> Pages in file:32
> Members on page:  2045
> Number of members (32*2045*10820):708060800
> Members per multixact (2075246000 - 2019511697)/708060800:12,70421916
> Multixact size (bytes) (2887696384/708060800):4,078316981 - It's a 
> lot?

Yeah, 12.7 members per multixact on average is a lot, unless you have 12 
processes concurrently locking the same tuples, all the time (although
that is possible).   My guess is that this is related to subtransactions
(either explicit SAVEPOINTs in your SQL code, or EXCEPTION blocks in plpgsql 
functions).

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, 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] Is it possible to delete a single value from an enum type?

[Melvin Davidson]

On Thu, Mar 31, 2016 at 10:25 AM, Tom Lane  wrote:

> Melvin Davidson  writes:
> > On Thu, Mar 31, 2016 at 9:24 AM, Nik Mitev  wrote:
> >>> In summary, I am looking for the opposite functionality to 'ALTER TYPE
> >>> typename ADD VALUE IF NOT EXISTS new_value'
> >>> e.g. 'ALTER TYPE typename DELETE VALUE IF NOT USED unused_value'.
>
> > It is not easy to delete values from enums, but it can be done.
>
> No, it is NOT SAFE TO DO THAT.  At least not unless you also drop or
> reindex every index on columns of the enum type.  Even if you've deleted
> every occurrence of the target value appearing in table rows, and vacuumed
> away those rows so that their leaf index entries are gone, the target
> value could still exist in upper index pages (as a page boundary value,
> for example).  Delete the pg_enum entry and you'll break the index,
> because enum_cmp() won't know what to do when visiting that index entry.
>
> Concerns like this are exactly why there is no ALTER TYPE DELETE VALUE,
> and probably never will be.  If you need a non-fixed set of key values,
> you're much better off using a foreign key instead of an enum type.
>
> regards, tom lane
>


AS NOTED IN MY LAST LINE > "That being said "ENUMS are EVIL"! As others
have said, it is much better to just just Foreign Keys for value
integrity."

I have previous advised that enums are a holdover from before Foreign Keys
were available and should be avoided.

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

Re: [GENERAL] Is it possible to delete a single value from an enum type?

[Tom Lane]

Melvin Davidson  writes:
> On Thu, Mar 31, 2016 at 9:24 AM, Nik Mitev  wrote:
>>> In summary, I am looking for the opposite functionality to 'ALTER TYPE
>>> typename ADD VALUE IF NOT EXISTS new_value'
>>> e.g. 'ALTER TYPE typename DELETE VALUE IF NOT USED unused_value'.

> It is not easy to delete values from enums, but it can be done.

No, it is NOT SAFE TO DO THAT.  At least not unless you also drop or
reindex every index on columns of the enum type.  Even if you've deleted
every occurrence of the target value appearing in table rows, and vacuumed
away those rows so that their leaf index entries are gone, the target
value could still exist in upper index pages (as a page boundary value,
for example).  Delete the pg_enum entry and you'll break the index,
because enum_cmp() won't know what to do when visiting that index entry.

Concerns like this are exactly why there is no ALTER TYPE DELETE VALUE,
and probably never will be.  If you need a non-fixed set of key values,
you're much better off using a foreign key instead of an enum type.

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] Is it possible to delete a single value from an enum type?

[Melvin Davidson]

On Thu, Mar 31, 2016 at 9:24 AM, Nik Mitev  wrote:

>
>
> On 31/03/16 14:14, Sándor Daku wrote:
>
> On 31 March 2016 at 14:35, Nik Mitev  wrote:
>
>> Hi,
>>
>> In summary, I am looking for the opposite functionality to 'ALTER TYPE
>> typename ADD VALUE IF NOT EXISTS new_value'
>> e.g. 'ALTER TYPE typename DELETE VALUE IF NOT USED unused_value'. The
>> [IF NOT USED] condition is optional, I can work around it and externally
>> check whether the value is used in the table.
>>
>> In more detail, and especially if the above is not possible for a good
>> reason and me needing it means I'm doing something bad:
>> I have a set of values where 90% of the rows would contain for example a
>> small set of email addresses, repeated potentially ~100K times. The
>> remaining 10% are random email addresses which may appear just once. I
>> am currently using an enumerated type for this field, and the set of
>> values is dynamically updated as needed, before new data is inserted.
>> This works and so far all is good, storing this as an enumerated type
>> rather than say varchar(128) should be saving space and search time.
>>
>> When I want to expire a set of data, simply deleting it from the table
>> could leave some enumerated type values unused, and they may never be
>> used again. Over time, the set of values for this enumerated type will
>> grow and will end up containing a huge amount of values which have long
>> since been deleted from the table and are unnecessary. So I am looking
>> for a way to remove them, without having to drop the type itself, as
>> that would mean dropping the table too.
>>
>> The only workaround I can come up with now is copying the table to a new
>> one , reinitialising the type in the process, deleting the old table and
>> moving the updated one in its place. That would be disruptive though and
>> rather clunky, so I think I'd rather give up on using an enumerated type
>> for this value altogether...
>>
>> I'd be grateful for any advice you may have.
>>
>> Cheers,
>> Nik
>>
>
>
> That seems to me a very unusual(a.k.a. crazy) design. :)
> I'd rather use a simple old fashioned table and foreign key construction
> to store the email addresses.
>
> Regards,
> Sándor
>
>
> A rather obvious workaround which somehow wasn't obvious to me until I
> read this :)
> I guess it's (mostly) what the enumerated type functionality does behind
> the scenes anyway...
>
> Thanks!
>
> Nik
>


It is not easy to delete values from enums, but it can be done.
First, you need to insure that the value you want to delete is not already
stored in a column of some table(s).

So you will need to do something like:
SELECT count(*)
  FROM {some_table}
 WHERE {column_name} = ;

Then you need to get the enumtypid and sortorder for the value to delete.
The following query will provide that info.

SELECT t.typname,
   e.enumlabel,
   e.enumsortorder,
   e.enumtypid
  FROM pg_type t
  JOIN pg_enum e ON e.enumtypid = t.oid
 WHERE t.typtype = 'e'
   AND e.enumlabel = ''
 ORDER BY 1, enumsortorder;

 Then, you can either do:
DELETE FROM pg_enum
WHERE enumtypid = {enumtypid_from_above}
  AND enumsortorder = {enumsortorder_from_above};

OR

DELETE FROM pg_enum
WHERE enumtypid = {enumtypid_from_above}
  AND enumlabel = '';


That being said "ENUMS are EVIL"! As others have said, it is much better to
just just Foriegn Keys for value integrity.


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

Re: [GENERAL] Re: Postgres 9.4.4/9.4.6: plpython2/3 intallation issues on a Windows 7 machine

[Adrian Klaver]

On 03/31/2016 02:53 AM, margrit drescher wrote:

I originally used the 9.4.6 version on
http://www.enterprisedb.com/products-services-training/pgdownload#windows for
64-bit windows and loaded the language pack recommended in the readme file.


Did you do this?:

If you are using Windows 64, you must set the following variables:
=
set PYTHONHOME=C:\EnterpriseDB\LanguagePack\9.4\x64\Python-3.3
set 
PATH=C:\EnterpriseDB\LanguagePack\9.4\x64\Python-3.3;C:\EnterpriseDB\LanguagePack\9.4\x64\Perl-5.16\bin;C:\EnterpriseDB\LanguagePack\9.4\x64\Tcl-8.5\bin;%PATH%



My suspicion is that plpython3u is finding the Python 3.2 install on the 
machine not the Python 3.3 one.




On 31 March 2016 at 11:25, Alex Ignatov-2 [via PostgreSQL] <[hidden
email] > wrote:



On 31.03.2016 10:50, margrit drescher wrote:

 > I installed postgres 9.4.6 on my Windows 7 (64-bit) machine, and
managed to
 > create the plpython3u extension without a problem.
 >
 > I then restored an existing DB (created in postgres 9.3).  The
plpython3
 > functions restored without a hitch and can execute.
 >
 >
 > However, as soon as I try to create a NEW plpython3 function on
the db the
 > postgres service terminates and does an auto-recovery.
 >
 > I then downgraded to postgres 9.4.4 and am still encountering the
same
 > issue.
 >
 >
 >
 > Example:
 >
 > When  running the following code:
 >
 > "create function test() returns void as $$ plpy.notice( 'xxx' );
$$ language
 > plpython3u"
 >
 >
 > the following is generated in the log:
 >
 > "
 > 2016-03-31 09:36:56 CAT LOG:  server process (PID 6376) exited
with exit
 > code 3
 > 2016-03-31 09:36:56 CAT DETAIL:  Failed process was running:  create
 > function test() returns void as $$ plpy.notice( 'xxx' ); $$ language
 > plpython3u
 > 2016-03-31 09:36:56 CAT LOG:  terminating any other active server
processes
 > 2016-03-31 09:36:56 CAT WARNING:  terminating connection because
of crash of
 > another server process
 > 2016-03-31 09:36:56 CAT DETAIL:  The postmaster has commanded
this server
 > process to roll back the current transaction and exit, because
another
 > server process exited abnormally and possibly corrupted shared
memory.
 > 2016-03-31 09:36:56 CAT HINT:  In a moment you should be able to
reconnect
 > to the database and repeat your command.
 > 2016-03-31 09:36:56 CAT WARNING:  terminating connection because
of crash of
 > another server process
 > 2016-03-31 09:36:56 CAT DETAIL:  The postmaster has commanded
this server
 > process to roll back the current transaction and exit, because
another
 > server process exited abnormally and possibly corrupted shared
memory.
 > 2016-03-31 09:36:56 CAT HINT:  In a moment you should be able to
reconnect
 > to the database and repeat your command.
 > 2016-03-31 09:36:56 CAT WARNING:  terminating connection because
of crash of
 > another server process
 > 2016-03-31 09:36:56 CAT DETAIL:  The postmaster has commanded
this server
 > process to roll back the current transaction and exit, because
another
 > server process exited abnormally and possibly corrupted shared
memory.
 > 2016-03-31 09:36:56 CAT HINT:  In a moment you should be able to
reconnect
 > to the database and repeat your command.
 >
 > "
 >
 > I am guessing that my problem might have something to do with the
fact that
 > the plpython2u language is not installed on the DB - although I
am not sure
 > why it should it need it, when executing a plpython3 command.  As I
 > understand it, Postgres 9.4 accomodates both versions of python.
   However I
 > have not been able to figure out how to load plpython2u onto the
db.  The
 > postgres version I installed does not ship with plpython2.dll and
I have not
 > been able to get hold of a copy. ( Python 2.7 /3.2 and 3.3 are
installed on
 > my machine, and I am running postgre versions  9.2, 9.3 and 9.4.4 on
 > different ports).
 >
 >
 >
 >
 >
 > --
 > View this message in context:

http://postgresql.nabble.com/Postgres-9-4-4-9-4-6-plpython2-3-intallation-issues-on-a-Windows-7-machine-tp5896157.html
 > Sent from the PostgreSQL - general mailing list archive at
Nabble.com.
 >
 >
Hello!
What postgres distr for Win  are you using?

--
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company



--
Sent via pgsql-general mailing list ([hidden email]
)
To make changes to your subscription:

Re: [GENERAL] Postgres 9.4.4/9.4.6: plpython2/3 intallation issues on a Windows 7 machine

[Adrian Klaver]

On 03/31/2016 12:50 AM, margrit drescher wrote:

I installed postgres 9.4.6 on my Windows 7 (64-bit) machine, and managed to
create the plpython3u extension without a problem.

I then restored an existing DB (created in postgres 9.3).  The plpython3
functions restored without a hitch and can execute.


So where was this database running?



However, as soon as I try to create a NEW plpython3 function on the db the
postgres service terminates and does an auto-recovery.

I then downgraded to postgres 9.4.4 and am still encountering the same
issue.



Example:

When  running the following code:

"create function test() returns void as $$ plpy.notice( 'xxx' ); $$ language
plpython3u"


the following is generated in the log:

"
2016-03-31 09:36:56 CAT LOG:  server process (PID 6376) exited with exit
code 3
2016-03-31 09:36:56 CAT DETAIL:  Failed process was running:  create
function test() returns void as $$ plpy.notice( 'xxx' ); $$ language
plpython3u
2016-03-31 09:36:56 CAT LOG:  terminating any other active server processes
2016-03-31 09:36:56 CAT WARNING:  terminating connection because of crash of
another server process
2016-03-31 09:36:56 CAT DETAIL:  The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2016-03-31 09:36:56 CAT HINT:  In a moment you should be able to reconnect
to the database and repeat your command.
2016-03-31 09:36:56 CAT WARNING:  terminating connection because of crash of
another server process
2016-03-31 09:36:56 CAT DETAIL:  The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2016-03-31 09:36:56 CAT HINT:  In a moment you should be able to reconnect
to the database and repeat your command.
2016-03-31 09:36:56 CAT WARNING:  terminating connection because of crash of
another server process
2016-03-31 09:36:56 CAT DETAIL:  The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2016-03-31 09:36:56 CAT HINT:  In a moment you should be able to reconnect
to the database and repeat your command.

"

I am guessing that my problem might have something to do with the fact that
the plpython2u language is not installed on the DB - although I am not sure
why it should it need it, when executing a plpython3 command.  As I
understand it, Postgres 9.4 accomodates both versions of python.   However I
have not been able to figure out how to load plpython2u onto the db.  The
postgres version I installed does not ship with plpython2.dll and I have not
been able to get hold of a copy. ( Python 2.7 /3.2 and 3.3 are installed on
my machine, and I am running postgre versions  9.2, 9.3 and 9.4.4 on
different ports).





--
View this message in context: 
http://postgresql.nabble.com/Postgres-9-4-4-9-4-6-plpython2-3-intallation-issues-on-a-Windows-7-machine-tp5896157.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





--
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] Is it possible to delete a single value from an enum type?

[Nik Mitev]

On 31/03/16 14:14, Sándor Daku wrote:
> On 31 March 2016 at 14:35, Nik Mitev  > wrote:
>
> Hi,
>
> In summary, I am looking for the opposite functionality to 'ALTER TYPE
> typename ADD VALUE IF NOT EXISTS new_value'
> e.g. 'ALTER TYPE typename DELETE VALUE IF NOT USED unused_value'. The
> [IF NOT USED] condition is optional, I can work around it and
> externally
> check whether the value is used in the table.
>
> In more detail, and especially if the above is not possible for a good
> reason and me needing it means I'm doing something bad:
> I have a set of values where 90% of the rows would contain for
> example a
> small set of email addresses, repeated potentially ~100K times. The
> remaining 10% are random email addresses which may appear just once. I
> am currently using an enumerated type for this field, and the set of
> values is dynamically updated as needed, before new data is inserted.
> This works and so far all is good, storing this as an enumerated type
> rather than say varchar(128) should be saving space and search time.
>
> When I want to expire a set of data, simply deleting it from the table
> could leave some enumerated type values unused, and they may never be
> used again. Over time, the set of values for this enumerated type will
> grow and will end up containing a huge amount of values which have
> long
> since been deleted from the table and are unnecessary. So I am looking
> for a way to remove them, without having to drop the type itself, as
> that would mean dropping the table too.
>
> The only workaround I can come up with now is copying the table to
> a new
> one , reinitialising the type in the process, deleting the old
> table and
> moving the updated one in its place. That would be disruptive
> though and
> rather clunky, so I think I'd rather give up on using an
> enumerated type
> for this value altogether...
>
> I'd be grateful for any advice you may have.
>
> Cheers,
> Nik
>
>
>  
> That seems to me a very unusual(a.k.a. crazy) design. :)
> I'd rather use a simple old fashioned table and foreign key
> construction to store the email addresses.
>
> Regards,
> Sándor

A rather obvious workaround which somehow wasn't obvious to me until I
read this :)
I guess it's (mostly) what the enumerated type functionality does behind
the scenes anyway...

Thanks!

Nik


signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Multixacts wraparound monitoring

[Alvaro Herrera]

Pavlov, Vladimir wrote:
> Hello,
> If I get you right:
> Latest checkpoint's NextMultiXactId:  2075246000
> Latest checkpoint's oldestMultiXid:   2019511697
> Number of members files:  10820
> Size pg_multixact/members/ (bytes) (2.7Gb):   2887696384
> Pages in file:32
> Members on page:  2045
> Number of members (32*2045*10820):708060800
> Members per multixact (2075246000 - 2019511697)/708060800:12,70421916
> Multixact size (bytes) (2887696384/708060800):4,078316981 - It's a 
> lot?

Yeah, 12.7 members per multixact on average is a lot, unless you have 12
processes concurrently locking the same tuples, all the time (although
that is possible).   My guess is that this is related to subtransactions
(either explicit SAVEPOINTs in your SQL code, or EXCEPTION blocks in
plpgsql functions).

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, 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] Is it possible to delete a single value from an enum type?

[Sándor Daku]

On 31 March 2016 at 14:35, Nik Mitev  wrote:

> Hi,
>
> In summary, I am looking for the opposite functionality to 'ALTER TYPE
> typename ADD VALUE IF NOT EXISTS new_value'
> e.g. 'ALTER TYPE typename DELETE VALUE IF NOT USED unused_value'. The
> [IF NOT USED] condition is optional, I can work around it and externally
> check whether the value is used in the table.
>
> In more detail, and especially if the above is not possible for a good
> reason and me needing it means I'm doing something bad:
> I have a set of values where 90% of the rows would contain for example a
> small set of email addresses, repeated potentially ~100K times. The
> remaining 10% are random email addresses which may appear just once. I
> am currently using an enumerated type for this field, and the set of
> values is dynamically updated as needed, before new data is inserted.
> This works and so far all is good, storing this as an enumerated type
> rather than say varchar(128) should be saving space and search time.
>
> When I want to expire a set of data, simply deleting it from the table
> could leave some enumerated type values unused, and they may never be
> used again. Over time, the set of values for this enumerated type will
> grow and will end up containing a huge amount of values which have long
> since been deleted from the table and are unnecessary. So I am looking
> for a way to remove them, without having to drop the type itself, as
> that would mean dropping the table too.
>
> The only workaround I can come up with now is copying the table to a new
> one , reinitialising the type in the process, deleting the old table and
> moving the updated one in its place. That would be disruptive though and
> rather clunky, so I think I'd rather give up on using an enumerated type
> for this value altogether...
>
> I'd be grateful for any advice you may have.
>
> Cheers,
> Nik
>


That seems to me a very unusual(a.k.a. crazy) design. :)
I'd rather use a simple old fashioned table and foreign key construction to
store the email addresses.

Regards,
Sándor


Ezt az e-mailt egy Avast védelemmel rendelkező, vírusmentes számítógépről
küldték.
www.avast.com

<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Re: [GENERAL] debugging server connection issue

[Stephen Constable]

In case anybody looks back on this thread in the future, I fixed the
problem (at least temporarily) by running the following in CentOS 7:
# echo "1025 65535" >/proc/sys/net/ipv4/ip_local_port_range
# sysctl -w net.ipv4.tcp_timestamps=1
# sysctl -w net.ipv4.tcp_tw_recycle=1
# sysctl -w tcp_tw_reuse=1

On both the database server and the client nodes.  It seems the clients
were eating up all the available ephemeral ports opening and closing
database connections.  I'm going to have a long talk with the developer
about why this is done in the first place :)

Cheers, and thanks for the tips.
--Steve

On Tue, Mar 29, 2016 at 6:51 PM Adrian Klaver 
wrote:

> On 03/29/2016 03:25 PM, Stephen Constable wrote:
> > Sorry, my client environment is Linux.
>
> Hmm, so I was reading win32.c wrong. It is mapping a Windows error
> message to that string.
>
> >
> > My current theory is that my clients are running out of available
> > ephemeral ports, like in this thread:
> >
> http://dba.stackexchange.com/questions/59650/pgbouncer-works-great-but-occasionally-becomes-unavailable
> (but
> > I"m not currently using pg bouncer).  I tried pg bouncer before and had
> > the same errors, which in retrospect makes the client-side issue seem
> > more likely.  Are there any configuration variables I can set to reduce
> > the number of ephemeral ports required in the postgresql client
> > libraries?  Otherwise, I will attempt to reconfigure the OS of the
> > client machines tomorrow morning.
>
> Not sure how that would work. To make a network connection would seem to
> me to require a port.
>
> Are you seeing the same sort of port churn on your 8.4 machine?
>
> Is the fact that is processing results slower maybe giving the ports a
> chance to timeout their wait time, versus not on the newer faster machine?
>
> The issue, to me at least, seems to be less the number of jobs, but the
> number of connections each job is producing. T
> >
> > Thanks,
> > Steve
> >
> > On Tue, Mar 29, 2016 at 4:44 PM Adrian Klaver  > > wrote:
> >
> > On 03/29/2016 01:28 PM, Stephen Constable wrote:
> >  > My apologies, I'm not sure what part of the networking stack the
> >  > messages are coming from.  It also states:
> >  > """
> >  > could not connect to server: Cannot assign requested address
> >  > Is the server running on host "" and accepting
> >  > TCP/IP connections on port ?
> >  > """
> >
> > Alright I lied, the above is a Postgres error message. I am just not
> > used to seeing 'Cannot assign requested address'. Turns out it is in
> > interfaces/libpq/win32.c.
> >
> > So your client is running on Windows?
> >
> >
> >  > This error is only printed under a 32-job load, never a single
> > job load.
> >  >
> >  > The processes are indeed connecting over a local network.
> >  >
> >  > I have only enabled the logging of connections and disconnections
> > since
> >  > I figured that would be the most telling :) perhaps that was not
> the
> >  > best idea.  but, FYI, I see over 5000 such notices in a single
> > minute.
> >  > I will reconfigure the logging to be more verbose.
> >  >
> >  > Thanks,
> >  > Steve
> >  >
> >  > On Tue, Mar 29, 2016 at 4:21 PM Adrian Klaver
> > 
> >  >  > >> wrote:
> >  >
> >  > On 03/29/2016 01:10 PM, Stephen Constable wrote:
> >  >  > Hi All,
> >  >  >
> >  >  > I'm a new-ish sysadmin working on porting legacy
> > scientific code
> >  > from a
> >  >  > local server/client to new supercomputer environment.  My
> > work is
> >  > mostly
> >  >  > done, except that my postgres database doesn't seem to be
> > able to
> >  > keep
> >  >  > up with the new environment.  The application is written
> > in-house
> >  > in a
> >  >  > mixture of FORTAN 77 and C, and uses postgres BLOBS as its
> > main data
> >  >  > store.  This application in particular only reads from the
> >  > database, it
> >  >  > never writes, which *should* make it easy to scale.
> >  >  >
> >  >  > My main problem is that this client application is unable
> to
> >  > connect to
> >  >  > the database under a modest load (32 simultaneous jobs).
> > The client
> >  >  > error logs print out messages like "could not connect to
> > server:
> >  > Cannot
> >  >  > assign requested address" and "Cannot connect to database
> >  > [runlog]!!!"
> >  >  > (an important database of ours).  The "cannot assign
> requested
> >  > address"
> >  >
> >  > Well those do not look like Postgres error 

[GENERAL] Is it possible to delete a single value from an enum type?

[Nik Mitev]

Hi,

In summary, I am looking for the opposite functionality to 'ALTER TYPE
typename ADD VALUE IF NOT EXISTS new_value'
e.g. 'ALTER TYPE typename DELETE VALUE IF NOT USED unused_value'. The
[IF NOT USED] condition is optional, I can work around it and externally
check whether the value is used in the table.

In more detail, and especially if the above is not possible for a good
reason and me needing it means I'm doing something bad:
I have a set of values where 90% of the rows would contain for example a
small set of email addresses, repeated potentially ~100K times. The
remaining 10% are random email addresses which may appear just once. I
am currently using an enumerated type for this field, and the set of
values is dynamically updated as needed, before new data is inserted.
This works and so far all is good, storing this as an enumerated type
rather than say varchar(128) should be saving space and search time.

When I want to expire a set of data, simply deleting it from the table
could leave some enumerated type values unused, and they may never be
used again. Over time, the set of values for this enumerated type will
grow and will end up containing a huge amount of values which have long
since been deleted from the table and are unnecessary. So I am looking
for a way to remove them, without having to drop the type itself, as
that would mean dropping the table too.

The only workaround I can come up with now is copying the table to a new
one , reinitialising the type in the process, deleting the old table and
moving the updated one in its place. That would be disruptive though and
rather clunky, so I think I'd rather give up on using an enumerated type
for this value altogether...

I'd be grateful for any advice you may have.

Cheers,
Nik



signature.asc
Description: OpenPGP digital signature

[GENERAL] Postgres warm standby in production

[Dmitry Mordovin]

Dear friends!

Anybody use Postgres 9.5 warm standby in production?

Which schemes you use?

My setup:
 - 2 DB servers (master and slave)
 - 4 APP servers (tomcat)

Each tomcat server have JDBC connect to master and prepared config for 
slave.
Business task permit switch between DB server on APP servers manually 
when failover.


What is the best scheme in my configuration?

BR, Dmitry



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

[Pavlov, Vladimir]

Hello,
If I get you right:
Latest checkpoint's NextMultiXactId:2075246000
Latest checkpoint's oldestMultiXid: 2019511697
Number of members files:10820
Size pg_multixact/members/ (bytes) (2.7Gb): 2887696384
Pages in file:  32
Members on page:2045
Number of members (32*2045*10820):  708060800
Members per multixact (2075246000 - 2019511697)/708060800:  12,70421916
Multixact size (bytes) (2887696384/708060800):  4,078316981 - It's a lot?


Kind regards,
 
Vladimir Pavlov


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

Pavlov, Vladimir wrote:
> Yes, VACUUM helps to solve the problem and the WARNING gone away.
> But, the problem is that the VACUUM for the entire database (2.4T) takes over 
> 7 hours, and it has to run every 15-20 hours (about 300 millions 
> transactions), otherwise:
> ERROR:  multixact "members" limit exceeded - and server stops working.
> The question is how to start the VACUUM at least once in three days.

You should have *started* the thread with this information.

My bet is that your multixacts are overly large and that's causing excessive 
vacuuming work; this is likely due to bug #8470 (which is fixed in 9.5 and 
master but not 9.3 and 9.4) and my bet is that you would very much benefit from 
the patch I posted in 
https://www.postgresql.org/message-id/20150620043650.GX133018%40postgresql.org
I didn't actually verify this; you could with some arithmetic on the deltas in 
multixact counters in pg_controldata output that you could take periodically.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, 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] More correlated (?) index woes

[Geoff Winkless]

On 31 Mar 2016 03:02, "bricklen"  wrote:
> Perhaps a partial index like "create index pa_sc_id_pidx on pa (sc_id) with 
> (fillfactor=100) where field1 IS NULL;" will help?

Thanks for the suggestion.

It might, but the problem with that is there's (something like)
field2-16 which are used in similar queries.

I'll stick with enable_seqscan=off, it seems to be doing the trick;
thanks though.

Geoff


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

[GENERAL] Re: Postgres 9.4.4/9.4.6: plpython2/3 intallation issues on a Windows 7 machine

[margrit drescher]

I originally used the 9.4.6 version on
http://www.enterprisedb.com/products-services-training/pgdownload#windows
for 64-bit windows and loaded the language pack recommended in the readme
file.

On 31 March 2016 at 11:25, Alex Ignatov-2 [via PostgreSQL] <
ml-node+s1045698n5896174...@n5.nabble.com> wrote:

>
>
> On 31.03.2016 10:50, margrit drescher wrote:
>
> > I installed postgres 9.4.6 on my Windows 7 (64-bit) machine, and managed
> to
> > create the plpython3u extension without a problem.
> >
> > I then restored an existing DB (created in postgres 9.3).  The plpython3
> > functions restored without a hitch and can execute.
> >
> >
> > However, as soon as I try to create a NEW plpython3 function on the db
> the
> > postgres service terminates and does an auto-recovery.
> >
> > I then downgraded to postgres 9.4.4 and am still encountering the same
> > issue.
> >
> >
> >
> > Example:
> >
> > When  running the following code:
> >
> > "create function test() returns void as $$ plpy.notice( 'xxx' ); $$
> language
> > plpython3u"
> >
> >
> > the following is generated in the log:
> >
> > "
> > 2016-03-31 09:36:56 CAT LOG:  server process (PID 6376) exited with exit
> > code 3
> > 2016-03-31 09:36:56 CAT DETAIL:  Failed process was running:  create
> > function test() returns void as $$ plpy.notice( 'xxx' ); $$ language
> > plpython3u
> > 2016-03-31 09:36:56 CAT LOG:  terminating any other active server
> processes
> > 2016-03-31 09:36:56 CAT WARNING:  terminating connection because of
> crash of
> > another server process
> > 2016-03-31 09:36:56 CAT DETAIL:  The postmaster has commanded this
> server
> > process to roll back the current transaction and exit, because another
> > server process exited abnormally and possibly corrupted shared memory.
> > 2016-03-31 09:36:56 CAT HINT:  In a moment you should be able to
> reconnect
> > to the database and repeat your command.
> > 2016-03-31 09:36:56 CAT WARNING:  terminating connection because of
> crash of
> > another server process
> > 2016-03-31 09:36:56 CAT DETAIL:  The postmaster has commanded this
> server
> > process to roll back the current transaction and exit, because another
> > server process exited abnormally and possibly corrupted shared memory.
> > 2016-03-31 09:36:56 CAT HINT:  In a moment you should be able to
> reconnect
> > to the database and repeat your command.
> > 2016-03-31 09:36:56 CAT WARNING:  terminating connection because of
> crash of
> > another server process
> > 2016-03-31 09:36:56 CAT DETAIL:  The postmaster has commanded this
> server
> > process to roll back the current transaction and exit, because another
> > server process exited abnormally and possibly corrupted shared memory.
> > 2016-03-31 09:36:56 CAT HINT:  In a moment you should be able to
> reconnect
> > to the database and repeat your command.
> >
> > "
> >
> > I am guessing that my problem might have something to do with the fact
> that
> > the plpython2u language is not installed on the DB - although I am not
> sure
> > why it should it need it, when executing a plpython3 command.  As I
> > understand it, Postgres 9.4 accomodates both versions of python.
> However I
> > have not been able to figure out how to load plpython2u onto the db.
> The
> > postgres version I installed does not ship with plpython2.dll and I have
> not
> > been able to get hold of a copy. ( Python 2.7 /3.2 and 3.3 are installed
> on
> > my machine, and I am running postgre versions  9.2, 9.3 and 9.4.4 on
> > different ports).
> >
> >
> >
> >
> >
> > --
> > View this message in context:
> http://postgresql.nabble.com/Postgres-9-4-4-9-4-6-plpython2-3-intallation-issues-on-a-Windows-7-machine-tp5896157.html
> > Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> >
> >
> Hello!
> What postgres distr for Win  are you using?
>
> --
> Alex Ignatov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
>
> --
> Sent via pgsql-general mailing list ([hidden email]
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
> --
> If you reply to this email, your message will be added to the discussion
> below:
>
> http://postgresql.nabble.com/Postgres-9-4-4-9-4-6-plpython2-3-intallation-issues-on-a-Windows-7-machine-tp5896157p5896174.html
> To unsubscribe from Postgres 9.4.4/9.4.6: plpython2/3 intallation issues
> on a Windows 7 machine, click here
> 
> .
> NAML
> 

[GENERAL] Postgres 9.4.4/9.4.6: plpython2/3 intallation issues on a Windows 7 machine

[margrit drescher]

I installed postgres 9.4.6 on my Windows 7 (64-bit) machine, and managed to
create the plpython3u extension without a problem.

I then restored an existing DB (created in postgres 9.3).  The plpython3
functions restored without a hitch and can execute.


However, as soon as I try to create a NEW plpython3 function on the db the
postgres service terminates and does an auto-recovery.  

I then downgraded to postgres 9.4.4 and am still encountering the same
issue.



Example:

When  running the following code:

"create function test() returns void as $$ plpy.notice( 'xxx' ); $$ language
plpython3u"


the following is generated in the log:

"
2016-03-31 09:36:56 CAT LOG:  server process (PID 6376) exited with exit
code 3
2016-03-31 09:36:56 CAT DETAIL:  Failed process was running:  create
function test() returns void as $$ plpy.notice( 'xxx' ); $$ language
plpython3u
2016-03-31 09:36:56 CAT LOG:  terminating any other active server processes
2016-03-31 09:36:56 CAT WARNING:  terminating connection because of crash of
another server process
2016-03-31 09:36:56 CAT DETAIL:  The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2016-03-31 09:36:56 CAT HINT:  In a moment you should be able to reconnect
to the database and repeat your command.
2016-03-31 09:36:56 CAT WARNING:  terminating connection because of crash of
another server process
2016-03-31 09:36:56 CAT DETAIL:  The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2016-03-31 09:36:56 CAT HINT:  In a moment you should be able to reconnect
to the database and repeat your command.
2016-03-31 09:36:56 CAT WARNING:  terminating connection because of crash of
another server process
2016-03-31 09:36:56 CAT DETAIL:  The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2016-03-31 09:36:56 CAT HINT:  In a moment you should be able to reconnect
to the database and repeat your command.

"

I am guessing that my problem might have something to do with the fact that
the plpython2u language is not installed on the DB - although I am not sure
why it should it need it, when executing a plpython3 command.  As I
understand it, Postgres 9.4 accomodates both versions of python.   However I
have not been able to figure out how to load plpython2u onto the db.  The
postgres version I installed does not ship with plpython2.dll and I have not
been able to get hold of a copy. ( Python 2.7 /3.2 and 3.3 are installed on
my machine, and I am running postgre versions  9.2, 9.3 and 9.4.4 on
different ports).





--
View this message in context: 
http://postgresql.nabble.com/Postgres-9-4-4-9-4-6-plpython2-3-intallation-issues-on-a-Windows-7-machine-tp5896157.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] bdr replication

[Alvaro Aguayo Garcia-Rada]

I'd rather use something like this:

bdr_dump -N bdr -h localhost -U postgres -W mydb > /backup.sql

That's for each database. You would restore it, you must first create the empty 
database:

CREATE DATABASE mydb WITH ENCODING 'UTF-8' TEMPLATE template0;

(change encoding according to your needs. Run "psql --list" on your current 
system to see current encoding for each db)
Then, restore its contents:

cat /backup.sql | psql mydb

During restore, you may see some errors due to the backup containing the bdr 
truncate trigger on each table; it's normal at this point, so just ignore it.

You may need to backup your postgres users:

pg_dumpall -g > /globals.sql

And restore it BEFORE any database with

cat /globals.sql | psql

Backing up and restoring globals may be important when rewtoring databases, 
mostly to preserve permissions on tables/sequences during each database restore.

Regards,

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 
954183248
Website: www.ocs.pe

Sent from my Sony Xperia™ smartphone

 Slava Bendersky wrote 

Hello Alvaro, 
That sound like a good plan. I will trying tomorrow, because need maintenance 
window. 

To do back up I can use something like 

bdr_dump -Fp -h localhost -U postgres mydb -f /tmp/mydb.data > --data-only 
--exclude-table='bdr_* 

volga629 


From: "Alvaro Aguayo Garcia-Rada"  
To: "volga629"  
Cc: "pgsql-general" , "John R Pierce" 
 
Sent: Thursday, 31 March, 2016 02:41:17 
Subject: Re: [GENERAL] bdr replication 

We are overlaping mails :P 

What I don't understand is the need of a shared storage in this case. It would 
be a lot better to have the data folder inside each server virtual disk to 
avoid troubles with the shared storage; I really see no reason for such 
configuration here. 

Now, getting into the solution rather than in the problem. I suggest you to do 
the following: 

1. First of all, backup your data folder for both nodes. Just in case. Make 
backup with postgres stopped to avoid problems. 
2. Choose one node which will be considered up-to-date(Let's say "Node A") 
3. Dump your database(s) on that node, excluding the bdr schema on each db. 
Dump also your globals 
4. Wipe or rename your data folder on each node, and then initialize each node. 
Do not configure BDR yet. 
5. Restore your data(backed up at step 3) on Node A 
6. Configure BDR on Node A 
7. Add Node B to the replication group, using "bdr_init_copy" to make it 
replicate from Node A. 

That should do the trick. There is another possibility: Drop the replication 
configuration no Node A, and then start from scratch(Steps 1, 6 & 7). However, 
this can be troublesome, as it involves editing bdr & postgres schemas, and 
that can lead you to problems on the future, so I'd recommend you the "long" 
way. 

Feel free to ask any question regarding this issue. Looks serious 

Alvaro Aguayo 
Jefe de Operaciones 
Open Comb Systems E.I.R.L. 

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 
954183248 
Website: www.ocs.pe 

- Original Message - 
From: "Slava Bendersky"  
To: "Alvaro Aguayo Garcia-Rada"  
Cc: "pgsql-general" , "John R Pierce" 
 
Sent: Thursday, 31 March, 2016 12:28:09 AM 
Subject: Re: [GENERAL] bdr replication 



Hello Alvaro, 
We running BDR where each PostgreSQL vm is a master and shared storage only on 
hypervisor level. All vm leave with own virtual disk. Right now we have 2 
server for redundancy which have shared network between them. Issue that BDR is 
working right now see log below. And my question how to restore BDR replication 
correctly. 


volga629 

- Original Message - 

From: "Alvaro Aguayo Garcia-Rada"  
To: "volga629"  
Cc: "pgsql-general" , "John R Pierce" 
 
Sent: Thursday, 31 March, 2016 02:19:42 
Subject: Re: [GENERAL] bdr replication 


What's the purpose of such configuration? Doesn't makes sense for me. The only 
reasonable case where you would want to put the data folder on a shared storage 
is for usage with warm standby, where you can have a secondary server which 
serves as a read-only replica, and can be rpomoted to master on master failure. 

If you intend high availability, you'd rather try it at VM level, like vmware 
HA or Proxmox HA. That will make your VM run on any hypervisor in the group 
disregarding the failure of some node. 

Regards, 

Alvaro Aguayo 
Jefe de Operaciones 
Open Comb Systems E.I.R.L. 

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 
954183248 
Website: www.ocs.pe 

- Original Message - 
From: "Slava Bendersky"  
To: "John R Pierce"