Overloaded && operator from intarray module prevents index usage.

[Thomas Kellerer]

While testing a query on an integer array with a GIN index, I stumbled over a 
behaviour which surprised me and which I would consider a bug - but maybe I am 
wrong. 

Consider the following table:

create table idlist (ids int[], ... other columns ...);
create index on idlist using gin (ids array_ops);

Now the following query works fine and uses the index:

  select *
  from idlist
  where ids && array[1,2,3,4,6];

The above yields the following execution plan:

Bitmap Heap Scan on public.idlist  (cost=1748.30..70780.07 rows=423281 
width=240) (actual time=81.321..161.195 rows=423281 loops=1)
  Output: ids
  Recheck Cond: (idlist.ids && '{1,2,3,4,6}'::integer[])
  Heap Blocks: exact=67084
  Buffers: shared hit=67196
  ->  Bitmap Index Scan on idlist_ids_idx  (cost=0.00..1642.48 rows=423281 
width=0) (actual time=70.764..70.764 rows=423281 loops=1)
Index Cond: (idlist.ids && '{1,2,3,4,6}'::integer[])
Buffers: shared hit=112
Planning Time: 0.178 ms
Execution Time: 171.245 ms


But when I ran that on a database where the intarray extension is installed 
(and part of the search_path), Postgres uses the intarray operator which can't 
use the GIN index with the array_ops opclass, so there the query yields the 
following execution plan:

Seq Scan on public.idlist  (cost=0.00..76127.00 rows=423281 width=240) 
(actual time=0.021..5046.396 rows=423281 loops=1)
  Output: ids
  Filter: (idlist.ids && '{1,2,3,4,6}'::integer[])
  Rows Removed by Filter: 1576719
  Buffers: shared hit=67127
Planning Time: 0.123 ms
Execution Time: 5056.144 ms

I can work around that, using "OPERATOR(pg_catalog.&&)" instead of "&&", but 
that seems like a kludge to me. 
The above happens even if the intarray extension is a the end of the search 
path, e.g. "set search_path = public, intarray".
If I set the search path to only "public", the the index is used again. 

I tried the above with Postgres 11.2 on Windows and CentOS

Is this expected behaviour? Is this caused by the Postgres core (e.g. the 
optimizer to taking the opclass into account) or is it a "problem" in the way 
the intarray module defines its operators? 

I would have expected that the optimizer uses the operator that matches the 
opclass for the index, or at least the "first" one found in the search path. 

Any ideas?
Thomas

Re: Barman disaster recovery solution

[Achilleas Mantzios]

On 28/2/19 1:08 π.μ., Ahmed, Nawaz wrote:


Hi,

I believe the "file copy" method (listed in the table) in pgbackrest is based on pg_basebackup, so i think it should be "pg_basebackup over ssh" as pgbackrest internally calls pg_basebackup. David 
Steele can correct me.



No, apparently pgbackrest does not rely on pg_basebackup. Have you ever heard 
pg_basebackup supporting diff and incr backups ? Or checksums? Or compression ? 
Or aborted backup resumption ?


Best Regards,

**

*Nawaz Ahmed
Software Development Engineer

Fujitsu Australia Software Technology Pty Ltd*
14 Rodborough Road, Frenchs Forest NSW 2086, Australia
*T* +61 2 9452 9027
na...@fast.au.fujitsu.com 
fastware.com.au 

*From:*Achilleas Mantzios 
*Sent:* Wednesday, 27 February 2019 8:40 PM
*To:* pgsql-general@lists.postgresql.org
*Subject:* Re: Barman disaster recovery solution

On 21/2/19 9:28 π.μ., Achilleas Mantzios wrote:

On 21/2/19 9:17 π.μ., Julie Nishimura wrote:

Does anyone use this solution? any recommenations?

Thanks!


Barman will fit most requirements. PgBackRest excels when WAL traffic goes 
on 10 files/day or more. I have written an article, not yet publised, on a 
comparison on the 3 most known
solutions. Will post a link as soon as it gets published.


Hello, as promised here is my blog :
https://severalnines.com/blog/current-state-open-source-backup-management-postgresql




-- 


Achilleas Mantzios

IT DEV Lead

IT DEPT

Dynacom Tankers Mgmt




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


  Disclaimer

The information in this e-mail is confidential and may contain content that is subject to copyright and/or is commercial-in-confidence and is intended only for the use of the above named addressee. 
If you are not the intended recipient, you are hereby notified that dissemination, copying or use of the information is strictly prohibited. If you have received this e-mail in error, please 
telephone Fujitsu Australia Software Technology Pty Ltd on + 61 2 9452 9000 or by reply e-mail to the sender and delete the document and all copies thereof.



Whereas Fujitsu Australia Software Technology Pty Ltd would not knowingly transmit a virus within an email communication, it is the receiver’s responsibility to scan all communication and any files 
attached for computer viruses and other defects. Fujitsu Australia Software Technology Pty Ltd does not accept liability for any loss or damage (whether direct, indirect, consequential or economic) 
however caused, and whether by negligence or otherwise, which may result directly or indirectly from this communication or any files attached.



If you do not wish to receive commercial and/or marketing email messages from 
Fujitsu Australia Software Technology Pty Ltd, please email 
unsubscr...@fast.au.fujitsu.com




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

Re: Barman disaster recovery solution

[Achilleas Mantzios]

On 27/2/19 6:52 μ.μ., Mark Fletcher wrote:

On Wed, Feb 27, 2019 at 1:39 AM Achilleas Mantzios mailto:ach...@matrix.gatewaynet.com>> wrote:


Hello, as promised here is my blog :

https://severalnines.com/blog/current-state-open-source-backup-management-postgresql


Nice blog post. If you're aiming for a comprehensive run down of tools, I suggest including wal-g. We've been using it since it was released (and its predecessor wal-e for years before that) and 
it's been great. We currently use it to back up a replica to S3, and it has no issues doing that. In more recent versions, it supports delta backups, which decrease the time/load required for a 
backup immensely in our case.

Thanks, I know about wal-e, wal-g, I may include this in some future update.


Cheers,
Mark



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

Re: create unique constraint on jsonb->filed during create table

[David G. Johnston]

On Wednesday, February 27, 2019, Andy Fan  wrote:

>
> The following way works with 2 commands:
>
> zhifan=# create table t1 (a jsonb);
> CREATE TABLE
> zhifan=# create unique index t1_a_name on t1 ((a->'name'));
> CREATE INDEX
>
> but know I want to merge them into 1 command, is it possible?
>
> zhifan=# create table t2 (a jsonb, constraint uk_t2_a_name
> unique((a->'name')));
> ERROR:  syntax error at or near "("
> LINE 1: ...table t2 (a jsonb, constraint uk_t2_a_name unique((a->'name'...
>

Not according to the documentation.  Unique table constraints can only
reference columns in the table as a whole.  An expression index must be
created separately from the table to which it is attached.

Or add a trigger to the table, populate an actual second column (making it
unique), and add a table check constraint that that column and the
expression are equal.  I suspect you’ll be happier having the PK as actual
column data anyway.

David J.

create unique constraint on jsonb->filed during create table

[Andy Fan]

The following way works with 2 commands:

zhifan=# create table t1 (a jsonb);
CREATE TABLE
zhifan=# create unique index t1_a_name on t1 ((a->'name'));
CREATE INDEX

but know I want to merge them into 1 command, is it possible?

zhifan=# create table t2 (a jsonb, constraint uk_t2_a_name
unique((a->'name')));
ERROR:  syntax error at or near "("
LINE 1: ...table t2 (a jsonb, constraint uk_t2_a_name unique((a->'name'...

Re: cannot execute VACUUM during recovery

[Michael Paquier]

On Wed, Feb 27, 2019 at 10:39:10AM -0800, Stephen Eilert wrote:
> Are you running Vacuum on the slave node? It has to run on the master.

VACUUM performs an equivalent write activity so it has to be
restricted.  ANALYZE can work though.
--
Michael


signature.asc
Description: PGP signature

Re: Channel binding not supported using scram-sha-256 passwords

[Michael Paquier]

On Wed, Feb 27, 2019 at 10:21:00AM +0100, Peter Eisentraut wrote:
> On 2019-02-26 23:35, Michael Paquier wrote:
>> What I do in such cases is to compile OpenSSL by myself and link
>> Postgres to it, here is a command to build shared libraries (all that
>> is documented in INSTALL):
>> ./config --prefix=$INSTALLPATH shared
> 
> I did test it now using a custom-built OpenSSL, and I can confirm it works.

Thanks for confirming, Peter!  Committed and back-patched.
--
Michael


signature.asc
Description: PGP signature

Re: why not using a mountpoint as PGDATA?

[raf]

Peter J. Holzer wrote:

> On 2019-02-27 12:33:02 +0100, Julien Rouhaud wrote:
> > On Wed, Feb 27, 2019 at 12:22 PM Luca Ferrari  wrote:
> > >
> > > What's wrong with using a mountpoint?
> > 
> > You can see most obvious reasons at
> > https://bugzilla.redhat.com/show_bug.cgi?id=1247477
> 
> I see only one good reason there: The fact that pg_upgrade needs write
> access to the parent directory. Of course that alone might suffice.
> 
> The other reasons aren't good IMHO.
> 
> The first one (initdb checks for an empty directory) is more "We
> disallow it, therefore it is a bad idea" than a reason for disallowing
> it.
> 
> The second is just wrong: You can have a non-root owned mount-point on
> any Unixoid system I've worked with. (And I don't see why that would be
> a security problem)
> 
> The third is wrong at least on Debian: All server processes have
> /var/lib/postgresql/$version/$cluster as their working directory, so it
> cannot be unmounted while the database is up. Even if you could, the
> server would either immediately lose access to all files (in which case
> you could recover) or it would keep access to all files (so, not a
> problem). Plus being in a subdirectory wouldn't change that. Maybe it's
> a potential problem with other layouts.
> 
> hp
> 
> -- 
>_  | Peter J. Holzer| we build much bigger, better disasters now
> |_|_) || because we have much more sophisticated
> | |   | h...@hjp.at | management tools.
> __/   | http://www.hjp.at/ | -- Ross Anderson 

I didn't know you weren't supposed to do that.
I've been using ecryptfs mountpoints for my data
directories for ages without problem. Ecryptfs doesn't
create lost+found and the underlying directory is owned
by the postgres user and so the mountpoint is as well
(even though it is mounted by root).

However, the parent directory is root-owned so I guess
pg_upgrade wouldn't work. But I've never used
pg_upgrade so I've never encountered that problem.

Also, if it's not mounted, the underlying directory
isn't empty but postgresql can tell something's not
right and it won't start if told to do so prematurely.

But the next time I upgrade, I might put the data
directory in a sub-directory of the mountpoint in case
I ever want to start using pg_upgrade. It would also
mean I only need a single ecryptfs mountpoint. At the
moment, I create a new one for each major upgrade which
seems silly.

cheers,
raf

Re: Update does not move row across foreign partitions in v11

[Alvaro Herrera]

On 2019-Feb-22, Derek Hans wrote:

> I've set up 2 instances of PostgreSQL 11. On instance A, I created a table
> with 2 local partitions and 2 partitions on instance B using foreign data
> wrappers, following https://pgdash.io/blog/postgres-11-sharding.html.
> Inserting rows into this table works as expected, with rows ending up in
> the appropriate partition. However, updating those rows only moves them
> across partitions in some of the situations:
> 
>- From local partition to local partition
>- From local partition to foreign partition
> 
> Rows are not moved
> 
>- From foreign partition to local partition
>- From foreign partition to foreign partition
> 
> Is this the expected behavior? Am I missing something or configured
> something incorrectly?

Sounds like a bug to me.

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

Re: replication topography

[Steve Crawford]

On Wed, Feb 27, 2019 at 12:59 PM Julie Nishimura 
wrote:

> Hello Steve,
> Thanks a lot for your info yesterday, it was very useful. If I run this
> command on some of the servers and the results look like this, what would
> it tell you?
>
> select * from pg_extension  ;
>extname| extowner | extnamespace | extrelocatable | extversion |
> extconfig | extcondition
>
> --+--+--+++---+--
>  plpgsql  |   10 |   11 | f  | 1.0
> |   |
>  postgres_fdw |   10 | 2200 | t  | 1.0
> |   |
>
>
> postgres=# select * from pg_extension  ;
>  extname | extowner | extnamespace | extrelocatable | extversion |
> extconfig | extcondition
>
> -+--+--+++---+--
>  plpgsql |   10 |   11 | f  | 1.0
> |   |
> (1 row)
>
> I understood it is slony, the rest I need to find from hba file?
>
>
>
I am not experienced with Slony - others on this list will be better able
to help there. Somehow I was thinking it created an extension but I don't
think that's the case. You may find some info in the monitoring section of
the slony docs (http://www.slony.info/documentation/1.2/monitoring.html),
in particular looking for the existence of sl_* tables/views.

The postgres_fdw is the Foreign Data Wrapper extension (
https://www.postgresql.org/docs/current/postgres-fdw.html) and plpgsql is
the PL/pgSQL procedural language (
https://www.postgresql.org/docs/current/plpgsql.html).

(Be sure to reply-all so others on the list can help and future viewers can
find solutions.)

Cheers,
Steve

Re: Optimizing Database High CPU

[Michael Lewis]

>
> If those 50-100 connections are all active at once, yes, that is high.
> They can easily spend more time fighting each other over LWLocks,
> spinlocks, or cachelines rather than doing useful work.  This can be
> exacerbated when you have multiple sockets rather than all cores in a
> single socket.  And these problems are likely to present as high Sys times.
>
> Perhaps you can put up a connection pooler which will allow 100
> connections to all think they are connected at once, but forces only 12 or
> so to actually be active at one time, making the others transparently queue.
>

Can you expound on this or refer me to someplace to read up on this?

Context, I don't want to thread jack though: I think I am seeing similar
behavior in our environment at times with queries that normally take
seconds taking 5+ minutes at times of high load. I see many queries showing
buffer_mapping as the LwLock type in snapshots but don't know if that may
be expected. In our environment PgBouncer will accept several hundred
connections and allow up to 100 at a time to be active on the database
which are VMs with ~16 CPUs allocated (some more, some less, multi-tenant
and manually sharded). It sounds like you are advocating for connection max
very close to the number of cores. I'd like to better understand the
pros/cons of that decision.

Re: automated refresh of dev from prod

[Ron]

On 2/27/19 3:15 PM, Julie Nishimura wrote:
Hello everybody, I am new to postgresql environment, but trying to get up 
to speed.
Can you please share your experience on how you can automate refreshment 
of dev environment on regular basis (desirably weekly), taking for 
consideration some of prod dbs can be very large (like 20+ TB


Any suggestions?


Weekly refreshes of 20TB from prod to dev seems a bit excessive.


--
Angular momentum makes the world go 'round.

RE: automated refresh of dev from prod

[Scot Kreienkamp]

My method is complex and not so good for newbies, but it is incredibly fast and 
should scale to almost any size database.  Mine are not nearly as large though.

I use two methods... the normal backup/restore for longer lived development 
environments, and for shorter lived environments I use postgres native 
mirroring from a secondary prod server to all my dev environments, then use LVM 
snapshots to take a snapshot of the postgres mount.  Mount the snapshot and 
startup a second postgres instance in it and you have a mirror of production 
ready for use.  That only lasts for a finite amount of time though (until you 
fill the space dedicated to the snapshot) before it becomes unusable,
that's the downside... it can't be long lived (hours, 1-2 days maybe).  The 
upside is that the refresh from production in my environment for a 400G 
database is 3 seconds.  It is a trade-off and not fit for every use, that's why 
we also use the traditional backup/restore in some cases.



Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | Mobile: 
7349151444 | Email: scot.kreienk...@la-z-boy.com
From: Julie Nishimura [mailto:juliez...@hotmail.com]
Sent: Wednesday, February 27, 2019 4:16 PM
To: pgsql-gene...@postgresql.org
Subject: automated refresh of dev from prod


ATTENTION:   This email was sent to La-Z-Boy from an external source. Be 
vigilant when opening attachments or clicking links.
Hello everybody, I am new to postgresql environment, but trying to get up to 
speed.
Can you please share your experience on how you can automate refreshment of dev 
environment on regular basis (desirably weekly), taking for consideration some 
of prod dbs can be very large (like 20+ TB

Any suggestions?

Thank you!

This message is intended only for the individual or entity to which it is 
addressed.  It may contain privileged, confidential information which is exempt 
from disclosure under applicable laws.  If you are not the intended recipient, 
you are strictly prohibited from disseminating or distributing this information 
(other than to the intended recipient) or copying this information.  If you 
have received this communication in error, please notify us immediately by 
e-mail or by telephone at the above number. Thank you.

automated refresh of dev from prod

[Julie Nishimura]

Hello everybody, I am new to postgresql environment, but trying to get up to 
speed.
Can you please share your experience on how you can automate refreshment of dev 
environment on regular basis (desirably weekly), taking for consideration some 
of prod dbs can be very large (like 20+ TB

Any suggestions?

Thank you!

Re: Optimizing Database High CPU

[Jeff Janes]

On Wed, Feb 27, 2019 at 2:07 PM Scottix  wrote:

> Hi we are running a Postgresql Database 9.4.18 and we are noticing a
> high CPU usage. Nothing is critical at the moment but if we were to
> scale up more of what we are doing, I feel we are going to run into
> issues.
>

9.4 is old. A lot of improvements have been made sense then, including
around contention issues.  Such as replacing some use of spinlocks with use
of atomic operations instead.

You would be better off putting time into upgrading, rather than putting
time into worrying about performance issues on something that will soon be
end-of-life anyway.


It is a 2 x 6 core machine, 128GB ram, Raid 10 HDD
>
> The iostat metrics for the HDD look minimal < 10% util
> Available memory seems to be good.
>
> The CPU utilization is what bothering me
> user  5-7%
> sys50-70% - seems high
> wa<0.5%
>
> So trying to troubleshoot possible high cpu:
> Number of concurrent connections averages 50 to 100 - seems high
> although we max at 200.
>

If those 50-100 connections are all active at once, yes, that is high.
They can easily spend more time fighting each other over LWLocks,
spinlocks, or cachelines rather than doing useful work.  This can be
exacerbated when you have multiple sockets rather than all cores in a
single socket.  And these problems are likely to present as high Sys times.

Perhaps you can put up a connection pooler which will allow 100 connections
to all think they are connected at once, but forces only 12 or so to
actually be active at one time, making the others transparently queue.



> No long running queries
> Streaming replication to backup server
> High update tables - we have about 4 tables that have a high volume of
> updates
>

Is it a few transactions updating a lot of rows each, or many transactions
updating a few rows each?


> High update rate is what I am thinking is causing the issue and I
> found possibly setting fillfactor to a lower default
>

I don't think that that is promising.  I wouldn't expect high Sys time if
this was the problem.  And with a high rate of updates (unless each update
is essentially to every row in the table), I would expect the table to
reach a steady state of tuple density.  Basically a too-high fillfactor
will fix itself naturally over time, it might be just take a while to do
it.  If your system has been running for a while, it has probably already
arrived at a steady state.  You can use the extension pg_freespacemap to so
how the freespace is spread around in your table blocks.


> Are there any statistics I could run to see if a setting change would help.
>

I'd probably start with pg_stat_activity table's "state" column to see how
many of your connections are active at once, and its columns
"wait_event_type" and "wait_event" to see what they think they are waiting
on (but those last columns aren't present until 9.6).

Cheers,

Jeff

Re: why not using a mountpoint as PGDATA?

[Tom Lane]

Ron  writes:
> On 2/27/19 12:43 PM, Joe Conway wrote:
>> FWIW, if you want to read the whole gory details of that incident, here
>> it is:
>> https://www.postgresql.org/message-id/flat/41D04FA4.7010402%40joeconway.com#dfc38927745e238d49569ffd5b33beba

> What in the world was that SuSE maintainer -- and the people who should have 
> been looking over his shoulder -- thinking???

Well, I wouldn't blame them *that* much.  Up till that time, I don't think
anyone fully understood the hazards of auto-initdb in a startup script
(certainly none of the PG developers did).  But this has been in our
folklore long enough now that anybody who's still doing that definitely
should get yelled at ...

regards, tom lane

Optimizing Database High CPU

[Scottix]

Hi we are running a Postgresql Database 9.4.18 and we are noticing a
high CPU usage. Nothing is critical at the moment but if we were to
scale up more of what we are doing, I feel we are going to run into
issues.

It is a 2 x 6 core machine, 128GB ram, Raid 10 HDD

The iostat metrics for the HDD look minimal < 10% util
Available memory seems to be good.

The CPU utilization is what bothering me
user  5-7%
sys50-70% - seems high
wa<0.5%

So trying to troubleshoot possible high cpu:
Number of concurrent connections averages 50 to 100 - seems high
although we max at 200.
No long running queries
Streaming replication to backup server
High update tables - we have about 4 tables that have a high volume of updates

High update rate is what I am thinking is causing the issue and I
found possibly setting fillfactor to a lower default which the
internet says you need to do a vacuum full which I am trying to avoid
but if it needs to be done we can schedule it. Just want to make sure
if I am chasing the correct rabbit hole.

Are there any statistics I could run to see if a setting change would help.

Best,
Scott

--
T: @Thaumion
IG: Thaumion
scot...@gmail.com

Re: why not using a mountpoint as PGDATA?

[Ron]

On 2/27/19 12:43 PM, Joe Conway wrote:

On 2/27/19 11:49 AM, Peter J. Holzer wrote:

On 2019-02-27 10:42:12 -0500, Tom Lane wrote:

Luca Ferrari  writes:

On Wed, Feb 27, 2019 at 12:33 PM Julien Rouhaud  wrote:

You can see most obvious reasons at
https://bugzilla.redhat.com/show_bug.cgi?id=1247477

[...]

The case that I can recall most clearly was actually in the other
direction: during system bootup, some NFS volume that was being abused
this way (mount point == data dir) was slow to mount.  Compounding the
problem, postgres was being started through some init script that would
helpfully run initdb if it saw the specified data directory was empty.
So, rather than failing like a properly paranoid DBA would wish, it
ran initdb and then started the postmaster.

Ouch.

I wonder though why that directory was writable by the postgres user.
But maybe the helpful start script chown'ed it to fix the "wrong"
permissions.

FWIW, if you want to read the whole gory details of that incident, here
it is:

https://www.postgresql.org/message-id/flat/41D04FA4.7010402%40joeconway.com#dfc38927745e238d49569ffd5b33beba


What in the world was that SuSE maintainer -- and the people who should have 
been looking over his shoulder -- thinking???



--
Angular momentum makes the world go 'round.

Re: Update does not move row across foreign partitions in v11

[Derek Hans]

Hi all,
This behavior makes the new data sharding functionality in v11 only
marginally useful as you can't shard across database instances.
Considering data sharding appeared to be one of the key improvements in
v11, I'm confused - am I misunderstanding the expected functionality?

Thanks!

On Fri, Feb 22, 2019 at 9:44 AM Derek Hans  wrote:

> I've set up 2 instances of PostgreSQL 11. On instance A, I created a table
> with 2 local partitions and 2 partitions on instance B using foreign data
> wrappers, following https://pgdash.io/blog/postgres-11-sharding.html.
> Inserting rows into this table works as expected, with rows ending up in
> the appropriate partition. However, updating those rows only moves them
> across partitions in some of the situations:
>
>- From local partition to local partition
>- From local partition to foreign partition
>
> Rows are not moved
>
>- From foreign partition to local partition
>- From foreign partition to foreign partition
>
> Is this the expected behavior? Am I missing something or configured
> something incorrectly?
>
> Thanks,
> Derek
>


-- 
*Derek*
+1 (415) 754-0519 | derek.h...@gmail.com | Skype: derek.hans

Re: why not using a mountpoint as PGDATA?

[Joe Conway]

On 2/27/19 11:49 AM, Peter J. Holzer wrote:
> On 2019-02-27 10:42:12 -0500, Tom Lane wrote:
>> Luca Ferrari  writes:
>> > On Wed, Feb 27, 2019 at 12:33 PM Julien Rouhaud  wrote:
>> >> You can see most obvious reasons at
>> >> https://bugzilla.redhat.com/show_bug.cgi?id=1247477
> [...]
>> The case that I can recall most clearly was actually in the other
>> direction: during system bootup, some NFS volume that was being abused
>> this way (mount point == data dir) was slow to mount.  Compounding the
>> problem, postgres was being started through some init script that would
>> helpfully run initdb if it saw the specified data directory was empty.
>> So, rather than failing like a properly paranoid DBA would wish, it
>> ran initdb and then started the postmaster.
> 
> Ouch.
> 
> I wonder though why that directory was writable by the postgres user.
> But maybe the helpful start script chown'ed it to fix the "wrong"
> permissions.

FWIW, if you want to read the whole gory details of that incident, here
it is:

https://www.postgresql.org/message-id/flat/41D04FA4.7010402%40joeconway.com#dfc38927745e238d49569ffd5b33beba

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

Re: cannot execute VACUUM during recovery

[Stephen Eilert]

Are you running Vacuum on the slave node? It has to run on the master.

Thanks,

– Stephen
On Feb 27, 2019, 6:43 AM -0800, github kran , wrote:
> Hello Team,
>
> We are using a PostgreSQL 9.6 and seeing the below error while trying to run 
> a VACUUM on one of our live tables running in Production. We wanted to clean 
> up some DEAD tuples on the table.
>
>
> Command: VACUUM (ANALYZE,VERBOSE) table_name.
>
> ERROR:  cannot execute VACUUM during recovery
>
> Thanks
> Kranthi

Re: Barman disaster recovery solution

[David Steele]

On 2/27/19 4:48 PM, Achilleas Mantzios wrote:

On 27/2/19 4:16 μ.μ., David Steele wrote:

On 2/27/19 2:31 PM, Achilleas Mantzios wrote:

On 27/2/19 1:58 μ.μ., rich...@simkorp.com.br wrote:
Just to notice, I d o use backup from standby and WAL archive from 
standby. It is possible. But you have to configure standby with 
option of wal archive "always".


I guess there are issues with it. If this was so easy then pgbarman 
and pgbackrest would support it out of the box.


There are a few issues with it:

1) If you allow the primary and standby to archive to the same 
repository then there needs to be some conflict resolution if they 
write at the same time.  If they write to different repositories then 
you need to decided which one to use for a restore, or have some kind 
of conflict resolution between them.  It gets complicated.


2) Writing only from the standby reduces load on the primary but if 
the connection to the primary is down then you can get behind on 
archiving. If something then happens to the primary then your recovery 
point will be limited.


David to quote an older email from you:
"pgBackRest currently requires some files and all WAL to be sent from 
the primary even when doing backup from standby.  We may improve this in 
the future but it's not on the road map right now. "
So, I had the impression that receiving WALs from the standby was a 
greater technical problem.


No, it just increases the risk of being behind on archiving.

One of the things pgBackRest does well is move a *lot* of WAL and it is 
orders of magnitude faster than streaming replication, which is 
single-threaded and uncompressed.  So, in spite of the additional load 
it's generally safest to archive from the primary, especially on high 
write volume clusters.


--
-David
da...@pgmasters.net

Re: Barman disaster recovery solution

[Mark Fletcher]

On Wed, Feb 27, 2019 at 1:39 AM Achilleas Mantzios <
ach...@matrix.gatewaynet.com> wrote:

>
> Hello, as promised here is my blog :
>
> https://severalnines.com/blog/current-state-open-source-backup-management-postgresql
>
>
Nice blog post. If you're aiming for a comprehensive run down of tools, I
suggest including wal-g. We've been using it since it was released (and its
predecessor wal-e for years before that) and it's been great. We currently
use it to back up a replica to S3, and it has no issues doing that. In more
recent versions, it supports delta backups, which decrease the time/load
required for a backup immensely in our case.

Cheers,
Mark

Re: idle_in_transaction_session_timeout for a set of SQL statements

[Jeremy Finzel]

>
> SET lock_timeout TO '1s';
>

No, my assumption is that lock_timeout does not cover my use case here.  My
point is actually that any one statement is not hitting lock_timeout, but
as a whole the transaction takes too long.  For example if I set
lock_timeout to 1 second, but my migration actually has 10 SQL statements
each of which take just under a second, I have a total now of a near
10-second lockout.

Regardless of whether or not I am technically going idle, I want to be able
to abort based on transaction time length.  I do believe I can handle this
externally via the timeout command, but curious still if there is any way
to reliably do it from within postgres.

Thanks,
Jeremy

Re: why not using a mountpoint as PGDATA?

[Peter J. Holzer]

On 2019-02-27 10:42:12 -0500, Tom Lane wrote:
> Luca Ferrari  writes:
> > On Wed, Feb 27, 2019 at 12:33 PM Julien Rouhaud  wrote:
> >> You can see most obvious reasons at
> >> https://bugzilla.redhat.com/show_bug.cgi?id=1247477
[...]
> The case that I can recall most clearly was actually in the other
> direction: during system bootup, some NFS volume that was being abused
> this way (mount point == data dir) was slow to mount.  Compounding the
> problem, postgres was being started through some init script that would
> helpfully run initdb if it saw the specified data directory was empty.
> So, rather than failing like a properly paranoid DBA would wish, it
> ran initdb and then started the postmaster.

Ouch.

I wonder though why that directory was writable by the postgres user.
But maybe the helpful start script chown'ed it to fix the "wrong"
permissions.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: idle_in_transaction_session_timeout for a set of SQL statements

[Michael Lewis]

On Wed, Feb 27, 2019 at 7:56 AM Jeremy Finzel  wrote:

> I was hoping to use idle_in_transaction_session_timeout to prevent schema
> change migrations from running too long and thereby locking up the
> application for an extended period even if any one statement in the
> migration is very short.
>


I don't think you are actually going idle. Check pg_stat_activity for what
this transaction is doing. It should show waiting on locks, but not idle. I
think the command you seek is

SET lock_timeout TO '1s';

Re: 9.0 standby - could not open file global/XXXXX

[Filip Rembiałkowski]

OK I have it fixed;; just for anyone who's interested - the error was in
the base backup procedure.
When switched to plain "rsync -az" - it  works like a charm.

Most probably, the fault was I assumed that you can use the rsync --update
option when doing base backup.
You cannot, especially when time sync on both servers is not accurate. In
my case, destination server clock was few minutes in future.
So the pg_clog was broken due to this. Which means a completely corrupted
database.

thanks Stephen & Andres for your responses.



On Mon, Feb 25, 2019 at 8:06 PM Filip Rembiałkowski <
filip.rembialkow...@gmail.com> wrote:

> Hi.
>
> There is a large (>5T) database on PostgreSQL 9.0.23.
>
> I would like to setup new WAL-shipping standby.
> https://www.postgresql.org/docs/9.0/warm-standby.html
>
> On my way I find unexpected issues. Here's the story, in short:
>
> 1. WAL archiving to remote archive is setup & verified
>
> 2. base backup is transferred directly to new server using
> pg_start_backup + rsync + pg_stop_backup.
>
> 3. recovery.conf is created
>
> 4. Server is started and consumes all the remaining WAL segments
> accumulated in the archive - finishing with optimistic message LOG:
> consistent recovery state reached at 9FC1/112BEE10.
>
> 5. When I go to postgres on the standby and try to connect system
> "postgres" database psql: FATAL:  could not open file "global/11819":
> No such file or directory
>
> I guessed the OID refereds to the pg_authid, but other system tables
> might be affected too.
>
> What could be wrong here?
>
> Thanks!
>

Re: Why can I not get lexemes for Hebrew but can get them for Armenian?

[Tom Lane]

Sam Saffron  writes:
> So something is clearly different about the way the tokenisation is
> defined in PG. My question is, how do I figure out what is different
> and how do I make my mac install of PG work like the Linux one?

I'm not sure you can :-(.  This devolves to what the libc locale
functions (isalpha(3) and friends) do, and unfortunately the UTF8
locales on OS X are impossibly lame.  They tend not to provide
useful character classifications for high Unicode code points.
They don't sort very well either, though that's not your problem here.

Depending on what characters you actually need to work with,
you might have better luck using one of the ISO8859 character set
locales.  Though if you actually need both Hebrew and Armenian
in the same DB, that suggestion is a nonstarter.

regards, tom lane

Re: why not using a mountpoint as PGDATA?

[Tom Lane]

Luca Ferrari  writes:
> On Wed, Feb 27, 2019 at 12:33 PM Julien Rouhaud  wrote:
>> You can see most obvious reasons at
>> https://bugzilla.redhat.com/show_bug.cgi?id=1247477

> Thanks, I didn't see the lost+found problem because I'm on UFS (I'm
> wondering if this applies also to ZFS datasetes with mount point).
> Same story for the accidentally umount, since FreeBSD seems enough
> friendly to avoid umount while the database is running, but it could
> definetely happen with some brute force or on another operating
> system.

The case that I can recall most clearly was actually in the other
direction: during system bootup, some NFS volume that was being abused
this way (mount point == data dir) was slow to mount.  Compounding the
problem, postgres was being started through some init script that would
helpfully run initdb if it saw the specified data directory was empty.
So, rather than failing like a properly paranoid DBA would wish, it
ran initdb and then started the postmaster.  A bit later, the NFS volume
came online, replacing the mount-point directory, and now the postmaster
had a devil's brew of open files on the local volume containing the mount
point and open files on the NFS volume.  It didn't take long for that to
translate into hopeless catalog corruption.  I don't think they ever got
their data back.

The true value of data-dir-at-least-one-level-down is so that it will
fail to be there at all if the file system isn't mounted.  You need that,
and you do *not* want automated attempts to "repair" that.

regards, tom lane

Re: Barman disaster recovery solution

[Edson Carlos Ericksson Richter]

Em 27/02/2019 12:12, Achilleas Mantzios escreveu:

On 27/2/19 5:04 μ.μ., Edson Carlos Ericksson Richter wrote:


Em 27/02/2019 09:31, Achilleas Mantzios escreveu:

On 27/2/19 1:58 μ.μ., rich...@simkorp.com.br wrote:
Just to notice, I d o use backup from standby and WAL archive from 
standby. It is possible. But you have to configure standby with 
option of wal archive "always".


I guess there are issues with it. If this was so easy then pgbarman 
and pgbackrest would support it out of the box.




Well,


This setup it is working really well for past two years; prior to 
that, we used backup from primary server.

Using which tool?



Barman.




We have about 50 databases, half terabyte in total, primary and 
standby separated geographically.


We had to write special app to monitor if standby is behind primary 
(it compares the transaction id between primary and standby).


For eight years, we had no single failure from PgSQL databases (using 
since 9.0 and today on 9.6), replication is for "just in case" data 
center unavailability, and backup is for disaster recovery (in case 
two data centers in different states from different vendors get out 
of work at same time).



But we give no chance to bad luck: we monitor replication status 
every 2 minutes, we make full backup every 2 days with incremental 
backup in between, and test all backups on a recover server every 
day. As pointed, we have no single database failure that required to 
use the replication server or the backup server, but we will not 
lower the attention.

Which means you tested your backups?



Recover and run our main app on it.


Regards,


Edson





Regards,


Edson






Just my 2c,

Edson Richter

/Enviado do meu Telefone LG/

-- Mensagem original--
*De: *Achilleas Mantzios
*Data: *qua, 27 de fev de 2019 06:40
*Para: *pgsql-general@lists.postgresql.org 
;

*Cc:*
*As! sunto:*Re: Barman disaster recovery solution

On 21/2/19 9:28 π.μ., Achilleas Mantzios wrote:

On 21/2/19 9:17 π.μ., Julie Nishimura wrote:

Does anyone use this solution? any recommenations?

Thanks!


Barman will fit most requirements. PgBackRest excels when WAL 
traffic goes on 10 files/day or more. I have written an 
article, not yet publised, on a comparison on the 3 most known 
solutions. Will post a link as soon as it gets published.


Hello, as promised here is my blog :
https://severalnines.com/blog/current-state-open-source-backup-management-postgresql 





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



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



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

Re: Barman disaster recovery solution

[Achilleas Mantzios]

On 27/2/19 5:04 μ.μ., Edson Carlos Ericksson Richter wrote:


Em 27/02/2019 09:31, Achilleas Mantzios escreveu:

On 27/2/19 1:58 μ.μ., rich...@simkorp.com.br wrote:

Just to notice, I d o use backup from standby and WAL archive from standby. It is 
possible. But you have to configure standby with option of wal archive "always".


I guess there are issues with it. If this was so easy then pgbarman and 
pgbackrest would support it out of the box.




Well,


This setup it is working really well for past two years; prior to that, we used 
backup from primary server.

Using which tool?


We have about 50 databases, half terabyte in total, primary and standby 
separated geographically.

We had to write special app to monitor if standby is behind primary (it 
compares the transaction id between primary and standby).

For eight years, we had no single failure from PgSQL databases (using since 9.0 and today on 9.6), replication is for "just in case" data center unavailability, and backup is for disaster recovery 
(in case two data centers in different states from different vendors get out of work at same time).



But we give no chance to bad luck: we monitor replication status every 2 minutes, we make full backup every 2 days with incremental backup in between, and test all backups on a recover server every 
day. As pointed, we have no single database failure that required to use the replication server or the backup server, but we will not lower the attention.

Which means you tested your backups?



Regards,


Edson






Just my 2c,

Edson Richter

/Enviado do meu Telefone LG/

-- Mensagem original--
*De: *Achilleas Mantzios
*Data: *qua, 27 de fev de 2019 06:40
*Para: *pgsql-general@lists.postgresql.org 
;
*Cc:*
*As! sunto:*Re: Barman disaster recovery solution

On 21/2/19 9:28 π.μ., Achilleas Mantzios wrote:

On 21/2/19 9:17 π.μ., Julie Nishimura wrote:

Does anyone use this solution? any recommenations?

Thanks!


Barman will fit most requirements. PgBackRest excels when WAL traffic goes on 10 files/day or more. I have written an article, not yet publised, on a comparison on the 3 most known solutions. 
Will post a link as soon as it gets published.


Hello, as promised here is my blog :
https://severalnines.com/blog/current-state-open-source-backup-management-postgresql



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



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



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





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

Re: Barman disaster recovery solution

[Edson Carlos Ericksson Richter]

Em 27/02/2019 09:31, Achilleas Mantzios escreveu:

On 27/2/19 1:58 μ.μ., rich...@simkorp.com.br wrote:
Just to notice, I d o use backup from standby and WAL archive from 
standby. It is possible. But you have to configure standby with 
option of wal archive "always".


I guess there are issues with it. If this was so easy then pgbarman 
and pgbackrest would support it out of the box.




Well,


This setup it is working really well for past two years; prior to that, 
we used backup from primary server.


We have about 50 databases, half terabyte in total, primary and standby 
separated geographically.


We had to write special app to monitor if standby is behind primary (it 
compares the transaction id between primary and standby).


For eight years, we had no single failure from PgSQL databases (using 
since 9.0 and today on 9.6), replication is for "just in case" data 
center unavailability, and backup is for disaster recovery (in case two 
data centers in different states from different vendors get out of work 
at same time).



But we give no chance to bad luck: we monitor replication status every 2 
minutes, we make full backup every 2 days with incremental backup in 
between, and test all backups on a recover server every day. As pointed, 
we have no single database failure that required to use the replication 
server or the backup server, but we will not lower the attention.



Regards,


Edson






Just my 2c,

Edson Richter

/Enviado do meu Telefone LG/

-- Mensagem original--
*De: *Achilleas Mantzios
*Data: *qua, 27 de fev de 2019 06:40
*Para: *pgsql-general@lists.postgresql.org 
;

*Cc:*
*As! sunto:*Re: Barman disaster recovery solution

On 21/2/19 9:28 π.μ., Achilleas Mantzios wrote:

On 21/2/19 9:17 π.μ., Julie Nishimura wrote:

Does anyone use this solution? any recommenations?

Thanks!


Barman will fit most requirements. PgBackRest excels when WAL 
traffic goes on 10 files/day or more. I have written an article, 
not yet publised, on a comparison on the 3 most known solutions. 
Will post a link as soon as it gets published.


Hello, as promised here is my blog :
https://severalnines.com/blog/current-state-open-source-backup-management-postgresql



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



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



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

Re: why not using a mountpoint as PGDATA?

[Peter J. Holzer]

On 2019-02-27 12:33:02 +0100, Julien Rouhaud wrote:
> On Wed, Feb 27, 2019 at 12:22 PM Luca Ferrari  wrote:
> >
> > What's wrong with using a mountpoint?
> 
> You can see most obvious reasons at
> https://bugzilla.redhat.com/show_bug.cgi?id=1247477

I see only one good reason there: The fact that pg_upgrade needs write
access to the parent directory. Of course that alone might suffice.

The other reasons aren't good IMHO.

The first one (initdb checks for an empty directory) is more "We
disallow it, therefore it is a bad idea" than a reason for disallowing
it.

The second is just wrong: You can have a non-root owned mount-point on
any Unixoid system I've worked with. (And I don't see why that would be
a security problem)

The third is wrong at least on Debian: All server processes have
/var/lib/postgresql/$version/$cluster as their working directory, so it
cannot be unmounted while the database is up. Even if you could, the
server would either immediately lose access to all files (in which case
you could recover) or it would keep access to all files (so, not a
problem). Plus being in a subdirectory wouldn't change that. Maybe it's
a potential problem with other layouts.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

idle_in_transaction_session_timeout for a set of SQL statements

[Jeremy Finzel]

I was hoping to use idle_in_transaction_session_timeout to prevent schema
change migrations from running too long and thereby locking up the
application for an extended period even if any one statement in the
migration is very short.

I am not finding predictable behavior
using idle_in_transaction_session_timeout.  If I create a simple SQL file
with two ALTER TABLE statements, and intentionally block the first
statement long enough to exceed idle_in_transaction_session_timeout,
usually once I unblock the first statement, the migration succeeds.  I want
it to actually be killed once it has exceeded
idle_in_transaction_session_timeout and finished executing one SQL
statement and is about to move to another.

One of my tries, it actually did somehow exceed it and terminate, with the
exact same test:
$ psql test -f foo
SET
BEGIN
ALTER TABLE
ALTER TABLE
psql:foo:11: FATAL:  terminating connection due to idle-in-transaction
timeout
psql:foo:12: SSL connection has been closed unexpectedly
psql:foo:12: connection to server was lost

However, I only got that to happen once  usually it just executes fine
which I don't want.

Session 1:
SET idle_in_transaction_session_timeout = 1;
BEGIN;
ALTER TABLE foo ADD COLUMN bar text; -- block this for >
idle_in_transaction_session_timeout

-- I was hoping it would timeout here

ALTER TABLE bar ADD COLUMN foo text;
COMMIT;

Session 2:
BEGIN;
SELECT * FROM foo;
. wait then abort


Granted this example is contrived, but the goal is again to avoid allowing
a migration with many individual statements from taking longer than say 5
seconds to execute, locking up the application.

Is there any way to timeout a long transaction or any clarity around how
idle_in_transaction_session_timeout works when executing a file with
multiple SQL statements?

Thanks,
Jeremy

Re: Barman disaster recovery solution

[Achilleas Mantzios]

On 27/2/19 4:16 μ.μ., David Steele wrote:

On 2/27/19 2:31 PM, Achilleas Mantzios wrote:

On 27/2/19 1:58 μ.μ., rich...@simkorp.com.br wrote:

Just to notice, I d o use backup from standby and WAL archive from standby. It is 
possible. But you have to configure standby with option of wal archive "always".


I guess there are issues with it. If this was so easy then pgbarman and 
pgbackrest would support it out of the box.


There are a few issues with it:

1) If you allow the primary and standby to archive to the same repository then there needs to be some conflict resolution if they write at the same time.  If they write to different repositories 
then you need to decided which one to use for a restore, or have some kind of conflict resolution between them.  It gets complicated.


2) Writing only from the standby reduces load on the primary but if the connection to the primary is down then you can get behind on archiving. If something then happens to the primary then your 
recovery point will be limited.


David to quote an older email from you:
"pgBackRest currently requires some files and all WAL to be sent from the primary 
even when doing backup from standby.  We may improve this in the future but it's not on 
the road map right now. "
So, I had the impression that receiving WALs from the standby was a greater 
technical problem.



Regards,



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

cannot execute VACUUM during recovery

[github kran]

Hello Team,

We are using a PostgreSQL 9.6 and seeing the below error while trying to
run a VACUUM on one of our live tables running in Production. We wanted to
clean up some DEAD tuples on the table.


*Command*: VACUUM (ANALYZE,VERBOSE) table_name.

ERROR:  cannot execute VACUUM during recovery

Thanks
Kranthi

Re: Barman disaster recovery solution

[David Steele]

On 2/27/19 2:31 PM, Achilleas Mantzios wrote:

On 27/2/19 1:58 μ.μ., rich...@simkorp.com.br wrote:
Just to notice, I d o use backup from standby and WAL archive from 
standby. It is possible. But you have to configure standby with option 
of wal archive "always".


I guess there are issues with it. If this was so easy then pgbarman and 
pgbackrest would support it out of the box.


There are a few issues with it:

1) If you allow the primary and standby to archive to the same 
repository then there needs to be some conflict resolution if they write 
at the same time.  If they write to different repositories then you need 
to decided which one to use for a restore, or have some kind of conflict 
resolution between them.  It gets complicated.


2) Writing only from the standby reduces load on the primary but if the 
connection to the primary is down then you can get behind on archiving. 
If something then happens to the primary then your recovery point will 
be limited.


Regards,
--
-David
da...@pgmasters.net

Re: Barman disaster recovery solution

[Achilleas Mantzios]

On 27/2/19 1:58 μ.μ., rich...@simkorp.com.br wrote:

Just to notice, I d o use backup from standby and WAL archive from standby. It is 
possible. But you have to configure standby with option of wal archive "always".


I guess there are issues with it. If this was so easy then pgbarman and 
pgbackrest would support it out of the box.



Just my 2c,

Edson Richter

/Enviado do meu Telefone LG/

-- Mensagem original--
*De: *Achilleas Mantzios
*Data: *qua, 27 de fev de 2019 06:40
*Para: *pgsql-general@lists.postgresql.org 
;
*Cc:*
*As! sunto:*Re: Barman disaster recovery solution

On 21/2/19 9:28 π.μ., Achilleas Mantzios wrote:

On 21/2/19 9:17 π.μ., Julie Nishimura wrote:

Does anyone use this solution? any recommenations?

Thanks!


Barman will fit most requirements. PgBackRest excels when WAL traffic goes on 10 files/day or more. I have written an article, not yet publised, on a comparison on the 3 most known solutions. 
Will post a link as soon as it gets published.


Hello, as promised here is my blog :
https://severalnines.com/blog/current-state-open-source-backup-management-postgresql



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



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



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

Re: why not using a mountpoint as PGDATA?

[Luca Ferrari]

On Wed, Feb 27, 2019 at 12:33 PM Julien Rouhaud  wrote:
> You can see most obvious reasons at
> https://bugzilla.redhat.com/show_bug.cgi?id=1247477

Thanks, I didn't see the lost+found problem because I'm on UFS (I'm
wondering if this applies also to ZFS datasetes with mount point).
Same story for the accidentally umount, since FreeBSD seems enough
friendly to avoid umount while the database is running, but it could
definetely happen with some brute force or on another operating
system.
I see the upgrade problem being a lot more general in this sense.
However, all are good points.

Luca

Re: why not using a mountpoint as PGDATA?

[Julien Rouhaud]

On Wed, Feb 27, 2019 at 12:22 PM Luca Ferrari  wrote:
>
> What's wrong with using a mountpoint?

You can see most obvious reasons at
https://bugzilla.redhat.com/show_bug.cgi?id=1247477

why not using a mountpoint as PGDATA?

[Luca Ferrari]

I apparently cannot find an answer in the documentation, however initdb states:

initdb: directory "/mnt/data1" exists but is not empty
It contains a dot-prefixed/invisible file, perhaps due to it being a
mount point.
Using a mount point directly as the data directory is not recommended.
Create a subdirectory under the mount point.

and effectively /mnt/data1 is a mountpoint, therefore but belongs to
the user of the cluster, therefore the only difference I can see is
having '..' belonging to root at the mountpoint or the cluster user
within a subdirectory.
What's wrong with using a mountpoint?

Thanks,
Luca

Why can I not get lexemes for Hebrew but can get them for Armenian?

[Sam Saffron]

(This is a cross post from Stack Exchange, not getting much traction there)

On my Mac install of PG:

```
=# select to_tsvector('english', 'abcd สวัสดี');
 to_tsvector
-
 'abcd':1
(1 row)

=# select * from ts_debug('hello สวัสดี');
   alias   |   description   | token |  dictionaries  |  dictionary  | lexemes
---+-+---++--+-
 asciiword | Word, all ASCII | hello | {english_stem} | english_stem | {hello}
 blank | Space symbols   |  สวัสดี | {} |  |
(2 rows)
```

On my Linux install of PG:

```
=# select to_tsvector('english', 'abcd สวัสดี');
to_tsvector
---
 'abcd':1 'สวัสดี':2
(1 row)

=# select * from ts_debug('hello สวัสดี');
   alias   |description| token |  dictionaries  |  dictionary  | lexemes
---+---+---++--+-
 asciiword | Word, all ASCII   | hello | {english_stem} | english_stem | {hello}
 blank | Space symbols |   | {} |  |
 word  | Word, all letters | สวัสดี  | {english_stem} |
english_stem | {สวัสดี}
(3 rows)

```

So something is clearly different about the way the tokenisation is
defined in PG. My question is, how do I figure out what is different
and how do I make my mac install of PG work like the Linux one?

On both installs:

```
# SHOW default_text_search_config;
 default_text_search_config

 pg_catalog.english
(1 row)

# show lc_ctype;
  lc_ctype
-
 en_US.UTF-8
(1 row)
```

So somehow this mac install thinks that thai letters are spaces... how
do I debug this and fix the "Space Symbol" definition here.

Interestingly this install works with Armenian, but falls over when we
reach Hebrew

```
=# select * from ts_debug('ԵԵԵ');
 alias |description| token |  dictionaries  |  dictionary  | lexemes
---+---+---++--+-
 word  | Word, all letters | ԵԵԵ   | {english_stem} | english_stem | {եեե}
(1 row)

=# select * from ts_debug('אאא');
 alias |  description  | token | dictionaries | dictionary | lexemes
---+---+---+--++-
 blank | Space symbols | אאא   | {}   ||
(1 row)
```

Re: Barman disaster recovery solution

[Achilleas Mantzios]

On 21/2/19 9:28 π.μ., Achilleas Mantzios wrote:

On 21/2/19 9:17 π.μ., Julie Nishimura wrote:

Does anyone use this solution? any recommenations?

Thanks!


Barman will fit most requirements. PgBackRest excels when WAL traffic goes on 10 files/day or more. I have written an article, not yet publised, on a comparison on the 3 most known solutions. 
Will post a link as soon as it gets published.


Hello, as promised here is my blog :
https://severalnines.com/blog/current-state-open-source-backup-management-postgresql



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



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

Re: Channel binding not supported using scram-sha-256 passwords

[Peter Eisentraut]

On 2019-02-26 23:35, Michael Paquier wrote:
> What I do in such cases is to compile OpenSSL by myself and link
> Postgres to it, here is a command to build shared libraries (all that
> is documented in INSTALL):
> ./config --prefix=$INSTALLPATH shared

I did test it now using a custom-built OpenSSL, and I can confirm it works.

> Another trick would be to comment out the sections in libpq where
> HAVE_PGTLS_GET_PEER_CERTIFICATE_HASH is used to emulate a compilation
> with OpenSSL 1.0.1 features and older, while still linking with
> 1.0.2.

Yeah, that might have been easier. ;-)

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services