Re: [GENERAL] archive_command not being executed

2017-11-13 Thread Eric D 
I'd come to the same conclusion Jeremy had about 9.5 being the first version 
with the 'always' option for archive_mode.   Looking at pg_receivexlog, that 
might work, but with me being a total noob I'm wary of the various steps I'd 
have to take in going from:


Master -> streaming replication to -> SB1 -> pg_receivexlog to -> SB2


to:


New Master (Old SB1) -> streaming replication to -> SB2


And whether or not the conversion from pg_receivexlog to normal streaming 
replication would maintain data integrity.  I need to skew this towards 
simplicity or I'll likely screw it up.


My current thought is to cut off master, promote SB1, set up WAL file shipping 
to SB2, start a pg_basebackup, make SB1 live, then run for a couple days with 
no backup as the pg_basebackup runs.   Far from ideal but at least I have gone 
through most of this before.



From: Michael Paquier 
Sent: Monday, November 13, 2017 6:01 PM
To: Jeremy Schneider
Cc: eric...@hotmail.com; PostgreSQL General; Paul Jungwirth
Subject: Re: [GENERAL] archive_command not being executed

On Tue, Nov 14, 2017 at 8:56 AM, Jeremy Schneider
 wrote:
> From my reading of the docs and commit logs, standby databases
> couldn't archive their WALs until 9.5.

pg_receivexlog is available in 9.3. You could leverage your archives
with it easily, by for example connecting it to a standby you'd like
to get the archives from.
--
Michael

Re: [GENERAL] PostgreSQL walsender process doesn't exist after "pg_ctl stop -m fast"

2017-11-13 Thread Michael Paquier 
On Tue, Nov 14, 2017 at 12:05 AM, Tom Lane  wrote:
> y39chen  writes:
>> We encounter one problem that PostgreSQL walsender process doesn't exist
>> after "pg_ctl stop -m fast".
>> Uses PostgreSQL 9.6.2
>
> There was a fix in 9.6.4 that's at least related to this problem.
> It would be interesting to see if you can still reproduce it on
> current 9.6.

Commit that may matter here:
commit: e9d4aa594f2caa8c28d55c41c9926420b1efdb79
author: Tom Lane 
date: Fri, 30 Jun 2017 12:00:03 -0400
Fix walsender to exit promptly if client requests shutdown.

It's possible for WalSndWaitForWal to be asked to wait for WAL that doesn't
exist yet.  That's fine, in fact it's the normal situation if we're caught
up; but when the client requests shutdown we should not keep waiting.
The previous coding could wait indefinitely if the source server was idle.

In passing, improve the rather weak comments in this area, and slightly
rearrange some related code for better readability.

Back-patch to 9.4 where this code was introduced.

Discussion: https://postgr.es/m/14154.1498781...@sss.pgh.pa.us
-- 
Michael


-- 
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] archive_command not being executed

2017-11-13 Thread Michael Paquier 
On Tue, Nov 14, 2017 at 8:56 AM, Jeremy Schneider
 wrote:
> From my reading of the docs and commit logs, standby databases
> couldn't archive their WALs until 9.5.

pg_receivexlog is available in 9.3. You could leverage your archives
with it easily, by for example connecting it to a standby you'd like
to get the archives from.
-- 
Michael


-- 
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] archive_command not being executed

2017-11-13 Thread Jeremy Schneider 
Hi Eric,

Thanks for using PostgreSQL!

On Fri, Nov 10, 2017 at 9:26 AM, Paul Jungwirth
 wrote:
> Oh this has happened to me before. :-) On SB1 you need to set
> archive_mode to always (not on). Otherwise it is ignored when running as a
> standby.

It looks to me like this feature was not added until 9.5 and Eric is
running 9.3  :(

> On 11/10/2017 09:10 AM, Eric D wrote:
>> I have a standby db server (SB1) that will soon become the master.  SB1
>> is set up with streaming replication from the current master.  I'm
>> trying to set up a third server (SB2) as a slave/standby to SB1, so that
>> when SB1 becomes the master, there will be a standby for it.  First step
>> is to get WAL files shipped from SB1->SB2.

Eric,

>From my reading of the docs and commit logs, standby databases
couldn't archive their WALs until 9.5.

https://www.postgresql.org/message-id/cankgpbs7qgakgq-opzy0esam6+wue5mgpyehcgo_eoq7tjv...@mail.gmail.com
http://paquier.xyz/postgresql-2/postgres-9-5-feature-highlight-archive-mode-always/

There is a section in the 9.3 docs about cascading replication:
https://www.postgresql.org/docs/9.3/static/warm-standby.html#CASCADING-REPLICATION

It seems to me this is based on streaming replication only.  Looks
like it's not designed (in 9.3) to start archiving until it becomes a
primary.  "You will also need to set primary_conninfo in the
downstream standby to point to the cascading standby."

Are you able to configure a cascading replica by using streaming
replication on your 9.3 system, without WAL archiving on the standby?

-Jeremy

-- 
http://about.me/jeremy_schneider


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

Re: [GENERAL] PG-10 + ICU and abbreviated keys

2017-11-13 Thread Andreas Joseph Krogh 
På tirsdag 14. november 2017 kl. 00:44:11, skrev Peter Geoghegan mailto:p...@bowt.ie>>:
On Mon, Nov 13, 2017 at 2:05 PM, Andreas Joseph Krogh
  wrote:
 > When sorting on text, we're usually doing so using an multi-column index, 
like for instance "CREATE INDEX xxx ON my_table (lower(name) ASC, created 
ASC)". Will abbreviated keys help here?

 Yes, they'll help with that, even though the leading column might be
 low cardinality.
 
Nice to know, thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] PG-10 + ICU and abbreviated keys

2017-11-13 Thread Peter Geoghegan 
On Mon, Nov 13, 2017 at 2:05 PM, Andreas Joseph Krogh
 wrote:
> When sorting on text, we're usually doing so using an multi-column index, 
> like for instance "CREATE INDEX xxx ON my_table (lower(name) ASC, created 
> ASC)". Will abbreviated keys help here?

Yes, they'll help with that, even though the leading column might be
low cardinality.

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

[GENERAL] "Cascading Logical Replication" from a physical replica

2017-11-13 Thread Hannes Erven 

Hi,


as of PG10, it is not possible to create logical replication slots on 
standby servers.
Should that fact probably be mentioned more explicitly in 
https://www.postgresql.org/docs/10/static/logical-replication.html ?



There seems to be work by Craig Ringer going on, but that doesn't seem 
to be arriving soon...

https://commitfest.postgresql.org/12/788/


My requirement is to be notified of any row changes 
(insert/update/delete) that become visible on a standby server through 
streaming physical replication.
Table name and primary key would be sufficient, I don't need the actual 
row data. And there's no "replication" at all ;-)



Is this something that pglogical 
(https://www.2ndquadrant.com/en/resources/pglogical/ ) or pgxlogdump 
could provide?



The workaround I've come up so far would be: connect logical replication 
to the master and extract the events I'm interested together with the 
WAL position of the corresponding TX's commit.
Then, send that information to each replica; connect locally and poll 
the replayed WAL position every second or so. Work on the events that 
became visible and queue the rest.


But this seems to be quite complex compared to just reading the WAL off 
the standby where it already is, ideally using the logical output plugin 
interface to format the data.



Thanks for any insights!
Best regards,

-hannes erven






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

Re: [GENERAL] PG-10 + ICU and abbreviated keys

2017-11-13 Thread Andreas Joseph Krogh 
På mandag 13. november 2017 kl. 22:28:40, skrev Peter Geoghegan mailto:p...@bowt.ie>>:
On Mon, Nov 13, 2017 at 12:48 PM, Andreas Joseph Krogh
  wrote:
 > Thanks.

 As the person that worked on abbreviated keys, I'd like to hear about
 how you get with this. How much faster is it for you?

 I don't usually get to hear about this, because most users don't
 notice that anything in particular gets faster, because there are many
 performance enhancements added to a release.
 
We haven't migrated any of our databases to v10 yet so I really can't tell. 
I'm evaluating ICU-usage as the last step before we decide moving to v10. Being 
a per-column setting that means a pg_dump/reload won't cut it (AFAIU), so I'm 
not sure we'll take that route as it involves much manual tweaking which we're 
really not interessted in spending time on.
 
When sorting on text, we're usually doing so using an multi-column index, like 
for instance "CREATE INDEX xxx ON my_table (lower(name) ASC, created ASC)". 
Will abbreviated keys help here?
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] PG-10 + ICU and abbreviated keys

2017-11-13 Thread Peter Geoghegan 
On Mon, Nov 13, 2017 at 12:48 PM, Andreas Joseph Krogh
 wrote:
> Thanks.

As the person that worked on abbreviated keys, I'd like to hear about
how you get with this. How much faster is it for you?

I don't usually get to hear about this, because most users don't
notice that anything in particular gets faster, because there are many
performance enhancements added to a release.

-- 
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] PG-10 + ICU and abbreviated keys

2017-11-13 Thread Andreas Joseph Krogh 
På mandag 13. november 2017 kl. 21:46:08, skrev Peter Geoghegan mailto:p...@bowt.ie>>:
On Mon, Nov 13, 2017 at 12:40 PM, Peter Geoghegan  wrote:
 >> Do I have to explicitly specify collation when using ORDER by on that 
column for index and abbreviated keys to be used?
 >
 > Only if you didn't define the column with a per-column collation initially.

 BTW, if you specifically want to quickly verify whether or not
 abbreviated keys were used, you can do that by setting "trace_sort =
 on", and possibly setting "client_min_messages = LOG", too.

 There should be quite a bit of debug output from that that
 specifically mentions abbreviated keys.
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] PG-10 + ICU and abbreviated keys

2017-11-13 Thread Andreas Joseph Krogh 
På mandag 13. november 2017 kl. 21:40:47, skrev Peter Geoghegan mailto:p...@bowt.ie>>:
On Mon, Nov 13, 2017 at 11:48 AM, Andreas Joseph Krogh
  wrote:
 > Ok, so I have to explicitly specify like this:
 >
 > create table test(id serial primary key, name varchar collate "nb_NO" not 
null);

 That doesn't look like an ICU locale. You may mean "nb-NO-x-icu". But
 otherwise, yes.
 
 
Ok, is there a way I can get a list of ICU-collations?
 
 
> Will ICU be used here as long as PG is compiled with ICU-suppoert, as the 
debian-packages are, or do I have to specify collation-provider?

 If you did initdb with a version with ICU support, the ICU collations
 should be there.

 > Do I have to explicitly specify collation when using ORDER by on that 
column for index and abbreviated keys to be used?

 Only if you didn't define the column with a per-column collation initially.
 
Ok, thanks.
 
Looking forward to this being a per-database setting so it's (hopefully) more 
transparent.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] PG-10 + ICU and abbreviated keys

2017-11-13 Thread Peter Geoghegan 
On Mon, Nov 13, 2017 at 12:40 PM, Peter Geoghegan  wrote:
>> Do I have to explicitly specify collation when using ORDER by on that column 
>> for index and abbreviated keys to be used?
>
> Only if you didn't define the column with a per-column collation initially.

BTW, if you specifically want to quickly verify whether or not
abbreviated keys were used, you can do that by setting "trace_sort =
on", and possibly setting "client_min_messages = LOG", too.

There should be quite a bit of debug output from that that
specifically mentions abbreviated keys.

-- 
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] PG-10 + ICU and abbreviated keys

2017-11-13 Thread Peter Geoghegan 
On Mon, Nov 13, 2017 at 11:48 AM, Andreas Joseph Krogh
 wrote:
> Ok, so I have to explicitly specify like this:
>
> create table test(id serial primary key, name varchar collate "nb_NO" not 
> null);

That doesn't look like an ICU locale. You may mean "nb-NO-x-icu". But
otherwise, yes.

> Will ICU be used here as long as PG is compiled with ICU-suppoert, as the 
> debian-packages are, or do I have to specify collation-provider?

If you did initdb with a version with ICU support, the ICU collations
should be there.

> Do I have to explicitly specify collation when using ORDER by on that column 
> for index and abbreviated keys to be used?

Only if you didn't define the column with a per-column collation initially.

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

[GENERAL] Logical replication + before trigger = ERROR: attempted to lock invisible tuple

2017-11-13 Thread Thomas Rosenstein 

Hi,

I'm trying to execute a BEFORE UPDATE trigger for my logical replication 
subscription with Postgresql 10.1 but the apply worker crashes with:


ERROR:  attempted to lock invisible tuple


The trigger creation:

CREATE TRIGGER customers_anonymize_before_update BEFORE UPDATE ON 
customers FOR EACH ROW EXECUTE PROCEDURE customers_anonymize();


The "BEFORE INSERT" trigger seems to work fine.

I found this Issue on 2ndQuadrant: 
https://github.com/2ndQuadrant/pglogical/issues/97


Is this on the radar? Is it already fixed?

Thanks
BR
Thomas


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

Re: [GENERAL] PG-10 + ICU and abbreviated keys

2017-11-13 Thread Andreas Joseph Krogh 
På mandag 13. november 2017 kl. 19:07:53, skrev Peter Geoghegan mailto:p...@bowt.ie>>:
On Mon, Nov 13, 2017 at 12:27 AM, Andreas Joseph Krogh
  wrote:
 > In PG-10, with ICU enabled, is abbreviated keys now enabled?

 Yes. ICU will use abbreviated keys on every platform, including Windows.

 > If so, using locale=nb_NO.UTF-8, do I have to use a ICU-specific locale to 
take advantage of abbreviated keys?

 You need to use an ICU collation. It must be a per-column collation,
 as you cannot currently use ICU for an entire database. (This
 limitation should be removed in the next release or two.)
 
Ok, so I have to explicitly specify like this:
 
create table test(id serial primary key, name varchar collate "nb_NO" not 
null);
  
Will ICU be used here as long as PG is compiled with ICU-suppoert, as the 
debian-packages are, or do I have to specify collation-provider?
 
Do I have to explicitly specify collation when using ORDER by on that column 
for index and abbreviated keys to be used?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] PG-10 + ICU and abbreviated keys

2017-11-13 Thread Peter Geoghegan 
On Mon, Nov 13, 2017 at 12:27 AM, Andreas Joseph Krogh
 wrote:
> In PG-10, with ICU enabled, is abbreviated keys now enabled?

Yes. ICU will use abbreviated keys on every platform, including Windows.

> If so, using locale=nb_NO.UTF-8, do I have to use a ICU-specific locale to 
> take advantage of abbreviated keys?

You need to use an ICU collation. It must be a per-column collation,
as you cannot currently use ICU for an entire database. (This
limitation should be removed in the next release or two.)

-- 
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] PostgreSQL walsender process doesn't exist after "pg_ctl stop -m fast"

2017-11-13 Thread Tom Lane 
y39chen  writes:
> We encounter one problem that PostgreSQL walsender process doesn't exist
> after "pg_ctl stop -m fast".
> Uses PostgreSQL 9.6.2
 
There was a fix in 9.6.4 that's at least related to this problem.
It would be interesting to see if you can still reproduce it on
current 9.6.

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] sync the data's from catalog table

2017-11-13 Thread Dinesh kumar 
Hi,

Whenever the postgres user is trying to modify the user account's password
column in pg_authid table, we need to maintain a trigger in catalog table
(pg_authid) where it pop up the the "password column has been restricted
and it should not be modified". Is there any possible for the above
scenario in postgres? if it there please guide me how to proceed on this.
and one more question, Can we restrict the postgres user to not modify the
pg_authid table.  Please share your thoughts on it.

Thanks


On Mon, Nov 13, 2017 at 2:12 PM, Laurenz Albe 
wrote:

> Dinesh kumar wrote:
> > How can I sync the data's from pg_authid to manually created table (user
> table) whenever the update or insert happens on pg_authid table.
>
> You cannot do this, because you cannot define triggers on catalog tables.
>
> The question is:
> Why do you want to do this? What are you trying to achieve?
>
> Yours,
> Laurenz Albe
>

Re: [GENERAL] PG-10 + ICU and abbreviated keys

2017-11-13 Thread Adrien Nayrat 
On 11/13/2017 09:27 AM, Andreas Joseph Krogh wrote:
>  
> In PG-10, with ICU enabled, is abbreviated keys now enabled?
>  

Hello,


I think yes :

src/backend/utils/adt/varlena.c

1876 /*
1877  * Unfortunately, it seems that abbreviation for non-C collations is
1878  * broken on many common platforms; testing of multiple versions of 
glibc
1879  * reveals that, for many locales, strcoll() and strxfrm() do not 
return
1880  * consistent results, which is fatal to this optimization.  While no
1881  * other libc other than Cygwin has so far been shown to have a 
problem,
1882  * we take the conservative course of action for right now and disable
1883  * this categorically.  (Users who are certain this isn't a problem on
1884  * their system can define TRUST_STRXFRM.)
1885  *
1886  * Even apart from the risk of broken locales, it's possible that there
1887  * are platforms where the use of abbreviated keys should be disabled 
at
1888  * compile time.  Having only 4 byte datums could make worst-case
1889  * performance drastically more likely, for example.  Moreover, macOS's
1890  * strxfrm() implementation is known to not effectively concentrate a
1891  * significant amount of entropy from the original string in earlier
1892  * transformed blobs.  It's possible that other supported platforms are
1893  * similarly encumbered.  So, if we ever get past disabling this
1894  * categorically, we may still want or need to disable it for 
particular
1895  * platforms.
1896  */
1897 #ifndef TRUST_STRXFRM
1898 if (!collate_c && !(locale && locale->provider == COLLPROVIDER_ICU))
1899 abbreviate = false;
1900 #endif


But I did not do any test to compare performances.

Regards,

-- 
Adrien NAYRAT

http://dalibo.com - http://dalibo.org



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] pg on Debian servers

2017-11-13 Thread Mark Morgan Lloyd 

On 12/11/17 19:15, Karsten Hilbert wrote:

On Sat, Nov 11, 2017 at 01:03:18PM +, Mark Morgan Lloyd wrote:


Several legacy programs written in Delphi ground to a halt this morning,
which turned out to be because a Debian system had updated its copy of
PostgreSQL and restarted the server, which broke any live connections.

At least some versions of Delphi, not to mention other IDE/RAD tools with
database-aware components, don't automatically try to reestablish a database
session that's been interrupted. In any event, an unexpected server restart
(irrespective of all investment in UPSes etc.) has the potential of playing
havoc on a clustered system.

Is there any way that either the package maintainer or a site
administrator/programmer such as myself can mark the Postgres server
packages as "manual upgrade only" or similar? Or since I'm almost certainly
not the first person to be bitten by this, is there a preferred hack in
mitigation?


Apart from that (putting packages on hold), PostgreSQL
updates on Debian don't upgrade existing clusters
automatically. They do create a new cluster but the old one
is kept around and stays running, IIRC even on the very same
port.

(Having gone all the way from PG 7.1 to PG 10 on Debian :)


With the caveat that Debian has only comparatively-recently introduced 
unattended updates as the default... I think only with Stretch. If 
you're still on Jessie you can yet be saved :-)



What did

pg_lsclusters

say ?


I don't have it from the time of the problem, but currently it gives me

Ver Cluster Port Status OwnerData directory   Log file
9.6 main5432 online postgres /var/lib/postgresql/9.6/main 
/var/log/postgresql/postgresql-9.6-main.log


i.e. a single-server system, although I've since done a manual restart 
so that I could change some DIMMs.


However syslog and postgresql-9.6-main.log show me this:

Nov 11 06:27:38 postgres1 systemd[1]: Starting Daily apt upgrade and 
clean activities...

Nov 11 06:28:05 postgres1 systemd[1]: Reloading.
Nov 11 06:28:07 postgres1 systemd[1]: Reloading.
Nov 11 06:28:07 postgres1 systemd[1]: Stopped PostgreSQL RDBMS.
Nov 11 06:28:07 postgres1 systemd[1]: Stopping PostgreSQL Cluster 
9.6-main...

Nov 11 06:28:08 postgres1 systemd[1]: Stopped PostgreSQL Cluster 9.6-main.
Nov 11 06:28:10 postgres1 systemd[1]: Reloading.

2017-11-11 06:28:07.587 UTC [675] LOG:  received fast shutdown request
2017-11-11 06:28:07.587 UTC [675] LOG:  aborting any active transactions
[Session names here]
2017-11-11 06:28:07.607 UTC [730] LOG:  autovacuum launcher shutting down
[More session names here]
2017-11-11 06:28:07.680 UTC [727] LOG:  shutting down
2017-11-11 06:28:07.984 UTC [675] LOG:  database system is shut down
2017-11-11 06:28:13.039 UTC [11122] LOG:  database system was shut down 
at 2017-11-11 06:28:07 UTC
2017-11-11 06:28:13.081 UTC [11122] LOG:  MultiXact member wraparound 
protections are now enabled

2017-11-11 06:28:13.085 UTC [11126] LOG:  autovacuum launcher started
2017-11-11 06:28:13.085 UTC [11121] LOG:  database system is ready to 
accept connections
2017-11-11 06:28:13.371 UTC [11128] [unknown]@[unknown] LOG:  incomplete 
startup packet


All live applications saw that as a loss of database connectivity, yet 
when I was alerted by their squeals of anguish (MIDI on app servers has 
its uses :-) I found the database server running and accepting connections.



There must have been something additional at play.


The apps are written in Delphi, I admit not a very recent version and 
they're due to be converted to Lazarus which is an open-source and 
portable clone. I'll defend my choice of language since it is, 
basically, the best "4GL" you'll find.


However one flaw of Delphi etc. is that they assume that they can safely 
hold a database session open for an extended period. I can't speak for 
Delphi any more since it has, basically, priced itself out of our league 
particularly taking into account its lack of portability, but 
FPC/Lazarus appears to have something which is intended to reconnect a 
lost session, although it's so far unimplemented.


So I've got multiple options for fixing this at the application level: 
either fill in the unimplemented bit of the database control in the 
Lazarus Class Library, or prevent apps from holding database connections 
open. But the real problem, I feel, is that Debian is enabling 
unattended upgrades without checking with the user, and while an 
attended upgrade normally asks for confirmation before restarting a 
daemon an unattended one doesn't.


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

[GENERAL] missing public on schema public

2017-11-13 Thread Bo Thorbjørn Jensen 
Hi

We recently upgraded from 9.1 to 9.6 (now 9.6.6) and have, after dump/restore 
on 9.6, experienced the loss of public priviliges on schema public.

Is this a "feature" or some kind of bug ?

I have found a thread here that looks sortof similar with subject: "[GENERAL] 
intentional or oversight? pg_dump -c does not restore default priviliges on 
schema public"
But it is from march 2017 and it looks like it ends with a fix being pushed..

Version() is
"PostgreSQL 9.6.6, compiled by Visual C++ build 1800, 64-bit"
Binary from EDB

Thank you for your time.

Kind regards

Bo Thorbjørn Jensen
Udvikler hos budget123

Re: [GENERAL] sync the data's from catalog table

2017-11-13 Thread Laurenz Albe 
Dinesh kumar wrote:
> How can I sync the data's from pg_authid to manually created table (user 
> table) whenever the update or insert happens on pg_authid table.

You cannot do this, because you cannot define triggers on catalog tables.

The question is:
Why do you want to do this? What are you trying to achieve?

Yours,
Laurenz Albe


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

[GENERAL] PostgreSQL walsender process doesn't exist after "pg_ctl stop -m fast"

2017-11-13 Thread y39chen 
We encounter one problem that PostgreSQL walsender process doesn't exist
after "pg_ctl stop -m fast".
Uses PostgreSQL 9.6.2
Steps:
1)active postgres server  is up;
2)standby postgres intance take pg_basebackup 
3)usin command "pg_ctl stop -W -m fast -D /mnt/db/DBTestPostgres/db_data" to
stop active postgres server;
4)active postgres server can't stop successfully. It hanging by walsender
process and can't exit (we wait about 10min)
5)kill -9 walsender process, it exit right now. 

1. make postgres coredump, the log as bellow
  
 Stack trace of thread 2288:
 #0  0x7f7fae6ea013 __select
(libc.so.6)
 #1  0x0046df6b
ServerLoop (postgres)
 #2  0x0060d528
PostmasterMain (postgres)
 #3  0x0046f2dc main
(postgres)
 #4  0x7f7fae62a461
__libc_start_main (libc.so.6)
 #5  0x0046f35a _start
(postgres)

Nov 10 10:08:02 mn-1 systemd-coredump[8994]: Process 2645 (postgres) of user
550 dumped core.
 
 Stack trace of thread 2645:
 #0  0x7f7fae6f1d63
epoll_wait (libc.so.6)
 #1  0x0063ce69
WaitEventSetWait (postgres)
 #2  0x0063d1ab
WaitLatchOrSocket (postgres)
 #3  0x00622ead
WalSndLoop (postgres)
 #4  0x00623a8f
exec_replication_command (postgres)
 #5  0x006558fd
PostgresMain (postgres)
 #6  0x0046e631
ServerLoop (postgres)
 #7  0x0060d528
PostmasterMain (postgres)
 #8  0x0046f2dc main
(postgres)
 #9  0x7f7fae62a461
__libc_start_main (libc.so.6)
 #10 0x0046f35a _start
(postgres)


2. active postgres intance log:

Nov 09 12:29:53 mn-1 postgres[6073]: [3-1] DEBUG:  autovacuum: processing
database "DBTestPostgres"
Nov 09 12:30:13 mn-1 postgres[6204]: [3-1] DEBUG:  autovacuum: processing
database "postgreswd"
Nov 09 12:30:20 mn-1 postgres[2231]: [35-1] DEBUG:  checkpoint sync:
number=1 file=base/16385/16467 time=14.990 msec
Nov 09 12:30:20 mn-1 postgres[2231]: [36-1] LOG:  checkpoint complete: wrote
1065 buffers (6.5%); 0 transaction log file(s) added, 0 removed, 2 recycled;
write=59.892 s, sync=0.015 s, total=59.927 s; sync files=1, longest=0.014 s,
average=0.014 s; distance=12487 kB, estimate=19411 kB
Nov 09 12:30:20 mn-1 postgres[2231]: [37-1] LOG:  checkpoint starting: force
wait
Nov 09 12:30:20 mn-1 postgres[2231]: [38-1] DEBUG:  performing replication
slot checkpoint
Nov 09 12:30:21 mn-1 postgres[2231]: [39-1] DEBUG:  checkpoint sync:
number=1 file=base/16385/16467 time=59.400 msec
Nov 09 12:30:21 mn-1 postgres[2231]: [40-1] LOG:  checkpoint complete: wrote
1 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled;
write=1.102 s, sync=0.059 s, total=1.168 s; sync files=1, longest=0.059 s,
average=0.059 s; distance=3900 kB, estimate=17860 kB
Nov 09 12:30:23 mn-1 postgres[2235]: [28-1] DEBUG:  archived transaction log
file "00020193"
Nov 09 12:30:23 mn-1 postgres[2235]: [29-1] DEBUG:  archived transaction log
file "00020193.0108.backup"
Nov 09 12:30:24 mn-1 postgres[6263]: [3-1] DEBUG:  received replication
command: IDENTIFY_SYSTEM
Nov 09 12:30:24 mn-1 postgres[6263]: [4-1] DEBUG:  received replication
command: START_REPLICATION 0/6500 TIMELINE 2
Nov 09 12:30:24 mn-1 postgres[6263]: [5-1] DEBUG:  standby "walreceiver" has
now caught up with primary

Nov 09 12:30:24 mn-1 dbim-postgres[2039]: [2039-139870943506432] 
/opt/nokia/libexec/SS_RCPPostgress/bin/pg_ctl stop -W -m fast -D
/mnt/db/DBTestPostgres/db_data
Nov 09 12:30:24 mn-1 postgres[2157]: [3-1] LOG:  received fast shutdown
request
Nov 09 12:30:24 mn-1 postgres[2157]: [4-1] LOG:  aborting any active
transactions
Nov 09 12:30:24 mn-1 postgres[6073]: [4-1] err-2:  terminating autovacuum
process due to administrator command
Nov 09 12:30:24 mn-1 postgres[2234]: [3-1] LOG:  autovacuum launcher
shutting down
Nov 09 12:30:24 mn-1 postgres[2231]: [41-1] LOG:  shutting down
..
Nov 09 12:30:44 mn-1 postgres[6408]: [5-1] err-2:  the database system is
shutting down
Nov 09 12:30:46 mn-1 postgres[6411]: [5-1] err-2:  the database system is
shutting down
Nov 09 12:30:48 mn-1 postgres[6415]: [5-1] err-

[GENERAL] PG-10 + ICU and abbreviated keys

2017-11-13 Thread Andreas Joseph Krogh 
Hi.
 
In PG-10, with ICU enabled, is abbreviated keys now enabled?
 
If so, using locale=nb_NO.UTF-8, do I have to use a ICU-specific locale to 
take advantage of abbreviated keys?
 
Thanks.

 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com