Postgres Backup

Backup

• Olhando o status da replicação
• Backup/Migração Usando replicação master/slave

Migrar tabela de uma base para outra

• ref 16

Fazendo backup

COPY (
  SELECT 
  	cod_app_id,cod_class_id,cod_context_id
  FROM STM_SKIN
) TO '/var/lib/mageddo/stm_skin-v2.sql' WITH CSV HEADER DELIMITER ';';

Voltando

COPY STM_SKIN (
	cod_app_id,cod_class_id,cod_context_id
) FROM '/var/lib/postgresql/data/stm_skin-v2.csv' WITH HEADER DELIMITER ';' CSV;

Backup físico (Copiar os arquivos da base)

Backup com consistencia, pausavel, incremental, usando wall

• ref 18
• ref 19
• Backup usando wall

É POSSÍVEL fazer backup com a database online?

Sim, Vai ter consistência

Backup lógico (Gera arquivo com inserts, create table)

Gerar create table - DDL

pg_dump -t 'aschema.atable' --schema-only database-name

Backup gerando sql num formato mais cross database possível, usando insert ao invés de copy

pg_dump --inserts --host localhost --port 5432 --username root --format plain --schema my_schema --verbose db --file dump.sql

Fazendo backup de server para server sem arquivo intermediário

pg_dump -h remotehost -U remoteuser remotedbname | psql -U localuser localdbname

Backup de tabela para arquivo sql

pg_dump --host localhost --port 5432 --username root --format plain --verbose --table public.tablename dbname --file data.sql

Backup de um schema inteiro para arquivo sql do postgres (usando copy)

pg_dump --host localhost --port 5432 --username root --format plain --schema my_schema --verbose db --file dump.sql

Backup da base inteira para arquivo sql

pg_dump --host localhost --port 5432 --username root --format plain --ignore-version --verbose root --file data.sql

Backup cross version comprensando o dump

Fazendo backup

pg_dump --host localhost --port 5432 --username root --format=c --compress=9 --ignore-version --verbose root --file data.sql

Recuperando

pg_restore --format=c --clean --create --host=localhost --port=5435 --user=root --dbname=db < data.sql 

Se voce usar o backup em formato de diretórios, a performance vai ser melhor, pq ele vai poder paralelizar as tabelas https://stackoverflow.com/a/41402728/2979435 https://stackoverflow.com/questions/55164205/pg-dump-and-pg-restore-on-giant-databases

Isso nao vai fazer restore das roles, só do banco e schemas, caso queira isso use o pg_dumpall, porém ele nao tem o formato custom

Backup cross version em formato de diretórios para maior performance de exportacao e importacao

Fazendo backup

pg_dump --host localhost --port 5432 --username root --format=d -j 8 --verbose root --file ./backup-dir

Recuperando

pg_restore --verbose --format=d -j 8 --clean --create --host=localhost --port=5435 --user=root --dbname=mg_rpp backup-dir

Dump só dos dados

pg_dump --username root --host=postgresql-server.prod --column-inserts --data-only --table=PROD_MG.BSK_SKIN_MARKET_HISTORY db > BSK_SKIN_MARKET_HISTORY.SQL
pg_dump --column-inserts --data-only --schema=<table> <database>

para tirar o nome do schema do prefixo

 cat sqlfile.sql | sed 's/stg_mg.//g' > no_schema_sqlfile.sql

Restaurar backup quando o arquivo está em formato plain / Back restore

psql --host=localhost --port=5435 --user=root --dbname=db < data.sql 

Fazendo backup fisico completo com base online

Arquivo recovery.conf fica em /usr/share/postgresql/10/recovery.conf.sample

Fazer backup do filesystem

O primeiro passo é fazer backup do diretorio inteiro do postgres para servir como base, ele só é feito uma vez, depois disso você só terá que fazer backup dos arquivos wal.

Coloque a base em modo backup (ela continuará funcionando porém estará em modo safe para copiar os arquivos

SELECT pg_start_backup('database backup 2018-08-20');
 pg_start_backup 
-----------------
 1/88000028
(1 row)

Copiando os arquivos

cp -r /opt/databases/stg/postgresql/10.3/data ./data

Voltando a base ao funcionamento normal

SELECT pg_stop_backup();
NOTICE:  WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup
 pg_stop_backup 
----------------
 1/88000130
(1 row)

Esse NOTICE significa que você não configurou o archive_command isso significa que o postgres vai jogar os arquivos WAL na pasta pg_wal e não vai copiar isso para nenhum outro lugar (deveria estar por exemplo jogando em uma pasta de backup que voce definiu)

Voltando o backup

• Copie os arquivos da pasta pg_wal e guarde-os
• Delete todo o conteudo da pasta data
• Volte o backup inicial que voce fez usando o pg_start_backup, etc.
• Crie o arquivo restore.conf no diretorio data
• Configure o restore_command especificando como o postgres vai pegar os arqvuivos WAL que voce fez backup de volta ou sete standby_mode = on se voce colocou o arquivos WAL manualmente de volta
• Volte os arquivos que voce guardou no primeiro passo para a pasta pg_wal
• Suba o servidor e aguarde ele voltar o backup
• Teste a base olhando se os registros voltaram

Configurando o archive_command

wal_level = replica			# minimal, replica, or logical
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/data/bkp/wal/%f'
max_wal_size = 1GB
min_wal_size = 80MB

Forcar a base a fazer backup

select pg_switch_wal();

postgres commands, postgres copy

Usar UUIDs como PKs é uma boa ideia? AutoHotKey Commands

Comments