search

michaelsogos / pg-diff

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

Postgresql 9.4 and below throws error due to use of regrole #15

Closed ndastur closed 4 years ago

ndastur commented 4 years ago

The regrole type appears to have been introduced in PG 9.5+

In retrieveSchema.js this query return `SELECT nspname, nspowner::regrole::name as owner FROM pg_namespace WHERE nspname IN ('${schemas.join("','")}') fails on PG <= 9.4

I note there is a TODO to not use regrole casting. Maybe this would fix the issue?

michaelsogos commented 4 years ago

Dear @ndastur ,

We don't support PG prior to 9.6 from beginning because there are more than just the issue you found to fix in order to support entire 9.x branch line.

Also, even if not always possible, you have to consider update PGSQL Server to 11.

ndastur commented 4 years ago

Thanks @michaelsogos . Just so that you understand my use case. I was moving from a 9.4 server to 11. And wanted to do the diff as part of the upgrade process.

michaelsogos commented 4 years ago

Hi @ndastur ,

Great, it will be my pleasure to help you in case you encouter problems.

I understood your use case. My suggestion is to make a backup of the 9.4 DB and restore in a temporary 11 DB. Later on you can make a pg-diff between 11 Temporary DB to 11 Real DB.

The tool DBEaver can help you on this.

NOTE: If the source db is huge, but you don't need DATA COMPARE, then you can just backup and restore schema and db objects without records.

ndastur commented 4 years ago

@michaelsogos that was exactly my problem. I needed to move a live 9.4 PG database. The entire DB back up and restore would take some time. So had planned to do that and then do a diff for the small number of record changes that occurred in the meantime. Anyway, it's sorted and you have a great tool. Just couldn't help me with this use.

michaelsogos commented 4 years ago

Hi @ndastur ,

I don't know how can i help you on this. Of course bring support to 9.4 or below is not planned; so i guess i can't help you.

Did you look at DB MIGRATION feature from DBEaver? Maybe it can help you.

ndastur commented 4 years ago

No worries @michaelsogos. It's now sorted. Did it at 3am when least number of record changes ;)