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.
