PostgreSQL - Replicando PostgreSQL em 15 passos com Gabriel Prestes

Migração banco de dados:

1 - Criar estrutura de archives na instância de origem:

[root@sebaorig /]# mkdir -p data/archive_log [root@sebaorig /]# chown -R postgres. data/archive_log/

2 - Ativando archive na instância de origem:

[root@sebaorig /]# grep archive /var/lib/pgsql/data/postgresql.conf archive_mode = on # allows archiving to be done archive_command = 'rsync -arv %p /data/archive_log/%f' # command to use to archive a logfile segment archive_timeout = 0 # force a logfile segment switch after this [root@sebaorig /]# service postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ]

3 - Testando a gravação de archives:

[root@sebaorig /]# psql -U postgres psql (8.4.5) Type "help" for help. postgres=# SELECT pg_switch_xlog(); pg_switch_xlog ---------------- 61F/AF92C180 (1 row) postgres=# \q [root@sebaorig /]# ls -la /data/archive_log/ total 16412 drwxr-xr-x 2 postgres postgres 4096 Oct 18 20:24 . drwxr-xr-x 3 root root 4096 Oct 18 20:20 .. -rw------- 1 postgres postgres 16777216 Oct 18 20:24 000000010000061F000000AF

4 - Colocando a instância de origem para backup:

[root@sebaorig /]# psql -U postgres psql (8.4.5) Type "help" for help. postgres=# SELECT pg_start_backup('Cordeiro de Deus perdoai os pecados do mundo'); pg_start_backup ----------------- 61F/B0000020 (1 row) postgres=# \q

5 - Replicando a instância para o servidor de destino:

[root@sebaorig pgsql]# rsync -avog --delete-after data/ -e ssh root@sebadest:/var/lib/pgsql/data

6 - Criando estrutura de archives no servidor de destino:

[root@sebadest pgsql]# mkdir -p /data/archive_log [root@sebadest pgsql]# chown -R postgres. /data/archive_log

7 - Parando o backup na instância de origem:

[root@sebaorig archive_log]# psql -U postgres psql (8.4.5) Type "help" for help. postgres=# SELECT pg_stop_backup(); pg_stop_backup ---------------- 61F/B0000088 (1 row) postgres=# \q

8 - Replicando archives para o servidor de destino:

[root@sebaorig pgsql]# rsync -avog --delete-after /data/archive_log/* -e ssh root@sebadest:/data/archive_log

9 - Limpando a instância de destino:

[root@sebadest data]# rm postmaster.pid

10 - Criando recovery.conf na instância de destino e limpando pg_xlog:

[root@sebadest data]# vim recovery.conf Insira o seguinte conteúdo: # ------------------------------- # PostgreSQL recovery config file # ------------------------------- # # Edit this file to provide the parameters that PostgreSQL # needs to perform an archive recovery of a database. # # If "recovery.conf" is present in the PostgreSQL data directory, it is # read on postmaster startup. After successful recovery, it is renamed # to "recovery.done" to ensure that we do not accidentally re-enter # archive recovery mode. # # This file consists of lines of the form: # # name = 'value' # # (The quotes around the value are NOT optional, but the "=" is.) # # Comments are introduced with '#'. # # The complete list of option names and allowed values can be found # in the PostgreSQL documentation. The commented-out settings shown below # are example values. # #--------------------------------------------------------------------------- # REQUIRED PARAMETERS #--------------------------------------------------------------------------- # # restore_command # # specifies the shell command that is executed to copy log files # back from archival storage. The command string may contain %f, # which is replaced by the name of the desired log file, and %p, # which is replaced by the absolute path to copy the log file to. # # It is important that the command return nonzero exit status on failure. # The command *will* be asked for log files that are not present in the # archive; it must return nonzero when so asked. # # NOTE that the basename of %p will be different from %f; do not # expect them to be interchangeable. # restore_command = 'cp /data/archive_log/%f %p' # # #--------------------------------------------------------------------------- # OPTIONAL PARAMETERS #--------------------------------------------------------------------------- # # recovery_end_command # # specifies an optional shell command to execute at completion of recovery. # This can be useful for cleaning up after the restore_command. # #recovery_end_command = '' # # # By default, recovery will rollforward to the end of the WAL log. # If you want to stop rollforward before that point, you # must set a recovery target. # # You may set a recovery target either by transactionId, or # by timestamp. Recovery may either include or exclude the # transaction(s) with the recovery target value (ie, stop either # just after or just before the given target, respectively). # #recovery_target_time = '2004-07-14 22:39:00 EST' # #recovery_target_xid = '1100842' # #recovery_target_inclusive = 'true' # 'true' or 'false' # # # If you want to recover into a timeline other than the "main line" shown in # pg_control, specify the timeline number here, or write 'latest' to get # the latest branch for which there's a history file. # #recovery_target_timeline = '33' # number or 'latest' # # #--------------------------------------------------------------------------- [root@sebadest data]# rm -f pg_xlog/* [root@sebadest data]# rm -f pg_xlog/archive_status*

11 - Inicie a instância no servidor de destino:

[root@sebadest data]# /etc/init.d/postgresql start Starting postgresql service: [ OK ]

12 - Retire a instância de destino e origem do archive mode:

[root@sebadest data]# grep archive postgresql.conf archive_mode = off # allows archiving to be done #archive_command = 'rsync -arv %p /data/archive_log/%f' # command to use to archive a logfile segment #archive_timeout = 0 # force a logfile segment switch after this [root@sebadest data]# service postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ]

13 - Teste os ajustes do item 12:

[root@sebaorig pgsql]# psql -U postgres psql (8.4.5) Type "help" for help. postgres=# SELECT pg_start_backup('Cordeiro de Deus perdoai os pecados do mundo'); ERRO: arquivamento do WAL não está ativo HINT: archive_mode deve ser habilitado ao iniciar o servidor.

14 - Valide a instância replicada no servidor de destino:

[root@sebadest data]# psql -U postgres psql (8.4.13) Type "help" for help. postgres=# \l List of databases Name | Owner | Encoding | Collation | Ctype | Access privileges ----------------------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres (3 rows)

15 - Seja feliz, mas verifique no log da instância qual o ponto de consistência atingido do banco.