search

michaelsogos / pg-diff

PostgreSQL schema and data comparing tool
MIT License
170 stars 17 forks source link

Redshift compatibility #39

Open zeubs opened 3 years ago

zeubs commented 3 years ago

aws redshift uses a flavour of postgres and a couple of funcoitns aren't exactly the same. for instance: show server_version; actually is SELECT version(); because of this error the comparison fails instantly as I'm guessing that's pretty early in the process. please would you consider allowing us to specify redshift and alter some sys queries?

` Author: [object Object] Version: 2.2.0 PostgreSQL: 9.6+ License: MIT Description: PostgreSQL schema and data comparing tool

CONFIGURED OPTIONS Script Author: your-name-or-nickname-or-anything-else Output Directory: /home/zeubs/Downloads/pg-diff/sqlscripts Schema Namespaces: will be retrieve dynamically from database Data Compare: DISABLED

[|------------------] 10% - Connecting to source database ... error: must be superuser to examine "server_version" at Parser.parseErrorMessage (/usr/local/lib/node_modules/pg-diff-cli/node_modules/pg-protocol/dist/parser.js:287:98) at Parser.handlePacket (/usr/local/lib/node_modules/pg-diff-cli/node_modules/pg-protocol/dist/parser.js:126:29) at Parser.parse (/usr/local/lib/node_modules/pg-diff-cli/node_modules/pg-protocol/dist/parser.js:39:38) at Socket.stream.on (/usr/local/lib/node_modules/pg-diff-cli/node_modules/pg-protocol/dist/index.js:11:42) at Socket.emit (events.js:198:13) at addChunk (_stream_readable.js:288:12) at readableAddChunk (_stream_readable.js:269:11) at Socket.Readable.push (_stream_readable.js:224:10) at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17) `

michaelsogos commented 3 years ago

Dear @zeubs

For redshift support i will investigate.

Your problem however is not about different kind of command between a customized version of postgresql or not; the problem is more about ACCESS DENIED to some needed tables. This tool, in order to collect all metadata needed for comparison between databases, need a high privilege access to catalog tables. So, the __SHOW server_version;__ is just the first breaking point, but once resolved it in some other way (as you suggest) a new breaking point will be encountered going to query any pg_catalog table. That's the point! 😄

Here the potential breaking point. https://docs.aws.amazon.com/redshift/latest/dg/c_join_PG.html