Обсуждение: bdr replication
Hello Everyone,
I am looking for suggestion how to recover bdr replication.
The short story we have 2 virtual nodes with share storage. Share storage lost power and after I brought all online bdr doesn't work properly.
Here are some log
2016-03-30 20:47:26 EDT postgres@10.12.160.2(43714):freeswitch_prod01 [15897] LOG: starting logical decoding for slot "bdr_16386_6242730220644137235_1_17937__"
2016-03-30 20:47:26 EDT postgres@10.12.160.2(43714):freeswitch_prod01 [15897] DETAIL: streaming transactions committing after 0/39A248B0, reading WAL from 0/39A248B0
2016-03-30 20:47:26 EDT postgres@10.12.160.2(43712):fusionpbx_prod01 [15896] LOG: starting logical decoding for slot "bdr_20650_6242730220644137235_1_22220__"
2016-03-30 20:47:26 EDT postgres@10.12.160.2(43712):fusionpbx_prod01 [15896] DETAIL: streaming transactions committing after 0/39A248B0, reading WAL from 0/39A248B0
2016-03-30 20:47:26 EDT postgres@10.12.160.2(43712):fusionpbx_prod01 [15896] ERROR: requested WAL segment 000000010000000000000039 has already been removed
2016-03-30 20:47:26 EDT postgres@10.12.160.2(43714):freeswitch_prod01 [15897] ERROR: requested WAL segment 000000010000000000000039 has already been removed
2016-03-30 20:47:26 EDT postgres@10.12.160.2(43712):fusionpbx_prod01 [15896] LOG: could not receive data from client: Connection reset by peer
2016-03-30 20:47:26 EDT postgres@10.12.160.2(43714):freeswitch_prod01 [15897] LOG: could not receive data from client: Connection reset by peer
2016-03-30 20:47:28 EDT @: [977] LOG: starting background worker process "bdr (6242981939774453042,1,20650,)->bdr (6242730220644137235,1,"
2016-03-30 20:47:28 EDT @: [977] LOG: starting background worker process "bdr (6242981939774453042,1,16386,)->bdr (6242730220644137235,1,"
2016-03-30 20:47:28 EDT @: [15899] NOTICE: version "1.0" of extension "btree_gist" is already installed
2016-03-30 20:47:28 EDT @: [15899] NOTICE: version "0.9.3.0" of extension "bdr" is already installed
2016-03-30 20:47:28 EDT @: [15898] NOTICE: version "1.0" of extension "btree_gist" is already installed
2016-03-30 20:47:28 EDT @: [15898] NOTICE: version "0.9.3.0" of extension "bdr" is already installed
2016-03-30 20:47:28 EDT @: [15899] INFO: starting up replication from 1 at 0/0
2016-03-30 20:47:28 EDT @: [15898] INFO: starting up replication from 8 at 0/0
2016-03-30 20:47:28 EDT @: [15899] ERROR: data stream ended
2016-03-30 20:47:28 EDT @: [15898] ERROR: data stream ended
2016-03-30 20:47:28 EDT @: [977] LOG: worker process: bdr (6242981939774453042,1,20650,)->bdr (6242730220644137235,1, (PID 15898) exited with exit code 1
2016-03-30 20:47:28 EDT @: [977] LOG: worker process: bdr (6242981939774453042,1,16386,)->bdr (6242730220644137235,1, (PID 15899) exited with exit code 1
2016-03-30 20:47:26 EDT postgres@10.12.160.2(43714):freeswitch_prod01 [15897] DETAIL: streaming transactions committing after 0/39A248B0, reading WAL from 0/39A248B0
2016-03-30 20:47:26 EDT postgres@10.12.160.2(43712):fusionpbx_prod01 [15896] LOG: starting logical decoding for slot "bdr_20650_6242730220644137235_1_22220__"
2016-03-30 20:47:26 EDT postgres@10.12.160.2(43712):fusionpbx_prod01 [15896] DETAIL: streaming transactions committing after 0/39A248B0, reading WAL from 0/39A248B0
2016-03-30 20:47:26 EDT postgres@10.12.160.2(43712):fusionpbx_prod01 [15896] ERROR: requested WAL segment 000000010000000000000039 has already been removed
2016-03-30 20:47:26 EDT postgres@10.12.160.2(43714):freeswitch_prod01 [15897] ERROR: requested WAL segment 000000010000000000000039 has already been removed
2016-03-30 20:47:26 EDT postgres@10.12.160.2(43712):fusionpbx_prod01 [15896] LOG: could not receive data from client: Connection reset by peer
2016-03-30 20:47:26 EDT postgres@10.12.160.2(43714):freeswitch_prod01 [15897] LOG: could not receive data from client: Connection reset by peer
2016-03-30 20:47:28 EDT @: [977] LOG: starting background worker process "bdr (6242981939774453042,1,20650,)->bdr (6242730220644137235,1,"
2016-03-30 20:47:28 EDT @: [977] LOG: starting background worker process "bdr (6242981939774453042,1,16386,)->bdr (6242730220644137235,1,"
2016-03-30 20:47:28 EDT @: [15899] NOTICE: version "1.0" of extension "btree_gist" is already installed
2016-03-30 20:47:28 EDT @: [15899] NOTICE: version "0.9.3.0" of extension "bdr" is already installed
2016-03-30 20:47:28 EDT @: [15898] NOTICE: version "1.0" of extension "btree_gist" is already installed
2016-03-30 20:47:28 EDT @: [15898] NOTICE: version "0.9.3.0" of extension "bdr" is already installed
2016-03-30 20:47:28 EDT @: [15899] INFO: starting up replication from 1 at 0/0
2016-03-30 20:47:28 EDT @: [15898] INFO: starting up replication from 8 at 0/0
2016-03-30 20:47:28 EDT @: [15899] ERROR: data stream ended
2016-03-30 20:47:28 EDT @: [15898] ERROR: data stream ended
2016-03-30 20:47:28 EDT @: [977] LOG: worker process: bdr (6242981939774453042,1,20650,)->bdr (6242730220644137235,1, (PID 15898) exited with exit code 1
2016-03-30 20:47:28 EDT @: [977] LOG: worker process: bdr (6242981939774453042,1,16386,)->bdr (6242730220644137235,1, (PID 15899) exited with exit code 1
On 31 March 2016 at 09:38, Slava Bendersky <volga629@skillsearch.ca> wrote:
Hello Everyone,I am looking for suggestion how to recover bdr replication.The short story we have 2 virtual nodes with share storage.
Can you describe the "shared storage" setup in more detail?
In general, with PostgreSQL "shared storage" is a shortcut to "massive database corruption" unless you have extremely careful fencing and STONITH.
Hello Craig,
The current setup is two server which run libvirt and for storage which run glusterfs (storage server feed two virtual servers). Right now is no fencing in place. Each of the nodes have one PostgreSQL vm with bdr.
volga629.
From: "Craig Ringer" <craig@2ndquadrant.com>
To: "volga629" <volga629@skillsearch.ca>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Wednesday, 30 March, 2016 23:20:49
Subject: Re: [GENERAL] bdr replication
To: "volga629" <volga629@skillsearch.ca>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Wednesday, 30 March, 2016 23:20:49
Subject: Re: [GENERAL] bdr replication
On 31 March 2016 at 09:38, Slava Bendersky <volga629@skillsearch.ca> wrote:
Hello Everyone,I am looking for suggestion how to recover bdr replication.The short story we have 2 virtual nodes with share storage.
Can you describe the "shared storage" setup in more detail?
In general, with PostgreSQL "shared storage" is a shortcut to "massive database corruption" unless you have extremely careful fencing and STONITH.
--
On 31 March 2016 at 10:43, Slava Bendersky <volga629@skillsearch.ca> wrote:
Hello Craig,The current setup is two server which run libvirt and for storage which run glusterfs (storage server feed two virtual servers). Right now is no fencing in place. Each of the nodes have one PostgreSQL vm with bdr.
That's a disaster waiting to happen. You can't just share storage like that in PostgreSQL, BDR or otherwise. I'm amazed it didn't fail earlier.
Try to dump whatever data you can recover, initdb a new normal PostgreSQL instance, restore, and set up normal replication. Look into repmgr, pgbarman, etc. Read the manual on replication, backup and failover. Do _not_ use shared storage.
Hello Craig,
Is any share storage technology recommended for PostgreSQL in virtual environment ?
Ok what I will do is going take backups, shutdown both virtual servers and place all vm use local disk on server only.
volga629
From: "Craig Ringer" <craig@2ndquadrant.com>
To: "volga629" <volga629@skillsearch.ca>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Wednesday, 30 March, 2016 23:57:28
Subject: Re: [GENERAL] bdr replication
To: "volga629" <volga629@skillsearch.ca>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Wednesday, 30 March, 2016 23:57:28
Subject: Re: [GENERAL] bdr replication
On 31 March 2016 at 10:43, Slava Bendersky <volga629@skillsearch.ca> wrote:
Hello Craig,The current setup is two server which run libvirt and for storage which run glusterfs (storage server feed two virtual servers). Right now is no fencing in place. Each of the nodes have one PostgreSQL vm with bdr.
That's a disaster waiting to happen. You can't just share storage like that in PostgreSQL, BDR or otherwise. I'm amazed it didn't fail earlier.
Try to dump whatever data you can recover, initdb a new normal PostgreSQL instance, restore, and set up normal replication. Look into repmgr, pgbarman, etc. Read the manual on replication, backup and failover. Do _not_ use shared storage.
--
On 3/30/2016 8:09 PM, Slava Bendersky wrote: > Is any share storage technology recommended for PostgreSQL in virtual > environment ? > Ok what I will do is going take backups, shutdown both virtual servers > and place all vm use local disk on server only. 'share storage technology'... um. thats such a vague term, it can mean lots of things. each postgres instance needs its own data store, two instances can NOT share the same files under any condition. these data stores can be on SAN or NAS, as long the storage is reliable about committed random writes, and as long as two different servers aren't using the SAME directory for their data stores. -- john r pierce, recycling bits in santa cruz
In my case only virtual hosts are use share storage (feed from glusterfs), but actual virtual machines have own separate disks and all PostgreSQL run on separate data directories.
volga629
From: "John R Pierce" <pierce@hogranch.com>
To: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Thursday, 31 March, 2016 00:34:55
Subject: Re: [GENERAL] bdr replication
To: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Thursday, 31 March, 2016 00:34:55
Subject: Re: [GENERAL] bdr replication
On 3/30/2016 8:09 PM, Slava Bendersky wrote:
> Is any share storage technology recommended for PostgreSQL in virtual
> environment ?
> Ok what I will do is going take backups, shutdown both virtual servers
> and place all vm use local disk on server only.
'share storage technology'... um. thats such a vague term, it can
mean lots of things.
each postgres instance needs its own data store, two instances can NOT
share the same files under any condition. these data stores can be
on SAN or NAS, as long the storage is reliable about committed random
writes, and as long as two different servers aren't using the SAME
directory for their data stores.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> Is any share storage technology recommended for PostgreSQL in virtual
> environment ?
> Ok what I will do is going take backups, shutdown both virtual servers
> and place all vm use local disk on server only.
'share storage technology'... um. thats such a vague term, it can
mean lots of things.
each postgres instance needs its own data store, two instances can NOT
share the same files under any condition. these data stores can be
on SAN or NAS, as long the storage is reliable about committed random
writes, and as long as two different servers aren't using the SAME
directory for their data stores.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello Everyone,
Is possible recovery from my situation at all ? I was looking on tool which might will help and only bdr_init_copy. If possible initialize second node again ? Also is it good idea enable wal archiving with bdr ?
volga629
From: "volga629" <volga629@skillsearch.ca>
To: "John R Pierce" <pierce@hogranch.com>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Thursday, 31 March, 2016 00:57:13
Subject: Re: [GENERAL] bdr replication
To: "John R Pierce" <pierce@hogranch.com>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Thursday, 31 March, 2016 00:57:13
Subject: Re: [GENERAL] bdr replication
In my case only virtual hosts are use share storage (feed from glusterfs), but actual virtual machines have own separate disks and all PostgreSQL run on separate data directories.
volga629
From: "John R Pierce" <pierce@hogranch.com>
To: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Thursday, 31 March, 2016 00:34:55
Subject: Re: [GENERAL] bdr replication
To: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Thursday, 31 March, 2016 00:34:55
Subject: Re: [GENERAL] bdr replication
On 3/30/2016 8:09 PM, Slava Bendersky wrote:
> Is any share storage technology recommended for PostgreSQL in virtual
> environment ?
> Ok what I will do is going take backups, shutdown both virtual servers
> and place all vm use local disk on server only.
'share storage technology'... um. thats such a vague term, it can
mean lots of things.
each postgres instance needs its own data store, two instances can NOT
share the same files under any condition. these data stores can be
on SAN or NAS, as long the storage is reliable about committed random
writes, and as long as two different servers aren't using the SAME
directory for their data stores.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> Is any share storage technology recommended for PostgreSQL in virtual
> environment ?
> Ok what I will do is going take backups, shutdown both virtual servers
> and place all vm use local disk on server only.
'share storage technology'... um. thats such a vague term, it can
mean lots of things.
each postgres instance needs its own data store, two instances can NOT
share the same files under any condition. these data stores can be
on SAN or NAS, as long the storage is reliable about committed random
writes, and as long as two different servers aren't using the SAME
directory for their data stores.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
What's the purpose of such configuration? Doesn't makes sense for me. The only reasonable case where you would want to putthe data folder on a shared storage is for usage with warm standby, where you can have a secondary server which servesas a read-only replica, and can be rpomoted to master on master failure. If you intend high availability, you'd rather try it at VM level, like vmware HA or Proxmox HA. That will make your VM runon any hypervisor in the group disregarding the failure of some node. Regards, Alvaro Aguayo Jefe de Operaciones Open Comb Systems E.I.R.L. Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248 Website: www.ocs.pe ----- Original Message ----- From: "Slava Bendersky" <volga629@skillsearch.ca> To: "John R Pierce" <pierce@hogranch.com> Cc: "pgsql-general" <pgsql-general@postgresql.org> Sent: Wednesday, 30 March, 2016 10:57:13 PM Subject: Re: [GENERAL] bdr replication In my case only virtual hosts are use share storage (feed from glusterfs), but actual virtual machines have own separatedisks and all PostgreSQL run on separate data directories. volga629 ----- Original Message ----- From: "John R Pierce" <pierce@hogranch.com> To: "pgsql-general" <pgsql-general@postgresql.org> Sent: Thursday, 31 March, 2016 00:34:55 Subject: Re: [GENERAL] bdr replication On 3/30/2016 8:09 PM, Slava Bendersky wrote: > Is any share storage technology recommended for PostgreSQL in virtual > environment ? > Ok what I will do is going take backups, shutdown both virtual servers > and place all vm use local disk on server only. 'share storage technology'... um. thats such a vague term, it can mean lots of things. each postgres instance needs its own data store, two instances can NOT share the same files under any condition. these data stores can be on SAN or NAS, as long the storage is reliable about committed random writes, and as long as two different servers aren't using the SAME directory for their data stores. -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Hello Alvaro,
We running BDR where each PostgreSQL vm is a master and shared storage only on hypervisor level. All vm leave with own virtual disk. Right now we have 2 server for redundancy which have shared network between them. Issue that BDR is working right now see log below. And my question how to restore BDR replication correctly.
volga629
From: "Alvaro Aguayo Garcia-Rada" <aaguayo@opensysperu.com>
To: "volga629" <volga629@skillsearch.ca>
Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" <pierce@hogranch.com>
Sent: Thursday, 31 March, 2016 02:19:42
Subject: Re: [GENERAL] bdr replication
To: "volga629" <volga629@skillsearch.ca>
Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" <pierce@hogranch.com>
Sent: Thursday, 31 March, 2016 02:19:42
Subject: Re: [GENERAL] bdr replication
What's the purpose of such configuration? Doesn't makes sense for me. The only reasonable case where you would want to put the data folder on a shared storage is for usage with warm standby, where you can have a secondary server which serves as a read-only replica, and can be rpomoted to master on master failure.
If you intend high availability, you'd rather try it at VM level, like vmware HA or Proxmox HA. That will make your VM run on any hypervisor in the group disregarding the failure of some node.
Regards,
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe
----- Original Message -----
From: "Slava Bendersky" <volga629@skillsearch.ca>
To: "John R Pierce" <pierce@hogranch.com>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Wednesday, 30 March, 2016 10:57:13 PM
Subject: Re: [GENERAL] bdr replication
In my case only virtual hosts are use share storage (feed from glusterfs), but actual virtual machines have own separate disks and all PostgreSQL run on separate data directories.
volga629
----- Original Message -----
From: "John R Pierce" <pierce@hogranch.com>
To: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Thursday, 31 March, 2016 00:34:55
Subject: Re: [GENERAL] bdr replication
On 3/30/2016 8:09 PM, Slava Bendersky wrote:
> Is any share storage technology recommended for PostgreSQL in virtual
> environment ?
> Ok what I will do is going take backups, shutdown both virtual servers
> and place all vm use local disk on server only.
'share storage technology'... um. thats such a vague term, it can
mean lots of things.
each postgres instance needs its own data store, two instances can NOT
share the same files under any condition. these data stores can be
on SAN or NAS, as long the storage is reliable about committed random
writes, and as long as two different servers aren't using the SAME
directory for their data stores.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
If you intend high availability, you'd rather try it at VM level, like vmware HA or Proxmox HA. That will make your VM run on any hypervisor in the group disregarding the failure of some node.
Regards,
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe
----- Original Message -----
From: "Slava Bendersky" <volga629@skillsearch.ca>
To: "John R Pierce" <pierce@hogranch.com>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Wednesday, 30 March, 2016 10:57:13 PM
Subject: Re: [GENERAL] bdr replication
In my case only virtual hosts are use share storage (feed from glusterfs), but actual virtual machines have own separate disks and all PostgreSQL run on separate data directories.
volga629
----- Original Message -----
From: "John R Pierce" <pierce@hogranch.com>
To: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Thursday, 31 March, 2016 00:34:55
Subject: Re: [GENERAL] bdr replication
On 3/30/2016 8:09 PM, Slava Bendersky wrote:
> Is any share storage technology recommended for PostgreSQL in virtual
> environment ?
> Ok what I will do is going take backups, shutdown both virtual servers
> and place all vm use local disk on server only.
'share storage technology'... um. thats such a vague term, it can
mean lots of things.
each postgres instance needs its own data store, two instances can NOT
share the same files under any condition. these data stores can be
on SAN or NAS, as long the storage is reliable about committed random
writes, and as long as two different servers aren't using the SAME
directory for their data stores.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
We are overlaping mails :P What I don't understand is the need of a shared storage in this case. It would be a lot better to have the data folder insideeach server virtual disk to avoid troubles with the shared storage; I really see no reason for such configuration here. Now, getting into the solution rather than in the problem. I suggest you to do the following: 1. First of all, backup your data folder for both nodes. Just in case. Make backup with postgres stopped to avoid problems. 2. Choose one node which will be considered up-to-date(Let's say "Node A") 3. Dump your database(s) on that node, excluding the bdr schema on each db. Dump also your globals 4. Wipe or rename your data folder on each node, and then initialize each node. Do not configure BDR yet. 5. Restore your data(backed up at step 3) on Node A 6. Configure BDR on Node A 7. Add Node B to the replication group, using "bdr_init_copy" to make it replicate from Node A. That should do the trick. There is another possibility: Drop the replication configuration no Node A, and then start fromscratch(Steps 1, 6 & 7). However, this can be troublesome, as it involves editing bdr & postgres schemas, and that canlead you to problems on the future, so I'd recommend you the "long" way. Feel free to ask any question regarding this issue. Looks serious.... Alvaro Aguayo Jefe de Operaciones Open Comb Systems E.I.R.L. Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248 Website: www.ocs.pe ----- Original Message ----- From: "Slava Bendersky" <volga629@skillsearch.ca> To: "Alvaro Aguayo Garcia-Rada" <aaguayo@opensysperu.com> Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" <pierce@hogranch.com> Sent: Thursday, 31 March, 2016 12:28:09 AM Subject: Re: [GENERAL] bdr replication Hello Alvaro, We running BDR where each PostgreSQL vm is a master and shared storage only on hypervisor level. All vm leave with own virtualdisk. Right now we have 2 server for redundancy which have shared network between them. Issue that BDR is workingright now see log below. And my question how to restore BDR replication correctly. volga629 ----- Original Message ----- From: "Alvaro Aguayo Garcia-Rada" <aaguayo@opensysperu.com> To: "volga629" <volga629@skillsearch.ca> Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" <pierce@hogranch.com> Sent: Thursday, 31 March, 2016 02:19:42 Subject: Re: [GENERAL] bdr replication What's the purpose of such configuration? Doesn't makes sense for me. The only reasonable case where you would want to putthe data folder on a shared storage is for usage with warm standby, where you can have a secondary server which servesas a read-only replica, and can be rpomoted to master on master failure. If you intend high availability, you'd rather try it at VM level, like vmware HA or Proxmox HA. That will make your VM runon any hypervisor in the group disregarding the failure of some node. Regards, Alvaro Aguayo Jefe de Operaciones Open Comb Systems E.I.R.L. Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248 Website: www.ocs.pe ----- Original Message ----- From: "Slava Bendersky" <volga629@skillsearch.ca> To: "John R Pierce" <pierce@hogranch.com> Cc: "pgsql-general" <pgsql-general@postgresql.org> Sent: Wednesday, 30 March, 2016 10:57:13 PM Subject: Re: [GENERAL] bdr replication In my case only virtual hosts are use share storage (feed from glusterfs), but actual virtual machines have own separatedisks and all PostgreSQL run on separate data directories. volga629 ----- Original Message ----- From: "John R Pierce" <pierce@hogranch.com> To: "pgsql-general" <pgsql-general@postgresql.org> Sent: Thursday, 31 March, 2016 00:34:55 Subject: Re: [GENERAL] bdr replication On 3/30/2016 8:09 PM, Slava Bendersky wrote: > Is any share storage technology recommended for PostgreSQL in virtual > environment ? > Ok what I will do is going take backups, shutdown both virtual servers > and place all vm use local disk on server only. 'share storage technology'... um. thats such a vague term, it can mean lots of things. each postgres instance needs its own data store, two instances can NOT share the same files under any condition. these data stores can be on SAN or NAS, as long the storage is reliable about committed random writes, and as long as two different servers aren't using the SAME directory for their data stores. -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 3/30/2016 10:41 PM, Alvaro Aguayo Garcia-Rada wrote: > What I don't understand is the need of a shared storage in this case. It would be a lot better to have the data folderinside each server virtual disk to avoid troubles with the shared storage; I really see no reason for such configurationhere. I believe what the original poster is trying to say, his physical servers have a shared storage pool, but each VM has its own private virtual disks allocated out of this shared pool, and thats what his BDR database servers are using. this should be fine, so its a BDR problem. how to diagnose and repair BDR problems. I have no experience running BDR, so will step out of this thread (please delete me from CC's). -- john r pierce, recycling bits in santa cruz
Hello Alvaro,
That sound like a good plan. I will trying tomorrow, because need maintenance window.
To do back up I can use something like
bdr_dump -Fp -h localhost -U postgres mydb -f /tmp/mydb.data > --data-only --exclude-table='bdr_*
volga629
From: "Alvaro Aguayo Garcia-Rada" <aaguayo@opensysperu.com>
To: "volga629" <volga629@skillsearch.ca>
Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" <pierce@hogranch.com>
Sent: Thursday, 31 March, 2016 02:41:17
Subject: Re: [GENERAL] bdr replication
To: "volga629" <volga629@skillsearch.ca>
Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" <pierce@hogranch.com>
Sent: Thursday, 31 March, 2016 02:41:17
Subject: Re: [GENERAL] bdr replication
We are overlaping mails :P
What I don't understand is the need of a shared storage in this case. It would be a lot better to have the data folder inside each server virtual disk to avoid troubles with the shared storage; I really see no reason for such configuration here.
Now, getting into the solution rather than in the problem. I suggest you to do the following:
1. First of all, backup your data folder for both nodes. Just in case. Make backup with postgres stopped to avoid problems.
2. Choose one node which will be considered up-to-date(Let's say "Node A")
3. Dump your database(s) on that node, excluding the bdr schema on each db. Dump also your globals
4. Wipe or rename your data folder on each node, and then initialize each node. Do not configure BDR yet.
5. Restore your data(backed up at step 3) on Node A
6. Configure BDR on Node A
7. Add Node B to the replication group, using "bdr_init_copy" to make it replicate from Node A.
That should do the trick. There is another possibility: Drop the replication configuration no Node A, and then start from scratch(Steps 1, 6 & 7). However, this can be troublesome, as it involves editing bdr & postgres schemas, and that can lead you to problems on the future, so I'd recommend you the "long" way.
Feel free to ask any question regarding this issue. Looks serious....
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe
----- Original Message -----
From: "Slava Bendersky" <volga629@skillsearch.ca>
To: "Alvaro Aguayo Garcia-Rada" <aaguayo@opensysperu.com>
Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" <pierce@hogranch.com>
Sent: Thursday, 31 March, 2016 12:28:09 AM
Subject: Re: [GENERAL] bdr replication
Hello Alvaro,
We running BDR where each PostgreSQL vm is a master and shared storage only on hypervisor level. All vm leave with own virtual disk. Right now we have 2 server for redundancy which have shared network between them. Issue that BDR is working right now see log below. And my question how to restore BDR replication correctly.
volga629
----- Original Message -----
From: "Alvaro Aguayo Garcia-Rada" <aaguayo@opensysperu.com>
To: "volga629" <volga629@skillsearch.ca>
Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" <pierce@hogranch.com>
Sent: Thursday, 31 March, 2016 02:19:42
Subject: Re: [GENERAL] bdr replication
What's the purpose of such configuration? Doesn't makes sense for me. The only reasonable case where you would want to put the data folder on a shared storage is for usage with warm standby, where you can have a secondary server which serves as a read-only replica, and can be rpomoted to master on master failure.
If you intend high availability, you'd rather try it at VM level, like vmware HA or Proxmox HA. That will make your VM run on any hypervisor in the group disregarding the failure of some node.
Regards,
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe
----- Original Message -----
From: "Slava Bendersky" <volga629@skillsearch.ca>
To: "John R Pierce" <pierce@hogranch.com>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Wednesday, 30 March, 2016 10:57:13 PM
Subject: Re: [GENERAL] bdr replication
In my case only virtual hosts are use share storage (feed from glusterfs), but actual virtual machines have own separate disks and all PostgreSQL run on separate data directories.
volga629
----- Original Message -----
From: "John R Pierce" <pierce@hogranch.com>
To: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Thursday, 31 March, 2016 00:34:55
Subject: Re: [GENERAL] bdr replication
On 3/30/2016 8:09 PM, Slava Bendersky wrote:
> Is any share storage technology recommended for PostgreSQL in virtual
> environment ?
> Ok what I will do is going take backups, shutdown both virtual servers
> and place all vm use local disk on server only.
'share storage technology'... um. thats such a vague term, it can
mean lots of things.
each postgres instance needs its own data store, two instances can NOT
share the same files under any condition. these data stores can be
on SAN or NAS, as long the storage is reliable about committed random
writes, and as long as two different servers aren't using the SAME
directory for their data stores.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
What I don't understand is the need of a shared storage in this case. It would be a lot better to have the data folder inside each server virtual disk to avoid troubles with the shared storage; I really see no reason for such configuration here.
Now, getting into the solution rather than in the problem. I suggest you to do the following:
1. First of all, backup your data folder for both nodes. Just in case. Make backup with postgres stopped to avoid problems.
2. Choose one node which will be considered up-to-date(Let's say "Node A")
3. Dump your database(s) on that node, excluding the bdr schema on each db. Dump also your globals
4. Wipe or rename your data folder on each node, and then initialize each node. Do not configure BDR yet.
5. Restore your data(backed up at step 3) on Node A
6. Configure BDR on Node A
7. Add Node B to the replication group, using "bdr_init_copy" to make it replicate from Node A.
That should do the trick. There is another possibility: Drop the replication configuration no Node A, and then start from scratch(Steps 1, 6 & 7). However, this can be troublesome, as it involves editing bdr & postgres schemas, and that can lead you to problems on the future, so I'd recommend you the "long" way.
Feel free to ask any question regarding this issue. Looks serious....
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe
----- Original Message -----
From: "Slava Bendersky" <volga629@skillsearch.ca>
To: "Alvaro Aguayo Garcia-Rada" <aaguayo@opensysperu.com>
Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" <pierce@hogranch.com>
Sent: Thursday, 31 March, 2016 12:28:09 AM
Subject: Re: [GENERAL] bdr replication
Hello Alvaro,
We running BDR where each PostgreSQL vm is a master and shared storage only on hypervisor level. All vm leave with own virtual disk. Right now we have 2 server for redundancy which have shared network between them. Issue that BDR is working right now see log below. And my question how to restore BDR replication correctly.
volga629
----- Original Message -----
From: "Alvaro Aguayo Garcia-Rada" <aaguayo@opensysperu.com>
To: "volga629" <volga629@skillsearch.ca>
Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" <pierce@hogranch.com>
Sent: Thursday, 31 March, 2016 02:19:42
Subject: Re: [GENERAL] bdr replication
What's the purpose of such configuration? Doesn't makes sense for me. The only reasonable case where you would want to put the data folder on a shared storage is for usage with warm standby, where you can have a secondary server which serves as a read-only replica, and can be rpomoted to master on master failure.
If you intend high availability, you'd rather try it at VM level, like vmware HA or Proxmox HA. That will make your VM run on any hypervisor in the group disregarding the failure of some node.
Regards,
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe
----- Original Message -----
From: "Slava Bendersky" <volga629@skillsearch.ca>
To: "John R Pierce" <pierce@hogranch.com>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Wednesday, 30 March, 2016 10:57:13 PM
Subject: Re: [GENERAL] bdr replication
In my case only virtual hosts are use share storage (feed from glusterfs), but actual virtual machines have own separate disks and all PostgreSQL run on separate data directories.
volga629
----- Original Message -----
From: "John R Pierce" <pierce@hogranch.com>
To: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Thursday, 31 March, 2016 00:34:55
Subject: Re: [GENERAL] bdr replication
On 3/30/2016 8:09 PM, Slava Bendersky wrote:
> Is any share storage technology recommended for PostgreSQL in virtual
> environment ?
> Ok what I will do is going take backups, shutdown both virtual servers
> and place all vm use local disk on server only.
'share storage technology'... um. thats such a vague term, it can
mean lots of things.
each postgres instance needs its own data store, two instances can NOT
share the same files under any condition. these data stores can be
on SAN or NAS, as long the storage is reliable about committed random
writes, and as long as two different servers aren't using the SAME
directory for their data stores.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I'd rather use something like this:
bdr_dump -N bdr -h localhost -U postgres -W mydb > /backup.sql
That's for each database. You would restore it, you must first create the empty database:
CREATE DATABASE mydb WITH ENCODING 'UTF-8' TEMPLATE template0;
(change encoding according to your needs. Run "psql --list" on your current system to see current encoding for each db)
Then, restore its contents:
cat /backup.sql | psql mydb
During restore, you may see some errors due to the backup containing the bdr truncate trigger on each table; it's normal at this point, so just ignore it.
You may need to backup your postgres users:
pg_dumpall -g > /globals.sql
And restore it BEFORE any database with
cat /globals.sql | psql
Backing up and restoring globals may be important when rewtoring databases, mostly to preserve permissions on tables/sequences during each database restore.
Regards,
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe
Sent from my Sony Xperia™ smartphone
---- Slava Bendersky wrote ----
Hello Alvaro,
That sound like a good plan. I will trying tomorrow, because need maintenance window.
To do back up I can use something like
bdr_dump -Fp -h localhost -U postgres mydb -f /tmp/mydb.data > --data-only --exclude-table='bdr_*
volga629
From: "Alvaro Aguayo Garcia-Rada" <aaguayo@opensysperu.com>
To: "volga629" <volga629@skillsearch.ca>
Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" <pierce@hogranch.com>
Sent: Thursday, 31 March, 2016 02:41:17
Subject: Re: [GENERAL] bdr replication
To: "volga629" <volga629@skillsearch.ca>
Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" <pierce@hogranch.com>
Sent: Thursday, 31 March, 2016 02:41:17
Subject: Re: [GENERAL] bdr replication
We are overlaping mails :P
What I don't understand is the need of a shared storage in this case. It would be a lot better to have the data folder inside each server virtual disk to avoid troubles with the shared storage; I really see no reason for such configuration here.
Now, getting into the solution rather than in the problem. I suggest you to do the following:
1. First of all, backup your data folder for both nodes. Just in case. Make backup with postgres stopped to avoid problems.
2. Choose one node which will be considered up-to-date(Let's say "Node A")
3. Dump your database(s) on that node, excluding the bdr schema on each db. Dump also your globals
4. Wipe or rename your data folder on each node, and then initialize each node. Do not configure BDR yet.
5. Restore your data(backed up at step 3) on Node A
6. Configure BDR on Node A
7. Add Node B to the replication group, using "bdr_init_copy" to make it replicate from Node A.
That should do the trick. There is another possibility: Drop the replication configuration no Node A, and then start from scratch(Steps 1, 6 & 7). However, this can be troublesome, as it involves editing bdr & postgres schemas, and that can lead you to problems on the future, so I'd recommend you the "long" way.
Feel free to ask any question regarding this issue. Looks serious....
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe
----- Original Message -----
From: "Slava Bendersky" <volga629@skillsearch.ca>
To: "Alvaro Aguayo Garcia-Rada" <aaguayo@opensysperu.com>
Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" <pierce@hogranch.com>
Sent: Thursday, 31 March, 2016 12:28:09 AM
Subject: Re: [GENERAL] bdr replication
Hello Alvaro,
We running BDR where each PostgreSQL vm is a master and shared storage only on hypervisor level. All vm leave with own virtual disk. Right now we have 2 server for redundancy which have shared network between them. Issue that BDR is working right now see log below. And my question how to restore BDR replication correctly.
volga629
----- Original Message -----
From: "Alvaro Aguayo Garcia-Rada" <aaguayo@opensysperu.com>
To: "volga629" <volga629@skillsearch.ca>
Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" <pierce@hogranch.com>
Sent: Thursday, 31 March, 2016 02:19:42
Subject: Re: [GENERAL] bdr replication
What's the purpose of such configuration? Doesn't makes sense for me. The only reasonable case where you would want to put the data folder on a shared storage is for usage with warm standby, where you can have a secondary server which serves as a read-only replica, and can be rpomoted to master on master failure.
If you intend high availability, you'd rather try it at VM level, like vmware HA or Proxmox HA. That will make your VM run on any hypervisor in the group disregarding the failure of some node.
Regards,
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe
----- Original Message -----
From: "Slava Bendersky" <volga629@skillsearch.ca>
To: "John R Pierce" <pierce@hogranch.com>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Wednesday, 30 March, 2016 10:57:13 PM
Subject: Re: [GENERAL] bdr replication
In my case only virtual hosts are use share storage (feed from glusterfs), but actual virtual machines have own separate disks and all PostgreSQL run on separate data directories.
volga629
----- Original Message -----
From: "John R Pierce" <pierce@hogranch.com>
To: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Thursday, 31 March, 2016 00:34:55
Subject: Re: [GENERAL] bdr replication
On 3/30/2016 8:09 PM, Slava Bendersky wrote:
> Is any share storage technology recommended for PostgreSQL in virtual
> environment ?
> Ok what I will do is going take backups, shutdown both virtual servers
> and place all vm use local disk on server only.
'share storage technology'... um. thats such a vague term, it can
mean lots of things.
each postgres instance needs its own data store, two instances can NOT
share the same files under any condition. these data stores can be
on SAN or NAS, as long the storage is reliable about committed random
writes, and as long as two different servers aren't using the SAME
directory for their data stores.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
What I don't understand is the need of a shared storage in this case. It would be a lot better to have the data folder inside each server virtual disk to avoid troubles with the shared storage; I really see no reason for such configuration here.
Now, getting into the solution rather than in the problem. I suggest you to do the following:
1. First of all, backup your data folder for both nodes. Just in case. Make backup with postgres stopped to avoid problems.
2. Choose one node which will be considered up-to-date(Let's say "Node A")
3. Dump your database(s) on that node, excluding the bdr schema on each db. Dump also your globals
4. Wipe or rename your data folder on each node, and then initialize each node. Do not configure BDR yet.
5. Restore your data(backed up at step 3) on Node A
6. Configure BDR on Node A
7. Add Node B to the replication group, using "bdr_init_copy" to make it replicate from Node A.
That should do the trick. There is another possibility: Drop the replication configuration no Node A, and then start from scratch(Steps 1, 6 & 7). However, this can be troublesome, as it involves editing bdr & postgres schemas, and that can lead you to problems on the future, so I'd recommend you the "long" way.
Feel free to ask any question regarding this issue. Looks serious....
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe
----- Original Message -----
From: "Slava Bendersky" <volga629@skillsearch.ca>
To: "Alvaro Aguayo Garcia-Rada" <aaguayo@opensysperu.com>
Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" <pierce@hogranch.com>
Sent: Thursday, 31 March, 2016 12:28:09 AM
Subject: Re: [GENERAL] bdr replication
Hello Alvaro,
We running BDR where each PostgreSQL vm is a master and shared storage only on hypervisor level. All vm leave with own virtual disk. Right now we have 2 server for redundancy which have shared network between them. Issue that BDR is working right now see log below. And my question how to restore BDR replication correctly.
volga629
----- Original Message -----
From: "Alvaro Aguayo Garcia-Rada" <aaguayo@opensysperu.com>
To: "volga629" <volga629@skillsearch.ca>
Cc: "pgsql-general" <pgsql-general@postgresql.org>, "John R Pierce" <pierce@hogranch.com>
Sent: Thursday, 31 March, 2016 02:19:42
Subject: Re: [GENERAL] bdr replication
What's the purpose of such configuration? Doesn't makes sense for me. The only reasonable case where you would want to put the data folder on a shared storage is for usage with warm standby, where you can have a secondary server which serves as a read-only replica, and can be rpomoted to master on master failure.
If you intend high availability, you'd rather try it at VM level, like vmware HA or Proxmox HA. That will make your VM run on any hypervisor in the group disregarding the failure of some node.
Regards,
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe
----- Original Message -----
From: "Slava Bendersky" <volga629@skillsearch.ca>
To: "John R Pierce" <pierce@hogranch.com>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Wednesday, 30 March, 2016 10:57:13 PM
Subject: Re: [GENERAL] bdr replication
In my case only virtual hosts are use share storage (feed from glusterfs), but actual virtual machines have own separate disks and all PostgreSQL run on separate data directories.
volga629
----- Original Message -----
From: "John R Pierce" <pierce@hogranch.com>
To: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Thursday, 31 March, 2016 00:34:55
Subject: Re: [GENERAL] bdr replication
On 3/30/2016 8:09 PM, Slava Bendersky wrote:
> Is any share storage technology recommended for PostgreSQL in virtual
> environment ?
> Ok what I will do is going take backups, shutdown both virtual servers
> and place all vm use local disk on server only.
'share storage technology'... um. thats such a vague term, it can
mean lots of things.
each postgres instance needs its own data store, two instances can NOT
share the same files under any condition. these data stores can be
on SAN or NAS, as long the storage is reliable about committed random
writes, and as long as two different servers aren't using the SAME
directory for their data stores.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general