Обсуждение: [ADMIN] Data replication to PostgreSQL
Hi team,
We have an use case to copy the data from Salesforce to PostgreSQL environment but because of DDL changes that can happen to tables from Salesforce application side, replication would eventually fail. Do we have any open source tools that can also capture the DDL changes and replicate to PostgreSQL?
Thank you in advance,
Ram
I'm not sure I understand what you're doing. Nearly all DDL is written to the WAL segments. The only ones that aren't are things like unlogged and temporary tables. I don't think temporary tables used by queries are written either. Why do you think, however, that this isn't captured?
--
Jay--
On Thu, Dec 29, 2016 at 9:35 AM, Ram DBA <postgresdbas@gmail.com> wrote:
Hi team,We have an use case to copy the data from Salesforce to PostgreSQL environment but because of DDL changes that can happen to tables from Salesforce application side, replication would eventually fail. Do we have any open source tools that can also capture the DDL changes and replicate to PostgreSQL?Thank you in advance,Ram
On Thu, Dec 29, 2016 at 8:12 AM, John Scalia <jayknowsunix@gmail.com> wrote: > I'm not sure I understand what you're doing. Nearly all DDL is written to > the WAL segments. The only ones that aren't are things like unlogged and > temporary tables. I don't think temporary tables used by queries are written > either. Why do you think, however, that this isn't captured? Maybe OP is thinking about Slony replication?
Hmmm... I suppose that would depend what your Salesforce db is. This may not be the appropriate forum for this question, as PostgreSQL could certainly support all DDL changes. With no experience for Salesforce, I'm at loss to tell you what you would need.
--
Jay--
On Thu, Dec 29, 2016 at 10:46 AM, Ram DBA <postgresdbas@gmail.com> wrote:
Hello John,Thank you for the reply. Our requirement is to copy the data from Salesforce to our local PostgreSQL database for which we had to rely on 3rd party tools. But because of dynamic nature from application, the back-end tables in Salesforce are being changed frequently thus stalling the data replication to PostgreSQL.Is there any tool that also capture and replay DDL changes from Salesforce to PostgreSQL.Hope I am clear with the details now, kindly let me know in case of further questions.Thank you in advance,RamOn Thu, Dec 29, 2016 at 8:42 PM, John Scalia <jayknowsunix@gmail.com> wrote:I'm not sure I understand what you're doing. Nearly all DDL is written to the WAL segments. The only ones that aren't are things like unlogged and temporary tables. I don't think temporary tables used by queries are written either. Why do you think, however, that this isn't captured?Jay
--On Thu, Dec 29, 2016 at 9:35 AM, Ram DBA <postgresdbas@gmail.com> wrote:Hi team,We have an use case to copy the data from Salesforce to PostgreSQL environment but because of DDL changes that can happen to tables from Salesforce application side, replication would eventually fail. Do we have any open source tools that can also capture the DDL changes and replicate to PostgreSQL?Thank you in advance,Ram
Sounds like you are not using streaming replication. Can you confirm your standby has a recovery.conf file in $PGDATA ? Isn't Saleforce running with Streaming replication? If you want a proven solution running on Postgres, consider Jive which runs thousands of implementation on replicated Postgres without any issues and has been called a much better solution than Salesforce.
On Thu, Dec 29, 2016 at 7:50 AM, John Scalia <jayknowsunix@gmail.com> wrote:
Hmmm... I suppose that would depend what your Salesforce db is. This may not be the appropriate forum for this question, as PostgreSQL could certainly support all DDL changes. With no experience for Salesforce, I'm at loss to tell you what you would need.Jay
--On Thu, Dec 29, 2016 at 10:46 AM, Ram DBA <postgresdbas@gmail.com> wrote:Hello John,Thank you for the reply. Our requirement is to copy the data from Salesforce to our local PostgreSQL database for which we had to rely on 3rd party tools. But because of dynamic nature from application, the back-end tables in Salesforce are being changed frequently thus stalling the data replication to PostgreSQL.Is there any tool that also capture and replay DDL changes from Salesforce to PostgreSQL.Hope I am clear with the details now, kindly let me know in case of further questions.Thank you in advance,RamOn Thu, Dec 29, 2016 at 8:42 PM, John Scalia <jayknowsunix@gmail.com> wrote:I'm not sure I understand what you're doing. Nearly all DDL is written to the WAL segments. The only ones that aren't are things like unlogged and temporary tables. I don't think temporary tables used by queries are written either. Why do you think, however, that this isn't captured?Jay
--On Thu, Dec 29, 2016 at 9:35 AM, Ram DBA <postgresdbas@gmail.com> wrote:Hi team,We have an use case to copy the data from Salesforce to PostgreSQL environment but because of DDL changes that can happen to tables from Salesforce application side, replication would eventually fail. Do we have any open source tools that can also capture the DDL changes and replicate to PostgreSQL?Thank you in advance,Ram
Thanks,
Jorge Torralba
----------------------------
Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.
Jorge Torralba
----------------------------
Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.
> On Dec 29, 2016, at 09:35, Ram DBA <postgresdbas@gmail.com> wrote: > > Hi team, > > We have an use case to copy the data from Salesforce to PostgreSQL environment but because of DDL changes that can happento tables from Salesforce application side, replication would eventually fail. Do we have any open source tools thatcan also capture the DDL changes and replicate to PostgreSQL? Are you using an export tool provided by salesforce, or a custom tool against their APIs? Either way, you don't have access to their database, you should be dumping the data to a CSV and importing the CSV to postgres.It's been a while since I've monkeyed with it, There may be a new way of doing this, but I doubt it. As long asyour export tool always creates a csv in the right format, you'll be fine. If you aren't ordering your columns or are using*, you'll never get a good read. Use the toon or API to create a consistent file each time in some standard format andyou'll be as okay as possible. -- Scott Mead via mobile Skype : scottm.openscg Gtalk : scottm@openscg.com > > Thank you in advance, > Ram
Hi Jorge,
Thank you for the reply. Our intention is to copy the Salesforce data to local PostgreSQL database, not the other way around. We are using SOQL api calls to get the data of tables and replicating it to Postgres.
Thank you,
Ram
On Thu, Dec 29, 2016 at 10:46 PM, Jorge Torralba <jorge.torralba@gmail.com> wrote:
Sounds like you are not using streaming replication. Can you confirm your standby has a recovery.conf file in $PGDATA ? Isn't Saleforce running with Streaming replication? If you want a proven solution running on Postgres, consider Jive which runs thousands of implementation on replicated Postgres without any issues and has been called a much better solution than Salesforce.--On Thu, Dec 29, 2016 at 7:50 AM, John Scalia <jayknowsunix@gmail.com> wrote:Hmmm... I suppose that would depend what your Salesforce db is. This may not be the appropriate forum for this question, as PostgreSQL could certainly support all DDL changes. With no experience for Salesforce, I'm at loss to tell you what you would need.Jay
--On Thu, Dec 29, 2016 at 10:46 AM, Ram DBA <postgresdbas@gmail.com> wrote:Hello John,Thank you for the reply. Our requirement is to copy the data from Salesforce to our local PostgreSQL database for which we had to rely on 3rd party tools. But because of dynamic nature from application, the back-end tables in Salesforce are being changed frequently thus stalling the data replication to PostgreSQL.Is there any tool that also capture and replay DDL changes from Salesforce to PostgreSQL.Hope I am clear with the details now, kindly let me know in case of further questions.Thank you in advance,RamOn Thu, Dec 29, 2016 at 8:42 PM, John Scalia <jayknowsunix@gmail.com> wrote:I'm not sure I understand what you're doing. Nearly all DDL is written to the WAL segments. The only ones that aren't are things like unlogged and temporary tables. I don't think temporary tables used by queries are written either. Why do you think, however, that this isn't captured?Jay
--On Thu, Dec 29, 2016 at 9:35 AM, Ram DBA <postgresdbas@gmail.com> wrote:Hi team,We have an use case to copy the data from Salesforce to PostgreSQL environment but because of DDL changes that can happen to tables from Salesforce application side, replication would eventually fail. Do we have any open source tools that can also capture the DDL changes and replicate to PostgreSQL?Thank you in advance,RamThanks,
Jorge Torralba
----------------------------
Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.
Hello Scott,
Thank you for the inputs. We are using SOQL api calls to get the data from Salesforce back-end tables and replicating it to Postgres. One challenge we face is with metadata changes to Salesforce tables. They keep dynamically changing with the business requirements. Do we have any process to capture the DDL changes and replicate it Postgres?Thank you,
Ram
On Thu, Dec 29, 2016 at 10:58 PM, Scott Mead <scottm@openscg.com> wrote:
> On Dec 29, 2016, at 09:35, Ram DBA <postgresdbas@gmail.com> wrote:
>
> Hi team,
>
> We have an use case to copy the data from Salesforce to PostgreSQL environment but because of DDL changes that can happen to tables from Salesforce application side, replication would eventually fail. Do we have any open source tools that can also capture the DDL changes and replicate to PostgreSQL?
Are you using an export tool provided by salesforce, or a custom tool against their APIs?
Either way, you don't have access to their database, you should be dumping the data to a CSV and importing the CSV to postgres. It's been a while since I've monkeyed with it, There may be a new way of doing this, but I doubt it. As long as your export tool always creates a csv in the right format, you'll be fine. If you aren't ordering your columns or are using *, you'll never get a good read. Use the toon or API to create a consistent file each time in some standard format and you'll be as okay as possible.
--
Scott Mead via mobile
Skype : scottm.openscg
Gtalk : scottm@openscg.com
>
> Thank you in advance,
> Ram