Postgres Backup

Published: 2020-03-22, Updated: 2020-03-22

Backup

Migrar tabela de uma base para outra

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/mageddo/stm_skin-v2.sql' WITH HEADER DELIMITER ';' CSV;

Backup físico (Copiar os arquivos da base)

Backup com consistencia, pausavel, incremental, 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

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 da base inteira para arquivo sql

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

Backup comprensando o dump

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

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 / Back restore

psql db < /var/lib/mageddo/bkp/stg_mg_dump.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

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();

Usar UUIDs como PKs é uma boa ideia? AutoHotKey Commands

Comments