Thread: Trying to use binary replication - from tutorial
Hello,
I am somewhat new with postgresql trying to find a good method of replication for my company.
I am running through the tutorials on binary replication for postgresql 9.1. Both servers are virtual box Ubuntu 10.10 on a laptop.
I ran this rsync -av --exclude pg_xlog --exclude postgresql.conf /var/lib/postgresql/9.1/main/* postgres:<ip-address of standby>:/var/lib/postgresql/9.1/main/
The standby server then will not restart. It says it is missing pg_xlog/0000000000 ... files
I ran the rsync again not excluding the pg_xlog. This time both servers restart but there doesn't seem to be any replication occurring.
I am curious why this is going on - why it didn't work excluding the pg_xlog. Was there something wrong with my syntax? Is there something I am missing not found in the tutorial?
I will appreciate any help, advice.
Thank you,
Evan
On Thu, Oct 13, 2011 at 9:41 AM, Evan Walter <ewalter@decisionanalyst.com> wrote: > > > Hello, > I am somewhat new with postgresql trying to find a good method of > replication for my company. > I am running through the tutorials on binary replication for postgresql > 9.1. Both servers are virtual box Ubuntu 10.10 on a laptop. > > I ran this rsync -av --exclude pg_xlog --exclude postgresql.conf > /var/lib/postgresql/9.1/main/* postgres:<ip-address of > standby>:/var/lib/postgresql/9.1/main/ Shouldn't the destination be: postgres@<ip-address of standby>:/var/lib/postgresql/9.1/main/
Yes, sorry. Type
Evan
Evan
On Thu, Oct 13, 2011 at 12:20 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Thu, Oct 13, 2011 at 9:41 AM, Evan WalterShouldn't the destination be:
<ewalter@decisionanalyst.com> wrote:
>
>
> Hello,
> I am somewhat new with postgresql trying to find a good method of
> replication for my company.
> I am running through the tutorials on binary replication for postgresql
> 9.1. Both servers are virtual box Ubuntu 10.10 on a laptop.
>
> I ran this rsync -av --exclude pg_xlog --exclude postgresql.conf
> /var/lib/postgresql/9.1/main/* postgres:<ip-address of
> standby>:/var/lib/postgresql/9.1/main/
postgres@<ip-address of standby>:/var/lib/postgresql/9.1/main/
Do not rsync the pg_xlog. Basically that error means that the restore_command in your recovery.conf is not working. You have hot_standby archiving going on the master and a recovery_command on the slave, right?
On Thu, Oct 13, 2011 at 10:41 AM, Evan Walter <ewalter@decisionanalyst.com> wrote:
Hello,
I am somewhat new with postgresql trying to find a good method of replication for my company.
I am running through the tutorials on binary replication for postgresql 9.1. Both servers are virtual box Ubuntu 10.10 on a laptop.
I ran this rsync -av --exclude pg_xlog --exclude postgresql.conf /var/lib/postgresql/9.1/main/* postgres:<ip-address of standby>:/var/lib/postgresql/9.1/main/
The standby server then will not restart. It says it is missing pg_xlog/0000000000 ... files
I ran the rsync again not excluding the pg_xlog. This time both servers restart but there doesn't seem to be any replication occurring.
I am curious why this is going on - why it didn't work excluding the pg_xlog. Was there something wrong with my syntax? Is there something I am missing not found in the tutorial?
I will appreciate any help, advice.
Thank you,
Evan
I pretty much didn't change anything in the config files except what was in the tutorial at http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial
on the slave I created a recovery.conf file containing:
standby_mode = 'on'
primary_conninfo = 'host=<servers ip here>'
on the master postgresql.conf I set
listen_address = '*'
wav_level = hot_standby
max_wal_senders = 3
I didn't change any of postgresql.conf Archiving settings. They all seem to be commented out. Could that be the problem?
Evan
on the slave I created a recovery.conf file containing:
standby_mode = 'on'
primary_conninfo = 'host=<servers ip here>'
on the master postgresql.conf I set
listen_address = '*'
wav_level = hot_standby
max_wal_senders = 3
I didn't change any of postgresql.conf Archiving settings. They all seem to be commented out. Could that be the problem?
Evan
On Thu, Oct 13, 2011 at 3:19 PM, Mark Keisler <qa4437@motorola.com> wrote:
Do not rsync the pg_xlog. Basically that error means that the restore_command in your recovery.conf is not working. You have hot_standby archiving going on the master and a recovery_command on the slave, right?On Thu, Oct 13, 2011 at 10:41 AM, Evan Walter <ewalter@decisionanalyst.com> wrote:
Hello,
I am somewhat new with postgresql trying to find a good method of replication for my company.
I am running through the tutorials on binary replication for postgresql 9.1. Both servers are virtual box Ubuntu 10.10 on a laptop.
I ran this rsync -av --exclude pg_xlog --exclude postgresql.conf /var/lib/postgresql/9.1/main/* postgres:<ip-address of standby>:/var/lib/postgresql/9.1/main/
The standby server then will not restart. It says it is missing pg_xlog/0000000000 ... files
I ran the rsync again not excluding the pg_xlog. This time both servers restart but there doesn't seem to be any replication occurring.
I am curious why this is going on - why it didn't work excluding the pg_xlog. Was there something wrong with my syntax? Is there something I am missing not found in the tutorial?
I will appreciate any help, advice.
Thank you,
Evan
In your recovery.conf, you should also have a restore_command setting. That's what the standby postgres will use to grab the archived WAL logs from the master and it needs to. So yes, you need archive set to on and an archive_command command setting on the master. You at least need that for catching up from whatever happened between the pg_start_backup, rsync from master to standby, then pg_stop_backup before it can start streaming replication. Those steps are in the tutorial you have mentioned :).
On Thu, Oct 13, 2011 at 3:31 PM, Evan Walter <ewalter@decisionanalyst.com> wrote:
I pretty much didn't change anything in the config files except what was in the tutorial at http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial
on the slave I created a recovery.conf file containing:
standby_mode = 'on'
primary_conninfo = 'host=<servers ip here>'
on the master postgresql.conf I set
listen_address = '*'
wav_level = hot_standby
max_wal_senders = 3
I didn't change any of postgresql.conf Archiving settings. They all seem to be commented out. Could that be the problem?
EvanOn Thu, Oct 13, 2011 at 3:19 PM, Mark Keisler <qa4437@motorola.com> wrote:Do not rsync the pg_xlog. Basically that error means that the restore_command in your recovery.conf is not working. You have hot_standby archiving going on the master and a recovery_command on the slave, right?On Thu, Oct 13, 2011 at 10:41 AM, Evan Walter <ewalter@decisionanalyst.com> wrote:
Hello,
I am somewhat new with postgresql trying to find a good method of replication for my company.
I am running through the tutorials on binary replication for postgresql 9.1. Both servers are virtual box Ubuntu 10.10 on a laptop.
I ran this rsync -av --exclude pg_xlog --exclude postgresql.conf /var/lib/postgresql/9.1/main/* postgres:<ip-address of standby>:/var/lib/postgresql/9.1/main/
The standby server then will not restart. It says it is missing pg_xlog/0000000000 ... files
I ran the rsync again not excluding the pg_xlog. This time both servers restart but there doesn't seem to be any replication occurring.
I am curious why this is going on - why it didn't work excluding the pg_xlog. Was there something wrong with my syntax? Is there something I am missing not found in the tutorial?
I will appreciate any help, advice.
Thank you,
Evan
Yes thank you.
I actually did get it working eventually, with the things you mentioned. Also, the recovery.conf has to be in the directory that contains the data folder! I previously had it in the folder with the config files, which in my installation are different. Once I moved the recovery.conf (this was actually figured out by a colleague), the replication was working beautifully.
Thanks
Evan Walter
I actually did get it working eventually, with the things you mentioned. Also, the recovery.conf has to be in the directory that contains the data folder! I previously had it in the folder with the config files, which in my installation are different. Once I moved the recovery.conf (this was actually figured out by a colleague), the replication was working beautifully.
Thanks
Evan Walter
On Mon, Oct 17, 2011 at 10:20 AM, Mark Keisler <qa4437@motorola.com> wrote:
In your recovery.conf, you should also have a restore_command setting. That's what the standby postgres will use to grab the archived WAL logs from the master and it needs to. So yes, you need archive set to on and an archive_command command setting on the master. You at least need that for catching up from whatever happened between the pg_start_backup, rsync from master to standby, then pg_stop_backup before it can start streaming replication. Those steps are in the tutorial you have mentioned :).On Thu, Oct 13, 2011 at 3:31 PM, Evan Walter <ewalter@decisionanalyst.com> wrote:I pretty much didn't change anything in the config files except what was in the tutorial at http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial
on the slave I created a recovery.conf file containing:
standby_mode = 'on'
primary_conninfo = 'host=<servers ip here>'
on the master postgresql.conf I set
listen_address = '*'
wav_level = hot_standby
max_wal_senders = 3
I didn't change any of postgresql.conf Archiving settings. They all seem to be commented out. Could that be the problem?
EvanOn Thu, Oct 13, 2011 at 3:19 PM, Mark Keisler <qa4437@motorola.com> wrote:Do not rsync the pg_xlog. Basically that error means that the restore_command in your recovery.conf is not working. You have hot_standby archiving going on the master and a recovery_command on the slave, right?On Thu, Oct 13, 2011 at 10:41 AM, Evan Walter <ewalter@decisionanalyst.com> wrote:
Hello,
I am somewhat new with postgresql trying to find a good method of replication for my company.
I am running through the tutorials on binary replication for postgresql 9.1. Both servers are virtual box Ubuntu 10.10 on a laptop.
I ran this rsync -av --exclude pg_xlog --exclude postgresql.conf /var/lib/postgresql/9.1/main/* postgres:<ip-address of standby>:/var/lib/postgresql/9.1/main/
The standby server then will not restart. It says it is missing pg_xlog/0000000000 ... files
I ran the rsync again not excluding the pg_xlog. This time both servers restart but there doesn't seem to be any replication occurring.
I am curious why this is going on - why it didn't work excluding the pg_xlog. Was there something wrong with my syntax? Is there something I am missing not found in the tutorial?
I will appreciate any help, advice.
Thank you,
Evan