Re: [GENERAL] looking for a globally unique row ID

[Jehan-Guillaume de Rorthais]

Le 14 septembre 2017 19:11:19 GMT+02:00, Rafal Pietrak  a 
écrit :
>
>
>W dniu 14.09.2017 o 15:54, Merlin Moncure pisze:
>> On Thu, Sep 14, 2017 at 2:45 AM, Rafal Pietrak 
>wrote:
>>> Hello everybody,
>>>
>>> Can anybody help me find a way to implement an ID which:
>>>
>>> 1. guarantees being unique across multiple tables.
>>>
>>> 2. guarantees its uniqueness not only during INSERT, but also during
>the
>>> lifetime of the database/application (e.i. during future UPDATES).
>>>
>>> 3. guarantees persistence of value across database
>backup/restore/upgrade.
>>>
>>> an obvious candidate - a single SERIAL() (same serial) used in every
>>> table that needs that ID does not guarantee (2).
>> 
>> A shared sequence meets all of those requirements.  I tend to prefer
>
>Not really.
>
>As I said, I'm not looking for performance or "fair probability" of
>planetary-wide uniqueness.
>
>My main objective is the "guarantee". Which I've tried to indicate
>referring to "future UPDATEs".
>
>What I mean here is functionality similar to "primary key", or "unique
>constraint". Whenever somebody (application, like faulty application
>IMPORTANT!) tries to INSERT or UPDATE a not unique value there (which
>in
>fact could possibly be generated earlier by UUID algorithms, or even a
>sequence), if that value is among table that uses that (misterious)
>"global primary key"; that application just fails the transaction like
>any other "not unique" constraint failing.

I wrote something about this some years ago, this might do the trick for you, 
maybe with some adjustments depending on your schema. The main idea should help 
anyway. See:

http://blog.ioguix.net/postgresql/2015/02/05/Partitionning-and-constraints-part-1.html

(no, I never wrote the second part about fk :-/) 



-- 
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] Postgres HA

[Jehan-Guillaume de Rorthais]

On Wed, 22 Feb 2017 22:58:10 +
Dylan Luong  wrote:

> Hi
> 
> I am a DBA at the University of South Australia. For PostgreSQL High
> Availability, we currently have setup a Master/Slave across two datacenters
> using PostgreSQL (WAL) streaming replication. We use an LTM (load balancer)
> server that sits between the application servers and the PostgreSQL server
> that directs connections to the Master (and the Slave if failover occurs). We
> also have watchdog processes on the PostgreSQL servers that polls the LTM to
> determine who is Master and perform automatic failover if required.

And how do you deal with split brain ? Fencing? Network partition? What if the
network fail on the master side for 5 minutes? Will the LTM go back to the old
master as soon as the watchdog pool it again?

> I am looking at options to improve our high availability. I would like to
> know how other organizations in different industries (other than education)
> setup High Availability on their PostgreSQL environments. What  tools do you
> use. Are they commercial licensed products? How is the architecture setup and
> how do you do recovery of new slave. Your information is greatly appreciated.

We use Pacemaker with the PAF[1] resource agent. Pacemaker takes great care to
avoid split brain using fencing. It mostly supports local cluster, but it
supports multi-site clusters as well thanks to a layer called "Cluster Ticket
Registry"[2].

HA is a complex subject, it requires some time to get familiar with it. Good
luck :)

[1] http://dalibo.github.io/PAF/
[2] 
http://clusterlabs.org/doc/en-US/Pacemaker/1.1/html/Pacemaker_Explained/ch15.html


-- 
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] clarification about standby promotion

[Jehan-Guillaume de Rorthais]

On Thu, 9 Feb 2017 18:27:30 +
Rakesh Kumar  wrote:

> >Sure, but when you are doing a switchover, the standby is supposed to be
> >connected to the master when you shutdown the master. So based on the doc,
> >the standby should receive **everything** from the master before the master
> >actually shutdown.  
> 
> We use 9.5 and even in that version there is no handshake during role
> reversal. In fact PG does not have concept of handshake and role reversal
> unlike in Db2, oracle and sqlserver you can switchover from one to other by a
> single command.
> 
> Our DBAs use home grown script for switchover which does the following:
> 
> 1 - first kill postmaster in the outgoing primary.

Kill ? You mean "pg_ctl stop -m fast" right ?

> 2 - promote the standby as the new primary
> 3 - use timeline to resync former primary (of step 1) with the new primary
> (step 2). 

Use timeline to resync ? Timeline is an internal mechanism in PostgreSQL, not
a tool, so I don't get this step...You mean using pg_rewind ?

So far, I stick to my procedure (given in another answer) which looks a lot
more safer.

> I hope a more elegant way exists as in other RDBMS.

Me too. But it require a lot of work as a master is not able to "demote" as a
standby without a restart. As far as I know, the standby code path is only
accessible during startup.

Note that you could switchover in one command as well using external tools like
PAF [1][2]. But PAF comes with a lot more features than just switchover and
rely on Pacemaker...

[1] https://github.com/dalibo/PAF
[2] http://www.dalibo.org/_media/2016-pgconfeu-paf.html.gz

Cheers,


-- 
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] clarification about standby promotion

[Jehan-Guillaume de Rorthais]

On Thu, 9 Feb 2017 10:41:15 +1100
Venkata B Nagothi <nag1...@gmail.com> wrote:

> On Thu, Feb 9, 2017 at 4:53 AM, Benoit Lobréau <benoit.lobr...@gmail.com>
> wrote:
> 
> > Hi,
> >
> >
> > I would like to clarify something about standby promotion. From the
> > sentence below. I understand that, during the promotion process, postgres
> > will replay all the available wals (from the archive or pg_xlog).
> >  
> 
> Yes, that is correct.
> 
> 
> > https://www.postgresql.org/docs/9.5/static/warm-standby.
> > html#STREAMING-REPLICATION
> >
> > 25.2.2. Standby Server Operation
> > ...
> > Standby mode is exited and the server switches to normal operation when
> > pg_ctl promote is run or a trigger file is found (trigger_file). Before
> > failover, any WAL immediately available in the archive or in pg_xlog will
> > be restored, but no attempt is made to connect to the master.
> >
> > I have seen several articles like this one (https://www.enterprisedb.com/
> > switchoverswitchback-postgresql-93) where they say that
> > pg_last_xlog_receive_location() and pg_last_xlog_replay_location() should
> > be checked before promotion. I don't understand why since they will be
> > replayed anyway. Did something changed since 9.3 about this ?
> >  
> 
> The above link is about improvements related to switch-over/switch-back
> process from the version 9.3. What you are asking is about standby
> promotion process. When the standby is promoted, as mentioned in the docs,
> the standby server attempts to apply the available WAL during the promotion
> process and will not attempt to connect to master. 


Sure, but when you are doing a switchover, the standby is supposed to be
connected to the master when you shutdown the master. So based on the doc,
the standby should receive **everything** from the master before the master
actually shutdown.


> Which means, you would not know if there are any pending WALs yet to be
> streamed from master or in other words, standby may not know if the master is
> a-head. It is important that you know the standby current position by
> executing the above mentioned *xlog* functions.

Sure, but in the link pointed by Benoit, the check is only comparing what the
**standby** received with what the standby **replayed**. It seems there's no
point to do such check.

What you are describing is to check the very last LSN from the master (its
shutdown checkpoint) with what the slave received. The only way to check this
is to compare LSN from the shut down master to the LSN the slave received.

in PAF[1], this check is:

  * shut down the master
  * use pg_controldata to find its shutdown checkpoint
  * force a checkpoint on the standby (which in theory received everything
from the master by streaming)
  * using pg_dumpxlog, check you received the shutdown checkpoint


[1] https://github.com/dalibo/PAF

-- 
Jehan-Guillaume de Rorthais
Dalibo


-- 
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] pgbouncer increase pool_size, reload does not work

[Jehan-Guillaume de Rorthais]

On Thu, 2 Feb 2017 16:12:06 -0800
Adrian Klaver  wrote:

> On 02/02/2017 03:27 AM, alexanderfelipewo wrote:
> > hello!
> >
> > i have a question for pgbouncer in case someone has faced this and there is
> > a solution available.
> >
> > i have a db with pgbouncer where the pool_size is set to 50 (i ve noticed
> > this using both session/transaction modes). Sometimes there are waiting
> > sessions b/c more than 50 want to execute sth at once and usually each query
> > is not so fast. So let's say i want to increase the pool_size from 50 to 70.
> > I change the config file and then login to pgbouncer where i run a
> > 'reload;'. Now when i check the 'show config' i see the new value in the
> > 'default_pool_size' parameter.
> >
> > BUT
> >
> > the problem is that the amount of servers is still 50 and does not go up to
> > 70. At the same time there are clients in a 'waiting' state. If i do a
> > restart then it will work and the servers will be able to go up to 70.
> >
> > I ve tried these in different databases and it doesnt happen all the time
> > but it does happen pretty often.
> > Has anyone else seen this? if so, is there a solution (except restarting) or
> > explanation?  
> 
> Would this work?:
> 
> https://pgbouncer.github.io/faq.html#how-to-upgrade-pgbouncer-without-dropping-connections

Maybe, but I couldn't give it a try as my pgbouncer is managed by Systemd.



-- 
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] pgbouncer increase pool_size, reload does not work

[Jehan-Guillaume de Rorthais]

On Thu, 2 Feb 2017 04:27:45 -0700 (MST)
alexanderfelipewo  wrote:

> hello!
> 
> i have a question for pgbouncer in case someone has faced this and there is
> a solution available.
> 
> i have a db with pgbouncer where the pool_size is set to 50 (i ve noticed
> this using both session/transaction modes). Sometimes there are waiting
> sessions b/c more than 50 want to execute sth at once and usually each query
> is not so fast. So let's say i want to increase the pool_size from 50 to 70.
> I change the config file and then login to pgbouncer where i run a
> 'reload;'. Now when i check the 'show config' i see the new value in the
> 'default_pool_size' parameter. 
> 
> BUT
> 
> the problem is that the amount of servers is still 50 and does not go up to
> 70. At the same time there are clients in a 'waiting' state. If i do a
> restart then it will work and the servers will be able to go up to 70.
> 
> I ve tried these in different databases and it doesnt happen all the time
> but it does happen pretty often. 
> Has anyone else seen this? if so, is there a solution (except restarting) or
> explanation?

That's because internally, it seems each pool (pair of role/db) is set up when
it is created, eg. during the very first connexion.

Even if the default pool_size can be changed with a simple reload,
you will have to wait for your pool to be fully deallocated before it
can be recreated with the new pool_size value.

I didn't dig too much in the code, maybe a core hacker of pgbouncer can provide
some better details and tips about this.


-- 
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] [GENERAL] Replication between différent versions of the same OS.

[Jehan-Guillaume de Rorthais]

On Thu, 1 Dec 2016 20:11:06 +0100
Benoit Lobréau  wrote:

> Hi,
> 
> Is it possible to use the built in replication to replicate between two
> PostgreSQL in the same version but in different version of the same OS (Say
> Pg 9.1 Ubuntu 12 to Pg 9.1 Ubuntu 14)
> 
> I think I read in Hackers that since PostgreSQL uses the OS libraries for
> encoding. It could cause silent corruption because the encoding might be
> different between versions of the OS. But I cant find the email again so I
> can't find the exact context ... maybe I dreamed it ..

No, you did not. See:
https://www.postgresql.org/message-id/flat/BA6132ED-1F6B-4A0B-AC22-81278F5AB81E%40tripadvisor.com#ba6132ed-1f6b-4a0b-ac22-81278f5ab...@tripadvisor.com

> We would like to replicate to the other server then upgrade to a newer
> version. (the import is too long)

Use logical replication. You could use Slony for example.

Regards,


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

[Jehan-Guillaume de Rorthais]

On Wed, 30 Nov 2016 08:58:13 -0800
"Joshua D. Drake"  wrote:

> On 11/30/2016 06:00 AM, Marcin Giedz wrote:
> > Hello,
> >
> > Does anyone know any reliable open source HA solution with stream
> > replication and IP live migration in case of master node failure?
> > Recently I've been attending Fujitsu PostgreSQL webinar and they
> > presented so called "mirroring controler" + sync stream replication .
> > I'm wondering if this combines of regular stream replication + some kind
> > of keepalive mechanism ?  
> 
> LinuxHA/Pacemaker/Corosync can do this.

Just for the record, PAF is a new resource agent for such stack. It just
replaces the default "pgsql" RA delivered in the resource-agents package on
various distro.

Regards,


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

[Jehan-Guillaume de Rorthais]

On Wed, 30 Nov 2016 15:00:16 +0100 (CET)
Marcin Giedz  wrote:

> Hello, 
> 
> Does anyone know any reliable open source HA solution with stream replication
> and IP live migration in case of master node failure? Recently I've been
> attending Fujitsu PostgreSQL webinar and they presented so called "mirroring
> controler" + sync stream replication . I'm wondering if this combines of
> regular stream replication + some kind of keepalive mechanism ? 

You should look at PostgreSQL Automatic Failover project:

  http://dalibo.github.io/PAF/

It relies on the Pacemaker project, the industry reference for HA under Linux.
 
Make sure to consult the documentation, especially the quick starts:

  http://dalibo.github.io/PAF/documentation.html

Installation: http://dalibo.github.io/PAF/install.html

Here is some conference slides including video of demos:

  http://www.dalibo.org/_media/2016-pgconfeu-paf.html.gz

PAF only requiers streaming replication between nodes. Whether you want it sync
or async is your choice.

2.0 is perfect for production. 2.1 with minor improvements should be out before
2017 hopefully (see github commit history if curious).

It appears PAF 2.0 (or 2.1 if released before 2017) might be included in
Debian 9. There is a pending discussion with a Debian packager.

Regards,
-- 
Jehan-Guillaume (ioguix) de Rorthais
Dalibo


-- 
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] Streaming replication failover/failback

[Jehan-Guillaume de Rorthais]

On Thu, 17 Nov 2016 08:26:59 -0900
Israel Brewster <isr...@ravnalaska.net> wrote:

> > On Nov 16, 2016, at 4:24 PM, Adrian Klaver <adrian.kla...@aklaver.com>
> > wrote:
> > 
> > On 11/16/2016 04:51 PM, Israel Brewster wrote:  
> >> I've been playing around with streaming replication, and discovered that
> >> the following series of steps *appears* to work without complaint:
> >> 
> >> - Start with master on server A, slave on server B, replicating via
> >> streaming replication with replication slots.
> >> - Shut down master on A
> >> - Promote slave on B to master
> >> - Create recovery.conf on A pointing to B
> >> - Start (as slave) on A, streaming from B
> >> 
> >> After those steps, A comes up as a streaming replica of B, and works as
> >> expected. In my testing I can go back and forth between the two servers
> >> all day using the above steps.
> >> 
> >> My understanding from my initial research, however, is that this
> >> shouldn't be possible - I should need to perform a new basebackup from B
> >> to A after promoting B to master before I can restart A as a slave. Is
> >> the observed behavior then just a "lucky fluke" that I shouldn't rely  
> > 
> > You don't say how active the database is, but I going to say it is not
> > active enough for the WAL files on B to go out for scope for A in the time
> > it takes you to do the switch over.  
> 
> Yeah, not very - this was just in testing, so essentially no activity. So
> between your response and the one from Jehan-Guillaume de Rorthais, what I'm
> hearing is that my information about the basebackup being needed was
> obsoleted with the patch he linked to, and as long as I do a clean shutdown
> of the master, and don't do too much activity on the *new* master before
> bringing the old master up as a slave (such that WAL files are lost)

Just set up wal archiving to avoid this (and have PITR backup as a side effect).


-- 
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] Streaming replication failover/failback

[Jehan-Guillaume de Rorthais]

On Wed, 16 Nov 2016 15:51:26 -0900
Israel Brewster  wrote:

> I've been playing around with streaming replication, and discovered that the
> following series of steps *appears* to work without complaint:
> 
> - Start with master on server A, slave on server B, replicating via streaming
> replication with replication slots.
> - Shut down master on A
> - Promote slave on B to master
> - Create recovery.conf on A pointing to B
> - Start (as slave) on A, streaming from B
> 
> After those steps, A comes up as a streaming replica of B, and works as
> expected. In my testing I can go back and forth between the two servers all
> day using the above steps.
> 
> My understanding from my initial research, however, is that this shouldn't be
> possible - I should need to perform a new basebackup from B to A after
> promoting B to master before I can restart A as a slave. Is the observed
> behavior then just a "lucky fluke" that I shouldn't rely on? 

No, it's not a "lucky fluke".

See
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=985bd7d49726c9f178558491d31a570d47340459

The only thing you should really pay attention is that the standby was in
Streaming Rep when you instructed the master to shut down, and that it stays
connected until the full stop of the master.

If you really want to check everything, use pg_xlogdump on the standby and make
sure the standby received the "shutdown checkpoint" from the master and wrote
it in its WAL.

> Or is it expected behavior and my understanding about the need for a new
> basebackup is simply off?

This is expected, but taking a new basebackup was a requirement for some time.

> Does the new pg_rewind feature of 9.5 change things? If so, how?

pg_rewind helps if your standby was not connected when you lost/stopped your
master. It reverts the last transactions the master received and that was not
streamed to the promoted standby.

Regards,


-- 
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] Fast Primary shutdown only after wal_sender_timeout

[Jehan-Guillaume de Rorthais]

Le 28 octobre 2016 12:40:24 GMT+02:00, Michael Banck 
 a écrit :
>Hi,
>
>I'm doing some failover tests on a 2-node streaming replication cluster
>and shutting down the primary with 'pg_ctl -m fast' results in a
>timeout
>of 50-60 seconds, pg_ctl returns only after the latter message:
>
><718042016-10-28 10:01:37.833 CEST-5808e5a4.1187c-transid:0>LOG:
>database system is shut down
><62866-replicator-[unbekannt]-10.1.181.30(39609)-2016-10-28
>10:02:27.963
>CEST-581305b9.f592-transid:0>LOG:  terminating walsender process due to
>replication timeout
>
>If I set wal_sender_timeout (it has been commented out so far, i.e. set
>to 60 seconds) to something smaller like 10 seconds, I get a 10 second
>delay. There are no users logged into either primary or standby, nor is
>there any other activity. The hot_standby_feedback parameter is set to
>'on'.
>
>I would assume that the replication connection is shut down along with
>the backends, but this seems to be not the case, is this expected?

Yes, in normal situation. But the master ensure everything has been replicated 
to the connected standby before shutting down the connections. 

It it hits wal_sender_timeout, maybe you have a badly disconnected standby not 
detected by the master? Maybe a secondary IP address moved away from the master 
before its shutdown ?
>
>This is on 9.5.4, self-compiled.
>
>
>Michael

/ioguix 


-- 
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] Monitor pg_xlog size via SQL with postgres 9.4

[Jehan-Guillaume de Rorthais]

On Thu, 22 Sep 2016 14:23:20 +0200
Sylvain Marechal  wrote:

> Hello all,
> 
> is there a way to monitor the size of the pg_xlog directory in SQL? The
> goal is to monitor the pg_xlog file without ressorting to a 'du' like
> solution that needs a direct access to the machine.

You might be inspired by the following query:

https://github.com/OPMDG/check_pgactivity/blob/master/check_pgactivity#L6156

Regards,


-- 
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] PGDATA / data_directory

[Jehan-Guillaume de Rorthais]

On Sun, 4 Sep 2016 11:40:38 +0200
Christoph Berg  wrote:

> Re: Benoit Lobréau 2016-08-31
> 

Re: [GENERAL] Any work on better parallelization of pg_dump?

[Jehan-Guillaume de Rorthais]

On Mon, 29 Aug 2016 13:13:17 -0300
Alvaro Herrera <alvhe...@2ndquadrant.com> wrote:

> Jehan-Guillaume de Rorthais wrote:
> > On Mon, 29 Aug 2016 13:38:03 +0200
> > hubert depesz lubaczewski <dep...@depesz.com> wrote:
> > 
> > > Hi,
> > > we have rather uncommon case - DB with ~ 50GB of data, but this is
> > > spread across ~ 8 tables.
> > > 
> > > Running pg_dump -Fd -jxx dumps in parallel, but only data, and MOST of
> > > the time is spent on queries that run sequentially, and as far as I can
> > > tell, get schema of tables, and sequence values.
> > > 
> > > This happens on Pg 9.5. Are there any plans to make getting schema
> > > faster for such cases? Either by parallelization, or at least by getting
> > > schema for all tables "at once", and having pg_dump "sort it out",
> > > instead of getting schema for each table separately?
> 
> Depesz: I suggest you start coding ASAP.
> 
> > Another issue I found in current implementation is how pg_restore deal with
> > PK. As it takes an exclusif lock on the table, it is executed alone before
> > indexes creation. 
> > 
> > Splitting the PK in unique index creation then the constraint creation might
> > save a lot of time as other index can be built during the PK creation.
> 
> Yeah.  I recall there being some stupid limitation in ALTER TABLE .. ADD
> CONSTRAINT USING INDEX to create a primary key from a previously
> existing unique index, which would be very good to fix (I don't recall
> what it was, but it was something infuriatingly silly). 

Could you elaborate? I already had to implement some custom scripts to
restore some tables using this method. The scripts were using psql and
"xargs -P" to restore the indexes and the PK outside of pg_restore. 

> I suggest you start coding that ASAP.

I did start, but with no success so far as the code in pg_dump/pg_restore is
quite obscure at a first look. The few attempt I did to catch the PK creation
statement and split it in 2 distincts statements failed to be processed in
parallel IIRC. I probably dropped the patch in the meantime though.


-- 
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] Any work on better parallelization of pg_dump?

[Jehan-Guillaume de Rorthais]

On Mon, 29 Aug 2016 13:38:03 +0200
hubert depesz lubaczewski  wrote:

> Hi,
> we have rather uncommon case - DB with ~ 50GB of data, but this is
> spread across ~ 8 tables.
> 
> Running pg_dump -Fd -jxx dumps in parallel, but only data, and MOST of
> the time is spent on queries that run sequentially, and as far as I can
> tell, get schema of tables, and sequence values.
> 
> This happens on Pg 9.5. Are there any plans to make getting schema
> faster for such cases? Either by parallelization, or at least by getting
> schema for all tables "at once", and having pg_dump "sort it out",
> instead of getting schema for each table separately?

Another issue I found in current implementation is how pg_restore deal with PK.
As it takes an exclusif lock on the table, it is executed alone before indexes
creation. 

Splitting the PK in unique index creation then the constraint creation might
save a lot of time as other index can be built during the PK creation.

Regards,


-- 
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] Protect a table against concurrent data changes while allowing to vacuum it

[Jehan-Guillaume de Rorthais]

Le Wed, 22 Jun 2016 18:07:46 +0800,
Vlad Arkhipov  a écrit :

> I am running PostgreSQL 9.5.
> 
> CREATE TABLE t (id BIGINT NOT NULL PRIMARY KEY, name TEXT);

I guess this is not your definitive definition of the table and you might
have some other fields isn't it ? 

I can see multiple way to change this schema that seems broken, but we probably
lack informations to pick the right one...

> The constraint that the data must satisfy is `there is no more than 3 
> records with the same name`.
> 
> I am not in control of queries that modify the table, so advisory locks 
> can hardly be of help to me.
> 
> On 06/22/2016 05:20 PM, Sameer Kumar wrote:
> >
> >
> > On Wed, Jun 22, 2016 at 5:10 PM Vlad Arkhipov  > > wrote:
> >
> > Hello,
> >
> > I have a constraint that requires a table to be locked before checking
> > it (i.e. no more than 2 records with the same value in the same
> > column).
> > If I lock the table in the SHARE ROW EXCLUSIVE mode, any vacuuming (or
> > autovacuuming) process prevents me from checking the constraint. What
> > are possible solutions?
> >
> >
> > May be you would like to share-
> > - Table Structure
> > - PostgreSQL version
> >
> > This will help people who would try to help you.
> >
> > I think you might want to consider an optimistic way of locking your 
> > records, instead of locking them. Or look at advisory locks (but that 
> > depends on your Postgres version).


-- 
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] Protect a table against concurrent data changes while allowing to vacuum it

[Jehan-Guillaume de Rorthais]

Le Wed, 22 Jun 2016 10:49:13 +,
Albe Laurenz  a écrit :

> Sameer Kumar wrote:
> > On Wed, Jun 22, 2016 at 6:08 PM Vlad Arkhipov  wrote:
> >> I am running PostgreSQL 9.5.
> >> 
> >> CREATE TABLE t (id BIGINT NOT NULL PRIMARY KEY, name TEXT);
> >> 
> >> The constraint that the data must satisfy is `there is no more than 3
> >> records with the same name`.
> >> 
> >> I am not in control of queries that modify the table, so advisory locks
> >> can hardly be of help to me.
> > 
> > 
> > Define a function which does a count of the rows and if count is 3 it
> > return false if count is less it returns true.
> > 
> > Use check constraint with this function. I have not tried this so not sure
> > if you can use function with SELECT on same table in CHECK constraint. So
> > test it out first.
> > 
> > If this works, any insert trying to get the 4th record in table would fail.
> 
> You cannot use subqueries in a check constraint:
> 
> ALTER TABLE t
>ADD CONSTRAINT name_count
>   CHECK ((SELECT count(*) FROM t t1 WHERE t1.name = name) <= 3);
> ERROR:  cannot use subquery in check constraint
> 
> > A last resort could be using triggers. But either of these approaches will
> > cause issues if you have high concurrency.
> 
> Yes, triggers is the way to go:
> 
> CREATE OR REPLACE FUNCTION check_t() RETURNS trigger LANGUAGE plpgsql AS
> $$BEGIN
>IF (SELECT count(*) FROM t WHERE name = NEW.name) > 3 THEN
>   RAISE EXCEPTION 'More than three values!';
>END IF;
>RETURN NEW;
> END;$$;
> 
> CREATE TRIGGER check_t AFTER INSERT OR UPDATE ON t FOR EACH ROW
>EXECUTE PROCEDURE check_t();
> 
> But be warned that this will only work if all transactions involved use
> the isolation level SERIALIZABLE.
> 
> Otherwise two concurrent INSERTs would not see each other's entry, and the
> triggers would not raise an error even if there are more than three entries
> after COMMIT.

Use advisory locks to be able to use this in any isolation level:

  CREATE OR REPLACE FUNCTION check_t() RETURNS trigger LANGUAGE plpgsql AS
  $$BEGIN
 PERFORM pg_advisory_xact_lock(hashtext(NEW.name));

 IF (SELECT count(*) FROM t WHERE name = NEW.name) > 3 THEN
RAISE EXCEPTION 'More than three values!';
 END IF;
 RETURN NEW;
  END;$$;


-- 
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] Unique values on multiple tables

[Jehan-Guillaume de Rorthais]

Le Mon, 28 Mar 2016 12:44:51 +0200,
Emre Hasegeli  a écrit :

> > Could I use lock tables to fix this? Is postgres automaticaly locking a
> > table while running a trigger on that table?
> 
> You can use LOCK TABLE. See the documentation:
> 
> http://www.postgresql.org/docs/current/static/explicit-locking.html

Use advisory locks for better performance. See the following article (at
least the two lasts chapters) for some examples:

http://blog.ioguix.net/postgresql/2015/02/05/Partitionning-and-constraints-part-1.html

Cheers,


-- 
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] Script to check replication

[Jehan-Guillaume de Rorthais]

Le Fri, 4 Mar 2016 19:35:16 +,
Ashish Chauhan  a écrit :

> Hi,
> 
> We are running Streaming replication with Hot standby in our current
> production. I do have below script to check replication status and
> replication lag. Does anyone have script which runs every 15 mins to check
> replication status and send out email if replication is not running or
> lagging behind.
> 
> SELECT pg_last_xlog_receive_location() receive,
> pg_last_xlog_replay_location() replay, now() -
> pg_last_xact_replay_timestamp() AS replication_delay, (extract(epoch FROM
> now()) -   extract(epoch FROM pg_last_xact_replay_timestamp()))::int lag
> 
> Thanks for your help!

Usually, such requirement is fullfilled by a monitoring system. Eg. Nagios,
Zabbix, Munin, and so on.

Considering the replication check or lag check, we are using
check_pgactivity[1] (see releases [2]). See its "streaming_delta" service. If
you don't use Nagios or a compatible derivative, writing a wrapper around this
script is quite easy to do.

Regards,

[1] https://github.com/OPMDG/check_pgactivity
[2] https://github.com/OPMDG/check_pgactivity/releases


-- 
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] Auotmated postgres failover

[Jehan-Guillaume de Rorthais]

Le Thu, 21 Jan 2016 11:34:18 -0800,
John R Pierce <pie...@hogranch.com> a écrit :

> On 1/21/2016 11:07 AM, jwienc...@comcast.net wrote:
> >
> >
> > I'm looking for a tool to automate PostgreSQL cluster management 
> > failover in the event the master database were to become unavailable. 
> >   Currently are manually issuing a "pg_ctl promote"  once we become 
> > aware that the master database has crashed.
> >
> >
> > Is repmgr a via solution?  Please pass along experiences with repmgr.
> >
> > Are there any other  tools available to automatically issue the 
> > "promote" in the event of a master database crash?

Yes, 3 different Pacemaker resource agents exist for PostgreSQL:

 * official one, in the package "resource-agents" on most linux distribs.
   This one is pretty complex and support multistate and stateless setup.
 * a simple, stupid, easy and stateless, agent:
   https://github.com/dalibo/pgsql-resource-agent/tree/master/stateless
   This one is fine for a 2 node cluster
 * a multistate-aware agent:
   https://github.com/dalibo/pgsql-resource-agent/tree/master/multistate
   This one is nice for multi-node cluster, searching for the best known slave
   to elect after a master lost.

Some important docs are available in the pgsql-resource-agent (PRA) repo:
 * https://github.com/dalibo/pgsql-resource-agent/blob/master/FENCING.md
 * the stateless:
   
https://github.com/dalibo/pgsql-resource-agent/blob/master/stateless/README.md
 * the multistate:
   
https://github.com/dalibo/pgsql-resource-agent/blob/master/multistate/README.md
   
https://github.com/dalibo/pgsql-resource-agent/blob/master/multistate/INSTALL.md
   
https://github.com/dalibo/pgsql-resource-agent/blob/master/multistate/docs/Quick_Start.md

> repmgr is a tool you could use in conjunction with a generic cluster 
> management system like linuxha/heartbeat, vcs, etc.
> 
> the most difficult part is reliably determining that A) the master has 
> crashed, and B) fencing the failed old master so it doesn't wake up and 
> think its still in charge.

+1 
-- 
Jehan-Guillaume de Rorthais
Dalibo


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

[GENERAL] Strange avg value size for index on expression in pg_stats

[Jehan-Guillaume de Rorthais]

Hello,

I'm not sure if I should post here or on pgsql-hackers.

While investigating about a wrong result with my btree bloat estimation query,
I found a strange stat deviation between the average size of a value in its
table and its average size in one index on the lower expression.

Take the following scenario:

  postgres@test=# create table test as
  test-# select md5(t::text) AS t 
  test-# from generate_series(1, 100) t;
  SELECT 100
  
  postgres@test=# create index ON test (lower(t));
  CREATE INDEX
  
  postgres@test=# analyze test;
  ANALYZE
  
  postgres@test=# select tablename, attname, avg_width
  test-# from pg_stats
  test-# where schemaname = 'public';
 tablename| attname | avg_width 
  +-+---
   test   | t   |33
   test_lower_idx | lower   |36

Md5 values are always 32 bytes long. In the table, pg_stats reports 33 because
of the text header. In the index, the reported value is 36!

Looking at the page layout documentation and in the index using hexdump, I can
not find any answer about this 3 bytes. PFA the hexdump -C output from the
index. For each row, we clearly see a 8 bytes row header followed by a ONE byte
value header (43 'C'), the 32 bytes of the md5 and 7 bytes of padding (00).

A wild guess would be that ANALYZE is considering a text field from an
expression has always a 4 bytes header whatever its actual size (text field
header size is one if the value is  127 bytes long, 4 in other situations).


This tiny difference is the source of a very bad estimation with the Btree bloat
estimation query when values are around an alignement boundary. As instance,
here is the use case that lead me to this:

   tablename  | attname | avg_width 
  +-+---
   customer   | email   |23
   customer_lower_idx | lower   |26

We have an index on email, and another one on lower(index). The first one is
aligned on 24, the second one on 32. Leading to bloat estimation of 17% for
the first one and -1% for the second one (the estimated index size is bigger
than the real one).

Any information about this from a hacker? Do anyone have an explanation about
this? Is it something that's worth posting on pgsql-hackers?


Regards,  00 00 00 00 00 00 00 00  00 00 00 00 30 00 f0 1f  |0...|
0010  f0 1f 04 20 00 00 00 00  62 31 05 00 02 00 00 00  |... b1..|
0020  01 00 00 00 00 00 00 00  01 00 00 00 00 00 00 00  ||
0030  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  ||
*
1ff0  00 00 00 00 00 00 00 00  00 00 00 00 08 00 00 00  ||
2000  00 00 00 00 00 00 00 00  00 00 00 00 a8 01 30 0d  |..0.|
2010  f0 1f 04 20 00 00 00 00  c0 9f 60 00 90 9f 60 00  |... ..`...`.|
2020  60 9f 60 00 30 9f 60 00  00 9f 60 00 d0 9e 60 00  |`.`.0.`...`...`.|
2030  a0 9e 60 00 70 9e 60 00  40 9e 60 00 10 9e 60 00  |..`.p.`.@.`...`.|
2040  e0 9d 60 00 b0 9d 60 00  80 9d 60 00 50 9d 60 00  |..`...`...`.P.`.|
2050  20 9d 60 00 f0 9c 60 00  c0 9c 60 00 90 9c 60 00  | .`...`...`...`.|
2060  60 9c 60 00 30 9c 60 00  00 9c 60 00 d0 9b 60 00  |`.`.0.`...`...`.|
2070  a0 9b 60 00 70 9b 60 00  40 9b 60 00 10 9b 60 00  |..`.p.`.@.`...`.|
2080  e0 9a 60 00 b0 9a 60 00  80 9a 60 00 50 9a 60 00  |..`...`...`.P.`.|
2090  20 9a 60 00 f0 99 60 00  c0 99 60 00 90 99 60 00  | .`...`...`...`.|
20a0  60 99 60 00 30 99 60 00  00 99 60 00 d0 98 60 00  |`.`.0.`...`...`.|
20b0  a0 98 60 00 70 98 60 00  40 98 60 00 10 98 60 00  |..`.p.`.@.`...`.|
20c0  e0 97 60 00 b0 97 60 00  80 97 60 00 50 97 60 00  |..`...`...`.P.`.|
20d0  20 97 60 00 f0 96 60 00  c0 96 60 00 90 96 60 00  | .`...`...`...`.|
20e0  60 96 60 00 30 96 60 00  00 96 60 00 d0 95 60 00  |`.`.0.`...`...`.|
20f0  a0 95 60 00 70 95 60 00  40 95 60 00 10 95 60 00  |..`.p.`.@.`...`.|
2100  e0 94 60 00 b0 94 60 00  80 94 60 00 50 94 60 00  |..`...`...`.P.`.|
2110  20 94 60 00 f0 93 60 00  c0 93 60 00 90 93 60 00  | .`...`...`...`.|
2120  60 93 60 00 30 93 60 00  00 93 60 00 d0 92 60 00  |`.`.0.`...`...`.|
2130  a0 92 60 00 70 92 60 00  40 92 60 00 10 92 60 00  |..`.p.`.@.`...`.|
2140  e0 91 60 00 b0 91 60 00  80 91 60 00 50 91 60 00  |..`...`...`.P.`.|
2150  20 91 60 00 f0 90 60 00  c0 90 60 00 90 90 60 00  | .`...`...`...`.|
2160  60 90 60 00 30 90 60 00  00 90 60 00 d0 8f 60 00  |`.`.0.`...`...`.|
2170  a0 8f 60 00 70 8f 60 00  40 8f 60 00 10 8f 60 00  |..`.p.`.@.`...`.|
2180  e0 8e 60 00 b0 8e 60 00  80 8e 60 00 50 8e 60 00  |..`...`...`.P.`.|
2190  20 8e 60 00 f0 8d 60 00  c0 8d 60 00 90 8d 60 00  | .`...`...`...`.|
21a0  60 8d 60 00 30 8d 60 00  30 8d 60 00 00 00 00 00  |`.`.0.`.0.`.|
21b0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  ||
*
2d30  00 00 00 00 53 00 30 40  43 66 65 39 66 63 32 38  |S.0@Cfe9fc28|

Re: [GENERAL] Strange avg value size for index on expression in pg_stats

[Jehan-Guillaume de Rorthais]

On Mon, 10 Nov 2014 11:52:54 +0100
Jehan-Guillaume de Rorthais iog...@free.fr wrote:

 Hello,
 
 I'm not sure if I should post here or on pgsql-hackers.
 
 While investigating about a wrong result with my btree bloat estimation query,
 I found a strange stat deviation between the average size of a value in its
 table and its average size in one index on the lower expression.

Oh, I forgot to add links to this query, for testing purpose:

  * https://gist.github.com/ioguix/c29d5790b8b93bf81c27
  * explanations, see http://blog.ioguix.net/tag/bloat/

Cheers,


-- 
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] Strange avg value size for index on expression in pg_stats

[Jehan-Guillaume de Rorthais]

On Mon, 10 Nov 2014 09:39:23 -0500
Tom Lane t...@sss.pgh.pa.us wrote:

 Jehan-Guillaume de Rorthais iog...@free.fr writes:
  While investigating about a wrong result with my btree bloat estimation
  query, I found a strange stat deviation between the average size of a value
  in its table and its average size in one index on the lower expression.
 
 ANALYZE doesn't look at the physical index at all.  For an expression
 index, it will recompute the index expression at each sampled row, and
 then take statistics on those values.  In the case you've got here, it's
 re-running the lower() function and looking at the uncompressed result.
 So that accounts for the discrepancy in average width stats.

Ok, understood.

But in my demo scenario, I used *only* md5 to populate the test table. So
data length is always 32 and the average width length is exact. No possible
deviation, even with lower(). To be quite dense:

  postgres@test=# select 
length(lower(md5('a'))) = length(md5('b')), 
length(md5('c')) = length(md5('d'));
  -[ RECORD 1 ]
  ?column? | t
  ?column? | t

And here is another test with a static string for all rows:

  postgres@test=# create table test as
  select '1234567890123456789012'::text as t
  from generate_series(1,100);
  SELECT 100
  
  postgres@test=# create index ON test (lower(t));
  CREATE INDEX
  
  postgres@test=# analyze test;
  ANALYZE

  postgres@test=# select tablename, attname, avg_width
  from pg_stats
  where schemaname = 'public';
 tablename| attname | avg_width 
  +-+---
   test   | t   |23
   test_lower_idx | lower   |26


AFAIU, we should not have a discrepancy here.

  This tiny difference is the source of a very bad estimation with the
  Btree bloat estimation query when values are around an alignement
  boundary.
 
 TBH, if that query is relying on ANALYZE width estimates to be accurate
 to the last byte, its reliability is going to be horrid anyway.

Well, I'm aware of that. I don't need an accuracy to the last byte. This query
doesn't even pay attention to the data alignment padding anyway (I measured some
10% deviation in a case because of this).

This request only helps guessing the bloat evolution in Btrees or quickly
discover *big* deviations. In many situations, we can not afford a call to
pgstattuple.avg_leaf_density().

But this statistic difference between two values with the exact same size is
itching me. Sa far, I couldn't find a logical explanation and it just looks
like a wrong statistic.

Regards,
-- 
Jehan-Guillaume (ioguix) de Rorthais


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