Alterar tabela em bancos de dados de produção ao vivo

Quando você emitir um ALTER TABLEno PostgreSQL , será necessário um ACCESS EXCLUSIVEbloqueio que bloqueia tudo, incluindo arquivosSELECT . No entanto, esse bloqueio pode ser bastante breve se a tabela não exigir reescrita, nenhum novo UNIQUE, CHECKou FOREIGN KEYas restrições precisarem de verificações caras de tabela completa para verificar etc.

Em caso de dúvida, geralmente você pode apenas experimentá-lo! Todo DDL no PostgreSQL é transacional, então não há problema em cancelar um ALTER TABLEse demorar muito e começar a reter outras consultas. Os níveis de bloqueio exigidos por vários comandos estão documentados na página de bloqueio .

Algumas operações normalmente lentas podem ser aceleradas para serem executadas com segurança sem tempo de inatividade. Por exemplo, se você tem uma tabela te deseja alterar a coluna customercode integer NOT NULLpara textporque o cliente decidiu que todos os códigos de cliente devem agora começar com um X, você pode escrever:

ALTER TABLE t ALTER COLUMN customercode TYPE text USING ( 'X'||customercode::text );

... mas isso bloquearia toda a tabela para a reescrita. O mesmo acontece com a adição de uma coluna com um DEFAULT. Isso pode ser feito em algumas etapas para evitar o bloqueio longo, mas os aplicativos devem ser capazes de lidar com a duplicação temporária:

ALTER TABLE t ADD COLUMN customercode_new text;
BEGIN;
LOCK TABLE t IN EXCLUSIVE MODE;
UPDATE t SET customercode_new = 'X'||customercode::text;
ALTER TABLE t DROP COLUMN customercode;
ALTER TABLE t RENAME COLUMN customercode_new TO customercode;
COMMIT;

Isso só impedirá gravações durante o tprocesso; o nome do bloqueio EXCLUSIVEé um pouco enganoso, pois exclui tudo , excetoSELECT ; o ACCESS EXCLUSIVEmodo é o único que exclui absolutamente tudo. Consulte os modos de bloqueio . Existe o risco de que essa operação possa reverter em deadlock devido à atualização de bloqueio exigida pelo ALTER TABLE, mas na pior das hipóteses você terá que fazer isso novamente.

Você pode até evitar esse bloqueio e fazer tudo ao vivo criando uma função de gatilho tque sempre que um INSERTou UPDATEentra, preenche automaticamente customercode_newde customercode.

Também existem ferramentas integradas como CREATE INDEX CONCURRENTLYe que ALTER TABLE ... ADD table_constraint_using_indexsão projetadas para permitir que os DBAs reduzam as durações de bloqueios exclusivos, fazendo o trabalho mais lentamente de maneira amigável à simultaneidade.

A pg_reorgferramenta ou sua sucessora pg_repacktambém pode ser usada para algumas operações de reestruturação de tabelas.

Percona criou sua própria ferramenta para realizar alterações de esquema online

A ferramenta é chamada pt-online-schema-change

Envolve gatilhos, portanto, leia a documentação com atenção.

De acordo com a Documentação, as principais operações realizadas são

• Verificações de sanidade
• Chunking
• Alteração de esquema on-line
  • Criar e alterar tabela temporária
  • Capturar alterações da tabela para a tabela temporária
  • Copiar linhas da tabela para a tabela temporária
  • Sincronize a tabela e a tabela temporária
  • Troque/renomeie a tabela e a tabela temporária
  • Limpar

Desligar o sistema e fazer todas as alterações de uma vez pode ser muito arriscado. Se algo der errado, e frequentemente acontece, não há um caminho fácil de volta.

Como desenvolvedor Agile, às vezes preciso refatorar tabelas sem nenhum tempo de inatividade, pois essas tabelas estão sendo modificadas e lidas.

A abordagem a seguir tem baixo risco, porque a alteração é feita em várias etapas de baixo risco que são muito fáceis de reverter:

• Certifique-se de que todos os módulos que acessam a mesa estejam bem cobertos com testes automatizados.
• Crie uma nova tabela. Altere todos os procedimentos que modificam a tabela antiga, para que modifiquem as tabelas antigas e novas.
• Migre os dados existentes para a nova estrutura. Faça isso em lotes pequenos, para que não afete seriamente o desempenho geral do servidor.
• Verifique se a migração de dados foi bem-sucedida.
• Redirecione alguns dos procedimentos de seleção da tabela antiga para as novas. Use testes automatizados para garantir que os módulos alterados ainda estejam corretos. Certifique-se de que seu desempenho seja aceitável. Implante os procedimentos alterados.
• Repita a etapa anterior até que todos os relatórios usem a nova tabela.
• Altere os procedimentos que modificam as tabelas, para que acessem apenas a nova tabela.
• Arquive a tabela antiga e remova-a do sistema.

Usamos essa abordagem muitas vezes para alterar grandes tabelas de produção ao vivo sem tempo de inatividade, sem problemas.

Sim, muitos bancos de dados modernos permitem que você apenas adicione uma coluna ou altere as características de uma coluna, como adicionar ou remover valor nulo.

Se você descartar uma coluna, os dados serão perdidos, mas não há muito medo de corrupção.

A ferramenta Percona usa gatilhos para ajudar na alteração, e não funciona bem se sua mesa já tiver gatilhos existentes. Acabei tendo que escrever um que realmente lide bem com os gatilhos existentes, pois eles são super importantes para nosso banco de dados https://github.com/StirlingMarketingGroup/smg-live-alter

Para abordar a questão sobre o que acontece com uma ALTER TABLEdeclaração, isso depende da extensão de suas alterações. Em casos específicos, se você adicionar uma nova coluna, pelo menos no MS SQL Server, o mecanismo criará uma cópia temporária da tabela, enquanto cria a nova definição de tabela e insere os dados de volta lá. Durante a mudança, a tabela ficaria inacessível aos usuários.

Um exemplo das operações específicas para o servidor MSSQL está aqui: http://support.microsoft.com/kb/956176/en-us

Eu diria que outros RMDBs têm métodos semelhantes, embora a implementação exata seja algo que você teria que verificar com a documentação do fornecedor.