Há um sistema PostgreSQL PITR rodando em um sistema bastante grande, com centenas de bancos de dados com centenas de tabelas em cada um, e estamos enfrentando problemas baseados em restrições de tempo ao tentar restaurar tabelas individuais de um único banco de dados ou um conjunto de bancos de dados . O PITR está sendo gerenciado com Barman.
Com essa configuração, precisamos restaurar um cluster inteiro (que pode conter dezenas de dbs), extrair a tabela única e restaurar os dados - o que pode levar horas.
Estatisticamente, devido ao número de dbs envolvidos e ao número de pessoas envolvidas, isso acontece com frequência suficiente para se tornar um problema.
Existem soluções mais rápidas que nos dariam recursos semelhantes ao PITR? Eu pensei em fazer pg_dumpbackups manuais para cada tabela individualmente a cada hora, mas talvez existam soluções melhores?
Suponho que você já tenha tentado a abordagem "envergonhar as pessoas para serem mais cuidadosas" sem sucesso.
Acho que tentaria uma solução no banco de dados, como adicionar colunas de carimbo de data e hora à tabela e adicionar colunas para "exclusões temporárias" em vez de fazer exclusões definitivas, de forma que você possa executar consultas para restaurar uma tabela ao seu estado anterior. Se proibir exclusões definitivas no nível do usuário não for uma opção, você poderá manter algumas tabelas de log que permitem criar essas consultas restaurativas. Eu uso este módulo extensivamente para esse propósito, embora se eu estivesse começando do zero eu poderia considerar algo mais moderno como isso . Os gatilhos necessários para executar esse registro impõem uma sobrecarga considerável em qualquer operação DML, portanto, você terá que avaliar se pode tolerar isso.
Um benefício dessa abordagem é que muitas vezes você pode criar consultas que corrigirão o erro de uma pessoa sem que todas as alterações após o erro sejam perdidas. É claro que se os dados ruins do oopsie levarem a erros em cascata porque o trabalho posterior dependeu dos dados ruins, isso pode não ser possível.
Se isso não funcionar, talvez uma abordagem híbrida seja uma boa ideia. Você pode salvar instantâneos pg_dump de hora em hora e mantê-los por 12 horas (ou qualquer quantidade de armazenamento que você esteja disposto a dedicar a esse propósito). Se você raramente precisar voltar mais de 12 horas, faça fallback para o método de recuperação PITR. Se você seguir esse caminho, talvez queira usar as opções
-Fcou-Fdpara pg_dump. Dessa forma, você pode fazer apenas um dump do banco de dados por hora, mas escolher quais tabelas restaurar no momento da restauração, em vez de no momento do dump.Se você quiser manter sua prática atual, há muitas maneiras de torná-la mais rápida, mas depende de qual é o gargalo atual. Se o gargalo estiver restaurando o backup básico do arquivo antes que você possa iniciar a recuperação, você pode manter algumas cópias já restauradas para que você possa iniciar a recuperação rapidamente usando um backup pré-estabelecido e apenas editando o arquivo pg_restore.conf e, em seguida, iniciando. Uma vez usado, você teria que jogá-lo fora e recriá-lo em segundo plano para que ele esteja pronto para a próxima vez. Se o gargalo for o tempo que leva para fazer o próprio PITR, você pode fazer backups de base mais frequentes e usar o mais recente, antigo o suficiente para ser usado no tempo de restauração desejado. Dessa forma, há menos trabalho para recuperar. Por exemplo, faça um backup básico todos os dias, mas guarde apenas um desde o início dos tempos, e um de hoje, e um de ontem. Sempre que você concluir com êxito um backup básico, exclua o de dois dias atrás.
Você também pode manter um hot standby atrasado com "recovery_min_apply_delay" que está sempre cerca de 12 horas atrasado, mas quando você parar esse servidor, altere a configuração para avançar rapidamente para o tempo que você precisa e, uma vez feito, jogue-o fora e recrie para a próxima vez, provavelmente não será muito mais rápido do que apenas manter um backup de base recente, mas adicionará um pouco de complexidade.