[Pgpool-general] What is the recommended way to migrate a DB to replicate it with pgpool

[Felix J. Ogris]

Hi,
 
> I have a huge DB here which I would like to replicate with pgpool.
> pgpool is configured for replication with two (backend-) servers. This

I am pretty much sure, but you can not replicate backends with pgpool
directly.

>   $ pg_dump -d to_be_replicated -h host_to_be_replicated -o -F c | \
>     pg_restore -d replicated_db -h host_replicated

Bad idea. Either you have to disable the source backend for normal user
access, or the destination backend might have outdated data after the
dump/restore has finished. If source and dest backend are the same hardware
(eg. both x86), you can synchronize them using write ahead logging.
Suggested steps are:

1. switch on WAL on the soure node
2. setup an archive_command on the source node (eg. copy WAL files to a
   separate directory, lets say /wal)
   (you have to restart Postgres for these steps to work)
3. wipe out $PGDATA on the dest node
4. issue a "SELECT pg_start_backup('any_backup_label');" on the source node
5. copy $PGDATA from the source to the dest node (if the postgres user id is
   equal on both nodes, a "tar -cf - $PGDATA | ssh dest 'tar -C / -xpf -'"
   will do it)
6. create a recovery.conf on the dest node, which will copy the archived WAL
   files from the local directory /wal into Postgres
7. shutdown Postgres on the source node
8. copy the archived WAL files from /wal and the current log files from
   $PGDATA/pg_xlog from the source node onto the dest node into /wal and
   $PGDATA/pg_xlog, respectively
9. start Postgres on both nodes
10. do a "SELECT pg_stop_backup()" on both nodes

You could enhance step no. 8 by using scp directly in the recovery.conf.

Best,
Felix