Обсуждение: alter table without an ACCESS EXCLUSIVE lock
Hi,
i'm searching for a solution for changing the type of a column, e.g. from varchar(128) to varchar(512), without an ACCESS EXCLUSIVE lock. The issue is that the relation is nearly 100 million rows big and in our environment every second of downtime is very bad.
In my current setup a slave database is replicated via skytools, so changing the relation first on slave, make a master / slave change and then do the alter table on the other database is one solution for a minimum downtime. But with postgres 9 I will use wal shipping for replication and here is the problem. On slave I have just read permissions and if I do the alter table on the master db a downtime is occuring and this is what I have to avoid.
regards,
Andi
i'm searching for a solution for changing the type of a column, e.g. from varchar(128) to varchar(512), without an ACCESS EXCLUSIVE lock. The issue is that the relation is nearly 100 million rows big and in our environment every second of downtime is very bad.
In my current setup a slave database is replicated via skytools, so changing the relation first on slave, make a master / slave change and then do the alter table on the other database is one solution for a minimum downtime. But with postgres 9 I will use wal shipping for replication and here is the problem. On slave I have just read permissions and if I do the alter table on the master db a downtime is occuring and this is what I have to avoid.
regards,
Andi
Andreas Berger <4postgres@gmail.com> writes:
> i'm searching for a solution for changing the type of a column, e.g. from
> varchar(128) to varchar(512), without an ACCESS EXCLUSIVE lock.
That specific case (increasing the max length of a varchar column) could
be handled by hacking the pg_attribute.atttypmod entry for the column,
since no change in the actual data is required.
However, I have no idea whether your replication environment would cope
with such a change --- experimenting on a non-production setup would be
highly recommendable.
regards, tom lane
Thanks for your answer! In this spacial case your suggestion is a solution, but I am searching for an opportunity to make every change (e.g. alter table) without effecting availability of the database and the modified table when using wal shipping (maybe something like concurrently when creating an index). When using skytools I can keep the downtime minimal by doing a master slave change after modifying the slave db. But the question now is, is it possible to use wal shipping without having downtimes when modifying tables? regards, Andi On 2011-06-16 20:21, Tom Lane wrote:
> Andreas Berger <4postgres@gmail.com> writes:>> i'm searching for a solution for changing the type of a column, e.g. from >> varchar(128) to varchar(512), without an ACCESS EXCLUSIVE lock.> > That specific case (increasing the max length of a varchar column) could > be handled by hacking the pg_attribute.atttypmod entry for the column, > since no change in the actual data is required. > > However, I have no idea whether your replication environment would cope > with such a change --- experimenting on a non-production setup would be > highly recommendable. > > regards, tom lane
Thanks for your answer! In this spacial case your suggestion is a solution, but I am searching for an opportunity to make every change (e.g. alter table) without effecting availability of the database and the modified table when using wal shipping (maybe something like concurrently when creating an index). When using skytools I can keep the downtime minimal by doing a master slave change after modifying the slave db. But the question now is, is it possible to use wal shipping without having downtimes when modifying tables? regards, Andi On 2011-06-16 20:21, Tom Lane wrote: > Andreas Berger <4postgres@gmail.com> writes: >> i'm searching for a solution for changing the type of a column, e.g. from >> varchar(128) to varchar(512), without an ACCESS EXCLUSIVE lock. > > That specific case (increasing the max length of a varchar column) could > be handled by hacking the pg_attribute.atttypmod entry for the column, > since no change in the actual data is required. > > However, I have no idea whether your replication environment would cope > with such a change --- experimenting on a non-production setup would be > highly recommendable. > > regards, tom lane -- Andreas Pötzlberger PAY.ON | www.payon.com Payment Technologies for Global Payment Solutions Jakob-Haringer-Str.1 | 5020 Salzburg | Austria mail: andreas.poetzlberger@payon.com phone DE: +49 89 45230 406 fax DE: +49 89 45230 407 phone AT: +43 662 890008 15 fax AT: +43 662 890008 99 PAY.ON AG Court: HR Munich | Docket-#: HRB 173756 | VAT-ID: DE 234431573 Executive Board: Markus Rinderer (Head), Nikolaus von Taysen Head of Supervisory Board: Alan Goslar PAY.ON GmbH Austria Court: LG Salzburg | Docket-#: FN 315081 f | VAT-ID: ATU64439405 Managing Director: Christian Bamberger This email and any attachments are issued by PAY.ON. It is confidential and intended for the exclusive use of the addressee only. You should not disclose its contents to any other person. If you are not the addressee (or responsible for delivery of the message to the addressee), please notify the originator immediately by return message and destroy the original message. This message and any attachments have been scanned for viruses prior leaving PAY.ON; however, PAY.ON does not guarantee the security of this message and will not be responsible for any damages arising as a result of any virus being passed on or arising from any alteration of this message by a third party. PAY.ON may monitor emails sent to and from PAY.ON.