Re: Configuring Standby Server in PostgreSQL 9.3.3

On 04/02/2014 09:07 PM, fburgess@radiantblue.com wrote:
> We are in the process of setting up a standby server. Instead of restoring from
> a backup taken from primary server,
> we want to shutdown the primary server, and then copy the primary database VM
> image to the standby server.
>
> We want to do this because our database is 7TB and takes 4-5 to backup and 8-9
> days to restore to the standby server.
>
> Mock Primary :192.168.1.50
> Mock standby:192.168.1.100
>
> 1.) Configure pg_hda.conf on primary
>
> # Allow the user "postgres" from host 192.168.1.100 to connect to the primary
> # as a replication standby if the user's password is correctly supplied.
> #
> # TYPE  DATABASE        USER            ADDRESS                 METHOD
> host    replication     postgres        192.168.1.100/32        md5
>
>
> 2.) Configure recovery.conf on standby
>
> standby_mode = 'on'
> primary_conninfo = 'host=192.168.1.50 port=5432 user=postgres password=foopass'
> restore_command = 'cp /path/to/archive/%f %p'
> archive_cleanup_command = 'pg_archivecleanup /path/to/archive %r'
>
> 3.) re-start both servers
>
> Question #1 Is this feasible?
> Question #2 Have I missed critical steps?

The short answer is: yes, it's feasible. I think you got steps right.

But I agree with the other discussions that it really shouldn't take
days to do a filesystem-level backup/restore of a 7TB database, which is
what you need to set up a standby. There must be some misunderstanding
there.

- Heikki